如何用Snowflake SQL实现月度产品列表的MoM增减对比

以下是针对需求的完整解决方案，包含基础聚合、月度对比逻辑及示例输出：

1. 模拟数据与基础聚合 #

先假设你的原始表结构包含partner_id（合作伙伴ID）、product（产品）、month（统计月份，格式如YYYY-MM）。我们先模拟示例数据并完成基础月度聚合：

-- 模拟原始数据（替换为你的实际表）
WITH partner_products AS (
    SELECT 'P1' AS partner_id, '1' AS product, '2024-01' AS month UNION ALL
    SELECT 'P1' AS partner_id, '2' AS product, '2024-01' AS month UNION ALL
    SELECT 'P1' AS partner_id, '2' AS product, '2024-02' AS month UNION ALL
    SELECT 'P1' AS partner_id, '3' AS product, '2024-02' AS month UNION ALL
    SELECT 'P1' AS partner_id, '1' AS product, '2024-03' AS month UNION ALL
    SELECT 'P1' AS partner_id, '3' AS product, '2024-03' AS month UNION ALL
    SELECT 'P1' AS partner_id, '4' AS product, '2024-03' AS month
),
-- 基础月度聚合：生成每月产品列表
monthly_agg AS (
    SELECT
        partner_id,
        month,
        LISTAGG(DISTINCT product, ', ') WITHIN GROUP (ORDER BY product) AS product_list,
        -- 获取上月的产品列表（同合作伙伴维度）
        LAG(LISTAGG(DISTINCT product, ', ') WITHIN GROUP (ORDER BY product)) 
            OVER (PARTITION BY partner_id ORDER BY month) AS prev_month_product_list
    FROM partner_products
    GROUP BY partner_id, month
)

2. 计算月度增减并生成最终结果 #

通过拆分字符串对比本月与上月的产品集合，生成MoM Change列：

SELECT
    partner_id,
    month,
    product_list,
    -- 组合增减信息，处理首月无历史数据的情况
    CASE
        WHEN prev_month_product_list IS NULL THEN 'No prior month data'
        ELSE 
            'Added: ' || NVL(
                (SELECT LISTAGG(DISTINCT t.value, ', ') FROM SPLIT_TO_TABLE(product_list, ', ') t 
                 WHERE t.value NOT IN (SELECT value FROM SPLIT_TO_TABLE(prev_month_product_list, ', '))), 
                'None'
            ) || ' | Removed: ' || NVL(
                (SELECT LISTAGG(DISTINCT t.value, ', ') FROM SPLIT_TO_TABLE(prev_month_product_list, ', ') t 
                 WHERE t.value NOT IN (SELECT value FROM SPLIT_TO_TABLE(product_list, ', '))), 
                'None'
            )
    END AS mom_change
FROM monthly_agg
ORDER BY partner_id, month;

3. 示例输出 #

执行上述语句后会得到如下结果：

关键注意事项 #

• 分隔符选择：如果产品名称包含逗号，需将LISTAGG的分隔符替换为无冲突字符（如'|'），避免拆分时出错。
• 去重处理：LISTAGG中加DISTINCT是为了避免同一产品当月多次出现导致列表冗余。
• 维度对齐：LAG函数的PARTITION BY partner_id确保仅对比同一合作伙伴的月度数据，避免跨伙伴的错误对比。

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