発売年別評価TOP10；2000年以降PCのみの実行結果

SQLの説明

検索結果

実行したSQL

      WITH params AS (
    SELECT 70.0::numeric AS global_avg, 60::numeric AS m  -- 全体平均C, 基準評価件数m
),
filtered_games AS (
    SELECT
        game_id,
        gamename,
        EXTRACT(YEAR FROM sellday)::int AS year,
        average,
        median,
        count,
        (average + median) AS score_sum
    FROM
        toukei_temp_table
WHERE
    sellday >= DATE '2000-01-01'
    AND count >= 20
    AND model = 'PC'
),
scored_games AS (
    SELECT
        f.*,
        ROUND(((count * average + p.m * p.global_avg) / (count + p.m))::numeric, 4) AS weighted_score
    FROM filtered_games f, params p
),
ranked_games AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY year ORDER BY weighted_score DESC) AS rank
    FROM scored_games
),
formatted_list AS (
    SELECT
        year,
        rank,
        rank || '位: <a href="https://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/game.php?game=' || 
        game_id || '">『' || gamename || '』</a> (補正平均' ||
        ROUND(weighted_score, 2) || '/中央値' ||
        median::text || '/件数' || count::text || ')' AS entry
    FROM ranked_games
    WHERE rank <= 10
)
SELECT
    year,
    STRING_AGG(entry, '<br>' ORDER BY rank) AS top10_list
FROM formatted_list
GROUP BY year
ORDER BY year;