Snowflake中遍历SEG_HISTO日期计算消费并生成堆叠视图的技术问询

现有Snowflake环境下两张表：

• SEG_HISTO：每月生成的分段表，字段包含Client ID、date（每月1日）、segment
• TCK：工单表，字段包含Ticket ID、Customer ID、Date、Amount

已写出手动指定日期的SQL，为SEG_OMNI表中每个客户计算过去一年的消费总额，但现在需要遍历SEG_HISTO中的所有唯一日期（通过SELECT DISTINCT TO_DATE(DT_MAJ) DT FROM "SHARE"."DATAMARTS_DATASCIENCE"."SEG_HISTO"获取），每次执行计算并将结果合并到视图中，遇到实现阻碍。

原手动SQL：

SELECT SEG_OMNI.*, TCK_12M.TOTAL_AMOUNT_HT
FROM "SHARE"."DATAMARTS_DATASCIENCE"."SEG_OMNI" SEG_OMNI
LEFT OUTER JOIN
(
SELECT DISTINCT PR_ID_BU,
    SUM(TOTAL_AMOUNT_HT) AS "TOTAL_AMOUNT_HT",
    COUNT(*) "NB_ACHAT"
    
    FROM 
        (
        SELECT * FROM "SHARE"."RAW_BDC"."TCK"
        WHERE TO_DATE(DT_SALE) >= DATEADD(YEAR, -1, '2022-07-01') -- 手动指定日期
        )
    GROUP BY PR_ID_BU
) TCK_12M
ON SEG_OMNI."pr_id_bu" = TCK_12M.PR_ID_BU

Snowflake是分布式列式数据库，逐行循环效率极低，推荐用集合式查询直接实现，以下是两种可行方案：

方法1：关联唯一日期，批量计算各日期的客户消费统计 #

直接将SEG_HISTO的唯一日期与TCK关联，一次性计算每个客户在每个参考日期的过去一年累计值，再关联SEG_OMNI生成视图：

CREATE OR REPLACE VIEW "SHARE"."DATAMARTS_DATASCIENCE"."SEG_OMNI_HISTORICAL" AS
WITH unique_dates AS (
    SELECT DISTINCT TO_DATE(DT_MAJ) AS ref_date
    FROM "SHARE"."DATAMARTS_DATASCIENCE"."SEG_HISTO"
),
customer_12m_stats AS (
    SELECT 
        ud.ref_date,
        t.PR_ID_BU,
        SUM(t.TOTAL_AMOUNT_HT) AS TOTAL_AMOUNT_HT,
        COUNT(*) AS NB_ACHAT
    FROM unique_dates ud
    LEFT JOIN "SHARE"."RAW_BDC"."TCK" t 
        ON TO_DATE(t.DT_SALE) >= DATEADD(YEAR, -1, ud.ref_date)
    GROUP BY ud.ref_date, t.PR_ID_BU
)
SELECT 
    so.*,
    c12m.ref_date,
    c12m.TOTAL_AMOUNT_HT,
    c12m.NB_ACHAT
FROM "SHARE"."DATAMARTS_DATASCIENCE"."SEG_OMNI" so
LEFT JOIN customer_12m_stats c12m 
    ON so.pr_id_bu = c12m.PR_ID_BU;

方法2：匹配SEG_HISTO的客户月度分段记录 #

如果需要关联SEG_HISTO中每个客户每月的分段信息，再匹配对应日期的过去一年消费：

CREATE OR REPLACE VIEW "SHARE"."DATAMARTS_DATASCIENCE"."SEG_HISTO_WITH_CONSUMPTION" AS
SELECT 
    sh.*,
    SUM(t.TOTAL_AMOUNT_HT) OVER (PARTITION BY sh."Client ID", sh.DT_MAJ) AS TOTAL_AMOUNT_HT,
    COUNT(t.Ticket ID) OVER (PARTITION BY sh."Client ID", sh.DT_MAJ) AS NB_ACHAT
FROM "SHARE"."DATAMARTS_DATASCIENCE"."SEG_HISTO" sh
LEFT JOIN "SHARE"."RAW_BDC"."TCK" t 
    ON sh."Client ID" = t.PR_ID_BU
    AND TO_DATE(t.DT_SALE) >= DATEADD(YEAR, -1, sh.DT_MAJ);

特殊场景：用存储过程实现循环（不推荐） #

如果业务逻辑必须用循环实现，可通过Snowflake存储过程+游标完成，但性能远低于集合式查询：

CREATE OR REPLACE PROCEDURE generate_historical_seg_data()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    date_cursor CURSOR FOR SELECT DISTINCT TO_DATE(DT_MAJ) AS ref_date FROM "SHARE"."DATAMARTS_DATASCIENCE"."SEG_HISTO";
    current_date DATE;
BEGIN
    -- 创建临时表存储结果
    CREATE OR REPLACE TEMP TABLE temp_seg_results AS
    SELECT so.*, NULL::DATE AS ref_date, NULL::NUMBER AS TOTAL_AMOUNT_HT, NULL::NUMBER AS NB_ACHAT
    FROM "SHARE"."DATAMARTS_DATASCIENCE"."SEG_OMNI" so
    WHERE 1=0;
    
    FOR current_date IN date_cursor DO
        -- 插入当前日期对应的计算结果
        INSERT INTO temp_seg_results
        SELECT 
            so.*,
            current_date,
            tck.TOTAL_AMOUNT_HT,
            tck.NB_ACHAT
        FROM "SHARE"."DATAMARTS_DATASCIENCE"."SEG_OMNI" so
        LEFT JOIN (
            SELECT 
                PR_ID_BU,
                SUM(TOTAL_AMOUNT_HT) AS TOTAL_AMOUNT_HT,
                COUNT(*) AS NB_ACHAT
            FROM "SHARE"."RAW_BDC"."TCK"
            WHERE TO_DATE(DT_SALE) >= DATEADD(YEAR, -1, current_date)
            GROUP BY PR_ID_BU
        ) tck ON so.pr_id_bu = tck.PR_ID_BU;
    END FOR;
    
    -- 将临时表数据写入目标视图
    CREATE OR REPLACE VIEW "SHARE"."DATAMARTS_DATASCIENCE"."SEG_OMNI_HISTORICAL" AS
    SELECT * FROM temp_seg_results;
    
    RETURN '数据生成完成';
END;
$$;

-- 执行存储过程
CALL generate_historical_seg_data();

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