`
53873039oycg
  • 浏览: 844019 次
  • 性别: Icon_minigender_1
社区版块
存档分类
最新评论

Mysql如何查询连续的时间次数

 
阅读更多

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

     http://www.oschina.net/question/573517_118821

     首先建表,填充测试数据:

    

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中怎么做呢?

   可以参考链接:

    http://www.explodybits.com/2011/11/mysql-row-number/ 

    首先看原文中给出的答案

   

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函数。

   运行结果是:  

 

  

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

    看下中间表再说:

   

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

   结果为:

  

   看了下可以这么做,连续日期取最大的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;

   结果是:

  

    这里存在的问题是:表里面的的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有问题也请指出,谢谢。

   全文完。   

 

 

 

  

  • 大小: 8.8 KB
  • 大小: 24.1 KB
  • 大小: 20 KB
0
0
分享到:
评论

相关推荐

    关系型数据库+Mysql+查询用户连续登陆天数+数据统计

    关系型数据库 MySQL 查询用户连续登录天数数据统计 关系型数据库是指使用关系模型来组织和存储数据的数据库管理系统。MySQL 是一种流行的关系型数据库管理系统。关系型数据库的主要特点是使用表来存储数据,每个表...

    MYSQL查询调优实战

    在数据库领域,MySQL作为广泛应用的关系型数据库管理系统之一,其查询性能优化是数据库管理员(DBA)和开发人员经常会面对的重要课题。为了提高查询效率,索引的使用是关键因素之一。本文将详细介绍MySQL中几种常用...

    mysql常用性能查询语句

    MySQL 常用性能查询语句 MySQL 是一种关系型数据库管理系统,提供了多种方式来查询和优化数据库性能。本文总结了一些常用的 MySQL 性能查询语句,帮助数据库管理员和开发人员更好地了解和优化数据库性能。 1. 查看...

    Mysql慢查询日志查看

    Mysql 慢查询日志查看是指在 Mysql 数据库中,记录和分析执行时间较长的查询语句,以便优化数据库性能和提高查询效率。慢查询日志查看可以帮助数据库管理员和开发者快速地定位和优化数据库中的性能瓶颈。 一、慢...

    MySQL常用查询状态命令

    查看服务器状态可以帮助我们了解MySQL服务器的运行情况,包括但不限于运行时间、线程数、查询次数等。可以通过执行以下命令来实现: ```sql mysql&gt; STATUS; ``` 这个命令返回的信息非常全面,其中包括了服务器的版本...

    MySQL优化篇:慢查询日志.pdf

    使用`mysqldumpslow -s c -t 10 /var/lib/mysql/htzw-slow.log`可以查看访问次数最多的10个慢查询;使用`mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/htzw-slow.log`可以查看包含左连接且按时间排序的...

    MySQL查询性能优化

    MySQL 查询性能优化 MySQL 查询性能优化是数据库管理和开发中非常重要的一方面。为了实现高性能,需要从多方面考虑,包括库表结构的设计、索引的建立、查询的设计等。只有合理的设计库表结构和索引,才能确保查询的...

    MySQL查询优化器的工作原理

    每个策略都是为了减少查询的总体成本,这包括减少数据页的读取次数、减少CPU处理时间以及减少返回给客户端的数据量。 整体而言,MySQL查询优化器是一个高度复杂的系统,它通过多种不同的优化策略和技术来提升查询...

    MySQL海量数据查询优化策略.

    在处理MySQL海量数据查询优化时,我们需要关注的策略包括但不限于以下几点: 1. 优化索引使用:避免全表扫描至关重要。为此,应当在查询条件(WHERE)和排序(ORDER BY)涉及的列上创建索引。索引有助于数据库管理...

    sqlserver/mysql按天、按小时、按分钟统计连续时间段数据【推荐】

    在SQL Server和MySQL数据库中,按天、按小时或按分钟统计连续时间段数据是一项常见的需求,这有助于分析数据峰值和趋势。以下是如何实现这一目标的详细步骤和知识点: 1. **master..spt_values表**: - `master.....

    高性能MySQL_ch04_查询性能优化.pdf

    4. **缓存策略**:对热门查询结果进行缓存,减少对数据库的直接访问次数。 #### 七、总结 通过以上分析可以看出,高性能MySQL查询性能优化是一个综合性的工作,需要从多个角度出发进行全面考虑。无论是SQL语句优化...

    五款常用mysql slow log分析工具

    MySQL慢查询日志(Slow Query Log)是一种用于记录执行时间超过预设阈值(默认为10秒,可通过`long_query_time`参数调整)的SQL语句的日志机制。启用方式有二:一是编辑`my.cnf`配置文件中的`log-slow-queries`参数...

    MySQL慢查询相关知识

    MySQL慢查询是指那些执行时间超过预设阈值的SQL语句。这些慢查询不仅会降低数据库性能,还可能导致用户体验下降。因此,了解如何诊断和优化慢查询是每个DBA或开发人员必备的技能之一。 #### 二、检查MySQL版本 要...

    MySQL根据某一个或者多个字段查找重复数据的sql语句

    sql 查出一张表中重复...2、查询出所有数据进行分组之后,和重复数据的重复次数的查询数据,先列下: select count(username) as '重复次数',username from xi group by username having count(*)&gt;1 order by userna

    MySQL缓存研究

    通过对MySQL缓存的深入研究,我们可以理解缓存在数据库性能中的关键作用,学习如何调整和优化缓存以适应不断变化的业务需求,从而提升系统的整体性能和响应时间。这不仅可以减轻数据库服务器的压力,还能为用户提供...

    mysql查询多少秒内的数据,分类统计用户,sum()函数的一个特殊用法 一条比较经典的语句

    ### MySQL 查询特定秒数内数据并使用 `SUM()` 函数进行分类统计 在数据库操作中,经常需要查询特定时间段内的数据,并对这些数据进行统计分析。本文将详细解析如何使用MySQL来实现对指定秒数内数据的查询以及利用 `...

    mysql 统计一天24小时数据默认补0SQL

    本文将通过具体的MySQL查询语句,详细介绍如何实现一天24小时的数据统计,并对缺失数据进行默认补0的操作。 #### 核心概念与背景 1. **左连接(LEFT JOIN)**:在两个表中,基于共同列进行连接操作时,左连接会...

    mysql 慢查询

    慢查询是MySQL中一种重要的性能调试手段,主要用于记录那些执行时间过长的SQL语句。慢查询日志可以帮助我们找到那些性能不佳的SQL语句,从而对其进行优化。默认情况下,慢查询日志功能是关闭的,需要手动配置启用。 ...

    MySQL实训5.doc

    数据库性能的评估指标有多种,包括查询速度、响应时间、连接数、事务处理能力等。 2. 数据库性能监视命令方法 MySQL 提供了多种数据库性能监视命令,帮助用户实时监控数据库性能。常用的命令包括: * SHOW 命令:...

Global site tag (gtag.js) - Google Analytics