mysql每日一题0720--窗口函数-连续多天统计

😄 🍺

测试数据

CREATE TABLE T0720 
(
UID INT NOT NULL ,
CALLBACK_DATE DATE NOT NULL
);
INSERT INTO T0720  VALUES (1,'2020-4-1');
INSERT INTO T0720  VALUES (1,'2020-4-5');
INSERT INTO T0720  VALUES (1,'2020-4-10');
INSERT INTO T0720  VALUES (1,'2020-4-19');
INSERT INTO T0720  VALUES (2,'2020-4-1');
INSERT INTO T0720  VALUES (2,'2020-4-15');
INSERT INTO T0720  VALUES (2,'2020-4-20');
INSERT INTO T0720  VALUES (2,'2020-4-16');

解决方案

主要涉及了窗口函数中的lag函数，方法如下：

select 
a.UID,a.CALLBACK_DATE,
case when a.callback_date-last_date is null then '不计费'
when a.callback_date-last_date <7 then '不计费'
else '计费' end  as CHARGE

from 

(SELECT *,lag(callback_date,1)  over (PARTITION by UID ) 
last_date FROM `t0720`) a