Snowflake中账户不匹配场景下收入同比(YoY)计算问题求解

问题根因 #

你当前使用当期数据左连上年同期数据的逻辑，只会保留当期有数据的账户记录，上年存在、当期无数据的账户对应的上年ARR不会被纳入结果，因此yoy_arr的汇总值会低于实际值。

优化方案 #

方案1：保留账户维度明细，用FULL OUTER JOIN关联 #

采用全外连接可以同时保留当期有数据、上年有数据的所有账户记录，对齐到当期日期维度，代码如下：

WITH CTE AS(
    SELECT account_id, activity_date, arr
    FROM arr_base
)
SELECT 
    c1.account_id,
    -- 统一日期为当期统计日期
    COALESCE(c1.activity_date, DATEADD(year, 1, c2.activity_date)) AS activity_date,
    c1.arr,
    c2.arr AS yoy_arr
FROM CTE c1
FULL OUTER JOIN CTE c2 
    ON c1.account_id = c2.account_id 
    AND c2.activity_date = DATEADD(year, -1, c1.activity_date)
-- 可按需添加日期过滤，比如只看2021年的数据
-- WHERE COALESCE(c1.activity_date, DATEADD(year, 1, c2.activity_date)) >= '2021-01-01'::DATE

运行该代码对应你的示例数据，会得到4条账户级明细：A、B的记录和你现有结果一致，额外多出C、D两条记录，对应account_id为C、D，arr为NULL，yoy_arr分别为30、30，所有yoy_arr加总就是上年同期完整的总ARR 135，不会漏算。
如果需要得到你给出的期望结果（将当期无数据的上年账户ARR合并为汇总行），可以在上述结果基础上补充聚合逻辑：

WITH CTE AS(
    SELECT account_id, activity_date, arr
    FROM arr_base
),
account_level AS (
    SELECT 
        c1.account_id,
        COALESCE(c1.activity_date, DATEADD(year, 1, c2.activity_date)) AS activity_date,
        c1.arr,
        c2.arr AS yoy_arr
    FROM CTE c1
    FULL OUTER JOIN CTE c2 
        ON c1.account_id = c2.account_id 
        AND c2.activity_date = DATEADD(year, -1, c1.activity_date)
    WHERE COALESCE(c1.activity_date, DATEADD(year, 1, c2.activity_date)) = '2021-01-31'::DATE
)
SELECT * FROM account_level WHERE account_id IS NOT NULL
UNION ALL
-- 汇总当期无匹配的上年账户ARR
SELECT NULL, activity_date, NULL, SUM(yoy_arr) 
FROM account_level 
WHERE account_id IS NULL
GROUP BY activity_date

方案2：仅需按天维度统计同比总和，先聚合再关联 #

如果不需要账户维度的明细，直接按日期维度聚合后再关联，性能更高且结果直接符合统计需求，代码如下：

WITH daily_arr_agg AS (
    SELECT 
        activity_date,
        SUM(arr) AS total_arr
    FROM arr_base
    GROUP BY activity_date
)
SELECT 
    curr.activity_date,
    curr.total_arr AS current_total_arr,
    prev.total_arr AS yoy_total_arr
FROM daily_arr_agg curr
LEFT JOIN daily_arr_agg prev 
    ON prev.activity_date = DATEADD(year, -1, curr.activity_date)

对应你的示例数据，2021年1月31日的yoy_total_arr会直接返回135，是上年同期的完整总和。

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