如何在Firebolt中用SQL计算25天滚动独立用户数？

核心思路 #

要实现每日滚动统计过去25天的独立用户数，核心是为每个统计日期划定包含当天在内的过去25天时间范围，再对范围内的user_id去重计数。Firebolt支持标准SQL语法，可通过两种高效方式实现：

方法一：日期序列关联法（适合需完整日期序列场景） #

如果需要输出所有连续日期（即使某天无用户数据），可以先生成完整的日期序列，再关联用户数据计算：

WITH date_series AS (
    -- 生成从数据最早日期到最晚日期的每日序列
    SELECT date_trunc('day', generate_series(
        (SELECT MIN(date) FROM your_table),
        (SELECT MAX(date) FROM your_table),
        INTERVAL '1 day'
    )) AS report_date
),
-- 先对用户和日期去重，避免同一用户同一天多条记录干扰计数
unique_user_dates AS (
    SELECT DISTINCT 
        date, 
        user_id
    FROM your_table
    -- 可选：如果只统计有产品使用行为的用户，添加过滤条件
    -- WHERE product_usage = 1
)
SELECT 
    ds.report_date,
    -- 对过去25天内的用户去重计数
    COUNT(DISTINCT uud.user_id) AS rolling_25d_unique_users
FROM date_series ds
LEFT JOIN unique_user_dates uud
    -- 划定范围：当天往前推24天（含当天共25天）
    ON uud.date >= ds.report_date - INTERVAL '24 days'
    AND uud.date <= ds.report_date
GROUP BY ds.report_date
ORDER BY ds.report_date;

方法二：窗口函数法（性能更优） #

利用Firebolt支持的窗口函数，直接在用户数据上计算滚动范围的去重用户数：

WITH unique_user_dates AS (
    SELECT DISTINCT 
        date, 
        user_id,
        -- 将日期转换为从epoch开始的天数，用于窗口范围计算
        EXTRACT(EPOCH FROM date) / 86400 AS day_num
    FROM your_table
    -- 可选：过滤有产品使用行为的用户
    -- WHERE product_usage = 1
)
SELECT 
    date,
    -- 滚动统计过去25天（含当天）的独立用户数
    COUNT(DISTINCT user_id) OVER (
        ORDER BY day_num
        RANGE BETWEEN 24 PRECEDING AND CURRENT ROW
    ) AS rolling_25d_unique_users
FROM unique_user_dates
GROUP BY date, day_num
ORDER BY date;

关键注意事项 #

1. 时间范围界定：过去25天包含统计当天，因此范围是report_date - 24天到report_date，而非减25天。
2. 去重前置处理：必须先对date和user_id去重，否则同一用户同一天的多条记录会导致重复计数。
3. 性能优化：如果数据量极大，可将COUNT(DISTINCT)替换为Firebolt的近似计数函数APPROX_COUNT_DISTINCT，在可接受的误差范围内大幅提升查询速度。
4. 过滤规则：根据业务需求决定是否保留product_usage = 1的过滤条件——如果统计的是所有接触产品的用户（包括未使用的），则去掉该条件。

测试数据示例结果 #

以你提供的测试数据为例，若启用product_usage = 1过滤：

若不启用过滤：

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