模板
教程
環境
性能
功能
管理
一個有趣的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 BETWEEN ’2012-1-1 00:00:00′ AND ’2012-1-11 23:59:59′ AND
t1.login_time >= ’2012-1-2′ AND 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就是我們要用的。
SQL 語句為：
GROUP BY t.login_time, t.uid
HAVING BIT_OR(1 << datediff(t1.login_time, t.login_time)) = b’1111110′;
NOTE: 從測試結果看，沒有索引時BIT_OR要比DISTINCT好一點點，不是非常明顯。當DISTINCT的
字段上有索引時，要比BIT_OR要好一點點.
- 去掉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)
RETURNS BOOL
BEGIN
WHILE bits <> 0 DO
IF ((bits & trait) = trait) THEN
RETURN TRUE;
END IF;
SET bits = bits >> 1;
END WHILE;
RETURN FALSE;
END|
DELIMITER ;
SQL 語句為：
SELECT uid AS bit FROM tmp_test
WHERE login_time BETWEEN ’2012-1-1 00:00:00′ AND ’2012-1-17 23:59:59′
GROUP BY uid
HAVING 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 DISTINCT uid
FROM (SELECT MAX(date)-MIN(date) less,uid
FROM (SELECT date-rn diff, uid, date, rn
FROM (SELECT @wy:=@wy+1 rn, uid,
datediff(login_time,’1971-01-01′) date,login_time
FROM (SELECT date(login_time) login_time, uid FROM tmp_test
WHERE login_time>=’2012-01-01 00:00:00′ AND
login_time <’2012-01-18 00:00:00′
GROUP BY uid, date(login_time)
原文轉自：http://blogread.cn/it/article/5057
