SQL查询优化需求：优先检索非空TrackId条目的高性能实现

需求说明 #

需要实现以下逻辑：

• 当某UniqueID存在非空TrackId条目时，仅保留该UniqueID下的非空TrackId条目
• 若某UniqueID不存在任何非空TrackId条目，则保留其空TrackId条目

当前已通过UNION ALL实现该逻辑，但在大数据量场景下担忧性能问题，需要更高效的写法。

现有数据示例 #

原实现方案（UNION ALL） #

SELECT A.* FROM 
(  SELECT *  FROM [MY_PKG].[TEMP] WHERE TRACKID is not null) A
        WHERE A.UNIQUEID in 
        ( SELECT UNIQUEID FROM [MY_PKG].[TEMP] WHERE TRACKID is null
)
UNION ALL
SELECT B.* FROM
( SELECT * FROM [MY_PKG].[TEMP] WHERE TRACKID is null) B
        WHERE B.UNIQUEID not in 
        (  SELECT UNIQUEID FROM [MY_PKG].[TEMP] WHERE TRACKID is not null
)

这个方案需要多次扫描表，大数据量下IO开销会很高，性能瓶颈明显。

优化方案：使用窗口函数（仅扫描一次表） #

利用窗口函数COUNT()按UniqueID统计非空TrackId的数量，再根据统计结果筛选数据，全程只需要扫描一次表，性能大幅提升：

WITH unique_track_stats AS (
    SELECT 
        *,
        COUNT(TRACKID) OVER (PARTITION BY UNIQUEID) AS non_null_track_count
    FROM MY_PKG.TEMP
)
SELECT RowID, UniqueID, TrackId
FROM unique_track_stats
WHERE 
    -- 如果有非空TrackId，只保留非空条目
    (non_null_track_count > 0 AND TRACKID IS NOT NULL)
    -- 如果没有非空TrackId，保留空条目
    OR (non_null_track_count = 0 AND TRACKID IS NULL);

逻辑解释 #

1. COUNT(TRACKID)会自动忽略NULL值，所以non_null_track_count就是当前UniqueID下非空TrackId的总数
2. 筛选条件：
   • 当non_null_track_count > 0时，只保留TRACKID IS NOT NULL的行
   • 当non_null_track_count = 0时，保留TRACKID IS NULL的行

测试用表及数据脚本 #

CREATE TABLE MY_PKG.TEMP
(   RowID     INT IDENTITY(1,1) PRIMARY KEY, -- 补充RowID字段，与数据示例对应
    UNIQUEID  varchar(3),
    TRACKID   varchar(5)
);

INSERT INTO MY_PKG.TEMP
( UNIQUEID, TRACKID)
VALUES
('325',null),
('325','8zUAC'),
('325','99XER'),
('427',null),
('632','2kYCV'),
('533',null),
('774',null),
('774','94UAC');

性能优势对比 #

• 原方案：至少需要3次全表扫描（两次子查询+UNION ALL的两次扫描），大数据量下IO成本极高
• 优化方案：仅1次全表扫描+窗口函数计算，计算逻辑在内存中完成，IO开销和执行时间都显著降低

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