step1: 用户登录日期去重
因为一个用户同一天可能登录多次,所以我们首先需要用用户登录日期去重。...step3:日期减去计数值得到结果
因为菌哥这里演示用的是hql,所以这里获取日期差值使用了date_sub函数。...select shop,user_id,count(*) ct
from visit
group by shop,user_id;t1
计算结果:
?...完整SQL
好了,结果已经查询出来了,这里把上面step的SQL整合到一起~
select
shop,
user_id,
ct
from
(select
shop,
user_id,...ct,
rank() over(partition by shop order by ct) rk
from
(select
shop,
user_id,
count(*) ct
from visit