使用带TOP 1的Pivot SQL实现按phase_key取首次扫描数据透视

你需要将行存储的扫描数据透视，按shipment_id聚合，每个phase_key列对应首次扫描时间，但当前动态Pivot语句执行过慢，以下是针对性优化方案：

一、原语句的性能瓶颈分析 #

• 生成动态列时未过滤shipment_id，扫描了全表的data和event数据，当表数据量大时，这一步会占用大量资源。
• Pivot操作包含了所有phase_key（即使当前订单不存在的），增加了不必要的计算开销。
• 聚合函数使用MAX([time])不符合"首次扫描"的需求（应取最早时间，用MIN），同时无合适索引支撑聚合操作。

二、优化后的动态Pivot语句 #

DECLARE @DynamicTSQLStatement NVARCHAR(MAX),
        @DynamicPIVOTColumns NVARCHAR(MAX);

-- 仅获取当前订单关联的phase_key，减少透视列数
SELECT @DynamicPIVOTColumns = STRING_AGG(CAST(QUOTENAME([phase_key]) AS VARCHAR(MAX)), ',') WITHIN GROUP (ORDER BY [phase_key])
FROM
(
    SELECT DISTINCT e.[phase_key]
    FROM data d
    INNER JOIN event e ON d.id = e.data_id
    WHERE d.shipment_id = '99156136'
) DS ([phase_key]);

SET @DynamicTSQLStatement = N'
SELECT shipment_id, carrier_reference, ' + @DynamicPIVOTColumns + '
FROM
(
    SELECT 
        d.shipment_id,
        d.carrier_reference,
        e.phase_key,
        CONVERT(DATETIME, e.time) AS ScanTime -- 提前转换时间，避免重复计算
    FROM data d
    INNER JOIN event e ON d.id = e.data_id
    WHERE d.shipment_id = ''99156136''
) DS
PIVOT
(
    MIN(ScanTime) -- 首次扫描取最早时间，使用MIN聚合
    FOR [phase_key] IN (' + @DynamicPIVOTColumns + ')
) PVT';

EXEC sp_executesql @DynamicTSQLStatement;

三、索引优化建议 #

添加以下索引可以大幅提升查询速度：

• 针对data表的索引，加速按shipment_id过滤和关联：

CREATE NONCLUSTERED INDEX IX_data_shipment_id ON data(shipment_id) INCLUDE(id, carrier_reference);

• 针对event表的索引，加速关联和phase_key、time的查询：

CREATE NONCLUSTERED INDEX IX_event_data_id_phase_time ON event(data_id, phase_key) INCLUDE(time);

四、关键优化点说明 #

1. 动态列过滤：仅获取当前shipment_id对应的phase_key，避免生成大量无关列，减少Pivot的计算量。
2. 聚合函数修正：用MIN(ScanTime)获取首次扫描的最早时间，符合业务需求。
3. 提前转换时间：在子查询中完成time到datetime的转换，避免Pivot时重复执行转换操作。
4. 索引支撑：通过覆盖索引减少表扫描次数，加速数据过滤和关联过程。

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