Redshift同一SELECT查询实现多日期粒度聚合的问题求助

问题背景 #

你需要在Redshift中同时统计日粒度和月粒度的去重客户数，且月度统计需确保同一客户当月多次访问仅计数一次。原分组查询仅能得到日数据，直接求和会重复计算；尝试用窗口函数COUNT(DISTINCT)时因Redshift引擎限制报错：

[XX000] ERROR: WINDOW definition is not supported

解决方案 #

采用UNION ALL分别计算月度、日度的去重客户数，再合并结果集，同时通过排序让月度数据优先展示：

SELECT *
FROM (
    -- 月度去重客户统计：同一客户当月仅计一次
    SELECT 
        new_client,
        COUNT(DISTINCT clientId) AS number_clients,
        NULL AS date_day,
        DATE_TRUNC('month', _date) AS date_month,
        1 AS sort_order
    FROM ClientsTable
    GROUP BY new_client, DATE_TRUNC('month', _date)
    
    UNION ALL
    
    -- 日度去重客户统计：同一客户当日仅计一次
    SELECT 
        new_client,
        COUNT(DISTINCT clientId) AS number_clients,
        DATE_TRUNC('day', _date) AS date_day,
        DATE_TRUNC('month', _date) AS date_month,
        2 AS sort_order
    FROM ClientsTable
    GROUP BY new_client, DATE_TRUNC('day', _date), DATE_TRUNC('month', _date)
) combined
ORDER BY date_month, sort_order, new_client, date_day;

说明 #

1. 月度统计部分：按new_client和月份分组，用COUNT(DISTINCT clientId)得到当月每个new_client分组的唯一客户数，date_day设为NULL区分月度数据。
2. 日度统计部分：复用你原有的分组逻辑，按new_client、日期天和月份分组，得到每日的去重客户数。
3. 排序逻辑：通过sort_order字段让月度数据排在对应月份日度数据的前面，最终结果符合你期望的格式。
4. 窗口函数报错原因：Redshift不支持在窗口函数中使用COUNT(DISTINCT)，这是引擎的固有限制，因此采用分粒度聚合再合并的方案更可靠。

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