我需要SQL查询表中id的滚动90天不同计数，仅计数出现3次或更多次(至少3次交互)的id

我有一个'interactions'表，基本上每次id交互时都有一行。所以可以有很多重复的id:

tbody> <<tr>

id	dateinteracted
1651	9月15日,2017年,下午3:07
1366	6月28日,2017年,32点
1366	7月3日,2017年,第一点
1366	7月3日,2017年,22点
1212	8月24日,2017年,下午1:05
1366	9月18日,2017年,上午9:15

我将假设没有窗口函数的解决方案。这个想法是自连接表，并对每条记录和每个日期的记录进行计数。

create table interactions( id int,  dateinteracted datetime);
insert into interactions values
(1401,'2017-05-29 09:16:00')
,(1401,'2017-06-28 09:11:00')
,(1501,'2017-06-02 09:16:00')
,(1366,'2017-07-03 18:26:00')
,(1366,'2017-07-03 18:22:00')
,(1366,'2017-07-28 20:32:00')
,(1401,'2017-07-28 09:11:00')
,(1212,'2017-08-24 13:05:00')
,(1651,'2017-09-15 15:07:00')
,(1366,'2017-09-18 09:15:00')
,(1366,'2017-10-02 09:16:00')
,(1401,'2017-10-02 09:16:00')
;
with gr as(
select t1.id,t1.dateinteracted
,count(*) cnt
from interactions t1
left join interactions t2 
on t1.id=t2.id 
and t2.dateinteracted<t1.dateinteracted 
and t2.dateinteracted>=(t1.dateinteracted-90)
group by t1.id,t1.dateinteracted
)
select cast(dateinteracted as date) cdate
,sum(case when cnt>=2 then 1 else 0 end) cnt_in_day
from gr
where cnt>=2
group by cast(dateinteracted as date)

Cte结果

2017-07-03 18:22:00.0002017-07-03 18:26:00.0002017-07-28 20:32 .002017-09-18 09:15:00.00032017-10-02 09:16:00.0002017-05-29 09:16:00.0002017-06-28 09:11:00.0002017-07-28 09:11:00.0002017-10-02 09:16:00.0002017-06-02 09:16:00.0002017-09-15 15:07:00.000

相关内容

最新更新

热门标签：