HIVE去重及其应用之UV统计

HIVE的3种去重方法

SELECT DISTINCT user_id FROM t;

SELECT user_id FROM t GROUP BY user_id;

SELECT user_id FROM (
    SELECT
        user_id,
        ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY user_id)r
    FROM t
)t1 WHERE r=1;

第1种效率较低，第3种效率较高

场景：统计各页面每天 UV 和 PV

MySQL

SELECT
    page,
    ymd,
    COUNT(DISTINCT name) AS uv,
    COUNT(1) AS pv
FROM
    t
GROUP BY
    page,
    ymd
;

HIVE

WITH page_user_cnt as(
    SELECT
        name,
        page,
        ymd,
        COUNT(1) AS cnt
    FROM t
    GROUP BY name,page,ymd
)
SELECT
    page,
    ymd,
    COUNT(1) AS uv,
    SUM(cnt) AS pv
FROM page_user_cnt
GROUP BY page,ymd;

HIVE详细

https://blog.youkuaiyun.com/Yellow_python/article/details/119957868