TSQL - сложная группировка

Пожалуйста, смотрите скрипку: http://sqlfiddle.com/

У меня есть данные, как показано ниже:

DRIVER  DROP
1       1
1       2
1       ReturnToBase
1       4
1       5
1       ReturnToBase
1       6
1       7    
2       1
2       2
2       ReturnToBase
2       4

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

Мой вывод должен выглядеть так:

DRIVER      DROP           GROUP
1            1              1
1            2              1
1            ReturnToBase   1
1            4              2
1            5              2
1            ReturnToBase   2
1            6              3
1            7              3
1            ReturnToBase   3               
2            1              1           
2            2              1
2            ReturnToBase   1   
2            4              2

Я пытался получить этот результат с помощью комбинации оконных функций, но до сих пор был далеко

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

SELECT 
    ROW_NUMBER() OVER (Partition BY Driver order by Driver Desc) rownum,
    Count(1) OVER (Partition By Driver Order By Driver Desc) counter,
    Count
    DropNo,
    Driver,
    CASE DropNo 
       WHEN 'ReturnToBase' THEN 1 ELSE 0 END AS EnumerateRound
FROM 
    Rounds
Источник

2 ответа

Решение

Вы можете использовать следующий запрос:

SELECT id, DRIVER, DROPno, 
       1 + SUM(flag) OVER (PARTITION BY DRIVER ORDER BY id) -
       CASE 
          WHEN DROPno = 'ReturnToBase' THEN 1 
          ELSE 0 
       END AS grp       
FROM (
  SELECT id, DRIVER, DROPno, 
         CASE 
            WHEN DROPno = 'ReturnToBase' THEN 1 
            ELSE 0 
         END AS flag
  FROM rounds ) AS t

Демо здесь

Этот запрос использует оконную версию SUM с ORDER BY в OVER пункт для расчета промежуточного итога. Эта версия SUM доступно с SQL Server 2012 года AFAIK.

Немного поиграться с этим промежуточным значением - это все, что нам нужно, чтобы получить правильное значение. GROUP значение.

РЕДАКТИРОВАТЬ: (кредит переходит к @Conrad Frix)

С помощью CROSS APPLY вместо линейного представления можно значительно упростить вещи:

SELECT id, DRIVER, DROPno, 
       1 + SUM(x.flag) OVER (PARTITION BY DRIVER ORDER BY id) - x.flag
FROM rounds
CROSS APPLY (SELECT CASE WHEN DROPno = 'ReturnToBase' THEN 1 ELSE 0 END) AS x(flag)

Демо здесь

Источник

В ваш пример добавлен столбец с последовательным идентификатором для использования в рекурсивном CTE:

with cte as (
select ID,DRIVER,DROPno,1 as GRP
FROM rounds
where ID = 1
union all
select a.ID
,a.DRIVER
,a.DROPno
,case when b.DROPno = 'ReturnToBase' 
      or b.DRIVER <> a.DRIVER then b.GRP + 1 
      else b.GRP end
from rounds a
  inner join cte b
    on a.ID = b.ID + 1
)

select * from cte

SQL Fiddle

Источник
Другие вопросы по тегам