雄安移动网站建设,php网站用什么软件,外贸推广如何做,网站如何做提交的报名表现有各用户的登录记录表#xff08;login_events#xff09;如下#xff0c;表中每行数据表达的信息是一个用户何时登录了平台。 user_idlogin_datetime1002021-12-01 19:00:001002021-12-01 19:30:001002021-12-02 21:01:00
现要求统计各用户最长的连续登录天数#xff0…现有各用户的登录记录表login_events如下表中每行数据表达的信息是一个用户何时登录了平台。 user_idlogin_datetime1002021-12-01 19:00:001002021-12-01 19:30:001002021-12-02 21:01:00
现要求统计各用户最长的连续登录天数间断一天也算作连续例如一个用户在1,3,5,6登录则视为连续6天登录。期望结果如下
user_id int (用户id)max_day_count int (最大连续天数)10031016102310431051
话不多说某位大佬写的SQL我感觉挺牛的然后我就把它的SQL拿过来。 对于七天内连续三天登录断一天也算这种的SQL题基本上就是需要使用到lead,lag函数 以及使用日期减去排名之类的做法。 一位大佬给出了划分会话的形式来解决七天内连续三天的问题。并且使用拼接concat的方式。
我认为做的确实不错值得借鉴。 题解
-- 1.对日期进行格式化并且进行去重
WITH tmp as( --没啥好说的做一个数据格式化同时去重SELECTuser_id,date_format(login_datetime,yyyy-MM-dd) login_datefrom login_eventsgroup by user_id,date_format(login_datetime,yyyy-MM-dd) ) --2.取出登录日期的上一个日期 SELECTuser_id,login_date,--1.注解从这里看起取出用户上一次登录的日期lag(login_date,1,1970-01-01) over(PARTITION by user_id order by login_date) last_login --3.计算登陆日期和上一次登录日期的差值根据差值打标记
user_id,login_date,last_login,--2.进行判断今天登录的日期减去上一次登录的日期如果大于2的话就是一个会话的新起点给这个新起点标记一个1后面开窗聚合范围为上无边界到当前行同一个会话sum的值就会相同IF(datediff(login_date,last_login)2,1,0) flag
-- 4.按照用户分区日期排序划分会话 user_id,login_date, --3.这里拼上一个user_id的目的是给每一个会话建立一个唯一标识方便后面按照会话的唯一标识进行group byconcat(user_id,-,sum(flag) over(PARTITION by user_id order by login_date )) flagax_day_count
--5.按照用户会话分组求组内最大日期和最小日期的登录差值。 user_id,--4.这就是每一条子sql都保留login_date这个字段的意义同一个会话内的最大日期减去最小日期得到的就是连续登录的天数datediff(max(login_date),min(login_date)) 1 max_day_count
group by user_id,flag
5.最终SQL
WITH tmp as( --没啥好说的做一个数据格式化同时去重SELECTuser_id,date_format(login_datetime,yyyy-MM-dd) login_datefrom login_eventsgroup by user_id,date_format(login_datetime,yyyy-MM-dd) )SELECTuser_id,--4.这就是每一条子sql都保留login_date这个字段的意义同一个会话内的最大日期减去最小日期得到的就是连续登录的天数datediff(max(login_date),min(login_date)) 1 max_day_countfrom(SELECTuser_id,login_date, --3.这里拼上一个user_id的目的是给每一个会话建立一个唯一标识方便后面按照会话的唯一标识进行group byconcat(user_id,-,sum(flag) over(PARTITION by user_id order by login_date )) flagfrom(SELECTuser_id,login_date,last_login,--2.进行判断今天登录的日期减去上一次登录的日期如果大于2的话就是一个会话的新起点给这个新起点标记一个1后面开窗聚合范围为上无边界到当前行同一个会话sum的值就会相同IF(datediff(login_date,last_login)2,1,0) flagfrom(SELECTuser_id,login_date,--1.注解从这里看起取出用户上一次登录的日期lag(login_date,1,1970-01-01) over(PARTITION by user_id order by login_date) last_loginfrom tmp)t1)t2)t3 group by user_id,flag 在这个sql里面我们既需要使用lag,lead函数获取日期的上一行下一行也需要对日期差值打标记同时使用concat划分会话然后使用sum函数。之后需要求最大值与最小值差值
