数据如下:
上面的数据是用户在张三登录的情况。
现在求用户在某一时间点前后60内登录的次数:
select name,
start_time,
count(*) over (partition by name order by cast(start_time as date) desc range between 60 preceding and 60 following) as sum_cnt
from table_name
结果如下图:
1、上面的SQL中,使用的是窗口函数中windons子句中的range关键值,使用range关键字,order by 后面的字段,必须是日期或者数字类型,上面的SQL中使用的类型是date类型,range between 60 preceding and 60 following
里面跟的数字就是日期,这里是60天。
**2、如果把日期改成bigint或者timestamps类型,例如:
select name,
start_time,
count(*) over (partition by name order by cast(start_time as timestamp) desc range between 60 preceding and 60 following) as sum_cnt
from table_name
, 那么这里面的60就代表是秒的意思,就是不是日期了,计算结果就错了。**
具体windows子句的使用方式,可参考:https://blog.csdn.net/Ahuuua/article/details/127136611