mysql中的连续数据_Mysql查询连续数据

客官°小女子只卖身不卖艺 2023-01-11 01:11 168阅读 0赞

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

查询思想是：

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

顺序行号 如同 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

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次签到的用户

![1ed88387c65534b2a4775fbdadd41b7e.png][]

[1ed88387c65534b2a4775fbdadd41b7e.png]: /images/20221119/ca9858da9a4841c7b41838720cd6d9d9.png

发表评论取消回复

表情：

评论列表（有 0 条评论，168人围观）

还没有评论，来说两句吧...

相关阅读

相关 mysql-table中删除数据后，新添数据的id不连续

mysql-table中删除数据后，新添数据的id不连续问题：对于自增数据表中添加数据后，ID保持连续递增如：123456，但当删去某一数据如ID为3的数据后，原

小咪咪/ 2023年10月07日 11:49/ 0 赞/ 33 阅读

相关 MySQL连续出现的数字

MySQL连续出现的数字 SQL架构题目描述题解答1 答2 答3 ①首先遍历一遍整张表，找出每个数字的连续重复

小灰灰/ 2023年10月06日 18:48/ 0 赞/ 35 阅读

相关 mysql每天销售汇总_Mysql获取连续的15天的销售数据

之前有做一个项目是关于亚马逊那块的erp.然后有一个需求是获取连续15天的销售信息,连续十五周的销售信息和连续12个月的销售数据做成图表统计;并且不连续的数据要将其补充完整,补

怼烎@/ 2023年01月12日 11:38/ 0 赞/ 111 阅读

相关 MySQL取不连续id_MySQL 快速将不连续ID修复为连续

MySQL 表内的数据不连续情况可能有以下原因导致： 1、insert语句不管是否成功，都会增加AUTO\_INCREMENT值。 2、进行了delete相关操作。 3、

﹏ヽ暗。殇╰゛Y/ 2023年01月12日 04:24/ 0 赞/ 148 阅读

相关 mysql中的连续数据_Mysql查询连续数据

查询连续记录并对这些连续数据统计取出指定连续次数的记录，这类操作并不多，但出现时会比较棘手。查询思想是：顺序行号 - 减首差值 = 连续差块顺序行号如同 orac

客官°小女子只卖身不卖艺/ 2023年01月11日 01:11/ 0 赞/ 169 阅读

相关 mysql查询数据_MySQL学习——查询表里的数据

MySQL学习——查询表里的数据摘要：本文主要学习了使用DQL语句查询表里数据的方法。数据查询语法 1 select \[distinct\] 列1 \[as '

迷南。/ 2023年01月10日 05:26/ 0 赞/ 126 阅读

相关 mysql生成连续日期

SELECT DATE_FORMAT(ADDDATE('2020-11-01', INTERVAL @dateAdd:=@dateAdd+1 DAY)

た入场券/ 2022年12月19日 09:20/ 0 赞/ 117 阅读

相关 MySQL生成连续数字

MySQL 使用过程当中，经常需要有连续数字的表用来处理一些复杂的报表逻辑，这个blog介绍几种生成连续数字的表方法如下，我需要生成一张表 id连续的从 1-50 文章

偏执的太偏执、/ 2022年12月15日 11:21/ 0 赞/ 205 阅读

相关 mySQL 生成连续日期

案例生成，2015-01-01~2018-06-01的连续日期，一种方式，借助笛卡儿积，进行生成，SQL代码如下： select date\_format(add

傷城~/ 2022年05月21日 07:39/ 0 赞/ 175 阅读

相关 Mysql生成连续日期

Mysql生成连续日期 SELECT curdate() as click\_date union all SELECT date\_sub(curdate(

快来打我*/ 2022年05月17日 10:45/ 0 赞/ 291 阅读