Postgresql: группировка с ограничением размера группы с использованием оконных функций

Есть ли способ в Postgresql написать запрос, который группирует строки на основе столбца с ограничением, не отбрасывая дополнительные строки.

Скажем, у меня есть таблица с тремя столбцами id, color, score со следующими строками

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

Я могу получить группировку по цвету с помощью оконных функций со следующим запросом

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

с результатом

  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

который отбрасывает элемент с рангами> 2. Однако мне нужен результат вроде

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

Без отброшенных строк.

Изменить: Чтобы быть более точным в отношении логики, которая мне нужна:

  1. Дайте мне строку с наивысшим баллом
  2. Следующий ряд с тем же цветом и максимально возможным счетом
  3. Элемент с наивысшим баллом из оставшихся предметов
  4. То же, что и 2., но для строки из 3.
    ...

Продолжайте до тех пор, пока можно найти пары одного цвета, а затем упорядочите то, что осталось, по убыванию.

Операторы импорта для тестовой таблицы можно найти здесь. Спасибо за вашу помощь.


postgresql window-functions
person Mario Konschake    schedule 23.04.2013    source источник
comment
Какова именно логика заказа? Вы хотите, чтобы цвета были сгруппированы на основе самого низкого идентификатора цвета?   -  person Jakub Kania    schedule 23.04.2013
comment
Запрос в вашем вопросе был именно тем, что я искал. Спасибо   -  person Bill    schedule 10.08.2014

Ответы (2)


arrow_upward
3
arrow_downward

Это можно сделать с помощью двух вложенных оконных функций.

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;

При этом 2 является параметром размера группы.

person Mario Konschake    schedule 23.04.2013

arrow_upward
1
arrow_downward

Вы можете сделать ORDER BY (rank <= 2) DESC, чтобы получить строки с рангом‹=2 выше всего остального:

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

person Jakub Kania    schedule 23.04.2013
comment
Я отредактировал вопросы, чтобы уточнить логику порядка, которая мне нужна. Надеюсь, на этот раз будет более ясно. - person Mario Konschake; 23.04.2013
comment
@MarioKonschake Я обновил. Хотя я не смотрю на запрос выше, кажется, что это почти то же самое решение. - person Jakub Kania; 24.04.2013