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

SQL実行コスト

統計のキャッシュを利用する

データ数が多い順に、"ゲームのID"を100本出力します。

SQL (A)

SELECT game , COUNT(uid)
  FROM userreview
 WHERE play='t'
 GROUP BY game
 ORDER BY COUNT(uid) DESC
 LIMIT 100
 

SQL (B)

SELECT id , count2
  FROM gamelist
 WHERE count2 IS NOT NULL
 ORDER BY count2 DESC
 LIMIT 100
 

実行コスト (A)

Limit (cost=25465.55..25465.80 rows=100 width=11)

実行コスト (B)

Limit (cost=997.02..997.27 rows=100 width=8)}

AとBは、ほぼ同じ結果を得られますが、実行コストは雲泥の差です。
Aは全ユーザーのレビューから、リアルタイムな統計を出力します。
GROUP BY句による集約関数もコスト増に繋がりますが、なによりplayカラムにはインデックスが貼られていない為、50万件を超えるデータを総当りでチェックしています。
Bは、gamelist内の各ゲームのキャッシュされた統計情報を出力します。
集約関数も不要ですし、userreviewテーブルと比べれば、gamelistテーブルは参照するデータ数自体がかなり少なくて済みます。
キャッシュされた統計情報の取得タイミングは不明ですが、例えば「今日の投稿数」などを出力したい場合は、Aのようにリアルタイムな統計情報が必要です。
ですが、「累計の投稿数」などの場合はキャッシュ値で十分なケースが多いはずです。
そして、上のケースの場合、toukei_temp_tableテーブルを使うことをおススメします。

SQL (C)

SELECT *
  FROM toukei_temp_table
 ORDER BY COUNT DESC
 LIMIT 100
 

実行コスト (C)

Limit (cost=807.97..808.22 rows=100 width=8)}

もし、このテーブルの存在を知らなかった人がいれば、Cを実行してみれば、このテーブルの便利さはすぐ実感できるでしょう。
SQLに応じて、どのテーブルをどのように使うのかを考えることが重要です。

統計のキャッシュが格納されているテーブル・カラム

toukei_temp_tableのようなテーブル・カラムのメモです。サンプルとして、ゲームIDが1(game = 1)のゲームを指定しています。
逐次、追記予定。

• toukei_temp_table.median : ゲーム毎の中央値(こちらも参照のこと)

SELECT ROUND(AVG(DISTINCT tokuten), 0)
  FROM (SELECT UR1.tokuten
          FROM userreview AS UR1, userreview AS UR2
         WHERE UR1.game = 1 AND UR1.tokuten IS NOT NULL
           AND UR2.game = UR1.game 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 tmp
 

• toukei_temp_table.average : ゲーム毎の平均値

SELECT ROUND(AVG(tokuten), 2)
  FROM userreview
 WHERE game = 1 AND tokuten IS NOT NULL
 

• toukei_temp_table.stddev : ゲーム毎の標準偏差

SELECT ROUND(STDDEV(tokuten), 0)
  FROM userreview
 WHERE game = 1 AND tokuten IS NOT NULL
 

• toukei_temp_table.count : ゲーム毎の得点登録者数

SELECT COUNT(uid)
  FROM userreview
 WHERE game = 1 AND tokuten IS NOT NULL
 

• toukei_temp_table.allcount : ゲーム毎の得点登録者数＋積んでいるユーザー数

SELECT COUNT(uid)
  FROM userreview
 WHERE game = 1 AND ((possession = TRUE AND PLAY = FALSE) OR tokuten IS NOT NULL)
 

• gamelist.the_number_of_uid_which_input_pov : ゲーム毎のユニークなPOV登録者数

SELECT COUNT(DISTINCT uid)
  FROM povgroups
 WHERE game = 1
 

• gamelist.the_number_of_uid_which_input_play : ゲーム毎のプレイ登録者数

SELECT COUNT(*)
  FROM userreview
 WHERE play = TRUE AND game = 1
 

• total_pov_enrollment_of_a : ゲーム毎のPOVランクAの総数（b,cも同様）

SELECT COUNT(*)
  FROM povgroups
 WHERE game = 1 AND rank= 'A'
 

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