一个有趣的 SQL 查询(查询7天连续登陆)

一个有趣的 SQL 查询

一个朋友有这样一个SQL查询需求: 
有一个登录表(tmp_test),包含用户ID(uid)和登录时间(login_time)。表结构如下: 

*************************** 1. row ***************************
Field: uid
Type: int(10) unsigned
Null: NO
Key: MUL
Default: NULL
Extra:
*************************** 2. row ***************************
Field: login_time
Type: timestamp
Null: NO
Key: MUL
Default: 0000-00-00 00:00:00
Extra: 
问如何查询出所有在某一段时间内(如:2012-1-1至2012-1-17)连续7天都有登录的用户。

在写这个SQL时,发现一些很有意思东西,也许对大家写SQL有帮助,因此记录一下。

- 基本思路 Loop Join
  首先想到的思路是一个类似于Loop Join的方法:
  A. 取出2012-1-1到2012-1-11的每一条记录.
  B. 对取出的每一条记录,再去表中查询这个用户的接下来6天的记录。
     如果总数为6条记录,则满足连续7天的条件

 

- Range Join
  Loop Join的思路可以通过一个Join语句来实现。姑且称之为Range Join。通常join时,使用的都是
  等值join. 如果join列的值是唯一的,那么就是左表的一条记录对应右表的一条记录。而Range Join
  中,左表的一行数据对应右表的一个范围内的所有记录。

 SQL 语句为:

SELECT DISTINCT t.uid FROM tmp_test AS t JOIN tmp_test AS t1
ON date(t.login_time) + 1 <= date(t1.login_time) AND
   date(t.login_time) + 7 > date(t1.login_time) AND
   t.uid = t1.uid
WHERE t.login_time BETWEEN2012-1-1 00:00:00AND2012-1-11 23:59:59AND
      t1.login_time >=2012-1-2AND t.login_time <2012-1-18′(可去掉)

- COUNT(DISTINCT)
  “计算连续7天”,可以通过GROUP BY分组和COUNT()来完成。因为一个用户在1天内可能会有多次登录,
  这里需要使用(COUNT DISTINCT). SQL 语句为:

GROUP BY t.login_time, t.uid
HAVING COUNT(DISTINCT date(t1.login_time))=6

- BIT_OR
  考虑到DISTINCT操作需要缓存数据,就想到了用bit逻辑运算(可能会效率高一些)。因为连续的七天
  与第一天的差分别为,1,2,3,4,5,6,7.可以分别用1-7bit位来表示。根据这个特点,可以对分组中
  的每一行进行或(|)运算.如果最后的值等于b’1111110′(6个1).那么就是连续的7天。这个办法可以
  避免DISTINC操作。没想到MySQL中真的有了bit操作的聚合函数。BIT_OR就是我们要用的。

 

 

http://www.oschina.net/question/28_41179?sort=default&p=1

 

一个有趣的 SQL 查询(查询7天连续登陆)

上一篇:mysql日志 解析


下一篇:[MySQL] 按日期进行统计(前一天、本周、某一天)