mysql查询满足条件的连续时间_Mysql如何查询连续的时间次数

最新推荐文章于 2024-07-03 03:17:16 发布
最新推荐文章于 2024-07-03 03:17:16 发布
阅读量349 收藏
点赞数
文章标签： mysql查询满足条件的连续时间
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_30011833/article/details/113293518
版权

在网上看到一道有意思的题目，大意是如何在mysql查询连续的时间内登录的次数。原文链接：

首先建表，填充测试数据：

CREATE TABLE `tmysql_test_lianxu_3` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`uid` int(11) DEFAULT NULL,

`sts` datetime DEFAULT NULL COMMENT '登录时间',

`ets` datetime DEFAULT NULL COMMENT '离线时间',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

测试数据为：

INSERT INTO `tmysql_test_lianxu_3` VALUES (1, 1, '2014-1-1 21:00:00', '2014-1-2 07:00:00');

INSERT INTO `tmysql_test_lianxu_3` VALUES (2, 1, '2014-1-2 15:37:57', '2014-1-2 21:00:00');

INSERT INTO `tmysql_test_lianxu_3` VALUES (3, 2, '2014-1-1 09:00:00', '2014-1-1 15:00:00');

INSERT INTO `tmysql_test_lianxu_3` VALUES (4, 2, '2014-1-2 09:00:00', '2014-2-1 16:00:00');

INSERT INTO `tmysql_test_lianxu_3` VALUES (5, 1, '2014-1-4 10:00:00', '2014-1-4 18:00:00');

INSERT INTO `tmysql_test_lianxu_3` VALUES (6, 1, '2014-1-5 12:00:00', '2014-1-5 13:00:00');

INSERT INTO `tmysql_test_lianxu_3` VALUES (7, 2, '2014-1-10 00:00:00', '2014-1-10 06:00:00');

INSERT INTO `tmysql_test_lianxu_3` VALUES (8, 2, '2014-1-11 13:00:00', '2014-1-11 18:00:00');

INSERT INTO `tmysql_test_lianxu_3` VALUES (10, 2, '2014-1-12 12:00:00', '2014-1-12 18:00:00');

INSERT INTO `tmysql_test_lianxu_3` VALUES (11, 1, '2014-1-8 06:00:00', '2014-1-8 16:00:00');

INSERT INTO `tmysql_test_lianxu_3` VALUES (12, 2, '2014-1-11 21:00:00', '2014-1-12 06:00:00');

在Oracle中可以使用row_number搞定，mysql中怎么做呢？

可以参考链接：

首先看原文中给出的答案：

SELECT uid, days, COUNT(*) AS num

FROM (SELECT uid,

@cont_day :=

(CASE

WHEN (@last_uid = uid AND DATEDIFF(login_dt, @last_dt) = 1) THEN

(@cont_day + 1)

ELSE

1

END) AS days,

(@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,

@last_uid := uid,

@last_dt := login_dt

FROM (SELECT uid, DATE(sts) AS login_dt

FROM tmysql_test_lianxu_3

ORDER BY uid, sts) AS t,

(SELECT @last_uid := '',

@last_dt := '',

@cont_ix := 0,

@cont_day := 0) AS t1) AS t2

GROUP BY uid, days;

也是使用了mysql模拟oracle的row_number函数。

运行结果是:

eba541d85e2881278ddf945397755742.png

我看了半天发现结果好像不是我想要的，我想要的是要有开始时间，结束时间之类的。

看下中间表再说：

SELECT uid,

@cont_day :=

(CASE

WHEN (@last_uid = uid AND DATEDIFF(login_dt, @last_dt)=1) THEN

(@cont_day + 1)

ELSE

1

END) AS days,

(@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,

@last_uid := uid,

@last_dt := login_dt login_day

FROM (SELECT uid, DATE(sts) AS login_dt

FROM tmysql_test_lianxu_3

ORDER BY uid, sts) AS t,

(SELECT @last_uid := '',

@last_dt := '',

@cont_ix := 0,

@cont_day := 0) AS t1

结果为：

4e1a2400aa721080db56bd019faccfc6.png

看了下可以这么做，连续日期取最大的days,开始时间，结束时间去login_day，而是这样写了：

SELECT uid, max(days) lianxu_days, min(login_day) start_date,max(login_day) end_date

FROM (SELECT uid,

@cont_day :=

(CASE

WHEN (@last_uid = uid AND DATEDIFF(login_dt, @last_dt)=1) THEN

(@cont_day + 1)

ELSE

1

END) AS days,

(@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,

@last_uid := uid,

@last_dt := login_dt login_day

FROM (SELECT uid, DATE(sts) AS login_dt

FROM tmysql_test_lianxu_3

ORDER BY uid, sts) AS t,

(SELECT @last_uid := '',

@last_dt := '',

@cont_ix := 0,

@cont_day := 0) AS t1) AS t2

GROUP BY uid, cont_ix;

结果是：

5529fc463457c99a8d24e29f77d43c6c.png

这里存在的问题是：表里面的的sts登录时间不能有2条uid相同时间在同一天内。

解决方法是:在case中添加一个<1 的判断条件

SELECT uid, max(days) lianxu_days, min(login_day) start_date,max(login_day) end_date

FROM (SELECT uid,

@cont_day :=

(CASE

WHEN (@last_uid = uid AND DATEDIFF(login_dt, @last_dt)=1) THEN

(@cont_day + 1)

WHEN (@last_uid = uid AND DATEDIFF(login_dt, @last_dt)<1) THEN

(@cont_day + 0)

ELSE

1

END) AS days,

(@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,

@last_uid := uid,

@last_dt := login_dt login_day

FROM (SELECT uid, DATE(sts) AS login_dt

FROM tmysql_test_lianxu_3

ORDER BY uid, sts) AS t,

(SELECT @last_uid := '',

@last_dt := '',

@cont_ix := 0,

@cont_day := 0) AS t1) AS t2

GROUP BY uid, cont_ix;

存在的问题：

时间sts的时分秒不见了。

--------------------------------------------Oracle可以这样做-------------------------------------------------

create table TSQL_TEST_LIANXU_4

(

ID NUMBER(4) not null,

U_ID NUMBER(4),

STS TIMESTAMP(6),

ETS TIMESTAMP(6)

);

insert into TSQL_TEST_LIANXU_4 (ID, U_ID, STS, ETS)

values (1, 1, to_timestamp('01-01-2014 21:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), to_timestamp('02-01-2014 07:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'));

insert into TSQL_TEST_LIANXU_4 (ID, U_ID, STS, ETS)

values (2, 1, to_timestamp('02-01-2014 15:37:57.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), to_timestamp('02-01-2014 21:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'));

insert into TSQL_TEST_LIANXU_4 (ID, U_ID, STS, ETS)

values (3, 2, to_timestamp('01-01-2014 09:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), to_timestamp('01-01-2014 15:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'));

insert into TSQL_TEST_LIANXU_4 (ID, U_ID, STS, ETS)

values (4, 2, to_timestamp('02-01-2014 09:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), to_timestamp('01-02-2014 16:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'));

insert into TSQL_TEST_LIANXU_4 (ID, U_ID, STS, ETS)

values (5, 1, to_timestamp('04-01-2014 10:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), to_timestamp('04-01-2014 18:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'));

insert into TSQL_TEST_LIANXU_4 (ID, U_ID, STS, ETS)

values (6, 1, to_timestamp('05-01-2014 12:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), to_timestamp('05-01-2014 13:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'));

insert into TSQL_TEST_LIANXU_4 (ID, U_ID, STS, ETS)

values (7, 2, to_timestamp('10-01-2014 00:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), to_timestamp('10-01-2014 06:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'));

insert into TSQL_TEST_LIANXU_4 (ID, U_ID, STS, ETS)

values (8, 2, to_timestamp('11-01-2014 13:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), to_timestamp('11-01-2014 18:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'));

insert into TSQL_TEST_LIANXU_4 (ID, U_ID, STS, ETS)

values (10, 2, to_timestamp('12-01-2014 12:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), to_timestamp('12-01-2014 18:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'));

insert into TSQL_TEST_LIANXU_4 (ID, U_ID, STS, ETS)

values (11, 1, to_timestamp('08-01-2014 06:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), to_timestamp('08-01-2014 16:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'));

本来想使用row_number的，结果没搞定。

select t.u_id,

to_char(MIN(t.sts), 'yyyy-mm-dd') start_date,

to_char(MAX(t.sts), 'yyyy-mm-dd') end_date,

trunc(MAX(t.sts)) - trunc(MIN(t.sts)) + 1

from (select z.u_id, z.sts, trunc(z.sts) - trunc(z.min_days) - rownum rn

from (select (select min(sts) from tsql_test_lianxu_4) min_days,

t.*

FROM tsql_test_lianxu_4 t

order by u_id, sts) z) t

group by u_id, rn

order by 1, 2

Oracle中这样查询是有问题的，就是uid相同sts在同一天的记录不能有2条，

Oracle另一种方法:

SELECT u_id, MIN(sts) AS STARTDATE, MAX(sts), COUNT(u_id) AS ENDNUM

FROM (SELECT A.u_id,

to_date(to_char(A.sts, 'yyyy-mm-dd'), 'yyyy-mm-dd') sts,

to_date(to_char(A.sts, 'yyyy-mm-dd'), 'yyyy-mm-dd') - ROWNUM AS GNUM

FROM (SELECT *

FROM tsql_test_lianxu_4

ORDER BY u_id,sts) A)

GROUP BY u_id, GNUM

ORDER BY u_id, MIN(sts)

缺点：uid相同sts在同一天的记录不能有2条

欢迎各位留下更好的查询SQL,如本文中的SQL有问题也请指出，谢谢。

全文完。

梁浩赞
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
原来连续查询mysql天数的sql语句这么简单呀
qq_43239192的博客
09-06 952
在现实项目中碰到的项目，用户需要查询连续15天电量小于8度的所以日期的数据（每个日期只有一行数据） 创建如下表，cur_date表示日期、num表示度数。 -- 创建表 drop table if exists table_test1; create table table_test1( id int auto_increment PRIMARY key, cur_date TIMESTAMP, num int ) 创建存储过程用以生成数据。 -- 生成数据 DELIMITER $$ dr
mysql算法:判断时间的连续性_TD（时间差分法）
weixin_39827728的博客
12-16 592
很多时候，我们的智能体是在一个持续无限连续的环境中进行互动学习的。比如类人机器人，他们就是和人类一样在一个社会空间中不断的自我学习更新。MC控制法在这种情况下就不适用了。dboyaoao：MC--蒙特卡洛方法​zhuanlan.zhihu.comMC的Evaluation如下，是对于所有阶段任务中特定状态的平均（不太明白的朋友可以参考上面的MC链接）但是在连续性环境中，是无法获取所有的Gt的，这时...
mysql 查询连续的记录_sqlserver中如何查询出连续日期记录的代码
weixin_34693167的博客
02-18 360
Insus.NET尝试写了程序并做了测试，可以得到预期的结果，SQL代码可参考和学习之用。有在论坛上看到一帖，《》，截图如下：Insus.NET尝试写了程序并做了测试，可以得到预期的结果，SQL代码可参考和学习之用。代码如下:--创建一个临时表，将会存储连续日期的记录CREATE TABLE #temp (IDD VARCHAR(10),SDATE DATETIME)DECLARE @sD DAT...
mysql 判定连续日期
最新发布
weixin_34186408的博客
07-03 65
实现“mysql 判定连续日期”流程 步骤表格 erDiagram |步骤|描述| |---|---| |1|查询连续日期的数据| |2|计算日期间隔| |3|判定连续日期|代码示例 步骤1：查询连续日期的数据 登录后复制 -- 查询连续日期的数据 SELECT id, date ...
MySQL查连续记录、天数（2次或3次都可）的思路
weixin_42283422的博客
02-09 1206
文章目录1、查找连续记录2、查找连续n条记录 1、查找连续记录 问题好像是来源于力扣601题，因此做个记录。我随便造了一个数据，如下表 con id 赵 3 钱 10 孙 11 李 12 周 13 吴 14 郑 17 王 18 秦 19 汉 4 宋 21 元 22 明 23 皇甫 1 我们要找出id连续的记录，比如【3，4】；【21，22，23】等。思路如下，分为两步 1、 如果按照id升序排序，lag(1)位，找到二者差值为1的记录，
mysql查询怎么判断时间戳连续_mysql – 检查连续x天 – 给定数据库中的时间戳
weixin_39572794的博客
02-07 709
您可以使用移位的自外连接与变量结合来实现此目的.看到这个解决方案：SELECT IF(COUNT(1) > 0, 1, 0) AS has_consecFROM(SELECT *FROM(SELECT IF(b.login_date IS NULL, @val:=@val+1, @val) AS consec_setFROM tbl aCROSS JOIN (SELECT @val:=0) ...
mysql查询连续次数_Mysql如何查询连续的时间次数
weixin_42557537的博客
01-19 213
在网上看到一道有意思的题目，大意是如何在mysql查询连续的时间内登录的次数。原文链接：首先建表，填充测试数据：CREATE TABLE `tmysql_test_lianxu_3` (`id` int(11) NOT NULL AUTO_INCREMENT,`uid` int(11) DEFAULT NULL,`sts` datetime DEFAULT NULL COMMENT '登录时间',...
sqlserver/mysql按天、按小时、按分钟统计连续时间段数据【推荐】
09-09
在SQL Server和MySQL数据库中，按天、按小时或按分钟统计连续时间段数据是一项常见的需求，这有助于分析数据峰值和趋势。以下是如何实现这一目标的详细步骤和知识点： 1. **master..spt_values表**： - `master.....
mysql查询全表比加上搜索条件更快_Mysql索引数据结构详解及性能调优
weixin_39963174的博客
01-20 1288
以前学习了不少东西，都忘了不少，最近就想着总结一下，就想到想写博客文章来总结下自己这些年学习的东西，记录下各方面技术学习情况。如果觉得好看，请给个赞你有一个思想，我有一个思想，我们交换后，一个人就有两个思想If you can NOT explain it simply, you do NOT understand it well enough简单介绍下这篇文章的流程1.为什么使用索引A：那还用说...
mysql 统计当个用户从当前时间连续登录天数，以及多用户某时间段，最长连续登录天数查询
qq_22785497的博客
10-26 8022
1、当个用户当前连续登录天数查询 SELECT GETINTO_USERID, max(days) incessancy_days, min(ot) start_date, max(ot) end_date FROM ( SELECT * FROM ( SELECT GETINTO_USERID ,@cont_day := ( CASE WHEN ( @last_uid = GETINTO_USE...
mysql函数 用来查询匹配不到的数据_详解MySql基本查询、连接查询、子查询、正则表达查询...
weixin_29062963的博客
02-02 1430
select * from STUDENT;2.2、按条件查询(1) 比较运算符> , < ,= , != (< >),>= , <=select * from STUDENT where STU_AGE>13;in(v1,v2..vn) ，符合v1,v2,,,vn才能被查出IN关键字可以判断某个字段的值是否在指定的集合中。如果字段的值在集合中，则满足查询...
mysql 树形查询效率_Mysql一棵树原理以及索引优化
weixin_31178795的博客
01-18 1157
B+Tree原理及mysql的索引分析一、索引的本质MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。我们知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找(linear search)，这种复杂度为...
Mysql获取时间连续的数据用来查询
wm9028的专栏
01-06 746
第一种 建一个表，插入连续的时间，然后查询出对应表之间的数据 第二种 找个很大的表执行下面的语句 比如说我要查询2018-01-10到2018-01-20每天的数据，那么就可以写成 SELECT @cdate := DATE_ADD(@cdate, INTERVAL +1 DAY) cday FROM( SELECT @cdate := DATE_ADD('2018-01-10', INTERVAL -1 DAY) FROM data_t limit 15) t0 WHERE date(@cdate
MySQL查询连续的时间段
Bug_Lian的博客
12-15 2256
有时候我们会遇到这种需求，要求统计本月份每天日期的数据，意思就是说日期必须连续，不能中断。我的思路就是查询出一段连续的时间段作为一个表去和主要数据连表查询。表连接分组什么的相比大家都很熟练了，这里主要演示下如何用MySQL查询连续的时间段。 在此之前，先说下mysql自带的一个表 ----- help_topic。它有一个从0~657的顺序ID序列，一些短期的操作可以借助它来完成，比如生成连续的日期。 ...
mysql数据库如何实现连续日期的统计查询
willowleaf的专栏
10-01 3592
mysql数据库实现连续日期的统计查询
MySQL查询近7天、1年、5年内数据，无数据部分补0
Inevitable_wl的博客
12-21 9472
MySQL查询近7天、1年、5年内数据，无数据部分补0
mysql 查询特定时间内连续的年、月、日
ZHIXINGHEYITUOPENG的博客
12-15 2503
mysql查询以年为间隔查询连续的时间 第一种方式：使用mysql的help_topic表进行查询，也可以自己建立一个从小到大一次排序的临时表进行查询 SELECT DATE_SUB( CURRENT_DATE, INTERVAL help_topic_id YEAR ) dt FROM mysql.help_topic WHERE help_topic_id BETWEEN 0 AND 6 第二种方式：使用MySQL自定义变量的方式，此方式对mysql版本有要求，同时也需要借助任.
统计一段时间内，每天的信息发布数、已审核、未审核
qq_36826248的博客
01-22 1103
第一步：手动产生一个连续的日期 CREATE TABLE dateTemp (i int);   INSERT INTO dateTemp (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);   select adddate('2012-09-01', numlist.id) as `date` from   (SELEC...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值