使用CTE创建订单统计透视表的查询优化及替代方案咨询

一、优化现有CTE查询 #

如果你的原CTE存在重复扫描invoice表、多次子查询关联这类冗余操作，可以按下面的思路优化，核心是减少重复计算、简化逻辑：

先上优化后的代码：

WITH months AS (
    -- 一次性生成1-12月的基础维度，确保无订单月份也能显示
    SELECT 1 AS invoice_month UNION ALL
    SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
    SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
    SELECT 11 UNION ALL SELECT 12
),
invoice_stats AS (
    -- 只扫一次invoice表，提前聚合出目标年份的各月订单数
    SELECT
        EXTRACT(MONTH FROM invoice_date) AS inv_month,
        EXTRACT(YEAR FROM invoice_date) AS inv_year,
        COUNT(*) AS order_count
    FROM invoice
    WHERE inv_year BETWEEN 2011 AND 2013
    GROUP BY inv_month, inv_year
)
SELECT
    m.invoice_month,
    -- 用条件聚合替代多表关联，直接匹配年份统计
    COALESCE(MAX(CASE WHEN s.inv_year = 2011 THEN s.order_count END), 0) AS year_2011,
    COALESCE(MAX(CASE WHEN s.inv_year = 2012 THEN s.order_count END), 0) AS year_2012,
    COALESCE(MAX(CASE WHEN s.inv_year = 2013 THEN s.order_count END), 0) AS year_2013
FROM months m
LEFT JOIN invoice_stats s ON m.invoice_month = s.inv_month
GROUP BY m.invoice_month
ORDER BY m.invoice_month;

优化细节：

• 单独用months CTE生成1-12月，避免重复构造维度数据
• invoice_stats只扫描一次表，把各年各月的订单数先聚合好，减少后续关联次数
• 主查询用CASE WHEN条件聚合，代替原来可能的多次LEFT JOIN，降低查询开销
• COALESCE把无订单的NULL转为0，完全符合需求

二、其他简洁高效的实现方法 #

1. 用PIVOT子句（支持的数据库：SQL Server、PostgreSQL 11+、Oracle） #

如果你的数据库支持PIVOT语法，代码会更简洁直观，适合固定年份的透视需求：

以SQL Server为例：

WITH months AS (
    SELECT 1 AS invoice_month UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
    SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
),
invoice_data AS (
    SELECT
        EXTRACT(MONTH FROM invoice_date) AS inv_month,
        'year_' + CAST(EXTRACT(YEAR FROM invoice_date) AS VARCHAR) AS year_col,
        COUNT(*) AS order_count
    FROM invoice
    WHERE EXTRACT(YEAR FROM invoice_date) BETWEEN 2011 AND 2013
    GROUP BY inv_month, EXTRACT(YEAR FROM invoice_date)
)
SELECT
    invoice_month,
    ISNULL([year_2011], 0) AS year_2011,
    ISNULL([year_2012], 0) AS year_2012,
    ISNULL([year_2013], 0) AS year_2013
FROM months m
LEFT JOIN invoice_data d ON m.invoice_month = d.inv_month
PIVOT (
    MAX(order_count)
    FOR year_col IN ([year_2011], [year_2012], [year_2013])
) AS pvt
ORDER BY invoice_month;

2. 一步到位：直接生成维度+条件聚合（无需CTE） #

如果场景简单，不需要拆分CTE，直接用VALUES子句生成月份，配合条件聚合，代码更紧凑：

SELECT
    m.invoice_month,
    COALESCE(COUNT(CASE WHEN EXTRACT(YEAR FROM i.invoice_date) = 2011 THEN 1 END), 0) AS year_2011,
    COALESCE(COUNT(CASE WHEN EXTRACT(YEAR FROM i.invoice_date) = 2012 THEN 1 END), 0) AS year_2012,
    COALESCE(COUNT(CASE WHEN EXTRACT(YEAR FROM i.invoice_date) = 2013 THEN 1 END), 0) AS year_2013
FROM (
    VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
) AS m(invoice_month)
LEFT JOIN invoice i 
    ON EXTRACT(MONTH FROM i.invoice_date) = m.invoice_month
    AND EXTRACT(YEAR FROM i.invoice_date) BETWEEN 2011 AND 2013
GROUP BY m.invoice_month
ORDER BY m.invoice_month;

这种写法省去了CTE的拆分，直接把月份生成和统计放在一个查询里，逻辑清晰，执行效率也不差。

3. 创建永久月份维度表（适合长期复用） #

如果经常需要按月份做统计，建议建一张永久的dim_month表，以后查数据直接用，不用每次生成月份：

-- 先创建维度表（只执行一次）
CREATE TABLE dim_month (invoice_month INT PRIMARY KEY);
INSERT INTO dim_month VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);

-- 后续查询直接用这张表
SELECT
    m.invoice_month,
    COALESCE(MAX(CASE WHEN EXTRACT(YEAR FROM i.invoice_date)=2011 THEN COUNT(i.invoice_id) END),0) AS year_2011,
    COALESCE(MAX(CASE WHEN EXTRACT(YEAR FROM i.invoice_date)=2012 THEN COUNT(i.invoice_id) END),0) AS year_2012,
    COALESCE(MAX(CASE WHEN EXTRACT(YEAR FROM i.invoice_date)=2013 THEN COUNT(i.invoice_id) END),0) AS year_2013
FROM dim_month m
LEFT JOIN invoice i 
    ON EXTRACT(MONTH FROM i.invoice_date)=m.invoice_month
    AND EXTRACT(YEAR FROM i.invoice_date) BETWEEN 2011 AND 2013
GROUP BY m.invoice_month
ORDER BY m.invoice_month;

这样每次查询都不用再生成月份数据，适合长期重复使用的统计场景，效率更高。

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