WHAT'S NEW?
Loading...

SQLでちゃんとしたランキングを作る【2/3】

, , ,
実に7ヶ月ぶりにこのシリーズを再開することにした。
前回なにをやったのか、というと、率直にリンク先を見ていただければ良いと思う。
「SQLでちゃんとしたランキングを作る【1/3】」

つまりseletしてorderしてlimitするだけでは正しいランキングがでない、ということだ。

幸いにも識者の方から色々アドバイスをもらい、俺なりに試してみた結果を書いてみようと思う。

まず、識者、つまりDupont氏から引用してもらったSQLがこれだ。
view plainprint?
  1. select (select count(distinct b.sal) from emp b where b.sal <= a.sal) as rnk, a.sal from emp a  
個人的にSQLは改行を入れて見やすくする主義なので、以下のように適宜変更してみる。
view plainprint?
  1. select  
  2.   (  
  3.     select  
  4.       count(distinct b.sal)  
  5.     from  
  6.       emp b  
  7.     where  
  8.       b.sal <= a.sal  
  9.   ) as rnk, a.sal  
  10. from  
  11.   emp a  
コードというものは、改行やインデントで適切にフォーマットしておくべきだと思っている。

しかしSQLに関してはまだ拘りはないので、だいたいいつもフォーマットは違う。統一しておいたほうが良い場合と、統一しないでも良い場合があるのだが、SQLは俺にとってまだ後者の方だ。



で、実はこのSQLはサンプルコードそのままなので、当然データベース名もテーブル名もフィールド名も違う。

既存データベースは以下のような内容になっている。

このデータを生成するには以下のSQLでインサートしてほしい。
view plainprint?
  1. CREATE TABLE IF NOT EXISTS `ranking_history` (  
  2.   `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '主キー',  
  3.   `user_id` int(5) NOT NULL COMMENT 'ユーザID',  
  4.   `point` int(8) NOT NULL COMMENT 'ポイント',  
  5.   `created` date NOT NULL COMMENT '作成日',  
  6.   PRIMARY KEY (`id`),  
  7.   KEY `user_id` (`user_id`,`point`,`created`)  
  8. ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;  
  9.   
  10. INSERT INTO `ranking_history` (`id`, `user_id`, `point`, `created`) VALUES  
  11. (1, 1, 42, '2010-06-02'),  
  12. (2, 2, 26, '2010-06-02'),  
  13. (3, 3, 31, '2010-06-02'),  
  14. (4, 4, 26, '2010-06-02'),  
  15. (5, 5, 49, '2010-06-02'),  
  16. (6, 6, 12, '2010-06-02'),  
  17. (7, 7, 66, '2010-06-02'),  
  18. (8, 8, 31, '2010-06-02'),  
  19. (9, 9, 41, '2010-06-02'),  
  20. (10, 10, 49, '2010-06-02'),  
  21. (11, 11, 55, '2010-06-02'),  
  22. (12, 12, 19, '2010-06-02'),  
  23. (13, 13, 8, '2010-06-02'),  
  24. (14, 14, 17, '2010-06-02'),  
  25. (15, 15, 21, '2010-06-02'),  
  26. (16, 15, 51, '2010-06-02'),  
  27. (17, 16, 17, '2010-06-02'),  
  28. (18, 17, 21, '2010-06-02'),  
  29. (19, 18, 9, '2010-06-02'),  
  30. (20, 19, 26, '2010-06-02'),  
  31. (21, 20, 11, '2010-06-02'),  
  32. (22, 21, 21, '2010-06-02'),  
  33. (23, 21, 48, '2010-06-02'),  
  34. (24, 23, 30, '2010-06-02'),  
  35. (25, 24, 21, '2010-06-02'),  
  36. (26, 25, 20, '2010-06-02');  
SQL自体あまり詳しい方ではないのだが、俺なりに解釈して、既存データベースに沿った形に変更してみた。
view plainprint?
  1. select  
  2.   (  
  3.     select  
  4.       count(distinct B.point)  
  5.     from  
  6.       ranking_history as B  
  7.     where  
  8.       B.point >= A.point  
  9.   ) as RANK,  A.point  
  10. from  
  11.   ranking_history as A  
そして上記SQLを実行してみた結果が以下だ。
ん？なんだこれ？これは本当に俺が求めているデータなのか？一体全体どうなんだ？もしかして俺は大いなる勘違いをしてしまっているのではないか？

しかしそれはこの世の終わりではない。
とりあえずpointを昇順にしてみよう。
view plainprint?
  1. select  
  2.   (  
  3.     select  
  4.       count(distinct B.point)  
  5.     from  
  6.       ranking_history as B  
  7.     where  
  8.       B.point >= A.point  
  9.   ) as RANK,  A.point  
  10. from  
  11.   ranking_history as A  
  12. order by  
  13.  A.point desc  
このSQLの結果は以下のようになる。
うむ、どうにかまともになりつつある事がわかるとおもう。
RANKは昇順になっていて、pointもちゃんと降順に鳴っている。

しかしまだいろいろ足りない部分があるではないか。これではただ昇順データと降順データが建に並んでいるだけに過ぎず、何をもって正しいランキングなのかなぞ、誰も証明できないではないか。

しかしそれはこの世の終わりではない。
まずは重複データと思われる部分を細かく分けるために、user_Idフィールドも表示させてみよう。
view plainprint?
  1. select  
  2.   (  
  3.     select  
  4.       count(distinct B.point)  
  5.     from  
  6.       ranking_history as B  
  7.     where  
  8.       B.point >= A.point  
  9.   ) as RANK, A.user_id, A.point  
  10. from  
  11.   ranking_history as A  
  12. order by  
  13.  A.point desc  
内側のselectでA.user_idを追加しただけだ。
このSQLの結果は以下のようになる。
これで、今まで完全に重複していた8位の31ポイントなど、user_idが入ることで、しっかりと意味を持ったデータになった。
この時点で少々安心感が増した。

しかしここで1点考える。

現状、存在するすべてのデータを使っているため、ランキングが18位まで存在することになっている。
これを10位までにしたい場合どうするのか。

このSQLで単純にlimit句を使ってしまうと、単純に上から10個目、つまり8位で終わってしまう。
view plainprint?
  1. select  
  2.   (  
  3.     select  
  4.       count(distinct B.point)  
  5.     from  
  6.       ranking_history as B  
  7.     where  
  8.       B.point >= A.point  
  9.   ) as RANK, A.user_id, A.point  
  10. from  
  11.   ranking_history as A  
  12. order by  
  13.   A.point desc  
  14. limit  
  15.   10  
これの結果が、以下のようになってしまう。
たしかにlimit 10がしっかり働いていて大安心！
って、そうじゃない。そうではないんだ。
  • 上から10個
ではなく
  • RANKが10以下
にしたいわけだ。

多くの人がそうなのかも知れないし、俺だけなのかも知れないが、安易にlimitで出来るじゃん！とか思っていると、とたんにこう言った次のステージへ進むドアが勝手に開いてしまい、吸い込まれるように先に行かざるをえないケースというのが発生する。
このタイミングでレガーディな抵抗をするか、イノベーティブに前進するか、だ。

俺は素直に従おうと思う。

まずはwhere句でRANKの範囲を指定してみる。

view plainprint?
  1. select  
  2.   (  
  3.     select  
  4.       count(distinct B.point)  
  5.     from  
  6.       ranking_history as B  
  7.     where  
  8.       B.point >= A.point  
  9.   ) as RANK, A.user_id, A.point  
  10. from  
  11.   ranking_history as A  
  12. where  
  13.   A.RANK <= 10  
  14. order by  
  15.   A.point desc  
これを実行するとエラーが出る。
view plainprint?
  1. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 30' at line 17  
'LIMIT 0, 30'というのはphpMyAdminが勝手に付けるので無視するが、意味はこうだ。

貴様のSQLには文法上の誤りがある。
使っているMySQLのバージョンにあったマニュアルを一読されたし！

だ。相変わらず無粋且つ無礼なエラーメッセージである。正直、辟易である。

すでにしてもう意味を訳してみようなどと思わなくなるレベルまで達してしまっている。
もう少し、なんていうかこう、CakePHPのエラーまでじゃなくても、せめてphp位のエラーはだして欲しいと切に思う。

多分だけど、A.RANKがまずいような気がする。そして次回はそれをテストする。
まだまだ先は遠そうだが、出来るまでやるのが主義なので、頑張ろうと思う。
もし失敗してもまたやり直せば良い。それがこの世の終わりではないのだから。

続く。


Follow:

Created by ThemeXpose