How to use GROUP BY in an efficient way without losing attributes? [closed]

Question

I am working with PostgreSQL.

My idea is to group players who appear more than 3 times in table-a.

table-a = <player_id, year_world, c, d, e>

With the first subquery I get player_id and the number of times that player played a world cup. And with the NATURAL JOIN I recover the year or other attributes in the tuples that player appears.

The following query works but I want to optimize it:

SELECT player_id , year_world
FROM    (SELECT player_id , count(player_id ) player_id_count
        FROM <table-a> 
        GROUP BY player_id 
        HAVING count(player_id ) > 3) playersMoreThree
NATURAL JOIN <table-a>

I would like to know if it is possible not to use the NATURAL JOIN to obtain the other attributes of the table. Because when I put the rest of the attributes I want to project into SELECT and in theGROUP BY I get an empty table.

Keep in mind that the attribute that is added to the SELECT of the subquery of the GROUP BY has to be determined by the attribute that we are grouping. That is, for each player_id value, I can always have the same value of the new attribute, if for a same value of player_id I have different values of the new attribute, this is not determined. For add an attribute that is not correlated by what group I use NATURAL JOIN or another solution.

The following query without NATURAL JOIN does not work for me:

SELECT player_id, count(player_id ) player_id_count, year_world, c, d, e
FROM <table-a> 
GROUP BY player_id, year_world, c, d, e
HAVING count(player_id ) > 3

What would be the most efficient way in DML?

Answer 1

You can simply add more columns in the SELECT of the main query, without changing the subquery at all:

SELECT player_id , year_world, c, d, e                   -- all columns
FROM    (SELECT player_id , count(player_id ) player_id_count
        FROM <table-a> 
        GROUP BY player_id 
        HAVING count(player_id ) > 3) playersMoreThree
NATURAL JOIN <table-a>

Whether you do a NATURAL join or a JOIN .. ON doesn't matter for efficiency (but using a LATERAL join may do matter).

The other common method would be using window functions in a subquery or CTE and then restricting the result with a WHERE in the main query - exactly what Lennart's answer does.

In any case, the problem needs some kind of subquery to be solved.

For efficiency, an index on (player_id) is very likely to help, with either method you write the query. Memory settings, like work_mem should also be examined and can affect performance. Optimal settings and efficiency depend on many things (size of the tables, data distribution, RAM size of the whole server, number of connections, version of Postgres, etc.)

It could very well be that the solution with the join on the GROUP BY result (or the LATERAL variation) is more efficient than the solution using window functions. This depends largely on your data distribution and the indexes available. You have to compare the execution plan for all methods.

Answer 2

You can use a window function:

SELECT player_id , ... FROM (
    SELECT player_id, year_world, ...
         , COUNT(*) over (PARTITION BY player_id) AS player_id_count
    FROM <table-a> 
) AS T
WHERE player_id_count > 3

over (partition by player_id) defines the window where count(*) should be applied. In this case, we want the window to be all rows for the player in question. This means that you will get the count for player_id on all rows with player_id.