blueblueblue
Ebi's blog… / THIS PAGE IS CACHED AT 2017/05/27 07:30:25
エロスケ要望板(2014年11月30日 23時20分)へのレス

指定条件に該当するユーザーさん限定の統計表(中央値・3ヶ月版)

select chr(60)||'a href='||chr(34)||'game.php?game='||my.game_id||chr(34)||chr(62)
    || my.gamename
    || chr(60)||'/a'||chr(62)||'('||my.model||')' as ゲーム名
     , chr(60)||'a href='||chr(34)||'brand.php?brand='||my.brand_id||chr(34)||chr(62)
    || my.brandname
    || chr(60)||'/a'||chr(62) as ブランド名
     , my.sellday as 発売日
     , my.median as 中央値
     , chr(60)||'span'
    || ' style='||chr(34)||'font-size:75%;'
    || case when abs(my.trust_median - my.median) >= 5 then 'color:red;' else '' end
    || chr(34)||chr(62)
    || replace((my.trust_median - my.median)::text, '-', '▲')
    || chr(60)||'/span'||chr(62) as →
     , my.trust_median as 補正
     , my.toukei[1] as 平均値
     , chr(60)||'span'
    || ' style='||chr(34)||'font-size:75%;'
    || case when abs(my.trust_toukei[1] - my.toukei[1]) >= 5 then 'color:red;' else '' end
    || chr(34)||chr(62)
    || replace((my.trust_toukei[1] - my.toukei[1])::text, '-', '▲')
    || chr(60)||'/span'||chr(62) as →
     , my.trust_toukei[1] as 補正
     , my.toukei[2] as 標準偏差
     , chr(60)||'span'
    || ' style='||chr(34)||'font-size:75%;'
    || case when my.trust_toukei[2] > my.toukei[2] then 'color:red;' else '' end
    || chr(34)||chr(62)
    || replace((my.trust_toukei[2] - my.toukei[2])::text, '-', '▲')
    || chr(60)||'/span'||chr(62) as →
     , my.trust_toukei[2] as 補正
     , my.toukei[3] as 得点数
     , chr(60)||'span'
    || ' style='||chr(34)||'font-size:75%;'
    || case when my.trust_toukei[3] * 100.0 / my.toukei[3] <= 30 then 'color:red;' else '' end
    || chr(34)||chr(62)
    || replace((my.trust_toukei[3] - my.toukei[3])::text, '-', '▲')
    || chr(60)||'/span'||chr(62) as →
     , my.trust_toukei[3] as 補正
  from (select ttt.game_id
             , ttt.gamename
             , ttt.brand_id
             , ttt.brandname
             , ttt.sellday
             , ttt.model
             , ttt.median
             , (select round(avg(foo.tokuten),1)
                 from (select ur2.tokuten,
                              row_number() over(order by ur2.tokuten) as rank
                            , count(ur2.tokuten) over() as cnt
                         from userreview as ur2
                        where ur2.game = ttt.game_id
                          and ur2.uid = any(ur_wrap.userlist)
                      ) as foo
                where (mod(foo.cnt,2) = 0 and foo.rank in (foo.cnt/2.0, foo.cnt/2.0+1))
                   or (mod(foo.cnt,2) = 1 and foo.rank = ceil(foo.cnt/2.0))
               ) as trust_median
             , array[round(ttt.average::int, 1)
                   , round(ttt.stddev, 1)
                   , ttt.count] as toukei
             , (select array[round(avg(ur3.tokuten),1)
                           , round(stddev(ur3.tokuten),1)
                           , count(ur3.tokuten)]
                  from userreview as ur3
                 where ur3.game = ttt.game_id
                   and ur3.uid = any(ur_wrap.userlist)
               ) as trust_toukei
          from toukei_temp_table as ttt
             , (select array_agg(ur1.uid) as userlist
                  from (select ur0.uid
                          from userreview as ur0
                         group by ur0.uid
                        having avg(tokuten) between 40 and 90
                           and stddev(tokuten) between 7 and 27
                           and count(ur0.tokuten) >= 30
                           and max(ur0.play_tourokubi) - min(ur0.play_tourokubi) >= '12 months'::interval
                        ) as ur1
               ) as ur_wrap
         where ttt.sellday between current_date - '3 months'::interval and current_date
       ) as my
 where 0 < ALL(trust_toukei)
 order by my.trust_median desc

解説

79行目
where ttt.sellday between current_date - '3 months'::interval and current_date
ゲームの発売日が、現在から3ヶ月遡った日(current_date – ‘3 months’::interval)~現在(current_date)の期間という条件付けです。
ここを変更することで、期間を変えられます。記述の順番は「古い日付」から「新しい日付」です。逆では結果が帰ってきません。

エロゲー批評空間のトップページから、[ログイン]-[エロゲー入力メニューへ]-[SQLの追加]と進み、以下の画面のSQL欄にSQL全文を記入します。
タイトルと説明文はなんでもよいですが、省略できなので「あ」でも「い」でも「てst」でも、何か入れてください。
「公開する/しない」もどちらでもよいですが、公開する場合、ユーザー作成SQLに追加されます。
公開しない場合は、ユーザー作成SQLには追加されませんが、SQL登録者がログイン状態であれば、URLに直接アクセスすれば結果が確認できます。
「登録」ボタンを押したら、登録したSQL一覧でSQLのIDを確認し、http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/usersql_exec.php?sql_id=○○○○ の○○○○をIDに置き換えればOK。
new_SQL_es

SQL登録の際、SQLの構文がおかしかったり、実行コストが大きすぎると警告が出て登録できません。
実行コスト制限で登録できなかった場合は、期間を短くしてください。
※指定条件に該当するユーザーさん限定の統計表(中央値・3ヶ月版)の現在の実行コスト
 Sort (cost=69416.56..69416.56 rows=1 width=114)

コメント