SQL Server 依年、月統計欄位值分布數量

以日期欄位分組統計：年/月/類型三維度聚合

需求：統計表 AA 中 AA_YWLX（業務類型）在每年每月的筆數分布。

核心查詢

SELECT
    YEAR(AA_YWRQ) AS YEARS,
    MONTH(AA_YWRQ) AS MONTHS,
    AA_YWLX,
    COUNT(*) AS Number
FROM AA
GROUP BY
    YEAR(AA_YWRQ),
    MONTH(AA_YWRQ),
    AA_YWLX
ORDER BY
    YEARS, MONTHS, AA_YWLX;

關鍵點解析

• YEAR()/MONTH()：從 DATETIME/DATE 擷取年、月，作為分組依據。
• GROUP BY 必須包含 SELECT 清單中所有非聚合欄位。
• COUNT(*) 統計每群組列數；若需統計非 NULL 個數可用 COUNT(AA_YWLX)。

效能優化建議

1. 建立計算欄位索引（SQL Server 2005+）：

   ALTER TABLE AA ADD YR AS YEAR(AA_YWRQ), MT AS MONTH(AA_YWRQ);
   CREATE INDEX IX_AA_YM_TYPE ON AA (YR, MT, AA_YWLX);

2. 或使用 篩選索引 針對熱門年份：CREATE INDEX ... WHERE YR = 2024。
3. 避免在 WHERE 使用函數（如 WHERE YEAR(date)=2024），改用範圍：WHERE AA_YWRQ >= '2024-01-01' AND AA_YWRQ < '2025-01-01'。

擴充：產生完整年月矩陣（含零值月份）

-- 利用遞迴 CTE 產生年月序列，再 LEFT JOIN
WITH ym AS (
    SELECT 2023 AS yr, 1 AS mt
    UNION ALL
    SELECT CASE WHEN mt = 12 THEN yr + 1 ELSE yr END,
           CASE WHEN mt = 12 THEN 1 ELSE mt + 1 END
    FROM ym
    WHERE yr < 2024 OR (yr = 2024 AND mt < 12)
)
SELECT ym.yr, ym.mt, t.AA_YWLX, COALESCE(t.cnt, 0) AS Number
FROM ym
CROSS JOIN (SELECT DISTINCT AA_YWLX FROM AA) typ
LEFT JOIN (
    SELECT YEAR(AA_YWRQ) yr, MONTH(AA_YWRQ) mt, AA_YWLX, COUNT(*) cnt
    FROM AA GROUP BY YEAR(AA_YWRQ), MONTH(AA_YWRQ), AA_YWLX
) t ON t.yr = ym.yr AND t.mt = ym.mt AND t.AA_YWLX = typ.AA_YWLX
ORDER BY ym.yr, ym.mt, typ.AA_YWLX
OPTION (MAXRECURSION 0);

總結

掌握 YEAR/MONTH + GROUP BY 模式，即可快速產出多維度統計報表；搭配計算欄位索引，大表查詢亦能秒級回應。