如何在Snowflake中用SQL实现按产品和月份透视营收表？

需求说明 #

• 生成按产品、月份维度展示的营收透视表，数据可追溯至2020年
• 查询时支持自定义日期范围筛选
• 同一月份的多条营收记录需汇总为当月总额，无营收的月份显示0

现有数据表示例 #

用户测试代码 #

SELECT
    product,
    [1] AS Jan,
    [2] AS Feb,
    [3] AS Mar,
    [4] AS Apr,
    [5] AS May,
    [6] AS Jun,
    [7] AS Jul,
    [8] AS Aug,
    [9] AS Sep,
    [10] AS Oct,
    [11] AS Nov,
    [12] AS Dec
FROM
(Select 
product,
revenue,
date_trunc('month', date_sold) as month
  from
    fct_final_net_revenue) source
PIVOT
(   SUM(revenue)
    FOR month
    IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] )
) AS pvtMonth;

代码问题分析 #

原代码存在两个核心问题：

1. date_trunc('month', date_sold)返回的是完整的月份起始日期（如2020-04-01），但PIVOT子句中使用的是月份数字（[1]至[12]），二者类型不匹配，无法正确映射
2. 未处理无营收月份的NULL值，也未添加日期范围筛选逻辑

修正后的SQL代码 #

SELECT
    product,
    COALESCE([1], 0) AS Jan,
    COALESCE([2], 0) AS Feb,
    COALESCE([3], 0) AS Mar,
    COALESCE([4], 0) AS Apr,
    COALESCE([5], 0) AS May,
    COALESCE([6], 0) AS Jun,
    COALESCE([7], 0) AS Jul,
    COALESCE([8], 0) AS Aug,
    COALESCE([9], 0) AS Sep,
    COALESCE([10], 0) AS Oct,
    COALESCE([11], 0) AS Nov,
    COALESCE([12], 0) AS Dec
FROM
(
    SELECT 
        product,
        revenue,
        DATE_PART('month', date_sold) AS month_num
    FROM fct_final_net_revenue
    -- 自定义日期范围筛选，可根据需求修改起止日期
    WHERE date_sold >= '2020-01-01' 
      AND date_sold <= CURRENT_DATE
) AS source
PIVOT
(
    SUM(revenue)
    FOR month_num IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) AS pvtMonth
ORDER BY product;

代码说明 #

1. 月份提取：用DATE_PART('month', date_sold)提取1-12的月份数字，与PIVOT中的列表完全匹配，解决映射问题
2. 日期筛选：通过WHERE子句添加日期范围限制，支持自定义查询时间段
3. NULL值处理：用COALESCE函数将无营收月份的NULL值转为0，符合期望结果格式
4. 排序优化：添加ORDER BY product让结果按产品名称规整排序

扩展：按年份拆分月度营收 #

如果需要按年份+月份维度展示（即每年各月的营收），可使用以下代码：

SELECT
    product,
    year,
    COALESCE([1], 0) AS Jan,
    COALESCE([2], 0) AS Feb,
    COALESCE([3], 0) AS Mar,
    COALESCE([4], 0) AS Apr,
    COALESCE([5], 0) AS May,
    COALESCE([6], 0) AS Jun,
    COALESCE([7], 0) AS Jul,
    COALESCE([8], 0) AS Aug,
    COALESCE([9], 0) AS Sep,
    COALESCE([10], 0) AS Oct,
    COALESCE([11], 0) AS Nov,
    COALESCE([12], 0) AS Dec
FROM
(
    SELECT 
        product,
        revenue,
        DATE_PART('year', date_sold) AS year,
        DATE_PART('month', date_sold) AS month_num
    FROM fct_final_net_revenue
    WHERE date_sold >= '2020-01-01'
) AS source
PIVOT
(
    SUM(revenue)
    FOR month_num IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) AS pvtYearMonth
ORDER BY product, year;

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