SQL累计计数查询返回行数超预期：计算月累计值出现重复行问题

基础表结构 #

base_table #

存储账户每月的业务指标数据：

eom         account_id      closings  checkouts
    2018-11-01       1              21       147
    2018-12-01       1              20       214

calendar_table #

存储账户对应的活跃月份：

month       account_id
2020-11-01       1
2014-04-01       1

需求说明 #

基于两张表计算每个账户逐月的closings和checkouts累计值，以calendar_table作为主表保证所有活跃月份都被覆盖。

原查询问题现象 #

原SQL查询返回重复行，同一账户同一月份存在多条数据，累计值被重复累加，实际输出示例：

month     account_id closings cum_closings checkouts cum_checkouts
01/11/17      1         20          20         282         282
01/11/17      1         20          40         282         564
01/11/17      1         20          60         282         846
01/12/17      1         17          77         346         1192
01/12/17      1         17          94         346         1538
01/12/17      1         17          111        346         1884

预期输出要求每个账户每个月仅返回1条数据，累计值计算正确，示例如下：

month     account_id closings cum_closings checkouts cum_checkouts
    01/11/17      1         20          20         282         282
    01/12/17      1         17          37         346         628

问题根因 #

• 冗余的CROSS JOIN操作是核心问题：calendar_table本身已经生成了month + account_id的唯一活跃组合，额外交叉关联base_table的去重账户列表，会让每个活跃月份的行被复制N次（N等于base_table中不同账户的数量），直接导致重复行。
• 窗口函数基于重复后的数据集计算，当月数值被重复累加多次，最终累计值异常。

修正后SQL #

直接使用calendar_table的month + account_id组合作为主表，去掉冗余交叉连接，逻辑如下：

with 
base_table as (
    select eom, account_id, closings, checkouts 
    from base_table bt 
    where account_id in (3,30,122,152,161,179)
),
calendar_table as (
    select ct.month, c.external_id as account_id
    from calendar_table ct 
    left join customers c
        on c.id = ct.organization_id 
    where account_id in (3,30,122,152,161,179)
),
cumulative_table as (
    select  
        ct."month"
        ,ct.account_id
        ,coalesce(bt.closings,0) as closings
        ,coalesce(sum(coalesce(bt.closings,0)) OVER (
            PARTITION BY ct.account_id
            ORDER BY ct."month"
            rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ),0) as cum_closings
        ,coalesce(bt.checkouts,0) as checkouts
        ,coalesce(sum(coalesce(bt.checkouts,0)) OVER (
            PARTITION BY ct.account_id
            ORDER BY ct."month"
            rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ),0) AS cum_checkouts
    from calendar_table ct 
    left join base_table bt
        on bt.account_id = ct.account_id and bt.eom = ct.month
)
select * 
from cumulative_table

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