Presto SQL按动态日期范围统计去重活跃ID的技术问询

现有数据表包含month、date、id字段，若某ID在对应日期的表中存在，则判定为活跃用户。需要用Presto SQL实现：针对每个日期，统计当月起始日至该日期的去重活跃ID数量，也就是动态范围的COUNT DISTINCT统计。

曾尝试用DENSE_RANK函数，但因为COUNT无法结合分区逻辑，没成功。

期望输出示例：

Grass_Month | Grass_date  | active_users
2023-06-01  | 2023-06-01  | 234 -- 6月1日的活跃独立用户数
2023-06-01  | 2023-06-02  | 483 -- 6月1日至6月2日的累计活跃独立用户数

可以用两种方式实现需求，分别适配不同版本的Presto：

方法一：窗口函数实现（Presto 312+版本适用） #

Presto 312及以上版本支持窗口内的COUNT(DISTINCT)，直接利用窗口范围限定当月到当前日期的区间即可：

SELECT
    DATE_TRUNC('month', date) AS Grass_Month,
    date AS Grass_date,
    COUNT(DISTINCT id) OVER (
        PARTITION BY DATE_TRUNC('month', date)
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS active_users
FROM your_table
WHERE date >= '2023-06-01' AND date <= '2023-06-14' -- 指定目标日期范围
GROUP BY date, id -- 先去重每日的ID，避免同一日期重复统计同一个用户
ORDER BY date;

逻辑说明 #

1. GROUP BY date, id确保每个日期下的ID唯一，避免单日重复计数。
2. PARTITION BY DATE_TRUNC('month', date)按月份分区，保证只统计当月内的数据。
3. 窗口范围ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW覆盖当月第一天到当前日期的所有行，COUNT(DISTINCT id)直接统计该范围内的独立用户数。

方法二：日期序列关联（兼容低版本Presto） #

如果你的Presto版本不支持窗口内的COUNT(DISTINCT)，可以通过生成日期序列+关联统计的方式实现：

WITH date_series AS (
    SELECT
        DATE_TRUNC('month', date) AS Grass_Month,
        date AS Grass_date
    FROM your_table
    WHERE date >= '2023-06-01' AND date <= '2023-06-14'
    GROUP BY date
),
user_first_active AS (
    SELECT
        DATE_TRUNC('month', date) AS user_month,
        id,
        MIN(date) AS first_active_date -- 记录用户当月首次活跃日期
    FROM your_table
    WHERE date >= '2023-06-01' AND date <= '2023-06-14'
    GROUP BY user_month, id
)
SELECT
    ds.Grass_Month,
    ds.Grass_date,
    COUNT(ud.id) AS active_users
FROM date_series ds
LEFT JOIN user_first_active ud
    ON ds.Grass_Month = ud.user_month
    AND ud.first_active_date <= ds.Grass_date
GROUP BY ds.Grass_Month, ds.Grass_date
ORDER BY ds.Grass_date;

逻辑说明 #

1. date_series生成指定范围内的所有日期，确保每个日期都出现在结果中。
2. user_first_active统计每个用户当月的首次活跃日期，避免同一用户被重复计数。
3. 关联两个CTE，统计所有首次活跃日期早于等于当前日期的用户数，即为当月到当前日期的累计活跃用户数。

内容的提问来源于stack exchange，提问作者Jolo Cutaran