如何在SQL中实现示例中的双重Unpivot（逆透视）操作？

核心方案：用CROSS APPLY + VALUES一次性拆分 #

完全可以用CROSS APPLY实现，而且能避免两次UNPIVOT操作——通过VALUES子句把每一年的Days和Discharges列配对，一次扫描源表就完成转置，效率远高于两次Unpivot。

示例SQL代码（针对SQL Server，其他数据库可调整语法）：

SELECT 
    t.Zipcode,
    t.Age,
    v.Year,
    v.Days,
    v.Discharges
INTO dbo.target_table -- 提前创建好目标表结构
FROM dbo.your_source_table t
CROSS APPLY (
    VALUES
        (2022, t.[2022 Days], t.[2022 Discharges]),
        (2023, t.[2023 Days], t.[2023 Discharges]),
        (2024, t.[2024 Days], t.[2024 Discharges]),
        (2025, t.[2025 Days], t.[2025 Discharges]),
        (2026, t.[2026 Days], t.[2026 Discharges]),
        (2027, t.[2027 Days], t.[2027 Discharges]),
        (2028, t.[2028 Days], t.[2028 Discharges]),
        (2029, t.[2029 Days], t.[2029 Discharges]),
        (2030, t.[2030 Days], t.[2030 Discharges]),
        (2031, t.[2031 Days], t.[2031 Discharges]),
        (2032, t.[2032 Days], t.[2032 Discharges])
) v(Year, Days, Discharges)
-- 分批条件，示例按Zipcode范围筛选
WHERE t.Zipcode BETWEEN '00000' AND '10000'

分批处理策略 #

针对15亿行的超大数据集，必须分批处理以避免资源耗尽，推荐两种实用方式：

• 按分区键分段：如果源表按Zipcode分区，直接按分区批量处理；无分区则按Zipcode范围拆分（比如每10000个Zipcode为一批），循环执行上述SQL，每次更新WHERE条件的范围。
• 按ROW_NUMBER分批：给源表数据加行号，每次处理固定行数（比如1000万行）：

DECLARE @BatchSize INT = 10000000;
DECLARE @LastRow INT = 0;

WHILE 1=1
BEGIN
    INSERT INTO dbo.target_table (Zipcode, Age, Year, Days, Discharges)
    SELECT 
        t.Zipcode, t.Age, v.Year, v.Days, v.Discharges
    FROM (
        SELECT *, ROW_NUMBER() OVER (ORDER BY Zipcode, Age) AS RowNum
        FROM dbo.your_source_table
        WHERE RowNum > @LastRow
    ) t
    CROSS APPLY (
        VALUES
            (2022, t.[2022 Days], t.[2022 Discharges]),
            -- 其余年份按上述格式补充
            (2032, t.[2032 Days], t.[2032 Discharges])
    ) v(Year, Days, Discharges)
    WHERE t.RowNum <= @LastRow + @BatchSize;

    IF @@ROWCOUNT = 0 BREAK;
    SET @LastRow += @BatchSize;
END

性能优化要点 #

• 索引优化：源表提前创建包含Zipcode, Age的非聚集索引，避免全表扫描；目标表先禁用非聚集索引和外键约束，处理完成后再重建，减少写入开销。
• 日志控制：切换数据库到SIMPLE恢复模式，避免事务日志暴涨；每批处理后可手动执行CHECKPOINT释放日志空间。
• 并行处理：根据服务器核心数，在查询末尾添加OPTION (MAXDOP 8)（数字按需调整），开启并行查询加速单批处理速度。

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