Geek Answers Handbook

Postgresql: grouping with a group size limit using window functions

Is there a way in Postgresql to write a query that groups rows based on a column with a limit without dropping extra rows.

Say I have a table with three columns id, color, score with the following rows

 1 red 10.0 2 red 7.0 3 red 3.0 4 blue 5.0 5 green 4.0 6 blue 2.0 7 blue 1.0

I can get color-based grouping using window functions with the following query

 SELECT * FROM ( SELECT id, color, score, rank() OVER (PARTITION BY color ORDER BY score DESC) FROM grouping_test ) AS foo WHERE rank <= 2;

with the result

  id | color | score | rank ----+-------+-------+------ 4 | blue | 5.0 | 1 6 | blue | 2.0 | 2 5 | green | 4.0 | 1 1 | red | 10.0 | 1 2 | red | 7.0 | 2

which discards an element with a rank> 2. However, I need a result, for example

 1 red 10.0 2 red 7.0 4 blue 5.0 6 blue 2.0 5 green 4.0 3 red 3.0 7 blue 1.0

No discarded lines.

Edit: To be more precise about the logic I need:

  • Get me the row with the highest result.
  • The next line with the same color and the highest possible rating.
  • The item with the highest score for the remaining items
  • The same as 2., but for a line from 3.
    ...

Continue until pairs with the same color can be found, and then order what's left descending.

The import statements for the test pattern can be found here . Thank you for your help.

+4
source share
2 answers

This can be done using the two nested window functions.

 SELECT id FROM ( SELECT id, color, score, ((rank() OVER color_window) - 1) / 2 AS rank_window_id FROM grouping_test WINDOW color_window AS (PARTITION BY color ORDER BY score DESC) ) as foo WINDOW rank_window AS (PARTITION BY (color, rank_window_id)) ORDER BY (max(score) OVER rank_window) DESC, color;

C 2 is a parameter of group size.

+2
source

You can do ORDER BY (rank <= 2) DESC to get rows with rank <= 2, first of all:

 SELECT id,color,score FROM ( SELECT id, color, score, rank() OVER (PARTITION BY color ORDER BY score DESC), max(score) OVER (PARTITION BY color) mx FROM grouping_test ) AS foo ORDER BY (rank <= 2) DESC, CASE WHEN rank<=2 THEN mx ELSE NULL END DESC, id;

http://sqlfiddle.com/#!12/bbcfc/109

+1
source

More articles:


All Articles