无需动态SQL，优雅获取数据库月度统计的方法（订单量场景）

嘿，针对你要实现的月度订单统计需求，我整理了几个不用动态SQL、还兼具动态性和扩展性的方案，不管是SQL Server还是PostgreSQL都能直接用，而且能满足甚至超越你的输出要求～

核心思路是借助日期维度表来实现灵活性——提前维护一份包含所有日期属性的维度表，后续不管统计哪个时间段、扩展什么维度，都不用修改SQL结构，完全规避动态SQL的麻烦。

SQL Server 实现方案 #

1. 横向统计（匹配现有输出格式） #

如果需要保持现有横向列展示（比如一年12个月各一列），可以结合PIVOT和日期维度表来实现：

-- 第一步：创建日期维度表（如果还没有的话，可以一次性生成多年数据）
CREATE TABLE DateDim (
    DateKey DATE PRIMARY KEY,
    Year INT,
    Month INT,
    MonthName VARCHAR(20),
    YearMonth VARCHAR(7) -- 格式如 '2024-01'
);

-- 第二步：生成横向统计结果
SELECT 
    Year,
    [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 
        dd.Year,
        dd.Month,
        COUNT(o.OrderID) AS OrderCount
    FROM DateDim dd
    LEFT JOIN Orders o ON DATEFROMPARTS(YEAR(o.OrderDate), MONTH(o.OrderDate), 1) = DATEFROMPARTS(dd.Year, dd.Month, 1)
    WHERE dd.Year = YEAR(GETDATE()) -- 可替换为指定年份，或删除过滤所有年份
    GROUP BY dd.Year, dd.Month
) AS SourceData
PIVOT (
    SUM(OrderCount)
    FOR Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS PivotTable;

这里的优势是：日期维度表可以提前生成未来10年甚至更久的数据，后续统计不同年份只需要修改WHERE条件，完全不用动SQL的核心结构，扩展性拉满。

2. 纵向统计（更灵活的输出格式） #

如果可以接受纵向输出（每一行对应一个年月的统计数据），这种格式是最灵活的——不管统计多少年份、扩展什么维度，SQL都不用改：

SELECT 
    dd.YearMonth,
    dd.MonthName,
    COUNT(o.OrderID) AS OrderCount
FROM DateDim dd
LEFT JOIN Orders o ON DATEFROMPARTS(YEAR(o.OrderDate), MONTH(o.OrderDate), 1) = dd.DateKey
WHERE dd.Year BETWEEN 2022 AND 2024 -- 按需调整时间范围
GROUP BY dd.YearMonth, dd.MonthName
ORDER BY dd.YearMonth;

这种格式非常适合后续用Excel透视表、BI工具做可视化，完全不用担心月份数量变化的问题。

PostgreSQL 实现方案 #

1. 横向统计（匹配现有输出格式） #

PostgreSQL可以借助crosstab函数（需要先启用tablefunc扩展）来实现横向展示，同样结合日期维度表：

-- 先启用tablefunc扩展（仅需执行一次）
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 生成横向统计结果
SELECT * FROM crosstab(
    'SELECT 
        dd.year,
        dd.month,
        COUNT(o.order_id) AS order_count
     FROM date_dim dd
     LEFT JOIN orders o ON DATE_TRUNC(''month'', o.order_date) = DATE_TRUNC(''month'', dd.date_key)
     WHERE dd.year = EXTRACT(YEAR FROM CURRENT_DATE)::INT
     GROUP BY dd.year, dd.month
     ORDER BY dd.year, dd.month',
    'SELECT generate_series(1,12)'
) AS ct(
    year INT,
    jan INT, feb INT, mar INT, apr INT,
    may INT, jun INT, jul INT, aug INT,
    sep INT, oct INT, nov INT, dec INT
);

用generate_series(1,12)自动生成1-12月，避免硬编码月份，同时日期维度表保证了统计的动态性。

2. 纵向统计（更灵活的输出格式） #

PostgreSQL的纵向统计逻辑和SQL Server类似，用日期截断函数分组即可：

SELECT 
    TO_CHAR(dd.date_key, 'YYYY-MM') AS year_month,
    TO_CHAR(dd.date_key, 'FMMonth') AS month_name,
    COUNT(o.order_id) AS order_count
FROM date_dim dd
LEFT JOIN orders o ON DATE_TRUNC('month', o.order_date) = DATE_TRUNC('month', dd.date_key)
WHERE dd.year BETWEEN 2022 AND 2024 -- 按需调整时间范围
GROUP BY TO_CHAR(dd.date_key, 'YYYY-MM'), TO_CHAR(dd.date_key, 'FMMonth')
ORDER BY year_month;

这种格式同样无需修改SQL结构就能适配任意时间范围，扩展性极强。

通用建议 #

• 优先维护日期维度表：这是实现无动态SQL动态统计的核心，它可以包含年、月、季度、星期、节假日等各种属性，后续扩展季度统计、周统计只需要修改分组字段即可。
• 纵向输出优先：纵向格式更适合现代报表工具处理，而且完全不需要考虑时间范围的变化，是扩展性最好的选择。
• 避免硬编码时间范围：用参数或者动态条件（比如YEAR(GETDATE())）来指定时间，让SQL更灵活。

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