连续登陆天数sql

连续登陆天数sql

1. 创建模拟数据

CREATE TABLE user_login ( user_id VARCHAR ( 100 ), login_time datetime );
INSERT INTO user_login
VALUES
	( 1, '2016-11-25 13:30:45' ),
	( 1, '2016-11-24 13:30:45' ),
	( 1, '2016-11-24 10:30:45' ),
	( 1, '2016-11-24 09:30:45' ),
	( 1, '2016-11-23 09:30:45' ),
	( 1, '2016-11-10 09:30:45' ),
	( 1, '2016-11-09 09:30:45' ),
	( 1, '2016-11-01 09:30:45' ),
	( 1, '2016-10-31 09:30:45' ),
	( 2, '2016-11-25 13:30:45' ),
	( 2, '2016-11-24 13:30:45' ),
	( 2, '2016-11-23 10:30:45' ),
	( 2, '2016-11-22 09:30:45' ),
	( 2, '2016-11-21 09:30:45' ),
	( 2, '2016-11-20 09:30:45' ),
	( 2, '2016-11-19 09:30:45' ),
	( 2, '2016-11-02 09:30:45' ),
	( 2, '2016-11-01 09:30:45' ),
	( 2, '2016-10-31 09:30:45' ),
	( 2, '2016-10-30 09:30:45' ),
	( 2, '2016-10-29 09:30:45' );

CREATE TABLE user_login_date ( SELECT user_id, DATE( login_time ) FROM user_login );
SELECT
	* 
FROM
	user_login_date;

部分截图

sql 代码

USE user_login;-- SHOW TABLES;
CREATE TABLE rklogin ( SELECT user_id, date, rank () over ( PARTITION BY user_id ORDER BY date ) rk FROM user_login_date );
SELECT
	* 
FROM
	rklogin;
SELECT
	user_id,
	MIN( date ) 连续登陆起始日期,
	MAX( date ) 连续登陆结束日期,
	COUNT( date ) 
FROM
	( SELECT *, date_sub( date, INTERVAL rk DAY ) 辅助日期列 FROM rklogin ) a 
GROUP BY
	user_id,辅助日期列;

注意 date_sub() rank() partition by 的用法！
还有 DATE_SUB(date,INTERVAL expr type)
😃