手机版
你好,游客 登录 注册
背景:
阅读新闻

MySQL查询连续数据

[日期:2018-09-28] 来源:Linux社区  作者:php2012web [字体: ]

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

查询思想是:

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

顺序行号 如同 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次打卡的,并把日期展示出来。

查询的思路是:

1.提取出全表用户每次打卡记录与第一次打卡记录的差值但按用户与日期正排序

2.增加一个局部变量rownum与上面查询数据进行连查

3.在结果字段集里使用日期差值减去自增顺序行号值得到连续差块

4.通过分组用户与连续差块获取连续签到次数

5.通过having来提取超过6次签到的用户

Linux公社的RSS地址https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址https://www.linuxidc.com/Linux/2018-09/154486.htm

linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款