SQL_Tips004 - ErogameScape -エロゲー批評空間- の備忘録

SQLの実行コスト

中央値の算出

中央値の求め方はいくつかあります。
ここでは、2009/12/31発売のゲームの中央値一覧を取得する方法についてサンプルを示します。

自己非等値結合をHAVING句で使う

google等で検索するとヒットし易いパターンです。

SQL

SELECT gl.id
     , gl.gamename
     , (
       SELECT ROUND(AVG(DISTINCT foo.tokuten),1)
         FROM (SELECT ur1.tokuten
                 FROM userreview AS ur1, userreview AS ur2
                WHERE ur1.game = gl.id AND ur1.tokuten IS NOT NULL
                  AND ur2.game = gl.id AND ur2.tokuten IS NOT NULL
             GROUP BY ur1.tokuten
               HAVING SUM(CASE WHEN ur2.tokuten >= ur1.tokuten THEN 1 ELSE 0 END) 
                          >= COUNT(*) / 2.0
                  AND SUM(CASE WHEN ur2.tokuten <= ur1.tokuten THEN 1 ELSE 0 END) 
                          >= COUNT(*) / 2.0 ) AS foo
       ) AS median
  FROM gamelist AS gl
 WHERE gl.sellday = CAST('2009/12/31' AS DATE)
 

出力結果

id	gamename	median
13678	みんなのお姉さん	10.0
13706	死舞草	60.0
13839	ノーパンアルバイト！	85.0
13507	東方痴態抄	70.0
13543	魔封少女・小夜子	65.0
13538	姉と小町♂ ～巨乳姉妹の女装っ娘いじり～	72.0
13676	海と温泉と夏休み
13552	SchoolCaptain2 会王をねらえ!
13687	St/PinRO2nd	40.0
13482	くのいちお姉さん静音のご奉仕任務 ～拙者の全ては若様のもの素敵な子種でお世継ぎを授けてください～	70.0

実行コスト

Bitmap Heap Scan on gamelist gl (cost=2.77..60895.60 rows=3 width=28)

OFFSET句とLIMIT句による方法

中央値とは何かを、もっと単純に考えると、ORDER BYでソートして、OFFSETとLIMITで切り出す方法がイメージしやすい。

SQL

SELECT gl.id
     , gl.gamename
     ,(
       SELECT ROUND(AVG(foo.tokuten),1)
         FROM (
               SELECT tokuten
                 FROM userreview 
                WHERE game = gl.id AND tokuten IS NOT NULL 
                ORDER BY tokuten 
               OFFSET (
                       SELECT CASE WHEN CEIL(COUNT(tokuten)/2.0) > 0
                                        THEN CEIL(COUNT(tokuten)/2.0)-1
                                   ELSE 0
                              END
                         FROM userreview
                        WHERE game = gl.id AND tokuten IS NOT NULL
                      )
                LIMIT (
                       SELECT CASE WHEN MOD(COUNT(tokuten),2) = 0 THEN 2 ELSE 1 END
                         FROM userreview
                        WHERE game = gl.id AND tokuten IS NOT NULL
                      )
              ) AS foo
       ) AS median
  FROM gamelist AS gl
 WHERE gl.sellday = CAST('2009/12/31' AS DATE)
 

出力結果

id	gamename	median
13678	みんなのお姉さん	10.0
13706	死舞草	60.0
13839	ノーパンアルバイト！	85.0
13507	東方痴態抄	70.0
13543	魔封少女・小夜子	65.0
13538	姉と小町♂ ～巨乳姉妹の女装っ娘いじり～	72.0
13676	海と温泉と夏休み
13552	SchoolCaptain2 会王をねらえ!
13687	St/PinRO2nd	40.0
13482	くのいちお姉さん静音のご奉仕任務 ～拙者の全ては若様のもの素敵な子種でお世継ぎを授けてください～	70.0

実行コスト

Bitmap Heap Scan on gamelist gl (cost=2.77..2258.99 rows=3 width=28)

分析関数を使用する

批評空間のPostgreSQLのバージョンが8以降になった為、window関数が使えるようになった。これを使うと…

SQL

SELECT gl.id
     , gl.gamename
     ,(
       SELECT ROUND(AVG(foo.tokuten),1)
         FROM (SELECT ur.tokuten,
                      ROW_NUMBER() OVER(ORDER BY ur.tokuten) AS rank
                    , COUNT(ur.tokuten) OVER() AS cnt
                 FROM userreview AS ur
                WHERE ur.game = gl.id) AS foo
        WHERE (MOD(foo.cnt,2) = 0 AND foo.rank IN (foo.cnt/2.0, foo.cnt/2.0+1))
           OR (MOD(foo.cnt,2) = 1 AND foo.rank = CEIL(foo.cnt/2.0))
       ) AS median
  FROM gamelist AS gl
 WHERE gl.sellday = CAST('2009/12/31' AS DATE)
 

出力結果

id	gamename	median
13678	みんなのお姉さん	10.0
13706	死舞草	60.0
13839	ノーパンアルバイト！	85.0
13507	東方痴態抄	70.0
13543	魔封少女・小夜子	65.0
13538	姉と小町♂ ～巨乳姉妹の女装っ娘いじり～	72.0
13676	海と温泉と夏休み
13552	SchoolCaptain2 会王をねらえ!
13687	St/PinRO2nd	40.0
13482	くのいちお姉さん静音のご奉仕任務 ～拙者の全ては若様のもの素敵な子種でお世継ぎを授けてください～	70.0

実行コスト

Bitmap Heap Scan on gamelist gl (cost=2.77..794.46 rows=3 width=28)

ようは、同じ結果が得られても、やり方によってはこんなにコストが違うんだよ、というお話でした。

【管理】POSTボタンを生成しました。