如何不通过GROUP BY聚合丢行计算DAU及滚动30天MAU

绝大多数SQL引擎的count(distinct)窗口函数仅支持分区级全量计算，不支持搭配自定义滑动窗口范围（rows/range between）做去重计数，这也是你能直接用窗口函数算出单日DAU，但无法用同款逻辑实现滚动30天MAU的根本原因。
下面给两种可落地的实现方式：

方案1：聚合回表关联（全引擎兼容，数值100%准确） #

这个方案复用你已经验证过正确的聚合计算逻辑，先在日期粒度算出准确的DAU、滚动MAU，再关联回原始明细表，既保留所有明细字段，计算结果也和你之前的聚合输出完全一致。

with daily_metric as (
    -- 这部分完全复用你已验证正确的DAU/MAU计算逻辑
    with dau as (
        select 
            to_date("EventTime") as event_date, 
            count(distinct "UserKey") as dau
        from table
        group by event_date
    )
    select 
        event_date,
        dau,
        (
            select count(distinct "UserKey") 
            from table
            where to_date("EventTime") between dateadd(day, -29, dau.event_date) and dau.event_date
        ) as mau
    from dau
)
-- 关联回原始明细表，保留全部明细行
select 
    origin.*,
    to_date(origin."EventTime") as event_date,
    metric.dau,
    metric.mau
from table origin
left join daily_metric metric
on to_date(origin."EventTime") = metric.event_date;

这个方案的优势：

• 计算逻辑经过验证，不会出现去重计数错误
• 无语法依赖，MySQL、Hive、Spark、ClickHouse、Presto等所有支持SQL的引擎都可以运行
• 性能远高于直接在明细层做滑动窗口去重：聚合计算仅在日期粒度执行1次，不需要对全量明细行重复做30天范围的用户去重

方案2：原生滑动窗口写法（仅部分新版引擎支持） #

如果你使用的是Spark 3.1+、BigQuery、Snowflake等新版数仓引擎，且引擎支持count(distinct)搭配滑动窗口语法，可以直接在明细层计算，不需要额外关联：

select 
    *,
    to_date("EventTime") as event_date,
    count(distinct "UserKey") over (partition by to_date("EventTime")) as dau,
    count(distinct "UserKey") over (
        order by to_date("EventTime")
        range between interval 29 day preceding and current row
    ) as mau
from table;

注意：使用前请先用小批量数据验证语法兼容性，大部分旧版本引擎运行这段代码会报语法错误。如果业务允许1%以内的计数误差，可以把count(distinct)替换为引擎自带的近似去重函数（比如approx_count_distinct、uniq、HLL_COUNT.MERGE），计算性能会有数量级提升。

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