SQL Server超大规模表百分位计算性能优化求助

问题背景 #

有一张1亿+记录的SQL Server大表，表结构及示例数据如下：

+-----------+----------+----------+----------+----------+
|CustomerID |TransDate |Category | Num_Trans |Sum_Trans |
+-----------+----------+----------+----------+----------+
|457136432  |2022-12-31|TAXI     |18         |220.34    |
|863326783  |2022-12-31|FOOD     |76         |980.71    |
+-----------+----------+----------+----------+----------+

该表存储每个客户在TransDate之前6个月窗口内，各交易分类的交易数量（Num_Trans）和交易金额总和（Sum_Trans）。

为生成报表需计算百分位，执行的查询语句如下（修正原语句的语法错误）：

SELECT DISTINCT  
    TransDate, 
    Category,
    PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY Num_Trans) OVER (PARTITION BY TransDate, Category) AS P_LOWER_NUM,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Num_Trans) OVER (PARTITION BY TransDate, Category) AS P_MEDIAN_NUM,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY Num_Trans) OVER (PARTITION BY TransDate, Category) AS P_UPPER_NUM,
    PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY Sum_Trans) OVER (PARTITION BY TransDate, Category) AS P_LOWER_SUM,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Sum_Trans) OVER (PARTITION BY TransDate, Category) AS P_MEDIAN_SUM,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY Sum_Trans) OVER (PARTITION BY TransDate, Category) AS P_UPPER_SUM
FROM large_table

已创建非聚集索引：

CREATE NONCLUSTERED INDEX MyIndex 
ON large_table (TransDate, Category) 
INCLUDE (Num_Trans, Sum_Trans);

但查询执行时间超过30分钟，尝试按TransDate分区并创建分区索引后性能无显著提升。执行计划显示每个PERCENTILE_CONT计算都需单独排序，需优化计算速度，实现仅对Num_Trans和Sum_Trans各排序一次完成所有百分位计算。

优化方案 #

1. 预排序复用+手动计算百分位 #

SQL Server中PERCENTILE_CONT的WITHIN GROUP子句排序逻辑独立，无法直接复用，但可以通过预计算分区内的排序序列，手动推导百分位值，实现仅排序两次：

WITH RankedData AS (
    SELECT 
        TransDate,
        Category,
        Num_Trans,
        Sum_Trans,
        -- 预计算Num_Trans的排序行号和分区总记录数
        ROW_NUMBER() OVER (PARTITION BY TransDate, Category ORDER BY Num_Trans) AS RN_NUM,
        COUNT(*) OVER (PARTITION BY TransDate, Category) AS CNT_NUM,
        -- 预计算Sum_Trans的排序行号和分区总记录数
        ROW_NUMBER() OVER (PARTITION BY TransDate, Category ORDER BY Sum_Trans) AS RN_SUM,
        COUNT(*) OVER (PARTITION BY TransDate, Category) AS CNT_SUM
    FROM large_table
), PercentileCalcs AS (
    SELECT 
        TransDate,
        Category,
        -- 基于预排序结果计算Num_Trans的百分位（连续型逻辑）
        MAX(CASE WHEN RN_NUM = CEILING((CNT_NUM - 1)*0.05 + 1) THEN Num_Trans END) OVER (PARTITION BY TransDate, Category) AS P_LOWER_NUM,
        MAX(CASE WHEN RN_NUM = CEILING((CNT_NUM - 1)*0.5 + 1) THEN Num_Trans END) OVER (PARTITION BY TransDate, Category) AS P_MEDIAN_NUM,
        MAX(CASE WHEN RN_NUM = CEILING((CNT_NUM - 1)*0.95 + 1) THEN Num_Trans END) OVER (PARTITION BY TransDate, Category) AS P_UPPER_NUM,
        -- 基于预排序结果计算Sum_Trans的百分位（连续型逻辑）
        MAX(CASE WHEN RN_SUM = CEILING((CNT_SUM - 1)*0.05 + 1) THEN Sum_Trans END) OVER (PARTITION BY TransDate, Category) AS P_LOWER_SUM,
        MAX(CASE WHEN RN_SUM = CEILING((CNT_SUM - 1)*0.5 + 1) THEN Sum_Trans END) OVER (PARTITION BY TransDate, Category) AS P_MEDIAN_SUM,
        MAX(CASE WHEN RN_SUM = CEILING((CNT_SUM - 1)*0.95 + 1) THEN Sum_Trans END) OVER (PARTITION BY TransDate, Category) AS P_UPPER_SUM
    FROM RankedData
)
SELECT DISTINCT 
    TransDate, Category,
    P_LOWER_NUM, P_MEDIAN_NUM, P_UPPER_NUM,
    P_LOWER_SUM, P_MEDIAN_SUM, P_UPPER_SUM
FROM PercentileCalcs;

该方案仅通过ROW_NUMBER()对两列各排序一次，后续百分位计算基于已生成的行号完成，避免了多次排序开销。

2. 优化索引结构 #

当前索引未包含排序字段，无法直接提供有序数据。调整为覆盖排序的索引，让数据库直接扫描索引获取有序结果：

DROP INDEX IF EXISTS MyIndex ON large_table;
-- 针对Num_Trans排序的覆盖索引
CREATE NONCLUSTERED INDEX MyOptimizedIndex_Num
ON large_table (TransDate, Category, Num_Trans)
INCLUDE (Sum_Trans);
-- 针对Sum_Trans排序的覆盖索引
CREATE NONCLUSTERED INDEX MyOptimizedIndex_Sum
ON large_table (TransDate, Category, Sum_Trans)
INCLUDE (Num_Trans);

索引按TransDate, Category分区后直接排序目标字段，数据库无需额外排序即可获取计算百分位所需的有序数据集。

3. 改用离散型百分位函数（业务允许时） #

如果业务可以接受取实际存在的行值（无需插值），PERCENTILE_DISC性能优于PERCENTILE_CONT，结合优化后的索引可快速定位目标行：

SELECT DISTINCT  
    TransDate, 
    Category,
    PERCENTILE_DISC(0.05) WITHIN GROUP (ORDER BY Num_Trans) OVER (PARTITION BY TransDate, Category) AS P_LOWER_NUM,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Num_Trans) OVER (PARTITION BY TransDate, Category) AS P_MEDIAN_NUM,
    PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY Num_Trans) OVER (PARTITION BY TransDate, Category) AS P_UPPER_NUM,
    PERCENTILE_DISC(0.05) WITHIN GROUP (ORDER BY Sum_Trans) OVER (PARTITION BY TransDate, Category) AS P_LOWER_SUM,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Sum_Trans) OVER (PARTITION BY TransDate, Category) AS P_MEDIAN_SUM,
    PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY Sum_Trans) OVER (PARTITION BY TransDate, Category) AS P_UPPER_SUM
FROM large_table

4. 离线批量计算汇总表 #

若报表无需实时计算，可将结果预先存储到汇总表，后续直接查询汇总表：

-- 创建汇总表
CREATE TABLE PercentileSummary (
    TransDate DATE,
    Category VARCHAR(50),
    P_LOWER_NUM DECIMAL(18,2),
    P_MEDIAN_NUM DECIMAL(18,2),
    P_UPPER_NUM DECIMAL(18,2),
    P_LOWER_SUM DECIMAL(18,2),
    P_MEDIAN_SUM DECIMAL(18,2),
    P_UPPER_SUM DECIMAL(18,2),
    PRIMARY KEY (TransDate, Category)
);

-- 按时间段批量插入计算结果
INSERT INTO PercentileSummary
SELECT 
    TransDate, 
    Category,
    PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY Num_Trans) OVER (PARTITION BY TransDate, Category) AS P_LOWER_NUM,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Num_Trans) OVER (PARTITION BY TransDate, Category) AS P_MEDIAN_NUM,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY Num_Trans) OVER (PARTITION BY TransDate, Category) AS P_UPPER_NUM,
    PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY Sum_Trans) OVER (PARTITION BY TransDate, Category) AS P_LOWER_SUM,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Sum_Trans) OVER (PARTITION BY TransDate, Category) AS P_MEDIAN_SUM,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY Sum_Trans) OVER (PARTITION BY TransDate, Category) AS P_UPPER_SUM
FROM large_table
WHERE TransDate BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY TransDate, Category;

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