如何在SQL Server中将一行拆分为两行（Unpivot实现？）

在SQL Server中创建测试表并执行聚合查询时，遇到以下需求：

• DB_CR_CODE为'C'的记录需要被统计两次
• 当前聚合查询中，同时涉及PRODUCT_GROUP1和PRODUCT_GROUP2的行未拆分，需将这类行拆分为两行
• 该查询最终将在超大型表上运行，需要高性能的解决方案

测试表及原始查询代码如下：

DROP TABLE [dbo].[TestTable]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestTable]
(
       [BOOKING_DATE] [date] NOT NULL,
       [TIME_INTERVAL] [int] NOT NULL,
       [DB_CR_CODE] [varchar](1) NOT NULL,
       [CHANNEL] [varchar](4) NOT NULL,
       [NBR_OF_TXN] [smallint] NOT NULL,
       [AMOUNT] [bigint] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[TestTable] VALUES ('2023-04-06', 0029, 'D', 'ABCD', 1, 100)
INSERT INTO [dbo].[TestTable] VALUES ('2023-04-06', 0027, 'D', 'ABCD', 1, 200)
INSERT INTO [dbo].[TestTable] VALUES ('2023-04-06', 0044, 'D', 'ABCD', 1, 300)
INSERT INTO [dbo].[TestTable] VALUES ('2023-04-06', 0042, 'D', 'ABCD', 1, 400)
INSERT INTO [dbo].[TestTable] VALUES ('2023-04-06', 0029, 'C', 'ABCD', 1, 500)
INSERT INTO [dbo].[TestTable] VALUES ('2023-04-06', 0069, 'C', 'ABCD', 1, 600)
INSERT INTO [dbo].[TestTable] VALUES ('2023-04-06', 0067, 'C', 'XXCD', 1, 700)
INSERT INTO [dbo].[TestTable] VALUES ('2023-04-06', 0089, 'C', 'ABCD', 1, 800)
INSERT INTO [dbo].[TestTable] VALUES ('2023-04-06', 0079, 'C', 'XXCD', 1, 900)
INSERT INTO [dbo].[TestTable] VALUES ('2023-04-06', 0084, 'C', 'ABCD', 1, 1000)

SELECT * FROM dbo.TestTable

-- 原始未拆分的查询
SELECT
    booking_date, interval, product_group1, product_group2, 
    SUM(nbr_of_txn) AS nbr_txn, SUM(amount) AS amount
FROM
    (SELECT
         BOOKING_DATE, 
         CASE
             WHEN TIME_INTERVAL BETWEEN 0000 AND 0030 THEN 1
             WHEN TIME_INTERVAL BETWEEN 0031 AND 0060 THEN 2
             WHEN TIME_INTERVAL BETWEEN 0061 AND 0090 THEN 3
             ELSE 99 
         END AS interval,
         CASE
             WHEN DB_CR_CODE = 'C' THEN 'Credit'
             WHEN DB_CR_CODE = 'D' THEN 'Debet'        
             ELSE '' 
         END AS PRODUCT_GROUP1,
         CASE 
             WHEN DB_CR_CODE = 'C' AND CHANNEL = 'ABCD' THEN 'Credit_ABCD'  
             ELSE '' 
         END AS PRODUCT_GROUP2,
         NBR_OF_TXN, AMOUNT
     FROM
         dbo.TestTable) a
GROUP BY
    booking_date, interval, PRODUCT_GROUP1, PRODUCT_GROUP2

采用先拆分需要重复统计的行，再聚合的思路，避免先聚合后拆分的性能损耗，适配超大型表场景。

优化后查询代码 #

SELECT
    booking_date,
    interval,
    product_group,
    SUM(nbr_of_txn) AS nbr_txn,
    SUM(amount) AS amount
FROM (
    SELECT
        tt.BOOKING_DATE,
        -- 计算时间区间
        CASE
            WHEN tt.TIME_INTERVAL BETWEEN 0 AND 30 THEN 1
            WHEN tt.TIME_INTERVAL BETWEEN 31 AND 60 THEN 2
            WHEN tt.TIME_INTERVAL BETWEEN 61 AND 90 THEN 3
            ELSE 99 
        END AS interval,
        -- 根据拆分标识生成对应分组
        CASE
            WHEN split.flag = 1 THEN 
                CASE 
                    WHEN tt.DB_CR_CODE = 'C' THEN 'Credit' 
                    WHEN tt.DB_CR_CODE = 'D' THEN 'Debet' 
                    ELSE '' 
                END
            WHEN split.flag = 2 AND tt.DB_CR_CODE = 'C' AND tt.CHANNEL = 'ABCD' THEN 'Credit_ABCD'
            ELSE NULL -- 标记需要过滤的无效行
        END AS product_group,
        tt.NBR_OF_TXN,
        tt.AMOUNT
    FROM dbo.TestTable tt
    -- 交叉连接包含两个标识的虚拟表，实现行拆分
    CROSS JOIN (VALUES (1), (2)) AS split(flag)
    -- 过滤掉不需要的拆分行：flag=2时仅保留符合条件的C类记录
    WHERE 
        (split.flag = 1) 
        OR (split.flag = 2 AND tt.DB_CR_CODE = 'C' AND tt.CHANNEL = 'ABCD')
) AS split_data
-- 过滤掉无效的分组行
WHERE product_group IS NOT NULL
GROUP BY booking_date, interval, product_group
ORDER BY booking_date, interval, product_group;

方案说明 #

1. 行拆分逻辑：通过CROSS JOIN (VALUES (1), (2))为每一行生成两条记录，再通过WHERE条件和CASE语句，仅对DB_CR_CODE='C'且CHANNEL='ABCD'的记录保留两条有效分组行，其他记录仅保留一条有效行。
2. 性能优势：仅对原表进行一次全表扫描，交叉连接虚拟表的开销可以忽略，相比先聚合后拆分的方式减少了一次聚合计算，更适合超大型表。
3. 结果符合需求：
   • DB_CR_CODE='C'且CHANNEL='ABCD'的记录会拆分为Credit和Credit_ABCD两行，各自统计交易数和金额
   • DB_CR_CODE='C'但CHANNEL!='ABCD'的记录仅归入Credit分组
   • DB_CR_CODE='D'的记录仅归入Debet分组

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