CODEWITHSUNDEEP
sqlpostgresql
Davy Kamanzi
Davy Kamanzi

Reputation: 171

PostgresQL: Finding records that do not exist in query

Below, I have a query for a rankings table of players in a leaderboard.

Player information (including pseudonym) is stored in the player table, while rankings for each "matchday" (identified by edition_id) are stored in players_rankings as the competition is lineal (there's no points system, so rankings can't be computed mathematically). Information for each fixture is stored in set (sw denotes set wins, while sl denotes set losses).

SELECT
  players_rankings.rank, players_rankings.change, player.pseudonym,
  SUM(tot.sw) AS sw,
  SUM(tot.sl) AS sl
FROM players_rankings, player, (
    SELECT
      player1_id AS player_id,
      CASE
        WHEN score1 > score2 THEN 1 ELSE 0
      END AS sw,
      CASE
        WHEN score1 < score2 THEN 1 ELSE 0
      END AS sl
    FROM set WHERE edition_id = 1
    UNION ALL
    SELECT
      player2_id,
      CASE
        WHEN score1 < score2 THEN 1 ELSE 0
      END,
      CASE
        WHEN score1 > score2 THEN 1 ELSE 0
      END
    FROM set WHERE edition_id = 1
  ) AS tot
WHERE
  players_rankings.edition_id = 1 AND
  tot.player_id = players_rankings.player_id AND
  players_rankings.player_id = player.id
GROUP BY 1, 2, 3
UNION
SELECT players_rankings.rank, players_rankings.change, player.pseudonym, 0, 0
FROM players_rankings, player
WHERE
  players_rankings.edition_id = 1 AND
  players_rankings.player_id = player.id
ORDER BY 1;

Which produces the following result:

-----+--------+---------------+----+----+
rank | change | pseudonym     | sw | sl |
-----+--------+---------------+----+----+
1    | 0      | Player One    | 1  | 0  |
-----+--------+---------------+----+----+
1    | 0      | Player One    | 0  | 0  |
-----+--------+---------------+----+----+
2    | 0      | Player Two    | 0  | 0  |
-----+--------+---------------+----+----+
3    | 2      | Player Three  | 1  | 0  |
-----+--------+---------------+----+----+
3    | 2      | Player Three  | 0  | 0  |
-----+--------+---------------+----+----+
4    | -1     | Player Four   | 0  | 1  |
-----+--------+---------------+----+----+
4    | -1     | Player Four   | 0  | 0  |
-----+--------+---------------+----+----+
5    | -1     | Player Five   | 1  | 0  |
-----+--------+---------------+----+----+
5    | -1     | Player Five   | 0  | 0  |
-----+--------+---------------+----+----+
6    | 3      | Player Six    | 0  | 0  |
-----+--------+---------------+----+----+
6    | 3      | Player Six    | 1  | 0  |
-----+--------+---------------+----+----+
7    | -1     | Player Seven  | 0  | 0  |
-----+--------+---------------+----+----+
7    | -1     | Player Seven  | 0  | 1  |
-----+--------+---------------+----+----+
8    | -1     | Player Eight  | 0  | 0  |
-----+--------+---------------+----+----+
8    | -1     | Player Eight  | 0  | 1  |
-----+--------+---------------+----+----+
9    | -1     | Player Nine   | 0  | 0  |
-----+--------+---------------+----+----+
10   | 0      | Player Ten    | 0  | 1  |
-----+--------+---------------+----+----+
10   | 0      | Player Ten    | 0  | 0  |
-----+--------+---------------+----+----+
11   | 0      | Player Eleven | 0  | 0  |
-----+--------+---------------+----+----+
12   | 0      | Player Twelve | 0  | 0  |
-----+--------+---------------+----+----+

My goal with the query after the UNION was to get only registered players that didn't feature in the first "matchday" (players_rankings.edition_id = 1, i.e. players Two, Nine, Eleven, and Twelve), but I hit a brick wall trying different methods to achieve that, including different JOINs. As such, I went back to the drawing board and used the aforementioned query to start again with the duplicate values as shown above. Below is the desired result:

-----+--------+---------------+----+----+
rank | change | pseudonym     | sw | sl |
-----+--------+---------------+----+----+
1    | 0      | Player One    | 1  | 0  |
-----+--------+---------------+----+----+
2    | 0      | Player Two    | 0  | 0  |
-----+--------+---------------+----+----+
3    | 2      | Player Three  | 1  | 0  |
-----+--------+---------------+----+----+
4    | -1     | Player Four   | 0  | 1  |
-----+--------+---------------+----+----+
5    | -1     | Player Five   | 1  | 0  |
-----+--------+---------------+----+----+
6    | 3      | Player Six    | 1  | 0  |
-----+--------+---------------+----+----+
7    | -1     | Player Seven  | 0  | 1  |
-----+--------+---------------+----+----+
8    | -1     | Player Eight  | 0  | 1  |
-----+--------+---------------+----+----+
9    | -1     | Player Nine   | 0  | 0  |
-----+--------+---------------+----+----+
10   | 0      | Player Ten    | 0  | 1  |
-----+--------+---------------+----+----+
11   | 0      | Player Eleven | 0  | 0  |
-----+--------+---------------+----+----+
12   | 0      | Player Twelve | 0  | 0  |
-----+--------+---------------+----+----+

How should I go about achieving this?

Upvotes: 0

Views: 87

Answers (2)

persian-theme
persian-theme

Reputation: 6638

Use window function ROW_NUMBER() and partiton by rank and sort with case statement.

Using the Row_Number function, group the rows that have the same rank, and then, based on the fact that the row has the condition sw = 1 OR sl = 1, the value of one is included in the sort, otherwise the value 0 is then sorted in descending order.

In fact, the Row_Number function numbers the rows based on the same rank, and in the main query, the rows that are numbered number one are fetched.

SELECT rank,change,pseudonym,sw,sl
FROM
  (SELECT *,
       ROW_NUMBER() OVER(PARTITION BY rank ORDER BY CASE WHEN sw = 1 OR sl = 1 THEN 1 ELSE 0 END DESC)   AS num
  FROM
    (SELECT
      players_rankings.rank, players_rankings.change, player.pseudonym,
      SUM(tot.sw) AS sw,
      SUM(tot.sl) AS sl
    FROM players_rankings, player, (
        SELECT
          player1_id AS player_id,
          CASE
            WHEN score1 > score2 THEN 1 ELSE 0
          END AS sw,
          CASE
            WHEN score1 < score2 THEN 1 ELSE 0
          END AS sl
        FROM set WHERE edition_id = 1
        UNION ALL
        SELECT
          player2_id,
          CASE
            WHEN score1 < score2 THEN 1 ELSE 0
          END,
          CASE
            WHEN score1 > score2 THEN 1 ELSE 0
          END
        FROM set WHERE edition_id = 1
      ) AS tot
    WHERE
      players_rankings.edition_id = 1 AND
      tot.player_id = players_rankings.player_id AND
      players_rankings.player_id = player.id
    GROUP BY 1, 2, 3
    UNION
    SELECT players_rankings.rank, players_rankings.change, player.pseudonym, 0, 0
    FROM players_rankings, player
    WHERE
      players_rankings.edition_id = 1 AND
      players_rankings.player_id = player.id) T) T
WHERE num = 1      
ORDER BY 1;

Demo in db<>fiddle

Upvotes: 1

gsiems
gsiems

Reputation: 3790

Caveat: I haven't finished my morning coffee yet... If I understand your question correctly the following (un-tested) approach could work:

WITH pr AS (
    SELECT players_rankings.player_id,
            players_rankings.rank,
            players_rankings.change,
            player.pseudonym
        FROM players_rankings
        JOIN player
            ON ( players_rankings.player_id = player.id )
        WHERE players_rankings.edition_id = 1
),
tot AS (
    SELECT t.player_id,
            sum ( t.sw ) AS sw,
            sum ( t.sl ) AS sl
        FROM (
            SELECT player1_id AS player_id,
                    CASE
                        WHEN score1 > score2 THEN 1
                        ELSE 0
                        END AS sw,
                    CASE
                        WHEN score1 < score2 THEN 1
                        ELSE 0
                        END AS sl
                FROM SET
                WHERE edition_id = 1
            UNION ALL
            SELECT player2_id,
                    CASE
                        WHEN score1 < score2 THEN 1
                        ELSE 0
                        END,
                    CASE
                        WHEN score1 > score2 THEN 1
                        ELSE 0
                        END
                FROM SET
                WHERE edition_id = 1
            ) AS t
        GROUP BY t.player_id
)
SELECT pr.rank,
        pr.change,
        pr.pseudonym,
        0 AS sw,
        0 AS sl
    FROM pr
    FULL OUTER JOIN tot
        ON ( pr.player_id = tot.player_id )
    WHERE tot.pseudonym IS NULL
    ORDER BY 1 ;

edit fix columns in full outer join

Upvotes: 0

Related Questions