如何使用SQL Presto计算日期分区维度表的每日新增ID占比？

需求说明 #

给定一张按ds（日期）分区的维度表，每日记录包含前一日全部ID及当日新增ID，需计算每日新增ID占前一日ID总数的百分比，公式为：(当日ID数量 - 前一日ID数量)/前一日ID数量 * 100%。

实现思路 #

1. 统计每日唯一ID量：按日期分组，计算每天的去重ID总数。
2. 关联前一日数据：用LAG()窗口函数获取前一日的ID总数，作为计算基准。
3. 计算并格式化占比：根据公式算出占比后，格式化为带百分号的整数形式。
4. 过滤无效记录：排除无前置数据的第一天记录。

完整SQL代码 #

WITH daily_id_count AS (
    -- 统计每日去重ID数量
    SELECT 
        ds,
        COUNT(DISTINCT id) AS id_count
    FROM dim_table
    GROUP BY ds
)
SELECT 
    ds,
    -- 计算占比并格式化为百分比
    FORMAT_NUMBER(
        (id_count - LAG(id_count) OVER (ORDER BY ds)) * 100.0 / LAG(id_count) OVER (ORDER BY ds),
        '0%'
    ) AS "% of number of id change"
FROM daily_id_count
-- 过滤无前置数据的第一天
WHERE LAG(id_count) OVER (ORDER BY ds) IS NOT NULL
ORDER BY ds;

代码解释 #

• daily_id_count 公共表表达式：按日期聚合得到每日唯一ID数量，是后续计算的基础。
• LAG(id_count) OVER (ORDER BY ds)：按日期排序，获取当前行的前一行（前一日）的ID总数。
• FORMAT_NUMBER(..., '0%')：将小数占比转换为整数百分比格式，例如0.25转为25%、1转为100%。
• WHERE 子句：剔除没有前一日数据的记录，避免无效计算。

结果验证 #

代入题目示例数据执行后，会输出期望结果：

内容的提问来源于stack exchange，提问作者ramin vafadary