SQL 窗口函数统计每日 APP 版本占比

提示

Hive SQL 教程欢迎使用。提供建议、纠错、催更等加作者微信: gairuo123（备注：sql ）和关注公众号「盖若」ID: gairuo。跟作者学习，请进入 Python学习课程。欢迎关注作者出版的书籍：《深入浅出Pandas》和《Python之光》。

本例是业务运营人员想知道公司运营的 APP 的版本（APP 每个开发周期会向应该市场发布一个版本）在当前所有用户手机上各占比是多少。这个需求的背景是每次有新版本上线，新的版本占比会有一个上升的过程，其他版本不能在短时间时消失，有些版本会长期存在。在运营方面，需要此类数据作为支撑来判断接下来要执行的运营策略。

需求分析

数据需求：

核心需求：查看每天每个版本趋势
按天、按版本统计

确定数据结构：

[日期版本] 版本uv 日总UV 占比排名
期中方括号里的是维度

指标维度定义：

范围：2022年开始的所有日期
维度：日期、版本
指标：
- 版本 UV，当日当前版本的 UV 数，所有设备的去重数
- 日总 UV，当日不区分版本的所有 UV，用于做对比，计算占比时为分母
- 占比：当前版本在当时的占比
- 排名：当前版本在当日的排名，占比越大排名越高

根据以上我们需要的数据，我们找到了 dwd.dwd_app_active 数据表，它用来记录用户在 APP 上的活跃情况，主要涉及以下字段：

dt：日期
app_ver：版本
device_id：设备 ID

以上字段可以满足我们编写以上数据需求的 SQL。

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 后的别名）：

uv：当日本版本的 UV，先 DISTINCT 去重，然后用 count 统计；
day_rank：当日本版本的排名，窗口函数，按 dt 日期分组，再按当日本版本的 UV 降序排序，用 ROW_NUMBER() 返回序号；
dt_uv：当日总 UV，，窗口函数，按 dt 日期分组，函数使用 sum 对去重设备总数加和；
percentage：本版本在当日的 UV 占比，按 dt 日期分组，uv/dt_uv。

这样，我们就完成了需求。

注：以上 SQL 在 Hive 引擎上测试通过。

（完）

SQL 窗口函数统计每日 APP 版本占比

需求分析

SQL 代码

相关内容