MySQL使用自定義變數模擬分析函式

壹頁書發表於2014-08-26
MySql變數函式
初始化實驗結構和資料
  1. create table test(
  2.     tid varchar(32) primary key,
  3.     stat int not null,
  4.     createtime timestamp not null
  5. );
  6. INSERT INTO test (tid,stat,createtime) VALUES ('ac551ad7ba3f9067b19ac8bb20caca6d',-1,'2014-08-20 10:01:09');
  7. INSERT INTO test (tid,stat,createtime) VALUES ('dc9a4438e577f4b08f7033a305544d47',-1,'2014-08-20 10:00:19');
  8. INSERT INTO test (tid,stat,createtime) VALUES ('23055228532bbba5a68d6ada11bcf33f',-1,'2014-08-20 09:58:32');
  9. INSERT INTO test (tid,stat,createtime) VALUES ('5711ee1610d07a55e64c7948667de6e8',-1,'2014-08-20 09:58:09');
  10. INSERT INTO test (tid,stat,createtime) VALUES ('035e06d8afd681a9904bd74e9860f8cb',-1,'2014-08-20 09:57:52');
  11. INSERT INTO test (tid,stat,createtime) VALUES ('3890efc08f37fa489a4e130cb04f71ac',-1,'2014-08-20 09:57:48');
  12. INSERT INTO test (tid,stat,createtime) VALUES ('1b6ed9db663dae470b45c722a61d08b0',-1,'2014-08-20 09:56:40');
  13. INSERT INTO test (tid,stat,createtime) VALUES ('8fb3409015e6b2cf85ba6ee90f15b58f',-1,'2014-08-20 09:54:40');
  14. INSERT INTO test (tid,stat,createtime) VALUES ('0badb1f4c2b1a89f1c473b992183add3',-1,'2014-08-20 09:54:33');
  15. INSERT INTO test (tid,stat,createtime) VALUES ('89b8af5eb473b2d4f50dd9e10773a9cc',-1,'2014-08-20 09:53:54');
  16. INSERT INTO test (tid,stat,createtime) VALUES ('77923a7397110224b5f94e7d0bd297de',2,'2014-08-19 17:13:17');
  17. INSERT INTO test (tid,stat,createtime) VALUES ('0df1da77cfdbe64edcd4d645197174af',2,'2014-08-19 12:20:21');
  18. INSERT INTO test (tid,stat,createtime) VALUES ('43daef6bfbc46dbfdbb97e74173dab30',2,'2014-08-19 09:54:08');
  19. INSERT INTO test (tid,stat,createtime) VALUES ('d5d12c510391314f48054c6c9ab9535c',2,'2014-08-19 09:23:41');
  20. INSERT INTO test (tid,stat,createtime) VALUES ('f7c123143752498b7c9a226a9583ae49',2,'2014-08-19 01:14:21');
  21. INSERT INTO test (tid,stat,createtime) VALUES ('da6a9a78897a42ae0a565cd0fabd76bb',2,'2014-08-18 21:59:46');
  22. INSERT INTO test (tid,stat,createtime) VALUES ('9cd3f83ab04120504a880523702491d7',2,'2014-08-18 16:26:30');
  23. INSERT INTO test (tid,stat,createtime) VALUES ('4dfa129ba64e7062afa37e56bb9632de',2,'2014-08-18 14:32:41');
  24. INSERT INTO test (tid,stat,createtime) VALUES ('a9a731870e1c02278c22ce1ab36fa43c',2,'2014-08-18 14:31:26');
  25. INSERT INTO test (tid,stat,createtime) VALUES ('97f39d2a1e519f99e602e72cfc45fe0c',2,'2014-08-17 11:47:52');
  26. INSERT INTO test (tid,stat,createtime) VALUES ('31ba95265a96971221ddf9320c79eed8',3,'2014-08-20 02:08:50');
  27. INSERT INTO test (tid,stat,createtime) VALUES ('060d92222edcb6f583cb4cd0244aadc0',3,'2014-08-20 02:05:54');
  28. INSERT INTO test (tid,stat,createtime) VALUES ('7d3eb4ea201906b08e961b9fe7726fd4',3,'2014-08-20 02:00:11');
  29. INSERT INTO test (tid,stat,createtime) VALUES ('c633bc16cb8c3bb4ffa7f00682701b92',3,'2014-08-20 01:54:22');
  30. INSERT INTO test (tid,stat,createtime) VALUES ('e43bb7e7274259712b389e3feabc068f',3,'2014-08-20 01:49:36');
  31. INSERT INTO test (tid,stat,createtime) VALUES ('bdabf3d80fb097222112cb30cdc48117',3,'2014-08-20 01:48:48');
  32. INSERT INTO test (tid,stat,createtime) VALUES ('170e2bdc11d517a56b7ce23d85633e42',3,'2014-08-20 01:46:56');
  33. INSERT INTO test (tid,stat,createtime) VALUES ('7e79f6065ae8bb215cee43a4efbcd852',3,'2014-08-20 01:44:17');
  34. INSERT INTO test (tid,stat,createtime) VALUES ('04728676e3305de05a18333ddfc76c01',3,'2014-08-20 01:39:05');
  35. INSERT INTO test (tid,stat,createtime) VALUES ('d987176d350d4fefcc92b9a7ebb4f288',3,'2014-08-20 01:35:52');
  36. COMMIT;

要求:
Stat表示狀態,Createtime表示建立時間
查詢每種狀態最近3個記錄的內容

這個需求似曾相識
http://blog.itpub.net/29254281/viewspace-1257510/

但是這回是MySQL資料庫了。他沒有分析函式，只能用自定義變數模擬
test表的stat欄位為分組標識
gid是上一個記錄的分組標識,
cgid是當前記錄的分組標識,
如果gid和cgid不等，說明分組標識已經變了,Rank排序重置
這樣Rank是按照每個分組進行自增的,取前N個記錄就可以了。

  1. SELECT t3.tid, t3.stat, t3.createtime
  2. FROM (SELECT @gid := @cgid, @cgid := t1.stat, if(@gid = @cgid, @rank := @rank + 1, @rank := 1) AS rank, t1.*
  3.     FROM (SELECT *
  4.         FROM test
  5.         ORDER BY stat, createtime DESC
  6.         ) t1, (SELECT @gid := 1, @cgid := 1, @rank := 1) t2
  7.     ) t3
  8. WHERE t3.rank <= 3;
結果:


MySQL實現每個分組隨機抽取N個記錄的功能
(前面連結中的功能)
  1. SELECT t3.tid, t3.stat, t3.createtime
  2. FROM (SELECT @gid := @cgid, @cgid := t1.stat, if(@gid = @cgid, @rank := @rank + 1, @rank := 1) AS rank, t1.*
  3.     FROM (SELECT *
  4.         FROM test
  5.         ORDER BY stat, rand()
  6.         ) t1, (SELECT @gid := 1, @cgid := 1, @rank := 1) t2
  7.     ) t3
  8. WHERE t3.rank <= 3;

----------------------------分割線---------------------------------

上面這些都是使用MySQL自定義變數模擬分析函式,
使用傳統的相關子查詢也可以實現這個功能,只是效率低
  1. SELECT t1.tid, t1.stat, t1.createtime
  2. FROM test t1
  3. WHERE (
  4.     SELECT COUNT(*)
  5.     FROM test t2
  6.     WHERE t2.stat = t1.stat
  7.         AND t1.createtime < t2.createtime
  8.     ) < 3
  9. ORDER BY stat, createtime DESC
參考:
http://blog.itpub.net/29254281/viewspace-1142071/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1257911/，如需轉載，請註明出處，否則將追究法律責任。

相關文章