提示
Hive SQL 教程 欢迎使用。提供建议、纠错、催更等加作者微信: gairuo123(备注:sql )和关注公众号「盖若」ID: gairuo。跟作者学习,请进入 Python学习课程。欢迎关注作者出版的书籍:《深入浅出Pandas》 和 《Python之光》。
本例是业务运营人员想知道公司运营的 APP 的版本(APP 每个开发周期会向应该市场发布一个版本)在当前所有用户手机上各占比是多少。这个需求的背景是每次有新版本上线,新的版本占比会有一个上升的过程,其他版本不能在短时间时消失,有些版本会长期存在。在运营方面,需要此类数据作为支撑来判断接下来要执行的运营策略。
数据需求:
确定数据结构:
指标维度定义:
根据以上我们需要的数据,我们找到了 dwd.dwd_app_active
数据表,它用来记录用户在 APP 上的活跃情况,主要涉及以下字段:
以上字段可以满足我们编写以上数据需求的 SQL。
最终 SQL 代码如下:
SELECT dt AS day,
app_ver AS ver,
count(DISTINCT device_id ) AS uv,
ROW_NUMBER() over(PARTITION BY dt ORDER BY count(DISTINCT device_id) DESC) AS day_rank
sum(count(DISTINCT device_id)) over(PARTITION BY dt) AS dt_uv,
count(DISTINCT device_id)/sum(count(DISTINCT device_id)) over(PARTITION BY dt) AS percentage
FROM dwd.dwd_app_active
WHERE dt >= 20220101
GROUP BY dt,
app_ver
ORDER BY uv DESC
我们没有采用分层嵌套的方法编写 SQL,而是用了几个窗口函数来实现。具体指标计算逻辑为(以 as 后的别名):
这样,我们就完成了需求。
注:以上 SQL 在 Hive 引擎上测试通过。
(完)
更新时间:2022-01-27 16:05:10 标签:sql 窗口函数 占比