题45:
根据下表编写 SQL 语句,对于每个 user_id ,求出每次访问及其下一个访问(若该次访问是最后一次,则为今天)之间最大的空档期天数 window ;返回结果表,按用户编号 user_id 排序。
其中:此表没有主键。此表含有用户访问某特定零售商的日期日志。
解题思路:
在做题之前需要了解lead()函数:
lead(expression,len,default value)over(partition by order by)
其中:
- expression指的是lead作用的位置;
- len表示向后的行数,如果省略则系统会默认为1;
- default value是指如果当前位置没有后续行所返回的值,如果使用时省略,会返回null后面的over是窗口函数通用的。
第一步:利用datediff与lend函数算出空档期天数 window
select user_id,datediff(
lead(visit_date,1,'2021-01-01') over(partition by user_id order by visit_date),visit_date
) as a
from UserVisits
第二步:根据user_id分组和第一步合并即可。
select user_id,max(a) as biggest_window
from(
select user_id,datediff(
lead(visit_date,1,'2021-01-01') over(partition by user_id order by visit_date),visit_date
) as a
from UserVisits
) tmp
group by user_id;