SQL-запрос для подсчета самой длинной строки последовательных оценок каждого из 7

Question

У меня есть таблица SQL, которая выглядит следующим образом (с именем в столбце A и счетом в столбце B):

 N.  Name  Score
 1.  Jim   7   
 2.  Jim   4   
 3.  Jim   4   
 4.  Jim   7
 5.  Jim   7
 6.  Jim   7
 7.  Jim   7
 8.  Jim   4
 9.  Ted   4
 10. Ted   4
 11. Ted   7
 12. Ted   7
 .
 .
 .
 n. cont'd

Мне нужно оценить каждого человека по наибольшему количеству последовательных результатов из 7.

Максимум 7 результатов Джима: 4, потому что он получил счет 7 четыре раза подряд. Тед макс 7 результат 2; это его максимальное количество. Если бы я искал макс 4, их оценки были бы равны двум.

Как мне получить функцию SQL-запроса в SSMS, чтобы сказать мне, какая самая длинная строка из 7 баллов (или любое заданное значение) была для каждого человека?

Это образец длинного списка, более 100 миллионов строк с 1 миллионом уникальных имен (на самом деле это уникальные числа, не имена, а в целях иллюстрации ...).

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

Боб 133 Джейн 117 Фил 106 ... Джим 4 Тед 2

Поэтому я думаю, что это начнется с SELECT DISTINCT, поэтому у моего результата есть все уникальные имена.

Answer 1

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

1. Фильтровать данные
2. Внешне сгруппировать записи
3. Создавайте реляционные группы и подсчитывайте результаты

Код

 --Test data
 create table #testRank
 (
      RowNum int identity
    , Name   nvarchar(255)
    , Score  int
 )

 insert #testRank
 values   ('Jim',7)
        , ('Jim',4)
        , ('Jim',4)
        , ('Jim',7)
        , ('Jim',7)
        , ('Jim',7)
        , ('Jim',7)
        , ('Jim',4)
        , ('Ted',4)
        , ('Ted',4)
        , ('Ted',7)
        , ('Ted',7)
        -- I've added a second set of Jim data for testing
        , ('Jim',7)
        , ('Jim',7)
        , ('Jim',7)
        , ('Jim',7)

--minimize the dataset; if your dealing with millions of rows, filter out the scores less than 7
select RowNum, Name 
into #smallerDataSet
from #testRank where score = 7;

--create groups or strings delineated by NULL values indicating the start and end of the string of scores about 7
select 
      x.RowNum
    , x.Name
    , Case when z.NextRow is null then null else x.RowNum-1 end PrevRow
    , Case when y.PrevRow is null then null else x.RowNum+1 end NextRow
into #strings
from #smallerDataSet x
    Left Join (select RowNum-1 PrevRow, Name from #smallerDataSet) y on y.PrevRow = x.RowNum and y.Name = x.Name
    Left Join (select RowNum+1 NextRow, Name from #smallerDataSet) z on z.NextRow = x.RowNum and z.Name = x.Name
Where PrevRow is not null or NextRow is not null

--visulaize the query above
select * from #strings

--use the ROW_NUMBER function to seperate the groups from each other; particularly important when the same name has the same string count
--then simply do the math and order the list
Select Distinct p.Name, EndStr-StartStr+1 MaxString
from
(
    select ROW_NUMBER() over (order by RowNum) GroupNum, Name, RowNum StartStr 
    from #strings
    where PrevRow is null
) p
join
(
    select ROW_NUMBER() over (order by RowNum) GroupNum, Name, RowNum EndStr
    from #strings
    where NextRow is null
) n on n.GroupNum = p.GroupNum and n.Name = p.Name
order by MaxString desc

drop table #strings
drop table #smallerDataSet
drop table #testRank