Snowflake视图开发：按租户统计当日及多时段请求收集量

完整SQL代码 #

WITH daily_stats AS (
    SELECT 
        DATE_TRUNC(day, CREATED)::date AS CREATED_DATE,
        TENANT,
        COUNT(DISTINCT CASE WHEN comment = 'Non personalised order created' THEN SIMNO END) AS NUMBER_OF_REQUESTS,
        COUNT(DISTINCT CASE WHEN comment = 'Non personalised order collected by customer' THEN SIMNO END) AS NUMBER_OF_COLLECTIONS
    FROM <DATABASE>.<SCHEMA>.<TABLE>
    GROUP BY CREATED_DATE, TENANT
)
SELECT 
    CREATED_DATE,
    TENANT,
    NUMBER_OF_REQUESTS,
    NUMBER_OF_COLLECTIONS,
    -- 近1-7日请求数（含当日，共7天）
    SUM(NUMBER_OF_REQUESTS) OVER (
        PARTITION BY TENANT 
        ORDER BY CREATED_DATE 
        RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
    ) AS NUMBER_OF_REQUESTS_1_7_DAYS_AGO,
    -- 近1-12日请求数（含当日，共12天）
    SUM(NUMBER_OF_REQUESTS) OVER (
        PARTITION BY TENANT 
        ORDER BY CREATED_DATE 
        RANGE BETWEEN INTERVAL '11 days' PRECEDING AND CURRENT ROW
    ) AS NUMBER_OF_REQUESTS_1_12_DAYS_AGO,
    -- 近1-25日请求数（含当日，共25天）
    SUM(NUMBER_OF_REQUESTS) OVER (
        PARTITION BY TENANT 
        ORDER BY CREATED_DATE 
        RANGE BETWEEN INTERVAL '24 days' PRECEDING AND CURRENT ROW
    ) AS NUMBER_OF_REQUESTS_1_25_DAYS_AGO,
    -- 近1-7日收集数
    SUM(NUMBER_OF_COLLECTIONS) OVER (
        PARTITION BY TENANT 
        ORDER BY CREATED_DATE 
        RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
    ) AS NUMBER_OF_COLLECTIONS_1_7_DAYS_AGO,
    -- 近1-12日收集数
    SUM(NUMBER_OF_COLLECTIONS) OVER (
        PARTITION BY TENANT 
        ORDER BY CREATED_DATE 
        RANGE BETWEEN INTERVAL '11 days' PRECEDING AND CURRENT ROW
    ) AS NUMBER_OF_COLLECTIONS_1_12_DAYS_AGO,
    -- 近1-25日收集数
    SUM(NUMBER_OF_COLLECTIONS) OVER (
        PARTITION BY TENANT 
        ORDER BY CREATED_DATE 
        RANGE BETWEEN INTERVAL '24 days' PRECEDING AND CURRENT ROW
    ) AS NUMBER_OF_COLLECTIONS_1_25_DAYS_AGO
FROM daily_stats
ORDER BY CREATED_DATE, TENANT;

关键说明 #

1. 每日统计预处理（CTE部分）：

   • 补全GROUP BY CREATED_DATE, TENANT，这是原基础代码缺失的必要聚合逻辑，确保按日、租户维度统计当日数据。
   • 保留原逻辑中COUNT(DISTINCT)的统计方式，保证每个SIMNO在当日仅被计数一次。

2. 多时段累计计算（窗口函数）：

   • PARTITION BY TENANT确保累计计算限定在同一租户范围内，避免跨租户统计。
   • 使用RANGE BETWEEN INTERVAL 'X days' PRECEDING AND CURRENT ROW是核心：
     • 例如近7日统计用INTERVAL '6 days' PRECEDING，表示覆盖当前日期往前6天至当日的完整7天区间，即使中间存在无数据的日期（如节假日），也能准确匹配时间范围；若用ROWS BETWEEN会按行计数，忽略日期缺失的情况，导致统计范围偏差。

3. 结果匹配预期：

   • 输出列完全对应你给出的预期结果表，包含当日统计和三个时段的累计统计字段。

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