Упорядочивайте целые группы результатов по наивысшему значению в каждой группе

Например, допустим, что у меня есть таблица в PostgreSQL (выше 9.0), заполненная данными:

row_id    percent    isrc
1         100        123iee43
2         100        1234wr32
3         98         123iee43
4         99         1234wr32
5         95         12313be3
6         99         12313be3
7         96         12313be3

Я хотел бы, чтобы мой результат содержал ВСЕ приведенные выше строки, сгруппированные по столбцу isrc, а затем целые группы, упорядоченные по percent по убыванию. Вот как должен выглядеть результат:

row_id    percent    isrc
1         100        123iee43
3         98         123iee43
2         100        1234wr32
4         99         1234wr32
6         99         12313be3
7         96         12313be3
5         95         12313be3

Если бы я хотел упорядочить по возрастанию, это то, что я ожидал бы (я хочу упорядочить только по первой строке в одной группе, другие строки в одной группе не имеют значения):

row_id    percent    isrc
6         99         12313be3
7         96         12313be3
5         95         12313be3
1         100        123iee43
3         98         123iee43
2         100        1234wr32
4         99         1234wr32

Думаю, мне нужно как-то использовать оконные функции, но я не смог найти правильного решения, если оно существует. Кроме того, было бы здорово, если бы решение было максимально элегантным. :)


postgresql sql window-functions greatest-n-per-group
person Rok Dominko    schedule 05.04.2013    source источник
comment
Почему вы показываете 123iee43 перед 1234wr32 в третьем списке? Должно быть наоборот. Также: как вы хотите отсортировать isrc? По возрастанию, по убыванию или нет? Это актуально для разрыва связей.   -  person Erwin Brandstetter    schedule 05.04.2013

Ответы (2)


arrow_upward
2
arrow_downward

Оконная функция

SELECT row_id, percent, isrc
FROM   tbl
ORDER  BY max(percent) OVER(PARTITION BY isrc) DESC, isrc, percent DESC;

Агрегатная функция max() может использоваться как оконная функция. Я не использую ORDER BY в предложении window, потому что согласно документации:

Когда агрегатная функция используется как оконная функция, она агрегирует по строкам внутри оконного фрейма текущей строки. Агрегат, используемый с ORDER BY и определением фрейма окна по умолчанию, производит поведение типа «бегущая сумма», которое может быть, а может и не быть тем, что нужно. Чтобы получить агрегирование по всему разделу, опустите ORDER BY или используйте ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Другие спецификации кадра могут использоваться для получения других эффектов.

Оконную функцию нельзя использовать в предложении WHERE или HAVING, потому что они применяются до оконной функции. Но вы можете использовать его в предложении ORDER BY, которое применяется последним (даже после DISTINCT, но до LIMIT).

Оконные функции могут быть дорогими, но эта функция настолько упрощает запрос, что может быть даже быстрее, чем альтернативы.
И она, безусловно, самая элегантная.

Агрегатная функция

Плюс JOIN. Может быть, а может и не быть быстрее.

SELECT row_id, percent, isrc
FROM   tbl
JOIN  (SELECT isrc, max(percent) AS max_pct FROM tbl GROUP BY 1) x USING (isrc)
ORDER  BY x.max_pct DESC, isrc, percent DESC;

DISTINCT ON

Очень похоже на использование агрегатной функции.

SELECT t.*
FROM   tbl t
JOIN  (
    SELECT DISTINCT ON (isrc) isrc, percent
    FROM   tbl
    ORDER  BY isrc, percent DESC
    ) s USING (isrc)
ORDER BY s.percent DESC, s.isrc, t.percent DESC

Здесь вам не нужна оконная функция.

SQL Fiddle, демонстрирующий все вышеперечисленное.

person Erwin Brandstetter    schedule 05.04.2013
comment
Действительно исчерпывающий ответ! Спасибо! - person Rok Dominko; 05.04.2013
comment
@RokDominko: Пожалуйста. :) Не могли бы вы запустить эти альтернативы (включая Clodoaldo) с EXPLAIN ANALYZE и отчитаться о производительности (плюс версия Postgres и количество строк в ваших таблицах)? - person Erwin Brandstetter; 05.04.2013

arrow_upward
0
arrow_downward

SQL Fiddle

select t.*
from
    t
    inner join (
        select distinct on (isrc) isrc,
            row_number() over(order by percent desc) rn
        from t
        order by isrc, percent desc
    ) s on t.isrc = s.isrc
order by s.rn, t.percent desc
person Clodoaldo Neto    schedule 05.04.2013