# sql server连续统计次数问题？

xuminjun 2023-03-14 12:39:23

...全文
139 8 打赏 收藏 转发到动态 举报

8 条回复

• 打赏
• 举报

• 打赏
• 举报

• 打赏
• 举报

``````
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([B] Date,[C] nvarchar(21),[D] nvarchar(22))
Insert #T
select '2023-03-10',N'A',N'香蕉' union all
select '2023-03-11',N'A',N'香蕉' union all
select '2023-03-12',N'A',N'香蕉' union all
select '2023-03-14',N'A',N'香蕉' union ALL
select '2023-03-15',N'A',N'香蕉' union all
select '2023-03-01',N'B',N'香蕉' union all
select '2023-03-02',N'B',N'香蕉' union all
select '2023-03-15',N'B',N'香蕉'
Go
--测试数据结束
;WITH cte AS (
Select *,ROW_NUMBER()OVER(PARTITION BY C ORDER BY B) AS rn from #T
),cteb AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY C,DAY(B)-rn ORDER BY B)  AS rn1 FROM cte
)
SELECT B,C,cteb.D,rn1 AS 次数 FROM cteb

``````
xuminjun 2023-05-18
• 举报

@二月十六 谢谢版主

• 打赏
• 举报

• 打赏
• 举报

1. 最内层判断时间连续性，连续标 0，不连续标 1
2. 对上述标记取 cumsum 作为分组依据
3. 对 2 中分组做行号
``````select *, sum(1) over(partition by name, cumsum order by date) rownum
from
(select *, sum(tag) over(partition by name order by date) cumsum
from
(SELECT *,
case when date = lag(date) over(partition by name order by date) + 1
then 0 else 1 end tag
FROM test) t ) t
```sql
``````
xuminjun 2023-05-18
• 举报

@幸福感奇差 谢谢大佬

34,567

• 近7日
• 近30日
• 至今