Snowflake中如何将datetime字段按15分钟时间间隔分组统计

你可以通过分桶聚合+窗口函数直接实现需求，完整实现逻辑如下：

1. 先将带时区的kyc_updated_at字段向下对齐到15分钟刻度，作为时间分桶的唯一依据
2. 按规则生成你需要的短格式区间标签，完全匹配你给出的8-815/845-9这类格式
3. 按分桶聚合计算每个15分钟区间的平均决策时长
4. 按时间顺序滚动计算累计平均决策时长

完整可运行代码：

WITH time_bucket_base AS (
    SELECT
        -- 向下取整到最近的15分钟刻度，兼容带时区的datetime类型值
        DATE_TRUNC('MINUTE', kyc_updated_at) 
            - MOD(DATE_PART('MINUTE', kyc_updated_at), 15) * INTERVAL '1 MINUTE' AS bucket_start_time,
        decision_time -- 替换为你表中实际存储决策时长的字段名
    FROM your_kyc_table -- 替换为你的实际业务表名
    -- 可按需添加日期过滤条件，例如 WHERE kyc_updated_at >= '2022-06-01'
),
bucket_level_agg AS (
    SELECT
        bucket_start_time,
        -- 生成指定格式的区间标签
        CONCAT(
            DATE_PART('HOUR', bucket_start_time)::VARCHAR,
            -- 整0分的起始点省略分钟后缀
            REPLACE(LPAD(DATE_PART('MINUTE', bucket_start_time)::VARCHAR, 2, '0'), '00', ''),
            '-',
            DATE_PART('HOUR', DATEADD('MINUTE', 15, bucket_start_time))::VARCHAR,
            -- 整0分的结束点省略分钟后缀
            REPLACE(LPAD(DATE_PART('MINUTE', DATEADD('MINUTE', 15, bucket_start_time))::VARCHAR, 2, '0'), '00', '')
        ) AS KYC_Updated_at,
        AVG(decision_time) AS Average_decision_time
    FROM time_bucket_base
    GROUP BY bucket_start_time
)
SELECT
    KYC_Updated_at,
    ROUND(Average_decision_time, 4) AS Average_decision_time,
    -- 按时间顺序计算滚动累计平均
    ROUND(
        AVG(Average_decision_time) OVER (
            ORDER BY bucket_start_time 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ), 10
    ) AS cumulative_average_decision_time
FROM bucket_level_agg
ORDER BY bucket_start_time;

逻辑说明 #

• 标签生成规则完全匹配示例：8:00-8:15输出8-815，8:45-9:00输出845-9，不会额外补00后缀
• 原生支持你给出的带UTC时区的datetime样例值，不需要额外做时区转换
• 如果需要按原始明细数据计算累计平均（而非按分桶均值二次平均），可以把窗口函数直接写到time_bucket_base层，对decision_time字段做滚动聚合即可
• 示例表头存在拼写笔误（Avergae/cumilative为错误拼写），代码中使用了正确拼写的别名，需要完全匹配示例表头直接修改别名即可

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