« WindowsPhoneアプリ開発その1 | トップページ | EC-CUBE 2.13.1 モバイルサイトが真っ白な件 »

[SQL] 投稿数の多い人ランキング

今回の課題。。。

とあるSNS系のサイト構築にて。
ある一定期間に、多く呟いたユーザーベスト3を抽出することになりました。


 1位 hogeさん 20回
 2位 fugaさん 18回
 3位 pugiさん 11回

 ↑こんな感じに出したいというご要望。


使うテーブルは以下の2つ。
 ・TWEET_DATAテーブル 呟いたデータが格納されています。
 ・USER_MASTERテーブル ユーザのデータが格納されています。


【TWEET_DATA】

P REGIST_DATETIME DATETIME
P USER_ID         NUMBER
 BODY            VARCHAR2



【USER_MASTER】
P USER_ID         NUMBER
 HANDLE_MN       VARCHAR2
 REAL_NM         VARCHAR2
 PROFILE         VARCHAR2


で。
参考にさせていただきましたのは、以下のページです。
↓ ↓ ↓

http://www.geocities.jp/mickindex/database/db_TaT.html


今回は呟きテーブルの中で定められた期間の中で、出現回数が多いものをカウントす
るので、
テーブルそのものではなく、擬似テーブルを使ってランキングを抜き出すことにして
みた。


範囲指定の日付はバインド変数で指定。

抽出する範囲開始日 p_From = '2013/12/01 00:00:00'
抽出する範囲終了日 p_From = '2013/12/31 23:59:59'
抽出する順位        p_Rank = 3

-------------------------------------------------------------------------------

   SELECT MIN(DATA1.CNT) QUANT
         ,DATA2.USER_ID
         ,DATA2.HANDLE_MN
   FROM
      (
         SELECT COUNT(TD.USER_ID) AS CNT
               ,TD.USER_ID
               ,HANDLE_MN
         FROM  TWEET_DATA TD
               ,USER_MASTER MST
         WHERE TD.USER_ID = MST.USER_ID
               AND TD.REGIST_DATETIME BETWEEN :p_From AND :p_
To
         GROUP BY TD.USER_ID
               ,HANDLE_MN
      ) DATA1
      ,
       (
         SELECT COUNT(TD.USER_ID) AS CNT
               ,TD.USER_ID
               ,HANDLE_MN
         FROM  TWEET_DATA K
               ,USER_MASTER MST
         WHERE TD.USER_ID = MST.USER_ID
               AND TD.REGIST_DATETIME BETWEEN :p_From AND :p_
To
         GROUP BY TD.USER_ID
               ,HANDLE_MN
      ) DATA2
   WHERE DATA1.CNT >= DATA2.CNT
  GROUP BY DATA2.CNT
         ,DATA2.USER_ID
         ,DATA2.HANDLE_MN
  HAVING COUNT(DISTINCT DATA1.CNT ) <= :p_Rank
   ORDER BY QUANT DESC


-------------------------------------------------------------------------------



出力結果はこちら
↓ ↓ ↓
QUANT   USER_ID   HANDLE_MN
=========================================
38    0012906   hoge
38    0020313   fuga
36    1000008   pugi
36    0300915   boo
36    0234927   aaa
35    0010900   test



かなり近い結果は出ているものの、上位3位の考え方がちょっと違う。

同率1位と同率2位があるので、
このように出て欲しかったのだけれど。。。
↓ ↓ ↓
QUANT   USER_ID   HANDLE_MN
=========================================
38    0012906   hoge
38    0020313   fuga
36    1000008   pugi
36    0300915   boo
36    0234927   aaa



漏れるよりはいいので、取得したあとにプログラムで順位を数えるようにするしかな
いのかなー???

|

« WindowsPhoneアプリ開発その1 | トップページ | EC-CUBE 2.13.1 モバイルサイトが真っ白な件 »

コメント

コメントを書く



(ウェブ上には掲載しません)




トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/520403/59202845

この記事へのトラックバック一覧です: [SQL] 投稿数の多い人ランキング:

« WindowsPhoneアプリ開発その1 | トップページ | EC-CUBE 2.13.1 モバイルサイトが真っ白な件 »