如何用SQL PIVOT按产品统计近12个月各月订单总额并转置为列？

原始数据 #

需求 #

统计每个Product近12个月中各月的Amount总和，将每个月份作为单独一列展示，预期结果如下：

预期结果 #

你尝试的错误代码 #

select * ,
  monthname(to_date(Date)) month  
  from PRODUCTS
    pivot(sum(Amount) for month in ('Jan','Feb','Mar','Apr','Jun','Jul','Aug','Sep','Oct','Nov','Dec'))
      as p
  WHERE Date >= '2021-08-01'
  order by Product;

问题分析 #

你的代码存在几个关键问题：

• PIVOT子句位置错误，应该先完成日期提取和数据过滤，再执行透视操作
• 透视列名的大小写和提取的月份名称不匹配，导致映射失效
• 过滤条件放在透视之后，会丢失部分需要补0的产品数据
• 未处理透视后产生的NULL值，无法得到预期的0填充效果

正确解法1：使用PIVOT函数（标准SQL） #

WITH monthly_data AS (
    SELECT 
        Product,
        LOWER(MONTHNAME(TO_DATE(Date))) AS month_name,
        Amount
    FROM PRODUCTS
    WHERE Date >= DATEADD(MONTH, -12, CURRENT_DATE) -- 动态取近12个月，也可替换为固定日期'2021-08-01'
)
SELECT 
    Product,
    COALESCE(jan, 0) AS jan,
    COALESCE(feb, 0) AS feb,
    COALESCE(mar, 0) AS mar,
    COALESCE(apr, 0) AS apr,
    COALESCE(may, 0) AS may,
    COALESCE(jun, 0) AS jun,
    COALESCE(jul, 0) AS jul,
    COALESCE(aug, 0) AS aug,
    COALESCE(sep, 0) AS sep,
    COALESCE(oct, 0) AS oct,
    COALESCE(nov, 0) AS nov,
    COALESCE(dec, 0) AS dec
FROM monthly_data
PIVOT (
    SUM(Amount)
    FOR month_name IN (
        'jan' AS jan,
        'feb' AS feb,
        'mar' AS mar,
        'apr' AS apr,
        'may' AS may,
        'jun' AS jun,
        'jul' AS jul,
        'aug' AS aug,
        'sep' AS sep,
        'oct' AS oct,
        'nov' AS nov,
        'dec' AS dec
    )
) AS pivot_table
ORDER BY Product;

说明 #

1. 先用CTE提取产品、小写月份名和金额，同时过滤近12个月的数据
2. 在PIVOT中指定聚合函数SUM(Amount)，将每个月份映射为对应列名
3. 用COALESCE把透视后的NULL值转为0，符合预期结果的填充要求

正确解法2：条件聚合（兼容更多数据库） #

如果你的数据库不支持PIVOT函数，用条件聚合的方式更通用：

SELECT 
    Product,
    SUM(CASE WHEN LOWER(MONTHNAME(TO_DATE(Date))) = 'jan' THEN Amount ELSE 0 END) AS jan,
    SUM(CASE WHEN LOWER(MONTHNAME(TO_DATE(Date))) = 'feb' THEN Amount ELSE 0 END) AS feb,
    SUM(CASE WHEN LOWER(MONTHNAME(TO_DATE(Date))) = 'mar' THEN Amount ELSE 0 END) AS mar,
    SUM(CASE WHEN LOWER(MONTHNAME(TO_DATE(Date))) = 'apr' THEN Amount ELSE 0 END) AS apr,
    SUM(CASE WHEN LOWER(MONTHNAME(TO_DATE(Date))) = 'may' THEN Amount ELSE 0 END) AS may,
    SUM(CASE WHEN LOWER(MONTHNAME(TO_DATE(Date))) = 'jun' THEN Amount ELSE 0 END) AS jun,
    SUM(CASE WHEN LOWER(MONTHNAME(TO_DATE(Date))) = 'jul' THEN Amount ELSE 0 END) AS jul,
    SUM(CASE WHEN LOWER(MONTHNAME(TO_DATE(Date))) = 'aug' THEN Amount ELSE 0 END) AS aug,
    SUM(CASE WHEN LOWER(MONTHNAME(TO_DATE(Date))) = 'sep' THEN Amount ELSE 0 END) AS sep,
    SUM(CASE WHEN LOWER(MONTHNAME(TO_DATE(Date))) = 'oct' THEN Amount ELSE 0 END) AS oct,
    SUM(CASE WHEN LOWER(MONTHNAME(TO_DATE(Date))) = 'nov' THEN Amount ELSE 0 END) AS nov,
    SUM(CASE WHEN LOWER(MONTHNAME(TO_DATE(Date))) = 'dec' THEN Amount ELSE 0 END) AS dec
FROM PRODUCTS
WHERE Date >= DATEADD(MONTH, -12, CURRENT_DATE) -- 或固定日期'2021-08-01'
GROUP BY Product
ORDER BY Product;

说明 #

• 用CASE语句判断每条记录的归属月份，对应累加金额，非目标月份则加0
• 按Product分组后直接得到每个产品各月的总和，无需透视函数，兼容性更强

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