Выбор суммы значений TOP 2 в таблице с несколькими GROUP в SQL

Я играл с наборами в SQL Server 2000 и имею следующую структуру таблицы для одной из моих временных таблиц (#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
    ...

Я хотел бы рассчитать сумму 2-х самых длинных периодов отдыха для каждого HoursCTR, предпочтительно используя наборы и временные таблицы (а не курсоры или вложенные подзапросы).

Вот запрос мечты, который просто не будет работать в SQL (независимо от того, сколько раз я его запускаю):

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

HoursCTR может иметь любое количество периодов отдыха (в том числе ни одного).

Мое текущее решение не очень элегантно и в основном включает в себя следующие шаги:

Получить максимальную продолжительность отдыха, группа по HoursCTRВыберите первую (минимальную) строку RestCTR, которая возвращает эту максимальную продолжительность для каждого HoursCTRПовторите шаг 1 (за исключением строк, уже собранных на шаге 2)Повторите шаг 2 (снова, за исключением строк, собранных на шаге 2)Объедините строки RestCTR (из шагов 2 и 4) в одну таблицуПолучите СУММУ Длительности, на которую указывают строки в шаге 5, сгруппированные по HoursCTR

Если есть какие-либо установленные функции, которые сокращают этот процесс, они будут очень приветствоваться.

Ответы на вопрос(3)

ROW_NUMBER().
Коррелированные подзапросы могут потребовать дополнительного шага при использованииgroup by.

Это должно работать для вас:

SELECT 
    F.HoursCTR,
    MAX (F.LongestBreaks) AS LongestBreaks -- Dummy max() so that groupby can be used.
FROM
    (
        SELECT 
            Pm.HoursCTR, 
            (
                SELECT 
                    COALESCE (SUM (S.Duration), 0)    
                FROM 
                    (
                        SELECT TOP 2    T.Duration
                        FROM            #Periods    AS T
                        WHERE           T.HoursCTR  = Pm.HoursCTR 
                        AND             T.Rest      = 1
                        ORDER BY        T.Duration  DESC
                    ) AS S
             ) AS LongestBreaks
        FROM
            #Periods AS Pm
    ) AS F
GROUP BY
    F.HoursCTR

Лучший способ сделать это в SQL Server с помощьюобщее табличное выражениенумерация строк в каждой группе с помощью функции управления окнами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

редактировать: Я добавил в раздел предложение 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
     WHERE p2.HoursCTR = p1.HoursCTR 
     ORDER BY p2.Duration DESC) AS t) AS LongestBreaks
FROM #Periods AS p1
 Bill Karwin30 сент. 2010 г., 19:02
Возможно, потому что я забыл дать внутреннему подзапросу псевдоним таблицы. Я отредактирую это. Извините, у меня нет копии MS SQL Server 2000 для тестирования.
 bobs29 сент. 2010 г., 18:59
OP указывает на SQL Server 2000. ROW_NUMBER () и CTE недоступны.
 Arkadiy29 сент. 2010 г., 18:59
как он выбирает двасамый длинный перерывы? Что мне не хватает?
 alex.zambila30 сент. 2010 г., 18:31
Спасибо, Билл, поскольку это вложенный (коррелированный) подзапрос, я пытался его избежать, но если это не слишком много попаданий, я бы его рассмотрел. Сказав это, я не могу заставить ваше предложение работать вообще. Не могли бы вы взглянуть на синтаксис и посмотреть, какие заклинания необходимо сделать, чтобы SQL Server 2000 принял это предложение?
 Bill Karwin29 сент. 2010 г., 19:25
@bobs: Спасибо, я пропустил это. Я добавил другое решение.

коррелированные подзапросы, в зависимости от их структуры, будут запускаться несколько раз, что потенциально даст вам сотни отдельных запросов.

Поместите текущее решение в Query Analyzer, включите «Показать план выполнения» (Ctrl + K) и запустите его. Внизу у вас будет дополнительная вкладка, которая покажет вам, как движок собирал ваши результаты. Если вы сделаете то же самое с коррелированным подзапросом, вы увидите, что делает эта опция.

Я полагаю, что это может привести к тому, что таблица #Periods будет забивать столько раз, сколько отдельных строк в этой таблице.

Кроме того, мне кажется, что-то не так с коррелированным подзапросом. Поскольку я избегаю их как чумы, зная, что они злые, я не знаю, как это исправить.

 alex.zambila30 сент. 2010 г., 18:33
Благодаря Дэвиду, я надеялся, что кто-то узнает о хитрых хитростях, которые я не обнаружил, но похоже, что этот 6-шаговый процесс может стать концом строки.
 David T. Macknet30 сент. 2010 г., 21:58
Ну ... вы говорите движку базы данных, что и как нужно делать, что на самом деле не так уж и плохо. Двигатель не такой яркий, когда дело доходит до него. Откуда ему знать, что вы не хотите, чтобы он превратился в итеративный мусор, если вы скажете это сделать?

Ваш ответ на вопрос