SQL Server 2017中按QtchainId计算符合条件的Curr_num总和

现有查询 #

我正在使用SQL Server 2017，现有如下查询：

SELECT
  Id,
  QtchainId,
  QtimeLoopId,
  THRESHOLDNUM,
  MIN(THRESHOLDNUM) OVER (PARTITION BY QtchainId) AS leastinqtime_chain
FROM
  your_table_name;

查询返回的原始数据 #

该查询返回结果如下：

需求说明 #

需要新增一个result列，该列的值为同一QtchainId分组中，所有Thresholdnum大于等于组内最小Thresholdnum的Curr_num的总和。具体规则示例：

• QtchainId为3时，最小Thresholdnum是642，result为25+276=301
• QtchainId为5时，最小Thresholdnum是63，result为2+25+0+354=381

预期输出如下：

尝试过的错误查询 #

我尝试了以下CTE查询，但结果错误（会累加所有Curr_num）且运行耗时较长：

WITH MinCurrentNumWfrs AS (
  SELECT
    QtchainId,
    MIN(threshold_num) AS leastinqtime_chain
  FROM
    your_table_name
  GROUP BY
    QtchainId
)
SELECT  t1.Id,  t1.QtchainId,  t1.QtimeLoopId,  t1.threshold_num,  t1.Curr_num,  t2.leastinqtime_chain,  SUM(t3.Curr_num) AS wafercount
FROM  your_table_name t1
JOIN
  MinCurrentNumWfrs t2 ON t1.QtchainId = t2.QtchainId
LEFT JOIN
  your_table_name t3 ON t1.QtchainId = t3.QtchainId
                    AND t3.threshold_num >= t2.leastinqtime_chain
GROUP BY
  t1.Id, t1.QtchainId, t1.QtimeLoopId, t1.threshold_num, t1.Curr_num, t2.leastinqt_chain;

解决方案 #

可以用窗口函数结合条件聚合实现，无需多表连接，效率更高：

SELECT
  Id,
  QtchainId,
  QtimeLoopId,
  THRESHOLDNUM,
  Curr_num,
  SUM(CASE WHEN THRESHOLDNUM >= MIN_THRESHOLD THEN Curr_num ELSE 0 END) OVER (PARTITION BY QtchainId) AS result
FROM (
  SELECT
    *,
    MIN(THRESHOLDNUM) OVER (PARTITION BY QtchainId) AS MIN_THRESHOLD
  FROM your_table_name
) t;

说明 #

1. 内层子查询先通过窗口函数MIN(THRESHOLDNUM) OVER (PARTITION BY QtchainId)计算每个QtchainId分组的最小Thresholdnum，命名为MIN_THRESHOLD。
2. 外层查询使用SUM(CASE...) OVER (PARTITION BY QtchainId)，对分组内THRESHOLDNUM大于等于MIN_THRESHOLD的Curr_num进行求和，得到每个分组对应的result值，并且每行都会重复该分组的结果，符合预期输出。

这种方式只需要扫描表一次，相比原查询的多表连接，性能更优，逻辑也更简洁。

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