大数据场景下使用SQL求UV和PV的问题

表数据

现有user_age表如下：

user_app表如下：

需求

求：0-10岁的用户、11-20岁的用户、21-30岁的用户、30岁以上的用户使用app的人数（uv）和次数（pv）。

分析

需求乍一看不难，但是如何实现同时求出uv和pv？有的同学可能最先想到的就是uv = count(distinct uid)，pv = count(*)。在MySQL中这样写固然可以得到结果，但是在大数据场景中呢？如果是Hive中这样写发生数据倾斜了怎么处理？所以我们需要换一种思路。

• 首先可以在user_app表中，按照uid分组，这样就做到了对uid的去重，同时count(*)求出每个用户打开app的次数，记作u_pv。
• 然后根据user_age表，使用case when计算每个用户所属年龄段，记作age_range。
• 上面两张表做join，按照age_range分组，count(*)即uv（第一步user_app表已经按照uid去重），sum(u_pv)即为pv。

大数据场景优化版SQL

SELECT
    age_range,
    COUNT(*) uv,
    SUM(u_pv) pv
FROM
(
SELECT 
    uid,
    COUNT(*) u_pv
FROM user_app
GROUP BY uid
) t0
JOIN 
(
SELECT
    uid,
    (
    CASE WHEN age>0 AND age<=10 THEN '0-10岁'
    WHEN age>=11 AND age <=20 THEN '11-20岁'
    WHEN age>=21 AND age <=30 THEN '21-30岁'
    ELSE '31岁以上' END
    ) age_range
FROM
    user_age
) t1
ON t0.uid = t1.uid
GROUP BY age_range;

一般场景SQL

SELECT
    age_range,
    COUNT(DISTINCT uid) uv,
    count(*) pv
from
(
SELECT
 u.uid,age,
 (case when age>0 and age<=10 THEN '0-10岁'
  when age>=11 and age<=20 then '11-20岁'
  when age>=21 and age<=30 then '21-30岁'
  else '31岁以上' end) age_range
FROM
    user_age u
JOIN
    user_app a
ON u.uid = a.uid) t1
group by age_range;