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

一个有趣的 SQL 查询

一个朋友有这样一个SQL查询需求：

有一个登录表(tmp_test)，包含用户ID(uid)和登录时间(login_time)。表结构如下：

*************************** 1. row ***************************Field: uid

Type:int(10) unsignedNull: NOKey: MULDefault: NULLExtra:*************************** 2. row ***************************Field: login_time

Type:timestamp

Null: NOKey: MULDefault: 0000-00-00 00:00:00Extra:

问如何查询出所有在某一段时间内(如：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 ASt1ON date(t.login_time) + 1 <= date(t1.login_time) ANDdate(t.login_time)+ 7 > date(t1.login_time) ANDt.uid=t1.uidWHERE t.login_time BETWEEN ’2012-1-1 00:00:00′ AND ’2012-1-11 23:59:59′ ANDt1.login_time>= ’2012-1-2′ AND t.login_time < ’2012-1-18′(可去掉)

- COUNT(DISTINCT)

“计算连续7天”,可以通过GROUP BY分组和COUNT()来完成。因为一个用户在1天内可能会有多次登录，

这里需要使用(COUNT DISTINCT). SQL 语句为：

GROUP BYt.login_time, t.uidHAVING 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就是我们要用的。

SQL 语句为：

GROUP BYt.login_time, t.uidHAVING BIT_OR(1 << datediff(t1.login_time, t.login_time)) = b’1111110′;

- 去掉Range Join

虽说上面的思路实现了这个查询要求，但是由于使用了Range Join,效率并不好。在对uid建索引的情

况下，大约需要3.5s(总共约50000条记录). 有没有更好的方法呢？

受BIT_OR的启发，可以通过单表扫描，用bit位来记录每个用户2012-1-1至2012-1-17是否有登录。

然后根据这个值来判断是否有连续7天的情况。

我们需要一个辅助的函数来进行bit的运算：

DELIMITER |

/*判断一个Bit序列中，是否存在若干个连续的1*/

/*参数bits: bit序列*/

/*参数trait: 指定的若干连续的1.如b’111111‘*/

CREATE FUNCTION bits_find_N1(bits BIGINT, trait BIGINT)RETURNSBOOLBEGIN

WHILE bits <> 0DOIF ((bits & trait) = trait) THEN

RETURNTRUE;END IF;SET bits = bits >> 1;END WHILE;RETURNFALSE;END|DELIMITER ;

SQL 语句为：

SELECT uid AS bit FROMtmp_testWHERE login_time BETWEEN ’2012-1-1 00:00:00′ AND ’2012-1-17 23:59:59′GROUP BYuidHAVING bits_find_N1(BIT_OR(1 << datediff(login_time, ’2012-1-1′)),

b’1111111′) IS TRUE;

这个语句效率还是比较好的，即使不对uid建索引，也只需约0.27s

- 超高效率的语句

下面是另一个朋友写的SQL，虽然有点复杂，但是效率超高，只需要约0.17s是这样的

SET @wy=0;SELECT DISTINCTuidFROM (SELECT MAX(date)-MIN(date) less,uidFROM (SELECT date-rn diff, uid, date, rnFROM (SELECT @wy:=@wy+1rn, uid,datediff(login_time,’1971-01-01′) date,login_timeFROM (SELECT date(login_time) login_time, uid FROMtmp_testWHERE login_time>=’2012-01-01 00:00:00′ ANDlogin_time

)x

)x

)xGROUP BYdiff,uid

)xWHERE less>=6;

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

SELECT DISTINCT t.USER_ID FROM T_SD_COMMENT AS t JOIN T_SD_COMMENT ASt1ON date(t.CREATE_TIME) + 1 <= date(t1.CREATE_TIME) ANDdate(t.CREATE_TIME)+ 7 > date(t1.CREATE_TIME) ANDt.USER_ID = t1.USER_ID

GROUP BY t.CREATE_TIME, t.USER_ID

HAVING( BIT_OR(1 << datediff(t1.CREATE_TIME, t.CREATE_TIME)) = b'1111110');

SELECT DISTINCT USER_ID

FROM (SELECT MAX(date) - MIN(date) AS less, USER_ID

FROM (SELECT date - rn AS diff, USER_ID, date, rnFROM (SELECT @wy := @wy + 1 AS rn, USER_ID, datediff(CREATE_TIME, '1971-01-01') ASdate, CREATE_TIMEFROM (SELECT date(CREATE_TIME) AS CREATE_TIME, USER_ID

FROM T_SD_COMMENT, (SELECT @wy := 0) wGROUP BY USER_ID, date(CREATE_TIME)ORDER BY USER_ID, date(CREATE_TIME)

) x

) x

) xGROUP BY diff, USER_ID) xWHERE less >= 6