Оконная функция PostgreSQL: разделение по сравнению

Question

Оконная функция PostgreSQL: разделение по сравнению

Я пытаюсь найти способ сделать сравнение с текущей строкой в предложении PARTITION BY в функции WINDOW в запросе PostgreSQL.

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

WITH events AS(
  SELECT 1 as id, 12 as event, '2014-03-19 08:00:00'::timestamp as date
  UNION SELECT 2 as id, 12 as event, '2014-03-19 08:30:00'::timestamp as date
  UNION SELECT 3 as id, 13 as event, '2014-03-19 09:00:00'::timestamp as date
  UNION SELECT 4 as id, 13 as event, '2014-03-19 09:30:00'::timestamp as date
  UNION SELECT 5 as id, 12 as event, '2014-03-19 10:00:00'::timestamp as date
)
SELECT lag(id)  over w as previous_different, event
     , lead(id) over w as next_different
FROM events ev
WINDOW w AS (PARTITION BY event!=ev.event ORDER BY date ASC);

Я знаю сравнение event!=ev.event неверно, но это то, чего я хочу достичь.

Результат, который я получаю (так же, как если бы я удалил предложение PARTITION BY):

 |12|2
1|12|3
2|13|4
3|13|5
4|12|

И результат, который я хотел бы получить:

 |12|3
 |12|3
2|13|5
2|13|5
4|12|

Кто-нибудь знает, возможно ли это и как? Большое спасибо!

РЕДАКТИРОВАТЬ: я знаю, что могу сделать это с двумя JOINс, а ORDER BY и DISTINCT ON, но в реальном случае миллионов строк это очень неэффективно:

WITH events AS(
  SELECT 1 as id, 12 as event, '2014-03-19 08:00:00'::timestamp as date
  UNION SELECT 2 as id, 12 as event, '2014-03-19 08:30:00'::timestamp as date
  UNION SELECT 3 as id, 13 as event, '2014-03-19 09:00:00'::timestamp as date
  UNION SELECT 4 as id, 13 as event, '2014-03-19 09:30:00'::timestamp as date
  UNION SELECT 5 as id, 12 as event, '2014-03-19 10:00:00'::timestamp as date
)
SELECT DISTINCT ON (e.id, e.date) e1.id, e.event, e2.id
FROM events e
LEFT JOIN events e1 ON (e1.date<=e.date AND e1.id!=e.id AND e1.event!=e.event) 
LEFT JOIN events e2 ON (e2.date>=e.date AND e2.id!=e.id AND e2.event!=e.event) 
ORDER BY e.date ASC, e.id ASC, e1.date DESC, e1.id DESC, e2.date ASC, e2.id ASC

5

sql postgresql window-functions postgresql-performance

Источник

user743194 19 мар '14 в 16:32

1 ответ

Решение

Другие вопросы по тегам sql postgresql window-functions postgresql-performance

user939860 20 мар '14 в 01:08 2014-03-20 01:08 · Accepted Answer · 2014-03-20 01:08

Используя несколько разных оконных функций и два подзапроса, это должно работать довольно быстро:

WITH events(id, event, ts) AS (
  VALUES
   (1, 12, '2014-03-19 08:00:00'::timestamp)
  ,(2, 12, '2014-03-19 08:30:00')
  ,(3, 13, '2014-03-19 09:00:00')
  ,(4, 13, '2014-03-19 09:30:00')
  ,(5, 12, '2014-03-19 10:00:00')
   )
SELECT first_value(pre_id)  OVER (PARTITION BY grp ORDER BY ts)      AS pre_id
     , id, ts
     , first_value(post_id) OVER (PARTITION BY grp ORDER BY ts DESC) AS post_id
FROM  (
   SELECT *, count(step) OVER w AS grp
   FROM  (
      SELECT id, ts
           , NULLIF(lag(event) OVER w, event) AS step
           , lag(id)  OVER w AS pre_id
           , lead(id) OVER w AS post_id
      FROM   events
      WINDOW w AS (ORDER BY ts)
      ) sub1
   WINDOW w AS (ORDER BY ts)
   ) sub2
ORDER  BY ts;

С помощью ts как имя для столбца метки времени.
Если предположить, ts быть уникальным и проиндексированным ( уникальное ограничение делает это автоматически).

В тесте с реальной таблицей из 50 тыс. Строк требовалось только одно сканирование индекса. Итак, должен быть прилично быстрым даже с большими столами. Для сравнения, ваш запрос с объединением / отличным не завершился через минуту (как и ожидалось).
Даже оптимизированная версия, работающая с одним перекрестным соединением за раз (левое объединение с едва ли ограничивающим условием, по сути, является ограниченным перекрестным соединением), не завершилась через минуту.

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

Как?

В подзапросе sub1 посмотрите на событие из предыдущей строки и сохраните его только в том случае, если оно изменилось, пометив таким образом первый элемент новой группы. В то же время, получить id предыдущего и следующего ряда (pre_id, post_id).
В подзапросе sub2, count() учитывает только ненулевые значения. Результирующий grp отмечает пиров в блоках последовательных одинаковых событий.
В финале SELECT возьми первый pre_id и последнее post_id на группу для каждой строки, чтобы получить желаемый результат.
На самом деле, это должно быть еще быстрее во внешнем SELECT:
```
 last_value(post_id) OVER (PARTITION BY grp ORDER BY ts
                           RANGE BETWEEN UNBOUNDED PRECEDING
                                 AND     UNBOUNDED FOLLOWING) AS post_id
```
... так как порядок сортировки окна совпадает с окном для pre_id так что нужен только один вид. Быстрый тест, кажется, подтверждает это. Подробнее об этом определении кадра.

SQL Fiddle.