加入收藏 | 设为首页 | 会员中心 | 我要投稿 聊城站长网 (https://www.0635zz.com/)- 智能语音交互、行业智能、AI应用、云计算、5G!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

Mysql连续数据查询的简易方法

发布时间:2023-10-09 15:16:42 所属栏目:MySql教程 来源:
导读:下面一起来了解下Mysql连续数据查询的简单方法,相信大家看完肯定会受益匪浅,文字在精不在多,希望Mysql连续数据查询的简单方法这篇短内容是你想要的。

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

顺序行号 如同 orac
下面一起来了解下Mysql连续数据查询的简单方法,相信大家看完肯定会受益匪浅,文字在精不在多,希望Mysql连续数据查询的简单方法这篇短内容是你想要的。
 
顺序行号 - 减首差值 = 连续差块
 
顺序行号 如同 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
 
这里查询的是全表里连续超过3次打卡的,并把日期展示出来。
 
查询的思路是:
 
提取出全表用户每次打卡记录与第一次打卡记录的差值但按用户与日期正排序
 
增加一个局部变量rownum与上面查询数据进行连查
 
在结果字段集里使用日期差值减去自增顺序行号值得到连续差块
 
通过分组用户与连续差块获取连续签到次数
 
通过having来提取超过6次签到的用户
 
 

(编辑:聊城站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    推荐文章