如何实现按日统计过去365天的滚动唯一用户数（窗口函数COUNT(DISTINCT)替代）

你的代码存在两个核心问题：

1. 使用ROWS BETWEEN 365 PRECEDING是按行数取前365条记录，而非按日期范围筛选过去365天的数据，若存在日期缺失或单日多记录的情况，结果会偏差。
2. 多数SQL数据库不支持窗口函数中直接使用COUNT(DISTINCT)，这是导致唯一用户数计算失败的原因。

以下提供两种通用可行的解决方案：

方法一：自连接+聚合查询 #

这种方法适用于所有主流SQL数据库（MySQL、PostgreSQL、SQL Server等），逻辑清晰易理解：

SELECT
    s1.sale_date AS "Sales Date",
    -- 计算过去365天内的销售额总和
    SUM(s2.sales) AS "Trailing 365 day Sales",
    -- 统计过去365天内的唯一用户数
    COUNT(DISTINCT s2.user_id) AS "Trailing 365 day Unique User Count"
FROM (
    -- 先对每日用户记录去重，避免同一用户单日多次购买被重复统计
    SELECT DISTINCT sale_date, user_id, sales
    FROM sales_table
) s1
-- 关联自身，筛选出s1日期前365天内的所有记录
JOIN sales_table s2
    ON s2.sale_date >= s1.sale_date - INTERVAL '365 days'
    AND s2.sale_date <= s1.sale_date
GROUP BY s1.sale_date
ORDER BY s1.sale_date;

方法二：窗口函数+子查询（支持日期范围的数据库） #

如果你的数据库支持窗口函数的RANGE日期范围筛选（如PostgreSQL 11+、SQL Server 2022+），可以结合子查询优化性能：

SELECT
    sale_date AS "Sales Date",
    -- 窗口函数计算滚动销售额（按日期范围）
    SUM(daily_sales) OVER (
        ORDER BY sale_date
        RANGE BETWEEN INTERVAL '365 days' PRECEDING AND CURRENT ROW
    ) AS "Trailing 365 day Sales",
    -- 子查询统计该日期过去365天的唯一用户数
    (SELECT COUNT(DISTINCT user_id)
     FROM sales_table st2
     WHERE st2.sale_date BETWEEN st1.sale_date - INTERVAL '365 days' AND st1.sale_date) AS "Trailing 365 day Unique User Count"
FROM (
    -- 先按日期聚合每日销售额，减少后续计算量
    SELECT sale_date, SUM(sales) AS daily_sales
    FROM sales_table
    GROUP BY sale_date
) st1
ORDER BY sale_date;

补充说明 #

• 若你的数据库是MySQL，INTERVAL '365 days'需改为INTERVAL 365 DAY。
• 如果表数据量较大，建议给sale_date字段建立索引，提升关联查询的性能。

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