double precision型の0.5をroundで四捨五入すると0になる

PostgreSQL11.5の話題です。
double precision型の0.5をroundで四捨五入すると1になって欲しいところ0になります。
ap2=# SELECT round( CAST( 0.5 AS double precision ) ) ;
 round
-------
     0
double precision型はfloat8(倍精度浮動小数点数)なので、誤差が生じるのは分かるのですが…10進数の0.5は2進数で誤差無く表現できると思っているので、なぜroundで四捨五入すると0になるのか分かりませんでした。
ap2=# SELECT round( 0.5 ) AS "round( 0.5 )" , pg_typeof( 0.5 ) AS "pg_typeof( 0.5 )";
-[ RECORD 1 ]----+--------
round( 0.5 )     | 1
pg_typeof( 0.5 ) | numeric
numeric型でしたら、ちゃんと四捨五入して1になるので、numericに型変換すればOKです。
知らずにdouble precision型の数字に対してroundしてしまうこともあるかと思うので、roundするときはとりあえずnumericに型変換しておこうと思いました。

気がついた契機は以下のとおりです。
ErogameScapeでは中央値を計算することが多いのですが、PERCENTILE_CONT関数というとても便利な関数が追加されていたので、SQLを書き換えていました。

gamelistテーブルにはmedianという列があって、ゲームの中央値を格納しています。
このmedidnの列が、どういうわけか…INTEGER型で定義されています(本当に、どういうわけでしょうか…)

あるゲームに62点と63点がつけられた場合、中央値は62.5点になります。
が、medianがINTEGER型になってしまっているので、62.5を四捨五入して63を格納することにしています。

62点と63点の中央値を求めるSQLは以下のとおりです。
ap2=# SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tokuten) AS "62と63の中央値をPERCENTILE_CONT関数で求める"
ap2-#   FROM (
ap2(#          SELECT 62 AS tokuten
ap2(#           UNION ALL
ap2(#          SELECT 63 AS tokuten
ap2(#        ) AS t
ap2-# ;
-[ RECORD 1 ]-------------------------------+-----
62と63の中央値をPERCENTILE_CONT関数で求める | 62.5
これをroundすると…
ap2=# SELECT round( PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tokuten) ) AS "63になって欲しいけど62になる"
ap2-#   FROM (
ap2(#          SELECT 62 AS tokuten
ap2(#           UNION ALL
ap2(#          SELECT 63 AS tokuten
ap2(#        ) AS t;
-[ RECORD 1 ]----------------+---
63になって欲しいけど62になる | 62
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tokuten)の戻り値の型を調べると、
ap2=# SELECT pg_typeof(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tokuten)) AS "戻り値の型はdouble precision"
ap2-#   FROM (
ap2(#          SELECT 62 AS tokuten
ap2(#           UNION ALL
ap2(#          SELECT 63 AS tokuten
ap2(#        ) AS t;
-[ RECORD 1 ]----------------+-----------------
戻り値の型はdouble precision | double precision
double precisionなので、62.5をroundで四捨五入すると62になってしまいます。
そこで、PERCENTILE_CONTの戻り値をnumericに型変換してからroundで四捨五入します。
ap2=# SELECT round( CAST( PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tokuten) AS numeric ) ) AS "numericにCASTしてからなら63になってくれる"
ap2-#   FROM (
ap2(#          SELECT 62 AS tokuten
ap2(#           UNION ALL
ap2(#          SELECT 63 AS tokuten
ap2(#        ) AS t;
-[ RECORD 1 ]-----------------------------+---
numericにCASTしてからなら63になってくれる | 63
double precisionの0.5をroundで四捨五入すると0になる根本理由を知りたいのですが…浮動小数点のことから勉強し直さないと駄目でまったく感覚がなくなっているので…諦めることにしました…

committedが増えていく

ErogameScapeでは各種リソースをmuninで監視しています。
ある日からmemoryのcommittedが着々と増えていきました。
memory
committedがなんなのかをググってもよくわからなかったことと、増えててもサーバーの動作に支障が無いのでそのうち直るかな…と思って放置していました。
メモリを使いそうなのは
  1. PostgreSQL
  2. apache
  3. php
なので、それぞれ再起動してみるもNGでした。
ある日、たまたまスレッド数の画面を見たときに、スレッドも激増していることに気がつきました。
日単位で見ると、ゆるやかに増えているので気がつかなかったのですが、週単位/月単位で見ると激増していることがわかります。
threads
そこでスレッドの状態を確認しました。
# ps_aux_-L

中略

root     27385 27385  0.0    1  0.0 139800   260 ?        S    Sep02   0:00 CROND
ap2      27390 27390  0.0    1  0.0 299400   544 ?        Ss   Sep02   0:00 rsync -aurptz -e /usr/bin/ssh --exclude *~ --delete /home/ap2/public_html ap2@192.168.0.13:/home/ap2
ap2      27395 27395  0.0    1  0.0  60028  1364 ?        S    Sep02   0:00 /usr/bin/ssh -l ap2 192.168.0.13 rsync --server -ulogDtprze.iLs --delete . /home/ap2
root     27464 27464  0.0    1  0.0 139800   628 ?        S    Sep10   0:00 CROND
ap2      27469 27469  0.0    1  0.0 301576  5164 ?        Ss   Sep10   0:00 rsync -aurptz -e /usr/bin/ssh --exclude *~ --delete /home/ap2/public_html ap2@192.168.0.13:/home/ap2
ap2      27473 27473  0.0    1  0.0  60028  2264 ?        S    Sep10   0:00 /usr/bin/ssh -l ap2 192.168.0.13 rsync --server -ulogDtprze.iLs --delete . /home/ap2

以下、いっぱい、繰り返し

中略
ErogameScapeでは、メインのサーバーにスクリプト等をアップロードして、待機系のサーバーにはcronで定期的にrsyncでコピーする、ということをしています。
このrsyncが大量に動いているのか…ゴミとして残っているのか…していました。
手動でrsyncを実行したところ、エラーが滅茶苦茶でて、いっこうにrsyncが終わらないことを確認しました。
rsyncを実行するけど終わらなくて、次のrsyncが実行されて終わらなくて、というのが積み重なっていた模様です。
rsyncを全部殺して、エラーがでないようにrsyncのコマンドを見直して回復しました。 memory2
その後、muninの他のグラフを見たところ、プロセス数も日単位で見るとゆるやかに増えており、週/月単位で見ると激増していたので、閾値をもうけて通知するようにするか、週/月単位のグラフもたまに眺めるようにしないといけないな…と思いました。
processes

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関数はあるのですが、今回のケースではおそらく使えないのかな…と思いました。

psqlにおけるタブによるテーブル名等の補完の候補の表示について

psqlではクエリを書いている途中でタブを押すとテーブル名等が補完されます。
例えば以下のとおりです。
ap2=# SELECT * FROM game
game_banner_getchu_widesky         gamegrouplist                      gamelist_masterup_update_id_seq
game_banner_getchu_widesky_id_seq  gamegrouplist_id_seq               gamelist_sellday_update
game_music                         gamelist                           gamelist_sellday_update_id_seq
game_store_sp                      gamelist_id_seq
game_store_sp_id_seq               gamelist_masterup_update
PostgreSQLを以下の設定にすると、タブを押したときにどのように補完候補が抽出されているかのクエリが表示されます。

postgresql.conf
log_statement = 'all'
client_min_messages = log
SELECT * FROM gameli


LOG:  statement: SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN ('r', 'S', 'v', 'm', 'f', 'p') AND substring(pg_catalog.quote_ident(c.relname),1,6)='gameli' AND pg_catalog.pg_table_is_visible(c.oid) AND c.relnamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')
UNION
SELECT pg_catalog.quote_ident(n.nspname) || '.' FROM pg_catalog.pg_namespace n WHERE substring(pg_catalog.quote_ident(n.nspname) || '.',1,6)='gameli' AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,6) = substring('gameli',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1
UNION
SELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind IN ('r', 'S', 'v', 'm', 'f', 'p') AND substring(pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname),1,6)='gameli' AND substring(pg_catalog.quote_ident(n.nspname) || '.',1,6) = substring('gameli',1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1) AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,6) = substring('gameli',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1
LIMIT 1000;
このSQLを実行してみると以下の結果が得られます。
           quote_ident
---------------------------------
 gamelist_sellday_update
 gamelist
 gamelist_sellday_update_id_seq
 gamelist_masterup_update_id_seq
 gamelist_id_seq
 gamelist_masterup_update
(6 行)
補完候補ってクエリで抽出していたんだなあ…とちょっと感動したのでメモしておきます。

記事検索