用SQL计算连续重复字段

这是一个 缺口和岛屿 “问题是,你可以尝试以下方法。

;with cte 
     as (select *, 
                dateadd(day, -row_number() 
                                over (partition by status, personid 
                                  order by [date] ), [date]) AS grp 
         FROM   @table
     )
     ,cte1 
     AS (select *,row_number() over(partition by  personid, grp,status order by [date]) rn,
                count(*) over(partition by personid, grp) ct 
         from   cte
        ) 

select  ct as count, personid 
from cte1 
where rn=1

Online Demo

注: 您可能无法按相同的顺序获取行,因为您没有任何列可用于按照所需输出中显示的方式进行排序。

这种类型的问题称为 缺口和岛屿 “。您可以标识连续的数据集(孤岛),也可以标识两个孤岛之间的值范围(间隙)。有许多不同的方法可以实现同样适用于大型数据集的结果。你可以参考下面写得很好的文章。

https://www.itprotoday.com/sql-server/solving-gaps-and-islands-enhanced-window-functions

https://www.red-gate.com/simple-talk/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/

https://www.sqlshack.com/data-boundaries-finding-gaps-islands-and-more/

下面是对你的问题的尝试。

CREATE TABLE #test 
(
     dt DATETIME
    ,Status INT
    ,PersonID INT
)

INSERT INTO #Test (dt, Status, PersonID) VALUES
('2018/01/01', 2, 2015),
('2018/01/02', 2, 2015),
('2018/01/05', 2, 2015),
('2018/01/06', 2, 2015),
('2018/01/07', 2, 2015),
('2018/01/11', 2, 2015),
('2018/01/01', 2, 1018),
('2018/01/03', 2, 1018),
('2018/01/05', 2, 1018),
('2018/01/06', 2, 1018),
('2018/01/08', 2, 1018),
('2018/01/09', 2, 1018),
('2018/01/03', 2, 1625),
('2018/01/04', 2, 1625),
('2018/01/05', 2, 1625),
('2018/01/06', 2, 1625),
('2018/01/17', 2, 1625),
('2018/01/29', 2, 1625)

;with cte_dt_from
AS
(
    SELECT PersonID, MIN(Dt) dt_from_start
    FROM #Test
    GROUP BY PersonID
),
cte_offset_num
AS
(
SELECT      T1.PersonID, T1.dt, DATEDIFF(DAY, T2.dt_from_start, T1.dt) dt_offset
FROM        #test T1
INNER JOIN  cte_dt_from T2 ON T2.PersonID = T1.PersonID
),
cte_starting_point
AS
(
    SELECT A.PersonID, A.dt_offset, ROW_NUMBER() OVER(PARTITION BY A.PersonID ORDER BY A.dt_offset) AS rownum
    FROM cte_offset_num AS A
    WHERE NOT EXISTS (
        SELECT *
        FROM cte_offset_num AS B
        WHERE B.PersonID = A.PersonID AND B.dt_offset = A.dt_offset - 1)
)
,
cte_ending_point
AS
(
    SELECT A.PersonID, A.dt_offset, ROW_NUMBER() OVER(PARTITION BY A.PersonID ORDER BY A.dt_offset) AS rownum
    FROM cte_offset_num AS A
    WHERE NOT EXISTS (
        SELECT *
        FROM cte_offset_num AS B
        WHERE B.PersonID = A.PersonID AND B.dt_offset = A.dt_offset + 1)
)
SELECT (E.dt_offset - S.dt_offset)  + 1 AS [count], S.PersonID
FROM cte_starting_point AS S
JOIN cte_ending_point AS E ON E.PersonID = S.PersonID AND E.rownum = S.rownum
ORDER BY S.PersonID;

DROP TABLE #Test;

下面是最简单的小查询

 CREATE TABLE #T (
      [Date] date,
      [Status] int,
      PersonId int
    );
    INSERT #T
      VALUES ('2018/01/01', 2, 2015),
      ('2018/01/02', 2, 2015),
      ('2018/01/05', 2, 2015),
      ('2018/01/06', 2, 2015),
      ('2018/01/07', 2, 2015),
      ('2018/01/11', 2, 2015),
      ('2018/01/01', 2, 1018),
      ('2018/01/03', 2, 1018),
      ('2018/01/05', 2, 1018),
      ('2018/01/06', 2, 1018),
      ('2018/01/08', 2, 1018),
      ('2018/01/09', 2, 1018),
      ('2018/01/03', 2, 1625),
      ('2018/01/04', 2, 1625),
      ('2018/01/05', 2, 1625),
      ('2018/01/06', 2, 1625),
      ('2018/01/17', 2, 1625),
      ('2018/01/29', 2, 1625)


    SELECT
      MAX(cnt),
      personid
    FROM (SELECT
      ROW_NUMBER() OVER (PARTITION BY GRP ORDER BY [Date]) AS cnt,
      personid,
      GRP
    FROM (SELECT
      personid,
      [Date],
      DATEDIFF(DAY, '1900-01-01', [Date]) - ROW_NUMBER() OVER (ORDER BY Personid DESC) AS GRP
    FROM #T) A) AS B
    GROUP BY personid,
             GRP
    ORDER BY PersonId DESC

主要的挑战是找出两个日期之间的差距,以及关于每个日期,您可以使用 row_number() 解析函数及其应用 datediff 作用

with cte as
(

select '2018-01-01' as d, 2 as id , 2015 as pid
union all
select '2018-01-02',2,2015
union all
select '2018-01-05',2,2015 union all
select '2018-01-06',2,2015 union all
select '2018-01-07',2,2015 
union all
select '2018-01-11',2,2015  


), cte1 as (SELECT *, 
                datediff(day, Row_number() 
                                OVER ( 
                                  partition BY id, pid 
                                  ORDER BY [d] ), [d]) AS dif
         FROM   cte
         ) select distinct pid,count(*) over(partition by pid,dif) as cnt from cte1

WITH T1 AS
(SELECT Date,
       Date - ROW_NUMBER() OVER (PARTITION BY Status, PersonID ORDER BY Date) AS Grp
FROM myTable)
SELECT personid,
       ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY Date) AS Consecutive
FROM T1

在这个结果上,你可以应用 MAX() ,以获取每个personid的记录数。

参考 this 问题:获取细分细节