「SQLサンプル」の編集履歴（バックアップ）一覧はこちら
「SQLサンプル」(2013/03/23 (土) 13:39:29) の最新版変更点
追加された行は緑色になります。
削除された行は赤色になります。
*&this_page()
ユーザーSQLに登録するまでもないような小物SQL置き場
[[SQL実行フォーム>>http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/select.php]]で使用する場合は、冒頭のSELECTは省略してください
**ご案内
コメントが「SELECT」で始まっている時、SQL実行フォームへPOSTするボタンを自動生成するようにしました。他のページでも使いたい場合は、#include(【管理用】PostSQL)とページの最後に追記してください。
>ここは「SELECT」で始まっていないので、ボタンができない
#region(open,ゲーム関連)
**ゲーム関連
・発売一ヶ月以内のゲームリスト
>SELECT game_id, gamename, brandname, sellday, median, average, stddev, count FROM toukei_temp_table WHERE sellday BETWEEN CURRENT_DATE - CAST('1 months' AS interval) AND CURRENT_DATE
・批評空間全体のプレイ済み統計
>SELECT AVG(tokuten), STDDEV(tokuten), COUNT(uid) FROM userreview WHERE play
・批評空間全体のゲーム中央値の統計
>SELECT AVG(median), STDDEV(median), COUNT(median) FROM toukei_temp_table
・○○さんがプレイしたゲームの一覧と、ゲーム中央値との比較
>SELECT ttt.game_id, ttt.gamename, ttt.median, ur.tokuten, ur.tokuten - ttt.median AS compare, ur.hitokoto FROM userreview AS ur, toukei_temp_table AS ttt WHERE ur.game = ttt.game_id AND ur.play AND ur.uid = '○○'
・長文感想のrefererをドメイン別にカウント
>SELECT referer, count(*) from (select regexp_matches(referer,'^[https]+://([0-9a-z.-:]+?):?[0-9]*?/') as referer from userreview_display_log where game = 16806) as foo group by referer
#endregion()
#region(open,ユーザー関連)
**ユーザー関連
・沢山プレイしているユーザーTOP100
>SELECT uid, COUNT(game) FROM userreview WHERE play GROUP BY uid ORDER BY COUNT DESC LIMIT 100
・○○さんをお気に入りなユーザー
>SELECT voter_uid FROM user_user WHERE uid = '○○'
・○○さんが気に入らないユーザー
>SELECT voter_uid FROM user_dislike_user WHERE dislike_uid = '○○'
・誰が積んでるか（最後の1を調べたいゲームIDに変更）
>SELECT uid FROM userreview WHERE possession AND NOT play AND game = 1
・陵辱ゲーマー（総プレイ20本以上の内、5割以上が陵辱ゲー）
>SELECT uid, SUM(CASE WHEN axis_of_soft_or_hard = -1 THEN 1 ELSE 0 END) FROM userreview AS ur, gamelist AS gl WHERE ur.play AND ur.game = gl.id GROUP BY ur.uid HAVING SUM(CASE WHEN axis_of_soft_or_hard = -1 THEN 1 ELSE 0 END) * 1.0 / COUNT(game) >= 0.5 AND COUNT(game) >= 20 ORDER BY SUM DESC
・ユーザーSQLをお気に入りなユーザー（最後の1を調べたいSQLのIDに変更）
>SELECT * FROM usersql_touhyou WHERE id = 1
・中央値ゲッター（データ20以上のゲームの中央値±1点の得点数ランキング 結果のperは中央値ゲット率）
>SELECT ur.uid, SUM(CASE WHEN ttt.count >=20 AND ttt.median BETWEEN ur.tokuten - 1 AND ur.tokuten + 1 THEN 1 ELSE 0 END) AS count, ROUND(SUM(CASE WHEN ttt.median BETWEEN ur.tokuten - 1 AND ur.tokuten + 1 THEN 1 ELSE 0.0000000000001 END) * 100.0 / COUNT(ur.game) , 1) AS per FROM toukei_temp_table AS ttt, userreview AS ur WHERE ttt.game_id = ur.game and ttt.count >= 20 AND ur.play GROUP BY ur.uid HAVING SUM(CASE WHEN ttt.median BETWEEN ur.tokuten - 1 AND ur.tokuten + 1 THEN 1 ELSE 0 END) >= 10 ORDER BY count DESC LIMIT 100
・間接人気度
>SELECT rank() over (order by sum(count) desc) ,uu2.uid, sum(count) as point from (select uid, count(*) from user_user group by uid) as uu1 , user_user as uu2 where uu1.uid = uu2.voter_uid group by uu2.uid
・被お気に入り10以上のユーザーさんデビュー年
>SELECT to_char(foo.tourokubi,'yyyy') as debut_year, string_agg(foo.uid||'('||foo.count||')',' , ') from (select uu.uid,uu.count,min(ur.play_tourokubi) as tourokubi from (select uid,count(voter_uid) from user_user group by uid having count(voter_uid)>=10) as uu,userreview as ur where uu.uid=ur.uid group by uu.uid,uu.count) as foo group by to_char(foo.tourokubi,'yyyy') order by debut_year
#endregion()
#region(open,POV・属性関連)
**POV・属性・タグ関連
・POV登録数上位100名
>SELECT uid, COUNT(pov) FROM povgroups GROUP BY uid ORDER BY COUNT(pov) DESC LIMIT 100
・リニューアル作品のあるMS-DOSゲー（属性2種組み合わせ。44,98は属性のID）
>SELECT game_id, gamename, brandname, sellday, median, count FROM toukei_temp_table WHERE game_id IN (SELECT game FROM attributegroupsboolean WHERE attribute in (44,98) GROUP BY game HAVING COUNT(attribute) = 2)
・△△が含まれるPOVコメント一覧（正規表現）
>SELECT ttt.gamename, pl.system_title, pgs.rank, pgs.memo, pgs.uid, to_char(pgs.tourokubi, 'YYYY/MM/DD') FROM povlist AS pl, povgroups AS pgs, toukei_temp_table AS ttt WHERE pl.id = pgs.pov AND pgs.game = ttt.game_id AND pgs.memo ~ '△△' ORDER BY ttt.gamename LIMIT 200
・[[タグの登録数をカウントするSQLサンプル]]
#endregion()
#region(open,クリエイター関連)
**クリエイター関連
・クリエイターの参加作品一覧（cl.idの1を調べたいクリエイターIDに変更）
>SELECT cl.name, ttt.game_id, ttt.gamename, ttt.brandname, ttt.sellday, ttt.median FROM createrlist AS cl, shokushu AS ss, toukei_temp_table AS ttt WHERE cl.id = ss.creater AND ss.game = ttt.game_id AND cl.id = 1 ORDER BY sellday DESC
#endregion()
#region(open,SQL関連)
**SQL関連
・クエリの実行結果を仕切り文字で連結する（ARRAY＝配列化、ARRAY_TO_STRING＝配列を連結）
>SELECT '2009/12/31発売のゲームは、『' || ARRAY_TO_STRING(ARRAY(SELECT gamename FROM gamelist WHERE sellday = CAST('2009/12/31' AS date) ORDER BY furigana), '』 と 『') || '』です。'
・集約関数で配列を使う(ブランド名とカンマ区切りのゲームリストを出力)(ARRAY_AGG＝集合を配列化)
>SELECT brandname, ARRAY_TO_STRING(ARRAY_AGG(gamename), ',') FROM toukei_temp_table GROUP BY brand_id, brandname LIMIT 50
・HTMLを埋め込む（formの設置）
>SELECT CHR(60)||'form name='||CHR(34)||'TestForm'||CHR(34)||CHR(62) || CHR(60)||'label'||CHR(62) ||'テスト'||CHR(60)||'input name='||CHR(34)||'Test'||CHR(34)||' value='||CHR(34)||'テスト'||CHR(34)||' /'||CHR(62) || CHR(60)||'/label'||CHR(62) ||CHR(60)||'/form'||CHR(62)
・javacsriptを埋め込む（Hello World!!）
>SELECT CHR(60)||'script type='||CHR(34)||'text/javascript'||CHR(34)||CHR(62)|| 'str='||CHR(34)||'World!!'||CHR(34)||'; alert('||CHR(34)||'Hello '||CHR(34)||' + str);'||CHR(60)||'/script'||CHR(62)
・上位１０位を抽出するサンプル（最近１ヶ月位内に発売されたゲームの得点上位１０位までを抽出します）
>SELECT foo.id, foo.gamename, foo.tokuten, foo.rank FROM (SELECT gl.id, gl.gamename, ur.tokuten, RANK() OVER (PARTITION BY gl.id ORDER BY ur.tokuten DESC) FROM userreview AS ur, gamelist AS gl WHERE ur.game = gl.id AND ur.tokuten IS NOT NULL AND gl.sellday >= CURRENT_DATE - '1 MONTH'::INTERVAL) AS foo WHERE rank <= 10
#endregion()
#include(【管理用】PostSQL)
*&this_page()
ユーザーSQLに登録するまでもないような小物SQL置き場
[[SQL実行フォーム>>http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/select.php]]で使用する場合は、冒頭のSELECTは省略してください
**ご案内
コメントが「SELECT」で始まっている時、SQL実行フォームへPOSTするボタンを自動生成するようにしました。他のページでも使いたい場合は、#include(【管理用】PostSQL)とページの最後に追記してください。
>ここは「SELECT」で始まっていないので、ボタンができない
#region(open,ゲーム関連)
**ゲーム関連
・発売一ヶ月以内のゲームリスト
>SELECT game_id, gamename, brandname, sellday, median, average, stddev, count FROM toukei_temp_table WHERE sellday BETWEEN CURRENT_DATE - CAST('1 months' AS interval) AND CURRENT_DATE
・批評空間全体のプレイ済み統計
>SELECT AVG(tokuten), STDDEV(tokuten), COUNT(uid) FROM userreview WHERE play
・批評空間全体のゲーム中央値の統計
>SELECT AVG(median), STDDEV(median), COUNT(median) FROM toukei_temp_table
・○○さんがプレイしたゲームの一覧と、ゲーム中央値との比較
>SELECT ttt.game_id, ttt.gamename, ttt.median, ur.tokuten, ur.tokuten - ttt.median AS compare, ur.hitokoto FROM userreview AS ur, toukei_temp_table AS ttt WHERE ur.game = ttt.game_id AND ur.play AND ur.uid = '○○'
・ゲームID○○の長文感想のrefererをドメイン別にカウント
>SELECT referer, count(*) from (select regexp_matches(referer,'^[https]+://([0-9a-z.-:]+?):?[0-9]*?/') as referer from userreview_display_log where game = ○○) as foo group by referer
#endregion()
#region(open,ユーザー関連)
**ユーザー関連
・沢山プレイしているユーザーTOP100
>SELECT uid, COUNT(game) FROM userreview WHERE play GROUP BY uid ORDER BY COUNT DESC LIMIT 100
・○○さんをお気に入りなユーザー
>SELECT voter_uid FROM user_user WHERE uid = '○○'
・○○さんが気に入らないユーザー
>SELECT voter_uid FROM user_dislike_user WHERE dislike_uid = '○○'
・誰が積んでるか（最後の1を調べたいゲームIDに変更）
>SELECT uid FROM userreview WHERE possession AND NOT play AND game = 1
・陵辱ゲーマー（総プレイ20本以上の内、5割以上が陵辱ゲー）
>SELECT uid, SUM(CASE WHEN axis_of_soft_or_hard = -1 THEN 1 ELSE 0 END) FROM userreview AS ur, gamelist AS gl WHERE ur.play AND ur.game = gl.id GROUP BY ur.uid HAVING SUM(CASE WHEN axis_of_soft_or_hard = -1 THEN 1 ELSE 0 END) * 1.0 / COUNT(game) >= 0.5 AND COUNT(game) >= 20 ORDER BY SUM DESC
・ユーザーSQLをお気に入りなユーザー（最後の1を調べたいSQLのIDに変更）
>SELECT * FROM usersql_touhyou WHERE id = 1
・中央値ゲッター（データ20以上のゲームの中央値±1点の得点数ランキング 結果のperは中央値ゲット率）
>SELECT ur.uid, SUM(CASE WHEN ttt.count >=20 AND ttt.median BETWEEN ur.tokuten - 1 AND ur.tokuten + 1 THEN 1 ELSE 0 END) AS count, ROUND(SUM(CASE WHEN ttt.median BETWEEN ur.tokuten - 1 AND ur.tokuten + 1 THEN 1 ELSE 0.0000000000001 END) * 100.0 / COUNT(ur.game) , 1) AS per FROM toukei_temp_table AS ttt, userreview AS ur WHERE ttt.game_id = ur.game and ttt.count >= 20 AND ur.play GROUP BY ur.uid HAVING SUM(CASE WHEN ttt.median BETWEEN ur.tokuten - 1 AND ur.tokuten + 1 THEN 1 ELSE 0 END) >= 10 ORDER BY count DESC LIMIT 100
・間接人気度
>SELECT rank() over (order by sum(count) desc) ,uu2.uid, sum(count) as point from (select uid, count(*) from user_user group by uid) as uu1 , user_user as uu2 where uu1.uid = uu2.voter_uid group by uu2.uid
・被お気に入り10以上のユーザーさんデビュー年
>SELECT to_char(foo.tourokubi,'yyyy') as debut_year, string_agg(foo.uid||'('||foo.count||')',' , ') from (select uu.uid,uu.count,min(ur.play_tourokubi) as tourokubi from (select uid,count(voter_uid) from user_user group by uid having count(voter_uid)>=10) as uu,userreview as ur where uu.uid=ur.uid group by uu.uid,uu.count) as foo group by to_char(foo.tourokubi,'yyyy') order by debut_year
#endregion()
#region(open,POV・属性関連)
**POV・属性・タグ関連
・POV登録数上位100名
>SELECT uid, COUNT(pov) FROM povgroups GROUP BY uid ORDER BY COUNT(pov) DESC LIMIT 100
・リニューアル作品のあるMS-DOSゲー（属性2種組み合わせ。44,98は属性のID）
>SELECT game_id, gamename, brandname, sellday, median, count FROM toukei_temp_table WHERE game_id IN (SELECT game FROM attributegroupsboolean WHERE attribute in (44,98) GROUP BY game HAVING COUNT(attribute) = 2)
・△△が含まれるPOVコメント一覧（正規表現）
>SELECT ttt.gamename, pl.system_title, pgs.rank, pgs.memo, pgs.uid, to_char(pgs.tourokubi, 'YYYY/MM/DD') FROM povlist AS pl, povgroups AS pgs, toukei_temp_table AS ttt WHERE pl.id = pgs.pov AND pgs.game = ttt.game_id AND pgs.memo ~ '△△' ORDER BY ttt.gamename LIMIT 200
・[[タグの登録数をカウントするSQLサンプル]]
#endregion()
#region(open,クリエイター関連)
**クリエイター関連
・クリエイターの参加作品一覧（cl.idの1を調べたいクリエイターIDに変更）
>SELECT cl.name, ttt.game_id, ttt.gamename, ttt.brandname, ttt.sellday, ttt.median FROM createrlist AS cl, shokushu AS ss, toukei_temp_table AS ttt WHERE cl.id = ss.creater AND ss.game = ttt.game_id AND cl.id = 1 ORDER BY sellday DESC
#endregion()
#region(open,SQL関連)
**SQL関連
・クエリの実行結果を仕切り文字で連結する（ARRAY＝配列化、ARRAY_TO_STRING＝配列を連結）
>SELECT '2009/12/31発売のゲームは、『' || ARRAY_TO_STRING(ARRAY(SELECT gamename FROM gamelist WHERE sellday = CAST('2009/12/31' AS date) ORDER BY furigana), '』 と 『') || '』です。'
・集約関数で配列を使う(ブランド名とカンマ区切りのゲームリストを出力)(ARRAY_AGG＝集合を配列化)
>SELECT brandname, ARRAY_TO_STRING(ARRAY_AGG(gamename), ',') FROM toukei_temp_table GROUP BY brand_id, brandname LIMIT 50
・HTMLを埋め込む（formの設置）
>SELECT CHR(60)||'form name='||CHR(34)||'TestForm'||CHR(34)||CHR(62) || CHR(60)||'label'||CHR(62) ||'テスト'||CHR(60)||'input name='||CHR(34)||'Test'||CHR(34)||' value='||CHR(34)||'テスト'||CHR(34)||' /'||CHR(62) || CHR(60)||'/label'||CHR(62) ||CHR(60)||'/form'||CHR(62)
・javacsriptを埋め込む（Hello World!!）
>SELECT CHR(60)||'script type='||CHR(34)||'text/javascript'||CHR(34)||CHR(62)|| 'str='||CHR(34)||'World!!'||CHR(34)||'; alert('||CHR(34)||'Hello '||CHR(34)||' + str);'||CHR(60)||'/script'||CHR(62)
・上位１０位を抽出するサンプル（最近１ヶ月位内に発売されたゲームの得点上位１０位までを抽出します）
>SELECT foo.id, foo.gamename, foo.tokuten, foo.rank FROM (SELECT gl.id, gl.gamename, ur.tokuten, RANK() OVER (PARTITION BY gl.id ORDER BY ur.tokuten DESC) FROM userreview AS ur, gamelist AS gl WHERE ur.game = gl.id AND ur.tokuten IS NOT NULL AND gl.sellday >= CURRENT_DATE - '1 MONTH'::INTERVAL) AS foo WHERE rank <= 10
#endregion()
#include(【管理用】PostSQL)
