MySQL查询连续数据

228次阅读

没有评论

共计 1910 个字符，预计需要花费 5 分钟才能阅读完成。

查询连续记录并对这些连续数据统计取出指定连续次数的记录，这类操作并不多，但出现时会比较棘手。

查询思想是：

顺序行号 – 减首差值 = 连续差块

顺序行号如同 Oracle 中的 rownum 但 MySQL 目前还没有这个功能，所以只能通过局部变量来实现，

减首差值就是每条记录与最开始记录的差（需要保证这个差值与顺序行号递增值相同，当然如果本来就是自增值则不需要单独计算）

只要顺序行号与减首差值保持相同递增值则连续差块值相同，就可以统计出连续长度

示例表：（以简单的签到表为例）

create table user_sign(
id int unsigned primary key auto_increment,
user_id int unsigned not null comment ‘ 用户 ID’,
date date not null comment ‘ 签到日期 ’,
created_time int unsigned not null comment ‘ 创建时间 ’,
updated_time int unsigned not null comment ‘ 修改时间 ’
)engine=innodb default charset=utf8 comment ‘ 用户签到 ’;

随机生成数据（创建函数随机生成签到数据）

create function insert_sign_data(num int)
returns int
begin
declare _num int default 0;
declare _date date;
declare _tmpdate date;
declare _user_id int;
declare line int default 0;
declare _get_last cursor for select date from user_sign where user_id=_user_id order by date desc limit 1;
declare continue handler for SQLSTATE ‘02000’ set line = 1;
while _num < num do
set _user_id = CEIL(RAND() * 500 );
open _get_last;
fetch _get_last into _tmpdate;
IF line THEN
set _date = FROM_UNIXTIME(unix_timestamp() – 86400 * round(RAND() * 200 ), ‘%Y-%m-%d’ );
set line = 0;
ELSE
set _date = FROM_UNIXTIME(unix_timestamp( _tmpdate) + 86400 * round(RAND() * 2 + 1), ‘%Y-%m-%d’ );
END IF;
INSERT INTO user_sign (user_id, date, created_time, updated_time) VALUES (_user_id, _date, unix_timestamp(), unix_timestamp());
set _num = _num + 1;
close _get_last;
end while;
return _num;
end

生成数据（由于生成时有判断最近打卡日期生成有会点慢）

select insert_sign_data(20000);

提取出连续打卡超过 6 天的用户

SELECT
user_id,
val – (@rownum := @rownum + 1) AS type,
group_concat(date) AS date_join,
count(1) num
FROM
(
SELECT
us1.date,
us1.user_id,
(unix_timestamp( us1.date) – min_timestamp ) / 86400 + 1 AS val
FROM
user_sign AS us1
LEFT JOIN (SELECT UNIX_TIMESTAMP( min( date) ) AS min_timestamp, user_id, min(date) AS min_date FROM user_sign GROUP BY user_id ) AS us2 ON us1.user_id = us2.user_id
ORDER BY
us1.user_id ASC,
us1.date ASC
) AS t1,
(SELECT @rownum := 0) AS t2
GROUP BY
user_id,
type
HAVING
num > 6