NBA 连胜连败用 SQL 如何写的

NBA 全队连胜连败记录

请看下图中的“连胜/负”这一列。这列数据是如何计算出来的呢？今天咱就说个清清楚楚，明明白白。

上代码

with detail as (
  select 'LA' as team_name , 1 as rs , '2020-01-01' as date_d
  union all select 'LA' as team_name , 1 as rs, '2020-01-02' as date_d
  union all select 'LA' as team_name , 0 as rs, '2020-01-03' as date_d
  union all select 'LA' as team_name , 0 as rs, '2020-01-04' as date_d
  union all select 'LA' as team_name , 1 as rs, '2020-01-05' as date_d
  union all select 'LA' as team_name , 1 as rs, '2020-01-06' as date_d
  union all select 'LA' as team_name , 0 as rs, '2020-01-07' as date_d
  union all select 'LA' as team_name , 1 as rs, '2020-01-08' as date_d

), detail1 as (
	select team_name 
		  ,rs  
		  ,row_number() over( partition by team_name , rs order by date_d) as sub_rn 
		  ,row_number() over(partition by team_name order by date_d) as all_rn
		  ,date_d
	from detail  
), detail2 as (
	select date_d ,team_name , rs , all_rn - sub_rn as D_value
	from detail1
) , detail3 as (
	select date_d ,team_name , rs
		 , row_number() over(partition by team_name , rs , D_value order by date_d ) as rn_number
	from detail2
)
select date_d ,team_name , rs
      ,case when rs = 1 then  cast(rn_number as varchar(50)) + ' 连胜' 
	        else cast(rn_number as varchar(50)) + '连败' end as aaaaa
from detail3
order by team_name , date_d 

分析思路，请看我是链接

我先对整体做一个等差数列，然后对连胜和连败做子等差数列，这样就获得了连胜连败的分组，然后在分组里面做 row_number ,获得的序号就是连胜和连败数。
打完收工。