※上記の広告は60日以上更新のないWIKIに表示されています。更新することで広告が下部へ移動します。

SQLサンプル

ユーザーSQLに登録するまでもないような小物SQL置き場
SQL実行フォーム で使用する場合は、冒頭のSELECTは省略してください

ご案内

コメントが「SELECT」で始まっている時、SQL実行フォームへPOSTするボタンを自動生成するようにしました。他のページでも使いたい場合は、#include(【管理用】PostSQL)とページの最後に追記してください。
ここは「SELECT」で始まっていないので、ボタンができない

- 

ゲーム関連

  • 発売一ヶ月以内のゲームリスト
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
- 

ユーザー関連

  • 沢山プレイしているユーザー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
- 

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
- 

クリエイター関連

  • クリエイターの参加作品一覧(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
- 

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)
  • 上位10位を抽出するサンプル(最近1ヶ月位内に発売されたゲームの得点上位10位までを抽出します)
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


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