指定条件に該当するユーザーさん限定の統計表（中央値・３ヶ月版）の実行結果

SQLの説明

gouyokuさん作の「指定条件に該当するユーザーさん限定の統計表」の改訂版

検索結果

実行したSQL

      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