Consulta SQL para contar a sequência mais longa de 7 caracteres consecutivos de cada pessoa

Question

Eu tenho uma tabela SQL que se parece com isso (com o nome na coluna A e a pontuação na coluna 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

Preciso classificar cada pessoa pelo maior número de resultados consecutivos de 7.

Resultado máximo de Jim 7: 4, porque ele conseguiu 7 pontos quatro vezes seguidas. Ted's max 7 resulta 2; esse é o seu número máximo. Se eu estivesse procurando pelo máximo de 4s, suas pontuações seriam duas.

Como obtenho a função de consulta SQL no SSMS para me informar qual foi a string de pontuação mais longa de 7 (ou qualquer valor fornecido) para cada pessoa?

Este é um exemplo de uma lista longa, com mais de 100 milhões de linhas, com 1 milhão de nomes únicos (na verdade, são números únicos, não nomes, mas para fins ilustrativos ...).

Idealmente, a consulta retornará uma lista semelhante a esta:

Bob 133 Jane 117 Phil 106 ... Jim 4 Ted 2

Então eu acho que vai começar com SELECT DISTINCT, então meu resultado tem todos os nomes exclusivos.

    

Answer 1

Aqui está o código que eu criei. Você poderia colocá-lo em uma grande consulta, mas eu o dividi em tabelas temporárias para maior clareza, para que você possa adaptá-lo mais facilmente. Basicamente, você precisará de um processo de várias etapas

1. Filtre os dados
2. Agrupe externamente os registros
3. Crie grupos relacionais e conte os resultados

Código

 --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