I'm trying to find the query that showes the loss rate of each player who has lost at least one-CodePudding

I need help to find the loss rate of each player who has lost at least one match. The loss rate is the ratio between the number of losses and the number of matches played. For each player who has lost at least one match, display only his identifier (id) and his defeat rate.

Here are the tables:

    CREATE TABLE champions
    (
        id_champion INT PRIMARY KEY NOT NULL,
        name VARCHAR(20),
        title VARCHAR(20),
        attack INT,
        defense INT,
        magic INT,
        difficulty INT
    );
    
    CREATE TABLE players
    (
        id_player INT PRIMARY KEY NOT NULL,
        name VARCHAR(20),
        country VARCHAR(20)
    );
    
    CREATE TABLE matchs
    (
        id_match INT,
        player INT,
        champion INT,
        number INT,
        gold INT,
        team INT,
        position VARCHAR(10),
        PRIMARY KEY (id_match, player),
        FOREIGN KEY(id_match) REFERENCES matchsmeta(id_match) ON DELETE SET NULL,
        FOREIGN KEY(player) REFERENCES players(id_player) ON DELETE SET NULL,
        FOREIGN KEY(champion) REFERENCES champions(id_champion) ON DELETE SET NULL
    
    );
    
    CREATE TABLE matchsmeta
    (
        id_match INT PRIMARY KEY NOT NULL,
        time INT,
        victory INT,
        date DATE,
        game_mode VARCHAR(10)
    );

Here is what i tried :

SELECT a.id_player, 
       ( (c.victory * 1.0) / COUNT(c.id_match)) AS "Taux de Defaite" FROM players AS a 
INNER JOIN matchs AS b 
INNER JOIN matchsmeta AS c ON a.id_player = player 
AND b.id_match = c.id_match;

But the problem is it's not showing me what i want.

Here is a very short data :

INSERT INTO champions VALUES(1, 'Dragon', 'Drag', 1000, 1000, 'Feu', 10);
INSERT INTO champions VALUES(2, 'Slime', 'Slime', 100, 100, 'Translucide', 1);
INSERT INTO champions VALUES(3, 'Demon', 'Demon', 10000, 5000, 'DarkSide', 10);

INSERT INTO players VALUES(1, 'Nobody1', 'France');
INSERT INTO players VALUES(2, 'Nobody2', 'France');
INSERT INTO players VALUES(3, 'Nobody3', 'France');
INSERT INTO players VALUES(4, 'Nobody4', 'France');
INSERT INTO players VALUES(5, 'Nobody5', 'France');

INSERT INTO matchs VALUES(1, 1, 1, 1, 1000, 100, 'TOP');
INSERT INTO matchs VALUES(1, 2, 2, 2, 1000, 100, 'JUNGLE');
INSERT INTO matchs VALUES(1, 3, 3, 3, 1000, 100, 'MID');
INSERT INTO matchs VALUES(3, 4, 3, 4, 1000, 200, 'SUPPORT');
INSERT INTO matchs VALUES(1, 5, 3, 5, 1000, 200, 'BOTTOM');

INSERT INTO matchsmeta VALUES(1, 60, 100, '2022-01-01', 'CLASSIC');
INSERT INTO matchsmeta VALUES(2, 90, 100, '2022-02-01', 'CLASSIC');
INSERT INTO matchsmeta VALUES(3, 60, 100, '2022-03-01', 'CLASSIC');

CodePudding user response：

In SQLITE, using an aggregate function like COUNT() automatically implies an aggregation. If you don't specify a GROUP BY clause, it will aggregate over all rows. You may also want to order by loss rate and filter out those without losses.

Try this:

SELECT a.id_player,(SUM(c.victory * 1.0) / COUNT(c.id_match)) AS "Taux de Defaite" FROM players AS a 
INNER JOIN matchs AS b 
INNER JOIN matchsmeta AS c ON a.id_player = player AND b.id_match = c.id_match
GROUP BY a.id_player
HAVING (SUM(c.victory * 1.0) / COUNT(c.id_match)) > 0
ORDER BY (SUM(c.victory * 1.0) / COUNT(c.id_match));

CodePudding user response：

If I understood the data correctly, you query should look something like this:

SELECT player
, COUNT(*) AS MatchesPlayed
, SUM(CASE WHEN m.team = mm.victory THEN 1 ELSE 0 END) AS MatchesWon
, SUM(CASE WHEN m.team != mm.victory THEN 1 ELSE 0 END) AS MatchesLost
, SUM(CASE WHEN m.team != mm.victory THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS LossRatio
FROM matchs m
INNER JOIN matchsmeta mm ON m.id_match = mm.id_match
GROUP BY m.player

Basically, if you want to find data per player you should group by player, calculate your aggregates (played, lost) and divide them.

CodePudding user response：

Join the table matchs to matchsmeta and use conditional aggregation:

SELECT m.player, 
       AVG(mm.victory <> m.team) [Taux de Defaite] 
FROM matchs m INNER JOIN matchsmeta mm 
ON mm.id_match = m.id_match 
GROUP BY m.player
HAVING [Taux de Defaite] > 0;

The table players is not needed because you want in the results only the player's id which exists in the table matchs.

See the demo.