-- 假设在任意4天内(不是最近4天),例如3号0点到7号23点59分 购买3次及以上 为 vip，求vip用户SELECT DISTINCT T1.`user` from
(SELECT `user`,amount,time,rank() over (PARTITION by `user` ORDER BY time) rn FROM `records`) T1
INNER JOIN
(SELECT `user`,amount,time,rank() over (PARTITION by `user` ORDER BY time) rn FROM `records`) T2
on T1.rn=T2.rn+2 and T1.`user`=T2.`user` and DATEDIFF(T1.time,T2.time)<= 4;
1.分组排序，若为；
2.自连接；
3.通过序号来达成“购买3次及以上”的条件，时间做差满足时间范围；
4.distinct 去重.
-- 假设在任意4天内(不是最近4天),例如3号0点到7号23点59分 购买3次及以上，且购买金额之和大于10为 vvip，求vvip用户SELECT A.`user` from
(
SELECT `user`,amount,rank() over (PARTITION by `user` ORDER BY time) div 3 `part`
from `records`
) A
INNER JOIN
(
SELECT T1.`user`,T1.rn div 3 `part` from
(SELECT `user`,amount,time,rank() over (PARTITION by `user` ORDER BY time) rn FROM `records`) T1
INNER JOIN
(SELECT `user`,amount,time,rank() over (PARTITION by `user` ORDER BY time) rn FROM `records`) T2
on T1.rn=T2.rn+2 and T1.`user`=T2.`user` and DATEDIFF(T1.time,T2.time)<= 4
) T3
on A.`user`= T3.`user` and A.`part`= T3.`part`
GROUP BY A.`user`,A.`part`
HAVING SUM(amount)>10