Snowflake中如何获取每日、上周及去年同日的渠道登录数据？

核心思路 #

你遇到的子查询报错是因为Snowflake对某些标量子查询的支持有限，改用窗口函数或自关联JOIN可以完美解决，同时针对去年同日的需求，有两种可靠实现方式。

1. 上周同日登录量（LAG函数替代子查询） #

利用ROW_NUM是周内天数行号的特性，同一渠道下，上周同日数据就是上一周的同序号记录，用LAG函数高效获取：

LAG(LOGINS, 1) OVER (
    PARTITION BY CHANNEL 
    ORDER BY REPORTING_YEAR, REPORTING_WEEK
) AS LAST_WEEK_LOGINS

2. 去年同日登录量（两种实现方案） #

方案A：基于日期精准匹配（推荐）

直接通过DATEADD计算去年的对应日期，用自关联JOIN匹配同渠道的同日数据，适配闰年等特殊日期场景：

SELECT
    D1.REPORTING_YEAR,
    D1.ACTIVITY_DATE,
    D1.REPORTING_WEEK,
    D1.CHANNEL,
    D1.LOGINS,
    -- 上周同日数据
    LAG(D1.LOGINS, 1) OVER (
        PARTITION BY D1.CHANNEL 
        ORDER BY D1.REPORTING_YEAR, D1.REPORTING_WEEK
    ) AS LAST_WEEK_LOGINS,
    -- 去年同日数据
    D2.LOGINS AS LAST_YEAR_SAME_DAY_LOGINS
FROM DAILY_LOGINS D1
LEFT JOIN DAILY_LOGINS D2
    ON D2.CHANNEL = D1.CHANNEL
    AND D2.ACTIVITY_DATE = DATEADD(YEAR, -1, D1.ACTIVITY_DATE);

方案B：基于年/周/行号关联

如果你的周数定义（比如ISO周）每年一致，也可以通过REPORTING_YEAR-1+同周+同行号来匹配，适合依赖周维度统计的场景：

SELECT
    D1.REPORTING_YEAR,
    D1.ACTIVITY_DATE,
    D1.REPORTING_WEEK,
    D1.CHANNEL,
    D1.LOGINS,
    -- 上周同日数据
    LAG(D1.LOGINS, 1) OVER (
        PARTITION BY D1.CHANNEL 
        ORDER BY D1.REPORTING_YEAR, REPORTING_WEEK
    ) AS LAST_WEEK_LOGINS,
    -- 去年同日数据
    D2.LOGINS AS LAST_YEAR_SAME_DAY_LOGINS
FROM DAILY_LOGINS D1
LEFT JOIN DAILY_LOGINS D2
    ON D2.CHANNEL = D1.CHANNEL
    AND D2.REPORTING_YEAR = D1.REPORTING_YEAR - 1
    AND D2.REPORTING_WEEK = D1.REPORTING_WEEK
    AND D2.ROW_NUM = D1.ROW_NUM;

为什么原查询报错？ #

Snowflake对嵌套标量子查询的支持存在限制，尤其是在关联条件涉及分区排序字段时，窗口函数或JOIN是更符合Snowflake优化器的写法，性能也更优。

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