如何在Snowflake中精准聚合SQL财年至今（FYTD）累计值？

现有产品销售数据维度包含日期、设备组、用户国家、财年、财周、产品组，当前计算FYTD累计值存在以下问题：

• 用Over Partition By计算的FYTD累计值，在按财年/财周汇总时，因重复累加每日FYTD值导致结果高估
• 采用dense_rank的方案仅在所有维度全量存在时有效，若前期财周有数据的产品在后期财周无数据，FYTD值会出现拆分错误，无法得到截至后期财周的真实累计值
• 需要实现可扩展至多财年、多财周场景的精准FYTD求和方案，确保取到最新粒度的真实累计值

核心思路：先按最细维度（财年、设备、国家、产品组、财周）聚合数据并计算FYTD，再通过生成财年-财周的完整序列，将每个维度组合的最新FYTD值关联到后续所有财周，最后按财年-财周汇总，确保即使产品后期无数据，也能携带最新累计值。

优化后的SQL代码：

with rawdata as (
    select * from
        values
            ('2022-10-01', 2022, 1, 'Desktop', 'UK', 'Shoes', 1),
            ('2022-10-01', 2022, 1, 'Desktop', 'UK', 'Flip Flops', 1),
            ('2022-10-01', 2022, 1, 'Desktop', 'UK', 'Sunglasses', 5),
            ('2022-10-01', 2022, 1, 'Mobile', 'UK', 'Shoes', 2),
            ('2022-10-01', 2022, 1, 'Tablet', 'UK', 'Shoes', 1),
            ('2022-10-02', 2022, 1, 'Desktop', 'UK', 'Shoes', 1),
            ('2022-10-02', 2022, 1, 'Mobile', 'UK', 'Shoes', 2),
            ('2022-10-02', 2022, 1, 'Tablet', 'UK', 'Shoes', 4),
            ('2022-10-03', 2022, 1, 'Desktop', 'UK', 'Shoes', 1),
            ('2022-10-03', 2022, 1, 'Mobile', 'UK', 'Shoes', 2),
            ('2022-10-03', 2022, 1, 'Tablet', 'UK', 'Shoes', 5),
            ('2022-10-01', 2022, 1, 'Desktop', 'UK', 'Socks', 1),
            ('2022-10-01', 2022, 1, 'Mobile', 'UK', 'Socks', 2),
            ('2022-10-01', 2022, 1, 'Tablet', 'UK', 'Socks', 1),
            ('2022-10-02', 2022, 1, 'Desktop', 'UK', 'Socks', 1),
            ('2022-10-02', 2022, 1, 'Mobile', 'UK', 'Socks', 2),
            ('2022-10-02', 2022, 1, 'Tablet', 'UK', 'Socks', 4),
            ('2022-10-03', 2022, 1, 'Desktop', 'UK', 'Socks', 1),
            ('2022-10-03', 2022, 1, 'Mobile', 'UK', 'Socks', 2),
            ('2022-10-03', 2022, 1, 'Tablet', 'UK', 'Socks', 5),
            ('2022-10-08', 2022, 2, 'Desktop', 'UK', 'Shoes', 7),
            ('2022-10-08', 2022, 2, 'Mobile', 'UK', 'Shoes', 8),
            ('2022-10-08', 2022, 2, 'Tablet', 'UK', 'Shoes', 4),
            ('2022-10-09', 2022, 2, 'Desktop', 'UK', 'Shoes', 6),
            ('2022-10-09', 2022, 2, 'Mobile', 'UK', 'Shoes', 2),
            ('2022-10-09', 2022, 2, 'Tablet', 'UK', 'Shoes', 8),
            ('2022-10-10', 2022, 2, 'Desktop', 'UK', 'Shoes', 12),
            ('2022-10-10', 2022, 2, 'Mobile', 'UK', 'Shoes', 22),
            ('2022-10-10', 2022, 2, 'Tablet', 'UK', 'Shoes', 5),
            ('2022-10-08', 2022, 2, 'Desktop', 'UK', 'Socks', 4),
            ('2022-10-08', 2022, 2, 'Mobile', 'UK', 'Socks', 1),
            ('2022-10-08', 2022, 2, 'Tablet', 'UK', 'Socks', 2),
            ('2022-10-09', 2022, 2, 'Desktop', 'UK', 'Socks', 3),
            ('2022-10-09', 2022, 2, 'Mobile', 'UK', 'Socks', 8),
            ('2022-10-09', 2022, 2, 'Tablet', 'UK', 'Socks', 9),
            ('2022-10-10', 2022, 2, 'Desktop', 'UK', 'Socks', 5),
            ('2022-10-10', 2022, 2, 'Mobile', 'UK', 'Socks', 4),
            ('2022-10-10', 2022, 2, 'Tablet', 'UK', 'Socks', 13),
            ('2022-10-01', 2023, 1, 'Desktop', 'UK', 'Shoes', 1),
            ('2022-10-01', 2023, 1, 'Mobile', 'UK', 'Shoes', 2),
            ('2022-10-01', 2023, 1, 'Tablet', 'UK', 'Shoes', 1),
            ('2022-10-02', 2023, 1, 'Desktop', 'UK', 'Shoes', 1),
            ('2022-10-02', 2023, 1, 'Mobile', 'UK', 'Shoes', 2),
            ('2022-10-02', 2023, 1, 'Tablet', 'UK', 'Shoes', 4),
            ('2022-10-03', 2023, 1, 'Desktop', 'UK', 'Shoes', 1),
            ('2022-10-03', 2023, 1, 'Mobile', 'UK', 'Shoes', 2),
            ('2022-10-03', 2023, 1, 'Tablet', 'UK', 'Shoes', 5),
            ('2022-10-01', 2023, 1, 'Desktop', 'UK', 'Socks', 1),
            ('2022-10-01', 2023, 1, 'Mobile', 'UK', 'Socks', 2),
            ('2022-10-01', 2023, 1, 'Tablet', 'UK', 'Socks', 1),
            ('2022-10-02', 2023, 1, 'Desktop', 'UK', 'Socks', 1),
            ('2022-10-02', 2023, 1, 'Mobile', 'UK', 'Socks', 2),
            ('2022-10-02', 2023, 1, 'Tablet', 'UK', 'Socks', 4),
            ('2022-10-03', 2023, 1, 'Desktop', 'UK', 'Socks', 1),
            ('2022-10-03', 2023, 1, 'Mobile', 'UK', 'Socks', 2),
            ('2022-10-03', 2023, 1, 'Tablet', 'UK', 'Socks', 5),
            ('2022-10-08', 2023, 2, 'Desktop', 'UK', 'Shoes', 7),
            ('2022-10-08', 2023, 2, 'Mobile', 'UK', 'Shoes', 8),
            ('2022-10-08', 2023, 2, 'Tablet', 'UK', 'Shoes', 4),
            ('2022-10-09', 2023, 2, 'Desktop', 'UK', 'Shoes', 6),
            ('2022-10-09', 2023, 2, 'Mobile', 'UK', 'Shoes', 2),
            ('2022-10-09', 2023, 2, 'Tablet', 'UK', 'Shoes', 8),
            ('2022-10-10', 2023, 2, 'Desktop', 'UK', 'Shoes', 12),
            ('2022-10-10', 2023, 2, 'Mobile', 'UK', 'Shoes', 22),
            ('2022-10-10', 2023, 2, 'Tablet', 'UK', 'Shoes', 5),
            ('2022-10-08', 2023, 2, 'Desktop', 'UK', 'Socks', 4),
            ('2022-10-08', 2023, 2, 'Mobile', 'UK', 'Socks', 1),
            ('2022-10-08', 2023, 2, 'Tablet', 'UK', 'Socks', 2),
            ('2022-10-09', 2023, 2, 'Desktop', 'UK', 'Socks', 3),
            ('2022-10-10', 2023, 2, 'Desktop', 'UK', 'Socks', 5),
            ('2022-10-10', 2023, 2, 'Mobile', 'UK', 'Socks', 4),
            ('2022-10-10', 2023, 2, 'Tablet', 'UK', 'Socks', 13)
         as a (date, fiscalyearno, fiscalweekno, devicegroup, usercountry, productgroup, bookings)
),
-- 按财年、设备、国家、产品组、财周聚合数据
weekly_agg as (
    select
        fiscalyearno,
        fiscalweekno,
        devicegroup,
        usercountry,
        productgroup,
        sum(bookings) as weekly_bookings
    from rawdata
    group by fiscalyearno, fiscalweekno, devicegroup, usercountry, productgroup
),
-- 计算每个维度组合的FYTD累计值
fytd_calc as (
    select
        fiscalyearno,
        fiscalweekno,
        devicegroup,
        usercountry,
        productgroup,
        weekly_bookings,
        sum(weekly_bookings) over (
            partition by fiscalyearno, devicegroup, usercountry, productgroup
            order by fiscalweekno asc
            rows between unbounded preceding and current row
        ) as fytd_bookings
    from weekly_agg
),
-- 生成所有财年-财周的完整序列
all_fy_weeks as (
    select distinct fiscalyearno, fiscalweekno
    from rawdata
    order by fiscalyearno, fiscalweekno
),
-- 生成所有维度组合（财年、设备、国家、产品组）
all_dimensions as (
    select distinct fiscalyearno, devicegroup, usercountry, productgroup
    from rawdata
),
-- 关联所有维度组合与所有财年-财周，确保每个维度组合对应所有后续财周
full_dim_week as (
    select
        d.fiscalyearno,
        w.fiscalweekno,
        d.devicegroup,
        d.usercountry,
        d.productgroup
    from all_dimensions d
    cross join all_fy_weeks w
    where w.fiscalyearno = d.fiscalyearno
),
-- 关联FYTD计算结果，填充每个维度组合在对应财周及之后的最新FYTD值
final_fytd as (
    select
        f.fiscalyearno,
        f.fiscalweekno,
        f.devicegroup,
        f.usercountry,
        f.productgroup,
        -- 取当前财周及之前的最大FYTD值，确保后期无数据时携带最新累计
        max(c.fytd_bookings) over (
            partition by f.fiscalyearno, f.devicegroup, f.usercountry, f.productgroup
            order by f.fiscalweekno asc
            rows between unbounded preceding and current row
        ) as latest_fytd_bookings,
        nvl(c.weekly_bookings, 0) as weekly_bookings
    from full_dim_week f
    left join fytd_calc c
        on f.fiscalyearno = c.fiscalyearno
        and f.fiscalweekno = c.fiscalweekno
        and f.devicegroup = c.devicegroup
        and f.usercountry = c.usercountry
        and f.productgroup = c.productgroup
)
-- 按财年-财周汇总最终结果
select
    fiscalyearno,
    fiscalweekno,
    sum(weekly_bookings) as totalbookings,
    sum(latest_fytd_bookings) as fytdbookings
from final_fytd
group by fiscalyearno, fiscalweekno
order by fiscalyearno, fiscalweekno;

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