☰

SQL 查询计算每个人最长的连续 7 个分数串

tag-icon • tag-icon sql-server sql ssms
SQL 查询计算每个人最长的连续 7 个分数串

我有一个如下所示的 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，他们的分数都是 2。

如何让 SSMS 中的 SQL 查询函数告诉我每个人的最长 7 分字符串（或任何给定值）是多少？

这是一份长列表的示例，超过 1 亿行，有 100 万个唯一名称（它们实际上是唯一的数字，而不是名称，但用于说明目的......）。

理想情况下，查询将返回如下列表：

鲍勃 133 简 117 菲尔 106 ... 吉姆 4 泰德 2

所以我认为它将以 SELECT DISTINCT 开始，所以我的结果包含所有唯一的名称。

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

相关内容