07/25 개발 일지

야금 야금 계속해서 개발 해오던 터라

일단 전적 검색시에 챔피언 이미지가 나왔으면 좋겠다고 해서 riot api에서 받아오도록 설정했다.

챔피언 영문명은 다행히 rust한다고 영문명 불러오는 컬럼을 만들어놔서 손쉽게 구현했다.

쿼리짜느라 머리가 깨질뻔했다..

SELECT
    ranked_data.champion AS champion,
    ranked_data.winRate AS winRate,
    ranked_data.played AS played,
    ranked_data.kda AS kda,
    ranked_data.tier AS tier,
    most_player.nickname AS mostPlayedBy,
    ranked_data.playersCount AS playersCount,
    ROUND(COALESCE(ban_data.banRate, 0), 2) AS banRate,
    championEnglish
FROM (
         SELECT
             g.champion AS champion,
             ROUND(SUM(CASE WHEN g.winning = TRUE THEN 1 ELSE 0 END) * 100.0 / COUNT(g.id), 2) AS winRate,
             COUNT(g.id) AS played,
             ROUND(SUM(g.kills + g.assists) / SUM(CASE WHEN g.deaths = 0 THEN 1 ELSE g.deaths END), 2) AS kda,
             COUNT(DISTINCT g.nickname) AS playersCount,
             CASE
                 WHEN PERCENT_RANK() OVER (
                     ORDER BY (
                         ROUND(SUM(CASE WHEN g.winning = TRUE THEN 1 ELSE 0 END) * 100.0 / COUNT(g.id), 2) +
                         (COALESCE(ban_data.banRate, 0) * 1.1) +
                         (ROUND(SUM(g.kills + g.assists) / SUM(CASE WHEN g.deaths = 0 THEN 1 ELSE g.deaths END), 2) * 0.8)
                         ) DESC, COUNT(g.id) DESC
                     ) <= 0.2 THEN 'Tier 1'
                 WHEN PERCENT_RANK() OVER (
                     ORDER BY (
                         ROUND(SUM(CASE WHEN g.winning = TRUE THEN 1 ELSE 0 END) * 100.0 / COUNT(g.id), 2) +
                         (COALESCE(ban_data.banRate, 0) * 1.1) +
                         (ROUND(SUM(g.kills + g.assists) / SUM(CASE WHEN g.deaths = 0 THEN 1 ELSE g.deaths END), 2) * 0.8)
                         ) DESC, COUNT(g.id) DESC
                     ) <= 0.4 THEN 'Tier 2'
                 WHEN PERCENT_RANK() OVER (
                     ORDER BY (
                         ROUND(SUM(CASE WHEN g.winning = TRUE THEN 1 ELSE 0 END) * 100.0 / COUNT(g.id), 2) +
                         (COALESCE(ban_data.banRate, 0) * 1.1) +
                         (ROUND(SUM(g.kills + g.assists) / SUM(CASE WHEN g.deaths = 0 THEN 1 ELSE g.deaths END), 2) * 0.8)
                         ) DESC, COUNT(g.id) DESC
                     ) <= 0.6 THEN 'Tier 3'
                 WHEN PERCENT_RANK() OVER (
                     ORDER BY (
                         ROUND(SUM(CASE WHEN g.winning = TRUE THEN 1 ELSE 0 END) * 100.0 / COUNT(g.id), 2) +
                         (COALESCE(ban_data.banRate, 0) * 1.1) +
                         (ROUND(SUM(g.kills + g.assists) / SUM(CASE WHEN g.deaths = 0 THEN 1 ELSE g.deaths END), 2) * 0.8)
                         ) DESC, COUNT(g.id) DESC
                     ) <= 0.8 THEN 'Tier 4'
                 ELSE 'Tier 5'
                 END AS tier
         FROM game_data g
                  LEFT JOIN (
             SELECT
                 ban_champion,
                 COUNT(*) * 100.0 / (SELECT COUNT(DISTINCT match_code) FROM ban) AS banRate
             FROM ban
             GROUP BY ban_champion
         ) AS ban_data ON ban_data.ban_champion = g.champion
         GROUP BY g.champion, ban_data.banRate
         HAVING COUNT(g.id) >= 3
     ) AS ranked_data
         LEFT JOIN (
    SELECT
        champion,
        nickname,
        ROW_NUMBER() OVER (PARTITION BY champion ORDER BY COUNT(id) DESC) AS player_rank
    FROM game_data
    GROUP BY champion, nickname
) AS most_player ON most_player.champion = ranked_data.champion AND most_player.player_rank = 1
         LEFT JOIN (
    SELECT
        ban_champion,
        COUNT(*) * 100.0 / (SELECT COUNT(DISTINCT match_code) FROM ban) AS banRate
    FROM ban
    GROUP BY ban_champion
) AS ban_data ON ban_data.ban_champion = ranked_data.champion
         LEFT JOIN champion ch
                   On ch.champion = ranked_data.champion
ORDER BY
    CASE
        WHEN ranked_data.tier = 'Tier 1' THEN 1
        WHEN ranked_data.tier = 'Tier 2' THEN 2
        WHEN ranked_data.tier = 'Tier 3' THEN 3
        WHEN ranked_data.tier = 'Tier 4' THEN 4
        WHEN ranked_data.tier = 'Tier 5' THEN 5
        END,
    ranked_data.winRate DESC,
    ranked_data.played DESC;

반쯤은 AI가 만들어줫지만...