Функция окна Postgres и группа по исключению

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

Вот запрос, который у меня есть до сих пор:

SELECT p.name, e.date, 
    sum(sp.payout) OVER (ORDER BY e.date)
    - sum(s.buyin) OVER (ORDER BY e.date) AS "Profit/Loss" 
FROM result r 
    JOIN game g ON r.game_id = g.game_id 
    JOIN event e ON g.event_id = e.event_id 
    JOIN structure s ON g.structure_id = s.structure_id 
    JOIN structure_payout sp ON g.structure_id = sp.structure_id
                            AND r.position = sp.position 
    JOIN player p ON r.player_id = p.player_id 
WHERE p.player_id = 17 
GROUP BY p.name, e.date, e.event_id, sp.payout, s.buyin
ORDER BY p.name, e.date ASC

Запрос будет запущен. Однако результат несколько неверен. Причина в том, что event может иметь несколько игр (с разными sp.payouts). Следовательно, вышесказанное выдается с несколькими строками, если у пользователя есть 2 результата в событии с разными выплатами (т.е. Есть 4 игры за событие, а пользователь получает £ 20 от одного и 40 фунтов за другого).

Очевидным решением было бы изменить GROUP BY на:

GROUP BY p.name, e.date, e.event_id

Однако Postgres жалуется на это, поскольку он, похоже, не признает, что sp.payout и s.buyin находятся внутри агрегатной функции. Я получаю сообщение об ошибке:

столбец "sp.payout" должен появиться в предложении GROUP BY или использоваться в агрегированная функция

Я запускаю 9.1 на сервере Ubuntu Linux.
Я что-то упускаю, или это может быть настоящим дефектом в Postgres?

Ответ 1

Вы не, на самом деле, используя агрегатные функции. Вы используете функции окна. Поэтому PostgreSQL требует, чтобы sp.payout и s.buyin включались в предложение GROUP BY.

Добавляя предложение OVER, агрегированная функция sum() превращается в функцию окна, которая агрегирует значения для каждого раздела, сохраняя все строки.

Вы можете объединить функции окна и агрегатные функции. Сначала применяются агрегирования. Я не понял из вашего описания, как вы хотите обрабатывать несколько выплат /buyins за событие. Думаю, я рассчитываю их сумму за каждое событие. Теперь я могу удалить sp.payout и s.buyin из предложения GROUP BY и получить одну строку за player и event:

SELECT p.name
     , e.event_id
     , e.date
     , sum(sum(sp.payout)) OVER w
     - sum(sum(s.buyin  )) OVER w AS "Profit/Loss" 
FROM   player            p
JOIN   result            r ON r.player_id     = p.player_id  
JOIN   game              g ON g.game_id       = r.game_id 
JOIN   event             e ON e.event_id      = g.event_id 
JOIN   structure         s ON s.structure_id  = g.structure_id 
JOIN   structure_payout sp ON sp.structure_id = g.structure_id
                          AND sp.position     = r.position
WHERE  p.player_id = 17 
GROUP  BY e.event_id
WINDOW w AS (ORDER BY e.date, e.event_id)
ORDER  BY e.date, e.event_id;

В этом выражении: sum(sum(sp.payout)) OVER w внешний sum() является оконной функцией, внутренний sum() является агрегированной функцией.

Предполагая, что p.player_id и e.event_id являются PRIMARY KEY в соответствующих таблицах.

Я добавил e.event_id в ORDER BY предложения WINDOW, чтобы получить детерминированный порядок сортировки. (В тот же день может быть несколько событий.) Кроме того, в результат добавлен event_id, чтобы различать несколько событий в день.

Пока запрос ограничивается одним игроком (WHERE p.player_id = 17), нам не нужно добавлять p.name или p.player_id в GROUP BY и ORDER BY. Если одно из объединений будет чрезмерно умножать строки, результирующая сумма будет неправильной (частично или полностью умноженной). Группировка p.name не может восстановить запрос.

Я также удалил e.date из предложения GROUP BY. Первичный ключ e.event_id охватывает все столбцы входной строки с PostgreSQL 9.1.

Если вы меняете запрос, чтобы сразу возвращать сразу несколько игроков, приспособите:

...
WHERE  p.player_id < 17  -- example - multiple players
GROUP  BY p.name, p.player_id, e.date, e.event_id  -- e.date and p.name redundant
WINDOW w AS (ORDER BY p.name, p.player_id, e.date, e.event_id)
ORDER  BY p.name, p.player_id, e.date, e.event_id;

Если p.name не определяется уникальным (?), группой и порядком на player_id дополнительно, чтобы получить правильные результаты в детерминированном порядке сортировки.

Я сохранил e.date и p.name в GROUP BY, чтобы иметь одинаковый порядок сортировки во всех предложениях, надеясь получить выгоду от производительности. Иначе вы можете удалить столбцы. (Аналогично только для e.date в первом запросе.)