在 SQL 中选择具有多个 GROUP 的表中的 TOP 2 值的总和 [英] Selecting SUM of TOP 2 values within a table with multiple GROUP in SQL

问题描述

我一直在使用 SQL Server 2000 中的集合，并且我的一个临时表 (#Periods) 具有以下表结构:

<上一页>RestCTR HoursCTR Duration Rest--------------------------------------1 337 2 02 337 46 13 337 2 04 337 46 15 338 1 06 338 46 17 338 2 08 338 46 19 338 1 010 339 46 1...

我想做的是计算每个 HoursCTR 的 2 个最长休息时间的总和，最好使用集合和临时表(而不是游标或嵌套子查询).

这是在 SQL 中无法运行的梦想查询(无论我运行多少次):

选择 HoursCTR, SUM (TOP 2 Duration) 作为 LongestBreaks从#Periods哪里休息 = 1按小时分组点击率

HoursCTR 可以有任意数量的休息期(包括没有).

我目前的解决方案不是很优雅，基本上涉及以下步骤:

1. 获取最长休息时间，按 HoursCTR 分组
2. 选择返回每个 HoursCTR 的最大持续时间的第一个(最小)RestCTR 行
3. 重复第 1 步(不包括第 2 步中已收集的行)
4. 重复第 2 步(同样，不包括第 2 步中收集的行)
5. 将 RestCTR 行(来自第 2 步和第 4 步)合并到单个表中
6. 获取第 5 步中的行所指向的持续时间的总和，按 HoursCTR 分组

如果有任何设置功能可以减少此过程，他们将非常受欢迎.

解决方案

在 SQL Server 中执行此操作的最佳方法是使用 普通表表达式，用开窗函数ROW_NUMBER():

与 NumberedPeriods AS (选择 HoursCTR、持续时间、ROW_NUMBER()OVER (PARTITION BY HoursCTR ORDER BY Duration DESC) AS RN从#Periods哪里休息 = 1)SELECT HoursCTR, SUM(Duration) AS LongestBreaksFROM NumberedPeriods其中 RN <= 2GROUP BY HoursCTR

我在分区中添加了一个 ORDER BY 子句，以获得两个最长的休息.

<小时>

过失，我没有注意到您需要它才能在 Microsoft SQL Server 2000 中工作.该版本不支持 CTE 或窗口函数.我会在上面留下答案，以防对其他人有帮助.

在 SQL Server 2000 中，常见的建议是使用相关子查询:

SELECT p1.HoursCTR, (SELECT SUM(t.Duration) FROM(SELECT TOP 2 p2.Duration FROM #Periods AS p2其中 p2.HoursCTR = p1.HoursCTRORDER BY p2.Duration DESC) AS t) AS LongestBreaks从 #Periods AS p1

I've been playing with sets in SQL Server 2000 and have the following table structure for one of my temp tables (#Periods):

    RestCTR     HoursCTR    Duration    Rest
    ----------------------------------------
    1           337         2           0
    2           337         46          1
    3           337         2           0
    4           337         46          1
    5           338         1           0
    6           338         46          1
    7           338         2           0
    8           338         46          1
    9           338         1           0
    10          339         46          1
    ...

What I'd like to do is to calculate the Sum of the 2 longest Rest periods for each HoursCTR, preferably using sets and temp tables (rather than cursors, or nested subqueries).

Here's the dream query that just won't work in SQL (no matter how many times I run it):

Select HoursCTR, SUM ( TOP 2 Duration ) as LongestBreaks
FROM #Periods
WHERE Rest = 1
Group By HoursCTR    

The HoursCTR can have any number of Rest periods (including none).

My current solution is not very elegant and basically involves the following steps:

1. Get the max duration of rest, group by HoursCTR
2. Select the first (min) RestCTR row that returns this max duration for each HoursCTR
3. Repeat step 1 (excluding the rows already collected in step 2)
4. Repeat step 2 (again, excluding rows collected in step 2)
5. Combine the RestCTR rows (from step 2 and 4) into single table
6. Get SUM of the Duration pointed to by the rows in step 5, grouped by HoursCTR

If there are any set functions that cut this process down, they would be very welcome.

解决方案

The best way to do this in SQL Server is with a common table expression, numbering the rows in each group with the windowing function ROW_NUMBER():

WITH NumberedPeriods AS (
  SELECT HoursCTR, Duration, ROW_NUMBER() 
    OVER (PARTITION BY HoursCTR ORDER BY Duration DESC) AS RN
  FROM #Periods
  WHERE Rest = 1
)
SELECT HoursCTR, SUM(Duration) AS LongestBreaks
FROM NumberedPeriods
WHERE RN <= 2
GROUP BY HoursCTR

edit: I've added an ORDER BY clause in the partitioning, to get the two longest rests.

---

Mea culpa, I did not notice that you need this to work in Microsoft SQL Server 2000. That version doesn't support CTE's or windowing functions. I'll leave the answer above in case it helps someone else.

In SQL Server 2000, the common advice is to use a correlated subquery:

SELECT p1.HoursCTR, (SELECT SUM(t.Duration) FROM 
    (SELECT TOP 2 p2.Duration FROM #Periods AS p2
     WHERE p2.HoursCTR = p1.HoursCTR 
     ORDER BY p2.Duration DESC) AS t) AS LongestBreaks
FROM #Periods AS p1

这篇关于在 SQL 中选择具有多个 GROUP 的表中的 TOP 2 值的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！