优化SQL查询：获取过去6个月各表累计行数及变化率

针对你需要扩展至过去6个月的表行数统计需求，以下是优化后的方案，解决原查询重复代码多、扩展困难的问题：

核心优化思路 #

• 用CTE统一管理时间偏移量，扩展月份只需新增一行定义，无需修改后续查询逻辑
• 采用条件聚合替代多次JOIN原表，减少重复代码，提升查询性能
• 一次性获取所有目标统计日期，避免多次单独查询的开销
• 统一变化率计算逻辑，减少冗余代码

优化后的SQL代码 #

DECLARE @LatestDate DATETIME;
SELECT @LatestDate = MAX(AnalysisDate) FROM DBA_TableSpaceUsedHistory;

WITH DateOffsets AS (
    -- 定义需要统计的时间区间：0=当前最新，1=1个月前，...，6=6个月前
    SELECT 0 AS MonthOffset, 'Current' AS PeriodName UNION ALL
    SELECT 1 AS MonthOffset, '1MonthAgo' AS PeriodName UNION ALL
    SELECT 2 AS MonthOffset, '2MonthsAgo' AS PeriodName UNION ALL
    SELECT 3 AS MonthOffset, '3MonthsAgo' AS PeriodName UNION ALL
    SELECT 4 AS MonthOffset, '4MonthsAgo' AS PeriodName UNION ALL
    SELECT 5 AS MonthOffset, '5MonthsAgo' AS PeriodName UNION ALL
    SELECT 6 AS MonthOffset, '6MonthsAgo' AS PeriodName
),
TargetDates AS (
    -- 获取每个时间区间对应的最新收集日期
    SELECT 
        do.PeriodName,
        do.MonthOffset,
        MAX(tsuh.AnalysisDate) AS TargetDate
    FROM DateOffsets do
    LEFT JOIN DBA_TableSpaceUsedHistory tsuh
        ON tsuh.AnalysisDate < DATEADD(mm, -do.MonthOffset, @LatestDate)
    GROUP BY do.PeriodName, do.MonthOffset
),
TablePeriodData AS (
    -- 关联原表，获取每个表在各时间区间的行数和空间数据
    SELECT 
        tsuh.TableSchema,
        tsuh.TableName,
        td.PeriodName,
        tsuh.TableRows,
        tsuh.TotalSpaceKB
    FROM DBA_TableSpaceUsedHistory tsuh
    INNER JOIN TargetDates td
        ON tsuh.AnalysisDate = td.TargetDate
)
SELECT 
    TableSchema,
    TableName,
    -- 展示各时间点的行数
    FORMAT(MAX(CASE WHEN PeriodName = 'Current' THEN TableRows END), '#,##0') AS [当前行数],
    FORMAT(MAX(CASE WHEN PeriodName = '1MonthAgo' THEN TableRows END), '#,##0') AS [1个月前行数],
    FORMAT(MAX(CASE WHEN PeriodName = '2MonthsAgo' THEN TableRows END), '#,##0') AS [2个月前行数],
    FORMAT(MAX(CASE WHEN PeriodName = '3MonthsAgo' THEN TableRows END), '#,##0') AS [3个月前行数],
    FORMAT(MAX(CASE WHEN PeriodName = '4MonthsAgo' THEN TableRows END), '#,##0') AS [4个月前行数],
    FORMAT(MAX(CASE WHEN PeriodName = '5MonthsAgo' THEN TableRows END), '#,##0') AS [5个月前行数],
    FORMAT(MAX(CASE WHEN PeriodName = '6MonthsAgo' THEN TableRows END), '#,##0') AS [6个月前行数],
    -- 1天差值（保留原需求中的前一天统计逻辑）
    FORMAT(
        MAX(CASE WHEN PeriodName = 'Current' THEN TableRows END) - 
        ISNULL(MAX(CASE WHEN tsuh.AnalysisDate = DATEADD(hh, -24, @LatestDate) THEN TableRows END), 0), 
        '#,##0'
    ) AS [1天差值],
    -- 各时间区间的变化率计算
    CASE 
        WHEN MAX(CASE WHEN PeriodName = '1MonthAgo' THEN TableRows END) > 0 
        THEN FORMAT(
            ((MAX(CASE WHEN PeriodName = 'Current' THEN TableRows END) - MAX(CASE WHEN PeriodName = '1MonthAgo' THEN TableRows END)) * 1.0 / 
             MAX(CASE WHEN PeriodName = '1MonthAgo' THEN TableRows END)) * 100, 
            '##0.00'
        )
        ELSE '100'
    END AS [1个月变化率(%)],
    CASE 
        WHEN MAX(CASE WHEN PeriodName = '3MonthsAgo' THEN TableRows END) > 0 
        THEN FORMAT(
            ((MAX(CASE WHEN PeriodName = 'Current' THEN TableRows END) - MAX(CASE WHEN PeriodName = '3MonthsAgo' THEN TableRows END)) * 1.0 / 
             MAX(CASE WHEN PeriodName = '3MonthsAgo' THEN TableRows END)) * 100, 
            '##0.00'
        )
        ELSE '100'
    END AS [3个月变化率(%)],
    CASE 
        WHEN MAX(CASE WHEN PeriodName = '6MonthsAgo' THEN TableRows END) > 0 
        THEN FORMAT(
            ((MAX(CASE WHEN PeriodName = 'Current' THEN TableRows END) - MAX(CASE WHEN PeriodName = '6MonthsAgo' THEN TableRows END)) * 1.0 / 
             MAX(CASE WHEN PeriodName = '6MonthsAgo' THEN TableRows END)) * 100, 
            '##0.00'
        )
        ELSE '100'
    END AS [6个月变化率(%)]
FROM TablePeriodData
LEFT JOIN DBA_TableSpaceUsedHistory tsuh
    ON tsuh.TableSchema = TablePeriodData.TableSchema
    AND tsuh.TableName = TablePeriodData.TableName
    AND tsuh.AnalysisDate = DATEADD(hh, -24, @LatestDate)
GROUP BY TableSchema, TableName
-- 按1天差值降序，取TOP5（保留原需求排序逻辑）
ORDER BY (MAX(CASE WHEN PeriodName = 'Current' THEN TableRows END) - ISNULL(MAX(CASE WHEN tsuh.AnalysisDate = DATEADD(hh, -24, @LatestDate) THEN TableRows END), 0)) DESC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

关键优势 #

1. 扩展性强：如果需要增加更多月份统计（比如12个月），只需在DateOffsets CTE中新增一行SELECT 12 AS MonthOffset, '12MonthsAgo' AS PeriodName，再在SELECT语句中对应新增列即可
2. 性能提升：避免了原查询中多次JOIN大表的操作，改为一次关联后用条件聚合处理，减少IO开销
3. 代码简洁：统一的日期管理和变化率计算逻辑，减少冗余代码，便于维护

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