今天遇到一个帖子的问题,是一个连续数据的问题,具体问题链接,
比如A表,字段B为日期,字段C为人,字段D为买东西。请问怎么写代码达到下面效果。第一天A买东西,统计为一次,第二天A又买了,统计为两次。第三天A没有买,统计为0.第四天买了,统计为一次。只要断了就重新计数。
实现方式主要考虑使用开窗函数,直接上结果:
--测试数据
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
考虑每个日期都要有数的话,可以利用master.dbo.spt_values 获得连续的日期,然后left join实现