発売一年の作品の世代別得点の実行結果

SQLの説明

cnt_all,avg_all すべての方の得点数と平均
cnt60,avg60 1960年代以前の方の得点数と平均
cnt70,avg70 1970年代の方の得点数と平均
cnt80,avg80 1980年代の方の得点数と平均
cnt90,avg90 1990年代以降の方の得点数と平均

検索結果

実行したSQL

      SELECT gl.id
     , gl.gamename
     , COUNT(tokuten) AS cnt_all
     , ROUND(AVG(tokuten), 1) AS avg_all
     , COUNT(CASE WHEN ub.birth < 1970 THEN tokuten ELSE NULL END) AS cnt60
     , ROUND(AVG(CASE WHEN ub.birth < 1970 THEN tokuten ELSE NULL END), 1)  AS avg60
     , COUNT(CASE WHEN ub.birth BETWEEN 1970 AND 1979 THEN tokuten ELSE NULL END) AS cnt70
     , ROUND(AVG(CASE WHEN ub.birth BETWEEN 1970 AND 1979 THEN tokuten ELSE NULL END), 1)  AS avg70
     , COUNT(CASE WHEN ub.birth BETWEEN 1980 AND 1989 THEN tokuten ELSE NULL END) AS cnt80
     , ROUND(AVG(CASE WHEN ub.birth BETWEEN 1980 AND 1989 THEN tokuten ELSE NULL END), 1)  AS avg80
     , COUNT(CASE WHEN ub.birth >= 1990 THEN tokuten ELSE NULL END) AS cnt90
     , ROUND(AVG(CASE WHEN ub.birth >= 1990 THEN tokuten ELSE NULL END), 1)  AS avg90
FROM userreview AS ur
   , gamelist AS gl
   ,
(
SELECT uid, substr(birth::text, 1, 4)::int AS birth
  FROM myuserview
 WHERE substr(birth::text, 1, 4)::int BETWEEN 1950 AND TO_CHAR(CURRENT_DATE, 'YYYY')::int - 18
 ORDER BY birth
) AS ub
WHERE ur.game = gl.id
  AND ur.uid = ub.uid
  AND ur.play
  AND gl.sellday BETWEEN CURRENT_DATE - '1 year'::interval AND CURRENT_DATE
GROUP BY gl.id, gl.gamename
ORDER BY cnt_all DESC