如何高效生成序列间隔？优化大ID集的API查询压缩方案

我需要通过API查询大量自动计数器类型的数字ID，数据量可达数百万条，仅返回ID的payload就超过100MB。逐行返回每个ID会产生百万级行数据，因此希望通过查找序列中的间隔来压缩结果，返回如下格式的结果：

示例1：快速但不完整的查询 #

以下代码执行速度极快，900万行数据可在1秒内完成，但仅能获取连续区间结束后的下一个ID（即缺失ID的起始），无法得到完整的存在ID区间：

SELECT 
    TransactionNumber + 1   
FROM    TransactionJournal mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    TransactionJournal  mi 
        WHERE   mi.TransactionNumber = mo.TransactionNumber + 1
        )
ORDER BY
        mo.TransactionNumber

示例2：完整但性能低下的查询 #

以下代码能返回完整结果，但执行速度慢，且首行会返回null值。在高性能设备（线程撕裂者、128核、256GB内存）上处理900万行数据耗时15秒，随着数据量增长，会给API消费者带来高延迟和并发成本问题：

SELECT  TransactionNumber - EndId as GapSize,
    LAG(TransactionNumber) OVER (ORDER BY TransactionNumber) StartId,
        EndId
FROM    (
        SELECT  TransactionNumber,
                LAG(TransactionNumber) OVER (ORDER BY TransactionNumber) EndId              
        FROM    TransactionJournal
        ) q
WHERE   EndId <> TransactionNumber - 1
ORDER BY
        TransactionNumber

高效完整的解决方案 #

利用ROW_NUMBER()对连续ID序列分组，既能保证查询性能（900万行数据1秒内完成），又能直接返回完整的存在ID区间，不会出现null值：

WITH numbered AS (
    SELECT 
        TransactionNumber,
        TransactionNumber - ROW_NUMBER() OVER (ORDER BY TransactionNumber) AS group_id
    FROM TransactionJournal
)
SELECT 
    MIN(TransactionNumber) AS StartId,
    MAX(TransactionNumber) AS EndId
FROM numbered
GROUP BY group_id
ORDER BY StartId;

原理说明 #

连续递增的ID值减去对应的行号（ROW_NUMBER()生成的连续序号）会得到相同的group_id；当序列出现间隔时，后续ID减去行号会生成新的group_id。通过按group_id分组，即可快速提取每个连续ID区间的起止值。

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