I am working on a set of leaderboards for a game I am making and want to be able to have a "K/D" (kill-death ratio) leaderboard among them. That is, select the players with the top ratio of (total kills) / (total deaths).

My database is currently formatted such that there are two tables, a Kill table and a Death table (I am aware that in this example it seems silly to have these as two separate tables, but there are other details irrelevant to the problem at hand that make it so these need to be separate tables)

Kill table:

Death table:

(see https://i.imgur.com/V8OEVxo.png for what the tables are supposed to look like, since formatting above is messed up.)

Given the above data, querying for the top K/D ratios would return (2,27/13), (1, 15/22); i.e. all (player_id, K/D ratio) pairs sorted by descending K/D ratio.

I already have other leaderboards in place for things like highest overall kills, which I implemented as follows:

SELECT player_id, sum(kill_count) as totalkills from Kill group by player_id order by totalkills desc;

and this seems to work fine, so I thought I could get the highest K/D ratio using the following:

SELECT Kill.player_id, sum(kill_count)/sum(death_count) as KD from Kill, Death group by Kill.player_id order by KD desc;

but it gives totally bogus results.

Any pointers on the correct way to format my query would be appreciated :)

Edit: I am unsure why the tables do not appear correctly formatted in the question, as they look fine in the preview. Is this a bug?

CodePudding user response：

You can group together the kills and the deaths by player, and then combine them to find what you're looking for:

WITH total_kills AS (
    SELECT player_id, sum(kill_count) as kill_count 
    FROM kill 
    GROUP BY player_id
), 
total_deaths AS (
    SELECT player_id, sum(death_count) as death_count 
    FROM death
    GROUP BY player_id
) 
SELECT total_kills.player_id, 1.0 * kill_count / NULLIF(death_count, 0) as kd
FROM total_kills, total_deaths 
WHERE total_kills.player_id = total_deaths.player_id
ORDER BY 2 DESC;

Or if you prefer subqueries:

SELECT total_kills.player_id, 1.0 * kill_count / NULLIF(death_count, 0) as kd
FROM 
    (SELECT player_id, sum(kill_count) as kill_count 
     FROM kill 
     GROUP BY player_id) as total_kills, 
    (SELECT player_id, sum(death_count) as death_count
     FROM death 
     GROUP BY player_id) as total_deaths 
WHERE total_kills.player_id = total_deaths.player_id
ORDER BY 2 DESC;

The NULLIF is just there to prevent the query from breaking if the player has no deaths.

CodePudding user response：

Hi i don‘t Unterstand your Problem. I think the better way ist to use a Window Funktion.

Select player_id, Weapon_type, count() over ( partition by player_id ) as „killes pro player“ count() over ( Partition by player_id , weapon_type ) „killes by player and weapon“ from order by player_id;