《SQL学习记录之一》

        运营活动需求：1、新用户注册的话就送6元红包、2、用户当天的交易满3笔的话就送给用户8元红包，3、用户连续登录6天就送10元红包。

        连续6天就送代金卷==》连续N天就送代金==》动态可配置的，可根据运营现状即使调整策略。

        实现方式：

                1、新用户注册（注册接口）

                2、用户当天交易满3笔的话，那我直接查询交易记录是否当天记录总数超过3笔

                3、那连续6天如何实现呢？

        首先用户登录表结构设计：sys_user_login

ID	UM	NAME	LOGIN_TIME
1	ZHANGAN	张三	2022-09-01 00:00:00
2	LISI	李四	2022-09-01 00:00:00

--创建表结构
CREATE TABLE `sys_user_logins` (
  `Id` varchar(32) NOT NULL DEFAULT '1' COMMENT '自增主键',
  `um` varchar(255) DEFAULT NULL COMMENT '用户账号',
  `login_time` timestamp NULL DEFAULT NULL COMMENT '登陆时间',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE DEFINER=`root`@`localhost` TRIGGER `id_trigger` BEFORE INSERT ON `sys_user_logins` FOR EACH ROW BEGIN
	SET new.ID=REPLACE(UUID(),'-','');
END;

--动态生成日期

SELECT
	date(
	from_unixtime( unix_timestamp( '2017-01-01' ) + floor( rand() * ( unix_timestamp( '2018-08-08' ) - unix_timestamp( '2017-01-01' ) + 1 ) ) )) AS date;

--动态生成日期（日期:时分秒）

set global log_bin_trust_function_creators=TRUE;
DROP FUNCTION
IF
	EXISTS func_RandomDateTime;
#USAGE:
#SELECT func_RandomDateTime(DATE_FORMAT('2010-12-1 10:10:10','%Y-%m-%d %H:%i:%s'),DATE_FORMAT('2010-12-1 11:10:13','%Y-%m-%d %H:%i:%s')) AS t;
CREATE FUNCTION func_RandomDateTime(sd DATETIME,ed DATETIME) RETURNS DATETIME BEGIN
	DECLARE
		sub INT DEFAULT 0;
	DECLARE
		ret DATETIME;
	
	SET sub = ABS(
	UNIX_TIMESTAMP( ed )- UNIX_TIMESTAMP( sd ));
	
	SET ret = DATE_ADD( sd, INTERVAL FLOOR( 1+RAND ()*( sub - 1 )) SECOND );
	RETURN DATE_ADD( sd, INTERVAL FLOOR( 1+RAND ()*(( ABS( UNIX_TIMESTAMP( ed )- UNIX_TIMESTAMP( sd )))- 1 )) SECOND );

END;

show create function func_RandomDateTime;

SELECT func_RandomDateTime(DATE_FORMAT('2010-12-1 10:10:10','%Y-%m-%d %H:%i:%s'),DATE_FORMAT('2010-12-1 11:10:13','%Y-%m-%d %H:%i:%s')) AS t;

DROP PROCEDURE p01 ;

CREATE PROCEDURE p01 () BEGIN
	DECLARE
		i INT;	
		SET i = 1001;
	
		WHILE
				i <= 2000 DO
				INSERT INTO sys_user_logins ( um, login_time )
			VALUES
				( CONCAT( 'zhangsan00', i ), func_RandomDateTime ( DATE_FORMAT( '2020-12-1 10:10:10', '%Y-%m-%d %H:%i:%s' ), DATE_FORMAT( '2022-9-1 11:10:13', '%Y-%m-%d %H:%i:%s' )) );
			
			SET i = i + 1;
		END WHILE;
	
END;

delete from sys_user_logins;

DROP PROCEDURE p02 ;

CREATE PROCEDURE p02 () BEGIN
	DECLARE
		i INT;	
		SET i = 1;
		WHILE
				i <= 100 DO
				CALL p01();
				SET i = i + 1;
		END WHILE;
	
END;

CALL p02();

select * from sys_user_logins;

select count(*) from sys_user_logins;

select um,count(*) from sys_user_logins a group by um;


CALL p02()
> OK
> 时间: 460.159s

业务实现​

计算连续登陆天数的两种情况：①计算用户连续登录天数，以用户和登陆时间为指标。②计算用户在某个时间段内连续登录天数>N的情况

1 查看每位用户连续登陆的情况

2 查出在某个时间段内连续登录天数>=5天的用户

没有具体需求，用户在某一段日期内可能出现多次连续登录，比如出现这周连续登录三天，上周连续登录四天的情况，需要将这些信息全部输出，最后结果输出四个字段，分别是用户ID、首次登录日期、结束登录日期、连续登录天数。

首先对用户登录数据进行排序，这里使用窗口函数rank()，为了后续代码简洁，将执行的结果存到新表user_login_temp_01里面，代码如下：

create table user_login_temp_01(
    select *,rank() over(partition by um order by login_time) 排序
        from sys_user_logins);

#查看结果
select * from user_login_temp_01;

代码解读：针对um分区，把um一样的分到一个窗口里，每个窗口在针对登录日期升序排列，每个窗口内进行排序后最后拼接再一起，结果如下：

接下来是最重要的一步，用login_time - 排序。比如1号用户第一次登录是2016-10-31，减去,1天是2016-10-30，第二次登录是2016-11-01，减去2天是2016-10-30，说明这两天是连续登录的；第四次登录是2016-11-10，减去4天是2016-11-6不等于2016-10-30，说明这次登录与第一次登录就不是连续的了,代码如下(结果还是存到新表user_login_temp_02里）：

create table user_login_temp_02(

select *,date_sub(login_time, interval 排序 day) 辅助日期列

from user_login_temp_01);

#查看结果

select * from user_login_temp_02;

date_sub()是个日期运算函数，第一个参数是起始日期，第二个参数是起始日期减去几天，结果如下：

最后一步是根据上面得到的结果，用um和辅助日期列做分组字段，um和辅助日期列完全一样的被分到一个组，这个组是连续登录的，针对login_time取最小值就是起始登陆日期，取最大值就是连续登录的最后一天日期,每个组进行计数得到的是连续登录的天数。这样就完成需求了。把所有代码整合在一起，不需要创建临时表的代码如下,比较长...

select um,

    min(login_time) 起始登录日期,

    max(login_time) 结束登录日期,

    count(login_time) 连续登录天数

from (select *,date_sub(login_time, interval 排序 day) 辅助日期列

        from (select *,rank() over(partition by um order by login_time) 排序

          from (select distinct um, date(login_time) login_time  from sys_user_logins) as a

        )as b

) as c

group by um,辅助日期列

having 连续登录天数>3;

第二种情况：查出在某个时间段内连续登录天数>=5天的用户，这个需求可以用上面查询的结果进行筛选。但是仅针对这个需求，用上面的代码就特别麻烦，下面介绍一个简单的办法，引用一个新的静态窗口函数lead()

select *,lead(login_time,4) over(partition by um order by login_time) 第五次登录日期  from sys_user_logins

lead函数有三个参数，第一个参数是指定的列（这里用登陆日期），第二个参数是当前行向后几行的值，这里用的是4，也就是第五次登录的日期，第三个参数是如果返回的空值可以用指定值替代，这里没有使用第三个参数。 over语句里面是针对UM分窗，每个窗口针对登录日期升序。代码得到结果如下，可以看到新列的第四行为空值，因为在UM为1的窗口里，第四行是不能往后移动四行的，数据行数不够，所以返回空值。 

用第五次登录日期 - login_time+1，如果等于5，说明是连续登录五天的，如果得到空值或者大于5，说明没有连续登录五天，代码和结果如下：

select *,datediff(第五次登录日期,login_date) 相差天数

from (select *,lead(login_date,4) over(partition by user_id order by login_date) 第五次登录日期

from user_login_date) as a;

最后一步，筛选相差天数=5的，针对user_id去重，完成需求

select 
    distinct um
from (
        select *,datediff(第五次登录日期,login_time)+1 相差天数
                from (select *,lead(login_time,4) over(partition by um order by login_time) 
                    第五次登录日期    from sys_user_logins) as a
        ) as b  where 相差天数=8;

备注：

 001、1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in_肖广彪的博客-CSDN博客

 002、 使用MySQL窗口函数巧妙的解决用户连续登录天数等问题 - 知乎