mysql计算连续天数，mysql连续登录天数，连续天数统计

mysql计算连续天数，mysql连续登录天数，连续天数统计

 

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

蕃薯耀 2016年11月28日 09:29:10 星期一

http://fanshuyao.iteye.com/

 

mysql计算连续天数，mysql连续登录天数，连续天数统计：

http://fanshuyao.iteye.com/blog/2341455

 

Oracle计算连续天数，计算连续时间，Oracle连续天数统计

http://fanshuyao.iteye.com/blog/2341163

 

一、表结构及初始化数据

DROP TABLE user_login;

CREATE TABLE user_login(
	pid INT NOT NULL,
	login_time DATETIME NOT NULL
);

INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-25 13:30:45');
INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-24 13:30:45');
INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-24 10:30:45');
INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-24 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-23 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-10 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-09 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(1,'2016-11-01 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(1,'2016-10-31 09:30:45');


INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-25 13:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-24 13:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-23 10:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-22 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-21 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-20 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-19 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-02 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-11-01 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-10-31 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-10-30 09:30:45');
INSERT INTO user_login(pid, login_time) VALUES(2,'2016-10-29 09:30:45');

 

二、封装计算连续天数的方法

DELIMITER $$

CREATE
    FUNCTION f_continuty_days(id INT, start_time DATE, end_time DATE)
    RETURNS INT
    BEGIN
	DECLARE days INT;
	DECLARE flag INT;
	DECLARE previous_day DATE;
	SET days := 0;
	SET flag := 1;
	SET previous_day := DATE_SUB(end_time,INTERVAL 1 DAY);
	
	WHILE flag>0 DO
		SELECT COUNT(DISTINCT(DATE(login_time))) INTO flag  FROM  user_login 
		WHERE pid = id 
		AND DATE(login_time) = previous_day ;
		IF flag > 0 THEN 
			SET days := days + 1;
			SET previous_day := DATE_SUB(previous_day,INTERVAL 1 DAY);
		END IF;
	END WHILE; 
	RETURN days;
    END$$

DELIMITER ;

 

 

三、调用方法

把用户pid、开始时间、结束时间参数传进去。

SELECT f_continuty_days(1,DATE('2016-10-01'),DATE('2016-11-25'));

 

 

 

 

 

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

蕃薯耀 2016年11月28日 09:29:10 星期一

http://fanshuyao.iteye.com/

评论

2 楼 minisstep 2018-09-10  

借助rownumber即可求解;

如果是连续的记录，那么 diff(Date- rn) 肯定是相同的！

核心的代码：logindate - row_number() over (partition by userid order by logindate) as groupday

参考：[SQL数据分析](https://mp.weixin.qq.com/s?src=11&timestamp=1536580613&ver=1114&signature=d8p3CZhROyULs0gkxMG4W75gVReHv9K1UtrhfttQTJughERHfGlTM1Vi*0yk38ZDi0MTMayvKjHV4oWbntBdteuPzjDnmY7qCzrmNdLio12r7dWoPJMdFAoWQ7fcvc*F&new=1)

1 楼 蕃薯耀 2016-11-28  

mysql计算连续天数，mysql连续登录天数，连续天数统计

>>>>>>>>>>>>
蕃薯耀