SQL

CHECK制約はCASE式も使える

※今日までCHECK制約はCASE式も使えることを知らなかったので…メモします。

SQLの本やドキュメントのCHECK制約の説明は、制約の内容がとても単純なものになっていると思います。
例えば、PostgreSQLのドキュメントの場合、
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);
のようにpriceが0を超える値だけ許可という単純なものです。
CKECKの()の中身はboolを返す式だったらOKですので、CASE式が使えます。

ErogameScapeではユーザーさんが任意のゲームについて、動作したOSを記載する項目があります。
OSを入力するとともに、1:動作、2:条件付きで動作、3:動作不可・インストール不可を選択します。

OSに「Wine on macOS」を追加することになったのですが、「Wine on macOS」は組み合わせがたくさんあるので、「2:条件付きで動作」のみ選択可として、memo列に組み合わせを書いて頂くことにしました。

「WIne on macOS」を選択した場合は「2:条件付きで動作」だけを許容したいです。
これをCHECK制約として追加すると以下のようになります。
ap2=# SELECT * FROM platformlist ;
 id  |       name       | sort | model
-----+------------------+------+-------
   1 | Windows98        |  100 | PC
   2 | Windows2000      |  200 | PC
   3 | WindowsXP        |  300 | PC
   4 | WindowsVista     |  400 | PC
   5 | Windows7(32bit)  |  500 | PC
   6 | Windows7(64bit)  |  600 | PC
   7 | Windows8(32bit)  |  700 | PC
   8 | Windows8(64bit)  |  800 | PC
 164 | Windows10(32bit) |  900 | PC
 165 | Windows10(64bit) | 1000 | PC
 317 | Wine on macOS    |  650 | PC
 
 ap2=# \d support_platform
                       テーブル "public.support_platform"
    列     |             型              | 照合順序 | Null 値を許容 | デフォルト
-----------+-----------------------------+----------+---------------+------------
 uid       | text                        |          | not null      |
 game      | integer                     |          | not null      |
 platform  | integer                     |          | not null      |
 operation | integer                     |          | not null      |
 memo      | text                        |          |               |
 timestamp | timestamp without time zone |          | not null      | now()
インデックス:
    "support_platform_pkey" PRIMARY KEY, btree (uid, game, platform)
外部キー制約:
    "support_platform_game_fkey" FOREIGN KEY (game) REFERENCES gamelist(id)
    "support_platform_platform_fkey" FOREIGN KEY (platform) REFERENCES platformlist(id)
    "support_platform_uid_fkey" FOREIGN KEY (uid) REFERENCES myuser(uid) ON UPDATE CASCADE ON DELETE CASCADE

凡例
operation
 1 : 動作
 2 : 条件付きで動作
 3 : 動作不可・インストール不可

ap2=# ALTER TABLE support_platform
              ADD CONSTRAINT support_platform_operation_check
      CHECK (CASE
               WHEN platform = 317
                  THEN operation = 2
           END);
ap2=# INSERT INTO support_platform (uid,game,platform,operation) VALUES ('ap2',1,1,1);
INSERT 0 1
ap2=# INSERT INTO support_platform (uid,game,platform,operation) VALUES ('ap2',1,317,2);
INSERT 0 1
ap2=# INSERT INTO support_platform (uid,game,platform,operation) VALUES ('ap2',1,317,1);
ERROR:  new row for relation "support_platform" violates check constraint "support_platform_operation_check"
DETAIL:  Failing row contains (ap2, 1, 317, 1, null, 2019-09-30 19:45:35.259753).


WHERE句でPOVをいくつかに絞る場合など、多くの場合、ORよりINの方が速い

「WHERE句でPOVをいくつかに絞る場合など、多くの場合、ORよりINの方が速いです」とユーザーさんからコメントを頂いたので、なんでそうなるのかな?と思いましたので、EXPLAINで実行計画を確認してみました。

まず、たくさんのPOVをORで指定した場合の実行計画は以下のとおりです。
ap2=# EXPLAIN SELECT '<img  src="http://pics.dmm.co.jp/digital/pcgame/' || gamelist.dmm || '/' || gamelist.dmm || 'ps.jpg">' AS 画像 ,'' || gamelist.gamename || '' AS タイトル ,gamelist.sellday as 発売日 ,CASE WHEN brandlist.url IS NOT NULL THEN '' || brandlist.brandname || '' ELSE brandlist.brandname || ' (解散)' END AS ブランド ,gamelist.median as 中央値 ,gamelist.count2 as データ数 ,(CASE WHEN gamelist.dmm_subsc IS NOT NULL THEN '有' END) AS DMMプレミアム ,SUM(CASE WHEN povgroups.pov=33 THEN 1 ELSE 0 END) AS 萌ゲー ,SUM(CASE WHEN povgroups.pov=10 THEN 1 ELSE 0 END) AS コメディ ,SUM(CASE WHEN povgroups.pov=57 THEN 1 ELSE 0 END) AS シナリオいい ,SUM(CASE WHEN povgroups.pov=193 THEN 1 ELSE 0 END) AS 主人公強い ,SUM(CASE WHEN povgroups.pov=50 THEN 1 ELSE 0 END) AS ロリ ,SUM(CASE WHEN povgroups.pov=314 THEN 1 ELSE 0 END) AS 戦う女の子 ,SUM(CASE WHEN povgroups.pov=369 THEN 1 ELSE 0 END) AS 女装 ,SUM(CASE WHEN povgroups.pov=379 THEN 1 ELSE 0 END) AS 主人公モテモテ ,SUM(CASE WHEN povgroups.pov=1 THEN 1 ELSE 0 END) AS おかず ,SUM(CASE WHEN povgroups.pov=13 THEN 1 ELSE 0 END) AS 主人公素敵 ,SUM(CASE WHEN povgroups.pov=216 THEN 1 ELSE 0 END) AS アナルSEX ,SUM(CASE WHEN povgroups.pov=250 THEN 1 ELSE 0 END) AS 陵辱レイプ ,SUM(CASE WHEN povgroups.pov=124 THEN 1 ELSE 0 END) AS 女の子に襲 ,SUM(CASE WHEN povgroups.pov=3 THEN 1 ELSE 0 END) AS ゲーム性有 ,SUM(CASE WHEN povgroups.pov=34 THEN 1 ELSE 0 END) AS キャラ取巻く世界観 ,SUM(CASE WHEN povgroups.pov=235 THEN 1 ELSE 0 END) AS 奇人変人 ,SUM(CASE WHEN povgroups.pov=72 THEN 1 ELSE 0 END) AS 主人公ダメ ,SUM(CASE WHEN povgroups.pov=528 THEN 1 ELSE 0 END) AS 個別ルートダメ FROM (gamelist INNER JOIN brandlist ON gamelist.brandname = brandlist.id) INNER JOIN povgroups ON gamelist.ID=povgroups.game WHERE gamelist.median >= 70 AND ((gamelist.count2 >=80 AND gamelist.sellday >= '2008-01-01') OR (gamelist.count2 >=40 AND gamelist.sellday >= CURRENT_DATE - interval '6 month')) AND (povgroups.pov=33 OR povgroups.pov=10 OR povgroups.pov=57 OR povgroups.pov=193 OR povgroups.pov=50 OR povgroups.pov=314 OR povgroups.pov=369 OR povgroups.pov=379 OR povgroups.pov=1 OR povgroups.pov=13 OR povgroups.pov=216 OR povgroups.pov=250 OR povgroups.pov=124 OR povgroups.pov=3 OR povgroups.pov=34 OR povgroups.pov=235 OR povgroups.pov=72 OR povgroups.pov=528) GROUP BY gamelist.sellday,gamelist.count2,gamelist.median,gamelist.dmm,gamelist.id,brandlist.url,brandlist.brandname,gamelist.dmm_subsc ORDER BY gamelist.sellday DESC,gamelist.count2 DESC;

                    QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
 Sort  (cost=65952.58..65973.13 rows=8217 width=355)
   Sort Key: gamelist.sellday DESC, gamelist.count2 DESC
   ->  GroupAggregate  (cost=64206.29..65418.30 rows=8217 width=355)
         Group Key: gamelist.id, brandlist.url, brandlist.brandname
         ->  Sort  (cost=64206.29..64226.83 rows=8217 width=126)
               Sort Key: gamelist.id, brandlist.url, brandlist.brandname
               ->  Hash Join  (cost=11426.17..63672.01 rows=8217 width=126)
                     Hash Cond: (povgroups.game = gamelist.id)
                     ->  Bitmap Heap Scan on povgroups  (cost=9403.99..60155.36 rows=376612 width=8)
                           Recheck Cond: ((pov = 33) OR (pov = 10) OR (pov = 57) OR (pov = 193) OR (pov = 50) OR (pov = 314)
 OR (pov = 369) OR (pov = 379) OR (pov = 1) OR (pov = 13) OR (pov = 216) OR (pov = 250) OR (pov = 124) OR (pov = 3) OR (pov
= 34) OR (pov = 235) OR (pov = 72) OR (pov = 528))
                           ->  BitmapOr  (cost=9403.99..9403.99 rows=408734 width=0)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..344.66 rows=18164 width=0)
                                       Index Cond: (pov = 33)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..391.88 rows=20726 width=0)
                                       Index Cond: (pov = 10)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..1231.64 rows=65495 width=0)
                                       Index Cond: (pov = 57)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..386.18 rows=20500 width=0)
                                       Index Cond: (pov = 193)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..357.75 rows=18842 width=0)
                                       Index Cond: (pov = 50)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..351.49 rows=18541 width=0)
                                       Index Cond: (pov = 314)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..106.05 rows=5549 width=0)
                                       Index Cond: (pov = 369)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..439.66 rows=23364 width=0)
                                       Index Cond: (pov = 379)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..532.97 rows=28339 width=0)
                                       Index Cond: (pov = 1)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..785.58 rows=41754 width=0)
                                       Index Cond: (pov = 13)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..106.05 rows=5549 width=0)
                                       Index Cond: (pov = 216)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..292.88 rows=15526 width=0)
                                       Index Cond: (pov = 250)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..106.05 rows=5549 width=0)
                                       Index Cond: (pov = 124)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..106.05 rows=5549 width=0)
                                       Index Cond: (pov = 3)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..1071.77 rows=56979 width=0)
                                       Index Cond: (pov = 34)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..632.53 rows=33614 width=0)
                                       Index Cond: (pov = 235)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..360.01 rows=19144 width=0)
                                       Index Cond: (pov = 72)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..106.05 rows=5549 width=0)
                                       Index Cond: (pov = 528)
                     ->  Hash  (cost=2015.70..2015.70 rows=518 width=122)
                           ->  Hash Join  (cost=473.47..2015.70 rows=518 width=122)
                                 Hash Cond: (gamelist.brandname = brandlist.id)
                                 ->  Bitmap Heap Scan on gamelist  (cost=262.19..1797.30 rows=518 width=81)
                                       Recheck Cond: (median >= 70)
                                       Filter: (((count2 >= 80) AND (sellday >= '2008-01-01'::date)) OR ((count2 >= 40) AND
(sellday >= (('now'::cstring)::date - '6 mons'::interval))))
                                       ->  Bitmap Index Scan on tokuten_index  (cost=0.00..262.06 rows=8237 width=0)
                                             Index Cond: (median >= 70)
                                 ->  Hash  (cost=152.79..152.79 rows=4679 width=49)
                                       ->  Seq Scan on brandlist  (cost=0.00..152.79 rows=4679 width=49)
(57 行)
pov1つ1つにBitmap Index Scan on povgroups_povが動いています。
->  BitmapOr  (cost=9403.99..9403.99 rows=408734 width=0)
  ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..344.66 rows=18164 width=0)
    Index Cond: (pov = 33)
  ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..391.88 rows=20726 width=0)
    Index Cond: (pov = 10)
以下略

一方、たくさんのPOVをINで指定した場合の実行計画は以下のとおりです。
ap2=# EXPLAIN SELECT '<img src="http://pics.dmm.co.jp/digital/pcgame/' || gamelist.dmm || '/' || gamelist.dmm || 'ps.jpg">' AS 画像 ,'' || gamelist.gamename || '' AS タイトル ,gamelist.sellday as 発売日 ,CASE WHEN brandlist.url IS NOT NULL THEN '' || brandlist.brandname || '' ELSE brandlist.brandname || ' (解散)' END AS ブランド ,gamelist.median as 中央値 ,gamelist.count2 as データ数 ,(CASE WHEN gamelist.dmm_subsc IS NOT NULL THEN '有' END) AS DMMプレミアム ,SUM(CASE WHEN povgroups.pov=33 THEN 1 ELSE 0 END) AS 萌ゲー ,SUM(CASE WHEN povgroups.pov=10 THEN 1 ELSE 0 END) AS コメディ ,SUM(CASE WHEN povgroups.pov=57 THEN 1 ELSE 0 END) AS シナリオいい ,SUM(CASE WHEN povgroups.pov=193 THEN 1 ELSE 0 END) AS 主人公強い ,SUM(CASE WHEN povgroups.pov=50 THEN 1 ELSE 0 END) AS ロリ ,SUM(CASE WHEN povgroups.pov=314 THEN 1 ELSE 0 END) AS 戦う女の子 ,SUM(CASE WHEN povgroups.pov=369 THEN 1 ELSE 0 END) AS 女装 ,SUM(CASE WHEN povgroups.pov=379 THEN 1 ELSE 0 END) AS 主人公モテモテ ,SUM(CASE WHEN povgroups.pov=1 THEN 1 ELSE 0 END) AS おかず ,SUM(CASE WHEN povgroups.pov=13 THEN 1 ELSE 0 END) AS 主人公素敵 ,SUM(CASE WHEN povgroups.pov=216 THEN 1 ELSE 0 END) AS アナルSEX ,SUM(CASE WHEN povgroups.pov=250 THEN 1 ELSE 0 END) AS 陵辱レイプ ,SUM(CASE WHEN povgroups.pov=124 THEN 1 ELSE 0 END) AS 女の子に襲 ,SUM(CASE WHEN povgroups.pov=3 THEN 1 ELSE 0 END) AS ゲーム性有 ,SUM(CASE WHEN povgroups.pov=34 THEN 1 ELSE 0 END) AS キャラ取巻く世界観 ,SUM(CASE WHEN povgroups.pov=235 THEN 1 ELSE 0 END) AS 奇人変人 ,SUM(CASE WHEN povgroups.pov=72 THEN 1 ELSE 0 END) AS 主人公ダメ ,SUM(CASE WHEN povgroups.pov=528 THEN 1 ELSE 0 END) AS 個別ルートダメ FROM (gamelist INNER JOIN brandlist ON gamelist.brandname = brandlist.id) INNER JOIN povgroups ON gamelist.ID=povgroups.game WHERE gamelist.median >= 70 AND ((gamelist.count2 >=80 AND gamelist.sellday >= '2008-01-01') OR (gamelist.count2 >=40 AND gamelist.sellday >= CURRENT_DATE - interval '6 month')) AND (povgroups.pov IN (33,10,57,193,50,314,369,379,1,13,216,250,124,3,34,235,72,528 )) GROUP BY gamelist.sellday,gamelist.count2,gamelist.median,gamelist.dmm,gamelist.id,brandlist.url,brandlist.brandname,gamelist.dmm_subsc ORDER BY gamelist.sellday DESC,gamelist.count2 DESC;
                                                                                    QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------
 Sort  (cost=54480.58..54502.87 rows=8918 width=355)
   Sort Key: gamelist.sellday DESC, gamelist.count2 DESC
   ->  HashAggregate  (cost=53471.84..53895.45 rows=8918 width=355)
         Group Key: gamelist.id, brandlist.url, brandlist.brandname
         ->  Hash Join  (cost=9425.55..52602.34 rows=8918 width=126)
               Hash Cond: (povgroups.game = gamelist.id)
               ->  Bitmap Heap Scan on povgroups  (cost=7403.37..48958.22 rows=408734 width=8)
                     Recheck Cond: (pov = ANY ('{33,10,57,193,50,314,369,379,1,13,216,250,124,3,34,235,72,528}'::integer[]))
                     ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..7301.18 rows=408734 width=0)
                           Index Cond: (pov = ANY ('{33,10,57,193,50,314,369,379,1,13,216,250,124,3,34,235,72,528}'::integer
[]))
               ->  Hash  (cost=2015.70..2015.70 rows=518 width=122)
                     ->  Hash Join  (cost=473.47..2015.70 rows=518 width=122)
                           Hash Cond: (gamelist.brandname = brandlist.id)
                           ->  Bitmap Heap Scan on gamelist  (cost=262.19..1797.30 rows=518 width=81)
                                 Recheck Cond: (median >= 70)
                                 Filter: (((count2 >= 80) AND (sellday >= '2008-01-01'::date)) OR ((count2 >= 40) AND (selld
ay >= (('now'::cstring)::date - '6 mons'::interval))))
                                 ->  Bitmap Index Scan on tokuten_index  (cost=0.00..262.06 rows=8237 width=0)
                                       Index Cond: (median >= 70)
                           ->  Hash  (cost=152.79..152.79 rows=4679 width=49)
                                 ->  Seq Scan on brandlist  (cost=0.00..152.79 rows=4679 width=49)
(20 行)
->  Bitmap Index Scan on povgroups_pov  (cost=0.00..7301.18 rows=408734 width=0)
 Index Cond: (pov = ANY ('{33,10,57,193,50,314,369,379,1,13,216,250,124,3,34,235,72,528}'::integer[]))
と、一回のBitmap Index Scan on povgroups_pov でpovを探しています。

ORでつなぐよりINを使った方が速いです。

クリエイター別感想数(上位10まで表示)

レビューサイトさんの情報を表示する画面に「クリエイター別感想数(上位10まで表示)」という項目があります。
この情報はWindow関数(分析関数)を使って取り出しています。
※いい方法があったら教えて欲しいです。

表示している内容

原画

クリエイター感想数感想リスト
リバ原あき 4 デュアルソウル
魔女狩りの夜に
淫魔制服狩り(PC98)
脅迫
影崎夕那(影崎由那、影山由多) 4 HIGHスク~ルDAYS
BACTA2
霧島診療室の午後
FILE
横田守 4 クローンドール 課外授業
SHANGRLIA2
ここは楽園荘
ここは楽園荘2
吉澤友章 3 Esの方程式
黒の断章 -THE LITERARY FRAGMENT-
つもバカ日誌
蒼月裕忠 3 TAXI幻夢譚 ~ストレンジ・ワールド ACT.2~
PERSONA ~淫虐の仮面~
ストレンジ・ワールド
マーシーラビット(MERCY RABBIT、マーシー兎) 3 VRデート 五月倶楽部
わくわく麻雀パニック!2 -刻・視・夢・想-
わくわく麻雀パニック! -式・神・伝・承-
野々原幹(ぢょんたいらん) 2 リトルモニカ物語
Ricotte ~アルペンブルの歌姫~
新井和崎(新井カズキ) 2 殻の中の小鳥
雛鳥の囀
まさはる 2 Floralia ~フローラリア~
ときどきシュガー
本田直樹 2 つもバカ日誌
らいむいろ戦奇譚 ~明治日本、乙女防人ス。~

シナリオ

クリエイター感想数感想リスト
TAMAMI 2 STARS
Oh! きつねさま
大槻涼樹 2 Esの方程式
黒の断章 -THE LITERARY FRAGMENT-
秋津環 2 Wing&Wind
TALK to TALK
リバ原あき 2 魔女狩りの夜に
脅迫
朝凪軽(GM、夕凪) 2 リングアウト!!
ルージュの伝説
藤原将(藤原たすく) 2 Floralia ~フローラリア~
ときどきシュガー
安部三郎 2 魔性の貌(かお)
BACTA2
茂手木亮平 2 Blind Games
FILE
古我望 1 Wind -a breath of heart-
片岡とも(ぽこつ☆とも、へぽこ☆とも) 1 White ~セツナサのカケラ~
渡部好範 1 忘レナ草 ~Forget-me-Not~

SQLで取り出した内容
 shubetu | creater_id |                creater_name                | count | game_id |                  gamename                   |  sellday   | rank
---------+------------+--------------------------------------------+-------+---------+---------------------------------------------+------------+------
       1 |        534 | リバ原あき                                 |     6 |     456 | デュアルソウル                              | 1995-04-15 |    1
       1 |        534 | リバ原あき                                 |     6 |     721 | 魔女狩りの夜に                              | 1995-10-13 |    1
       1 |        534 | リバ原あき                                 |     6 |   16254 | 淫魔制服狩り(PC98)                          | 1995-07-15 |    1
       1 |        534 | リバ原あき                                 |     6 |    4871 | 脅迫                                        | 1996-04-27 |    1
       1 |        625 | 影崎夕那(影崎由那、影山由多)               |     4 |     549 | HIGHスク~ルDAYS                            | 1996-12-06 |    2
       1 |        625 | 影崎夕那(影崎由那、影山由多)               |     4 |    9185 | BACTA2                                      | 1994-02-05 |    2
       1 |        625 | 影崎夕那(影崎由那、影山由多)               |     4 |    9189 | 霧島診療室の午後                            | 1995-08-01 |    2
       1 |        625 | 影崎夕那(影崎由那、影山由多)               |     4 |    9187 | FILE                                        | 1994-11-01 |    2
       1 |        650 | 横田守                                     |     4 |    1000 | クローンドール 課外授業                     | 1995-12-21 |    3
       1 |        650 | 横田守                                     |     4 |    4617 | SHANGRLIA2                                  | 1993-09-30 |    3
       1 |        650 | 横田守                                     |     4 |    3248 | ここは楽園荘                                | 1994-04-22 |    3
       1 |        650 | 横田守                                     |     4 |    1054 | ここは楽園荘2                               | 1995-10-20 |    3
       1 |        974 | 吉澤友章                                   |     3 |      95 | Esの方程式                                  | 1996-09-13 |    4
       1 |        974 | 吉澤友章                                   |     3 |    3711 | 黒の断章 -THE LITERARY FRAGMENT-            | 1995-07-14 |    4
       1 |        974 | 吉澤友章                                   |     3 |     438 | つもバカ日誌                                | 1998-08-07 |    4
       1 |       1084 | 蒼月裕忠                                   |     3 |    1093 | TAXI幻夢譚 ~ストレンジ・ワールド ACT.2~   | 1998-02-06 |    5
       1 |       1084 | 蒼月裕忠                                   |     3 |    3718 | PERSONA ~淫虐の仮面~                      | 1996-02-09 |    5
       1 |       1084 | 蒼月裕忠                                   |     3 |   14269 | ストレンジ・ワールド                        | 1996-05-31 |    5
       1 |       4563 | マーシーラビット(MERCY RABBIT、マーシー兎) |     3 |     538 | VRデート 五月倶楽部                         | 1996-10-25 |    6
       1 |       4563 | マーシーラビット(MERCY RABBIT、マーシー兎) |     3 |    2004 | わくわく麻雀パニック!2 -刻・視・夢・想-   | 1996-07-12 |    6
       1 |       4563 | マーシーラビット(MERCY RABBIT、マーシー兎) |     3 |    2005 | わくわく麻雀パニック! -式・神・伝・承-    | 1995-01-31 |    6
       1 |         26 | 野々原幹(ぢょんたいらん)                   |     2 |    1598 | リトルモニカ物語                            | 2001-09-21 |    7
       1 |         26 | 野々原幹(ぢょんたいらん)                   |     2 |    3008 | Ricotte ~アルペンブルの歌姫~              | 2003-09-26 |    7
       1 |         80 | 新井和崎(新井カズキ)                       |     2 |     190 | 殻の中の小鳥                                | 1996-02-29 |    8
       1 |         80 | 新井和崎(新井カズキ)                       |     2 |     619 | 雛鳥の囀                                    | 1997-03-28 |    8
       1 |        128 | まさはる                                   |     2 |    2070 | Floralia ~フローラリア~                   | 2002-05-31 |    9
       1 |        128 | まさはる                                   |     2 |    2553 | ときどきシュガー                            | 2003-01-31 |    9
       1 |        310 | 本田直樹                                   |     2 |     438 | つもバカ日誌                                | 1998-08-07 |   10
       1 |        310 | 本田直樹                                   |     2 |    2444 | らいむいろ戦奇譚 ~明治日本、乙女防人ス。~ | 2002-12-13 |   10
       2 |        534 | リバ原あき                                 |     6 |    4871 | 脅迫                                        | 1996-04-27 |    0
       2 |        534 | リバ原あき                                 |     6 |     721 | 魔女狩りの夜に                              | 1995-10-13 |    0
       2 |        164 | TAMAMI                                     |     2 |    1202 | STARS                                       | 1997-03-14 |    1
       2 |        164 | TAMAMI                                     |     2 |    1247 | Oh! きつねさま                              | 1996-09-20 |    1
       2 |        311 | 大槻涼樹                                   |     2 |      95 | Esの方程式                                  | 1996-09-13 |    2
       2 |        311 | 大槻涼樹                                   |     2 |    3711 | 黒の断章 -THE LITERARY FRAGMENT-            | 1995-07-14 |    2
       2 |        408 | 秋津環                                     |     2 |    1127 | Wing&Wind                                   | 2001-03-16 |    3
       2 |        408 | 秋津環                                     |     2 |    1872 | TALK to TALK                                | 2002-02-01 |    3
       2 |        784 | 朝凪軽(GM、夕凪)                           |     2 |    1459 | リングアウト!!                              | 1996-04-12 |    4
       2 |        784 | 朝凪軽(GM、夕凪)                           |     2 |    3993 | ルージュの伝説                              | 1996-05-17 |    4
       2 |       1399 | 藤原将(藤原たすく)                         |     2 |    2070 | Floralia ~フローラリア~                   | 2002-05-31 |    5
この情報は以下のSQLで取り出しています。
SELECT *
  FROM (
         SELECT t.shubetu
              , t.creater_id
              , t.creater_name
              , t.count
              , t.game_id
              , t.gamename
              , t.sellday
              , SUM(t.flag) OVER( PARTITION BY t.shubetu ORDER BY t.count DESC, t.creater_id, t.flag DESC) AS rank
           FROM ( SELECT c.id AS creater_id
                       , c.name AS creater_name
                       , s.shubetu
                       , COUNT(*) OVER( PARTITION BY c.id ) AS count
                       , g.id AS game_id
                       , g.gamename
                       , g.sellday
                       , CASE WHEN LAG(c.id,1) OVER() = c.id
                              THEN 0
                              ELSE 1
                   END AS flag
                    FROM createrlist c
                   INNER JOIN shokushu s
                           ON s.creater = c.id
                   INNER JOIN reviewpagelist r
                           ON r.game = s.game
                   INNER JOIN gamelist g
                           ON g.id = s.game
                   WHERE r.reviewpage = 497
                     AND s.shubetu IN (1,2)
                   GROUP BY c.id
                          , c.name
                          , s.shubetu
                          , g.id
                          , g.gamename
                          , g.sellday
                   ORDER BY s.shubetu
                          , count DESC
                          , c.id
                ) AS t
          ORDER BY shubetu
                 , count DESC
                 , t.creater_id
       ) AS t2
 WHERE rank <= 10
;
最後のrankを簡単に取り出す方法が思いつきませでした。rankを取得するrank関数はあるのですが、今回のケースではおそらく使えないのかな…と思いました。

ラノベオールタイムベストまとめ 2巻目

はじめに

データベースの醍醐味は、データをいろいろいじってみることで自分で想像だにしなかったことが見えてくること、だと思っています。
前回の文書は、投票数順、レーベル別順、年代別順、と投票順に並べてみたものでした。
この文書は、ちょっと視点をかえて、「どなたとどなたが同じラノベに投票したか」に焦点をあててみるものです。

投票された方、投票された方のフォロワーの方々、にしか面白くない文書になりそうな雰囲気がしますが…どうでしょうか…

前回同様、表を作るのに使ったSQLも記載していきます。
自己結合ってこうやって使うんだなあと思って頂ければいいかなと思います。

自己結合については、自己結合の使い方がよい文書ではないかと思います。
ミックさんの文書はお勧めです。

本題

ラノベオールタイムベストの基本ルールは、1人が10のライトノベルに投票する、でした。
ある方とある方が同じライトノベルに投票した…それも数が多いほど、そのお二方の趣向は似ていると思って良いと考えられるでしょう。
最初に、同じライトノベルに投票した数が多かった方々のご紹介です。

概要
  1. 参加者数 : 197人
  2. 有効投票数 : 1776
  3. 1人あたりの平均投票数 : 9
同じライトノベルに投票した数が多かった方々

twittertwitter同じラノベに
投票した数
kobu_wirdrehpic07
VVV0201yogu4956
kobu_wirdsusuki_AKI6
koo_koutekeru6
moukwairehpic06
rehpic0susuki_AKI6
Kotobuki_Prehpic05
MEG_apr02kobu_wird5
MEG_apr02aoi_kana5
MEG_apr02kentosan275
MEG_apr02moyashi45
Nagara_KDZKsusuki_AKI5
Nagara_KDZKrehpic05
Raining_summerVVV02015
Rebellion_vpirows5
Rebellion_vtekeru5
aoi_kanakobu_wird5
kentosan27susuki_AKI5
kobu_wirdrinda09075
moyashi4tsukushi_k5
moyashi4rehpic05
nai_omoitsukaokatachi5
rehpic0tekeru5
rehpic0yui06245
rehpic0tsukushi_k5

5つ以上同じラノベに投票した方々の表です。同じラノベに投票した数順に並び替えています。
同じラノベに投票した数が多いほど趣向が似ているかもしれません。
SELECT a1.twitter
     , a2.twitter
     , COUNT(*)
  FROM lightnovel.alltimebest a1
 INNER JOIN lightnovel.alltimebest a2
         ON a1.isbn = a2.isbn
 WHERE a1.twitter <> a2.twitter
   AND a1.twitter < a2.twitter
 GROUP BY a1.twitter
        , a2.twitter
HAVING COUNT(*) >= 5
 ORDER BY COUNT(*) DESC
        , a1.twitter

ちなみに、1つ以上同じラノベに投票した方々の組み合わせ数は以下の通りです。

同じラノベに投票した数 同じラノベに投票した方の組み合わせ数
7 1
6 5
5 19
4 72
3 258
2 907
1 3704

投票したラノベが5つ、6つ、7つ被るのは相当すごいと思います。
SELECT count
     , COUNT(*)
  FROM (
         SELECT a1.twitter
              , a2.twitter
              , COUNT(*)
           FROM lightnovel.alltimebest a1
          INNER JOIN lightnovel.alltimebest a2
                  ON a1.isbn = a2.isbn
          WHERE a1.twitter <> a2.twitter
            AND a1.twitter < a2.twitter
          GROUP BY a1.twitter
                 , a2.twitter
         HAVING COUNT(*) >= 1
       ) AS t
 GROUP BY count
 ORDER BY COUNT(*) 
;

ライトノベルオールスターに参加された人数は197人でした。
ルールにのっとって10のラノベに投票された方で、投票したラノベが他の方と完全にかぶらない方は一人もいらっしゃいませんでした。
SELECT twitter
     , COUNT(*)
  FROM lightnovel.alltimebest
 WHERE twitter IN ( SELECT DISTINCT twitter
                      FROM lightnovel.alltimebest
                     WHERE twitter NOT IN (
                                            SELECT twitter
                                              FROM (
                                                     SELECT a1.twitter
                                                          , COUNT(*)
                                                       FROM lightnovel.alltimebest a1
                                                      INNER JOIN lightnovel.alltimebest a2
                                                              ON a1.isbn = a2.isbn
                                                      WHERE a1.twitter <> a2.twitter
                                                      GROUP BY a1.twitter
                                                             , a2.twitter
                                                     HAVING COUNT(*) >= 1
                                                   ) AS t1
                                          )
                   )
 GROUP BY twitter
HAVING COUNT(*) >= 10 ;

同様に、1つしかかぶらなかった方は、6人でした。
SELECT twitter
     , COUNT(*)
  FROM lightnovel.alltimebest
 WHERE twitter IN ( SELECT DISTINCT twitter
                      FROM lightnovel.alltimebest
                     WHERE twitter NOT IN (
                                            SELECT twitter
                                              FROM (
                                                     SELECT a1.twitter
                                                          , COUNT(*)
                                                       FROM lightnovel.alltimebest a1
                                                      INNER JOIN lightnovel.alltimebest a2
                                                              ON a1.isbn = a2.isbn
                                                      WHERE a1.twitter <> a2.twitter
                                                      GROUP BY a1.twitter
                                                             , a2.twitter
                                                     HAVING COUNT(*) >= 2
                                                   ) AS t1
                                          )
                   )
 GROUP BY twitter
HAVING COUNT(*) >= 10 ;
同様に、2つしかかぶらなかった方は、24人でした。
8割近くの方々には「3つ以上同じラノベを選んだ方」が1人はいらっしゃるかなと思います。

続きを読む

ラノベオールタイムベストまとめ

はじめに

2013年5月30日にi_j_さんつぶやきによって、5月30日から6月2日にかけて、ラノベクラスタの方々が、過去の全作品を通して、選出した良ラノベ10件をあげていく営みが行われました。
その結果、総参加者数197人、総有効投票数1776、というデータが集まりましたが、twitterは何もしないとデータは流れて消えて行くのみでもったいないと思いました。

そこで、「#ラノベオールタイムベスト」でつぶやかれた書籍を集計し報告するのがこの文書の目的です。
ついでに、集計のためにSQLのウィンドウ関数を使ってみたので、SQLのウィンドウ関数の使い方も実例を交えて書いていくことにします。

※フレームワークを使っていると、フレームワークに実装されたORMをなるべく使うことになる(直でSQL書いたら負けみたいな感じを受けます。自分は、SELECTについては、直でSQL書いちゃうのですが)…ので、ORMでデータを取り出し、スクリプト言語で取り出したデータを加工する…つまり集計することになるので、ちょっとしたサイトを構築する場合、ウィンドウ関数を使う機会はないのかなという気がしますが、こういう静的な文書を作る場合は、SQLでちょちょいと集計して加工できるのは便利かなと思います。

※書いてる途中で力尽きてきたので、ラノベオールタイムベストのデータの加工/集計については別記事で書くことにします。とりあえず、ErogameScapeで慣れている(そんな方は数少ないと思いますが…)のために、実行したSQLだけ記載しておきます。


私について

日曜プログラマーです。
仕事ではサーバー、NW機器を監視しています。

最近読んだラノベは、なれる!SE (9) ラクして儲かる?サービス開発 (電撃文庫)です。
SEな方々、またその周辺の方々に、大変お勧めのラノベかと思います。

最近読んだ小説はハゲタカ(上) (講談社文庫)です。
ハゲタカは…投資/投機に興味がないとちょっとつらいと思います。
同じ真山 仁さんの著書としてベイジン〈上〉 (幻冬舎文庫) の方が多くの方にお勧めだと思います。
書かれている内容が現実となってしまっているので、読んでいてなんとも言えない気分になります。

ブクスタ
というサービスを作ったので、ブクスタで積むネタがないかなあと思っていたら、「#ラノベオールタイムベスト」がはじまって、これはいいネタになると思って、集計作業をはじめたら、こんな文書ができあがりました。


元データについて

i_j_さんが、「みんなで好きなラノベを十作挙げよう」と宣言してはじまったのが「#ラノベオールタイムベスト」です。
10作あげていない方も、10作以上あげている方もいらっしゃいますが、すべてのデータを集計対象としました。
5件しかあげていない方の場合は5件、20件あげた方の場合は20件をデータとして扱っています。

つぶやいて頂いたタイトルは短縮されたものがあります。
極力調べて「これだろう」と思うものに正規化いたしました。(正規化という言葉でよいのか自信がないのですが、正規化ということにします。)
例えば「○マ」は「今日からマのつく自由業!」に変換しています。

正規化ができなかったものもあります。
たとえば「ずっと」というつぶやきもあったのですが、これがどの書籍にあたるか分かりませんでした。
正規化できはなかったものは集計対象外としました。

また「成田作品」というつぶやきもありました。
こちらも集計の対象外としました。

つぶやいて頂いたラノベは1巻で終わることが少なく大抵シリーズとなっています。
基本はシリーズを1レコードとして集計し、シリーズの中でも巻が特定されている場合は、特定の巻を1レコードとして集計しました。

例えば「涼宮ハルヒ」とつぶやいて頂いた場合は「涼宮ハルヒシリーズ」として集計しました。
「涼宮ハルヒの消失」とつぶやいて頂いた場合は「涼宮ハルヒの消失」として集計しました。

元となるデータをご覧になりたい方は以下のリンクをクリックし、表示されたフォームに「* FROM lightnovel.alltimebest ORDER BY id」と入力してください。
SQL入力フォーム

続きを読む

SQL初級者から見たO/Rマッパーについて

SQL上級者こそ知って欲しい、なぜO/Rマッパーが重要か? を読んで、確かにSQLにはWITH句があるけど、それはSQLの中での部品の再利用なので、スクリプトの中で同じようなSQLを実行したいときの再利用の機能はないなあ…と思いつつ、モダンなORMがSQLの完全な代替になるかというと、多分そうはならないんじゃないかなあと思いました。

自分と同じような感じ方をされる方がいらっしゃるかなと思ったらいらっしゃいました。
O/Rマッパーはなぜ悪かO/Rマッパーはなぜ悪か・2になります。

「は?最近のO/Rマッパーはそんなアホじゃないし?w」「あ,はい。そうだったんですねー」と瞬殺されたそうですので、この記事も瞬殺されそうですが…


私はErogameScapeでSQLを直接がりがり書いてます。
O/Rマッパーというものの存在を知ったのは、 CodeIgniterを学習したときになります。
O/Rマッパーを知って使ってみたときの感想は、INSERTやUPDATEは便利だけど、 SELECTは単純なものじゃないと使い物にならないな…でした。

※モダンなO/Rマッパーでしたら、複雑なSQLもスマートにかけるのでしょうか…

O/Rマッパーはなぜ悪かの文書にもあるように、O/RマッパーはとんでもないSQLを作成することがあって、びっくりすることがあります。
多分、そんなびっくりなSQLを生成しないように、ちゃんとしたSQLを生成するようにO/Rマッパーに値を渡すのだと思いますが、O/Rマッパーの使い方を学ぶよりSQLで直書きした方が、分かりやすいなあ…というのがSQLをガリガリ直接書いてきたものとしての感想です。

Javascriptに対するCoffeeScriptのような素晴らしいO/Rマッパーがでてくるかもしれませんが、まだそれはだいぶ先なのかなと思います。

※私はPHPのフレームワークのO/Rマッパーしか知らないので、JavaやRubyで使われるO/Rマッパーがすごかったらごめんなさい!

SQLで直書きしていれば、PHPのフレームワークで作っていたものをRuby on Railsに移植するときも問題ないと思いますが、現状O/Rマッパーは、フレームワークごとに違うと思いますので、一度開始したサービスのフレームワークをかえることはないかもしれませんが、あるとしたら…移植が大変なんじゃないかなあと思います。

うまく書けないのですが、リレーショナル・データベースの世界や大学等の授業で習うように、SQLは集合論を基礎としている言語で、O/Rマッパーで表現しようとすると、SQLで書くより複雑になってしまうこともあるんじゃないかなあと思います。その…ErogameScapeで実行しているSQLをCodeIgniterやFuelPHPやCakePHPのO/Rマッパーで書こうとすると、すごく悩んでしまうことがありました。

SQL上級者こそ知って欲しい、なぜO/Rマッパーが重要か? には、SQLには部品化の機能がない→SQLが長くなる・わかりくにいとありますが、私は…適切にインデントされたSQLの方がO/Rマッパーより分かりやすいなあと思います。

O/Rマッパーを使うと、SQL上級者にも納得がいくSQLを生成できて、SQLを知らなくてもSQL上級者並のSQLが生成されるようになったときに、SQL上級者の方々はO/Rマッパーもいいね、となるんじゃないかなあと思いました。

以上、10年くらい日曜プログラマーとして、SQLを書いてきたもののコメントです。

記事検索