Оконная функция 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|

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

EDIT: я знаю, что могу сделать это с двумя 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

postgresql sql window-functions postgresql-performance
person Aleix    schedule 19.03.2014    source источник
comment
Не связано: вместо написания SELECT ... UNION для генерации тестовых данных вы можете использовать гораздо более короткое предложение values: sqlfiddle .com/#!15/d41d8/1471   -  person a_horse_with_no_name    schedule 19.03.2014
comment
Является ли отметка времени (которая не должна называться date) уникальной?   -  person Erwin Brandstetter    schedule 20.03.2014
comment
Это сложно. Вам нужно динамическое окно, которое расширяется до тех пор, пока какое-либо другое значение не будет отличаться от текущего значения. Я думаю, что это можно сделать только с предложением окна RANGE, которое PostgreSQL пока не поддерживает. Я боюсь, что вы можете застрять с объединениями, поскольку диапазоны до и после потенциально не ограничены.   -  person Craig Ringer    schedule 20.03.2014
comment
@CraigRinger: я думаю, мне удалось его отклеить. :)   -  person Erwin Brandstetter    schedule 20.03.2014
comment
@ErwinBrandstetter, НЕТ, отметка времени может повторяться (два разных события в одну и ту же секунду, затем порядок, в котором они попадают в поле идентификатора). Извините, что это называется дата (это был перевод с оригинальных испанских имен)..   -  person Aleix    schedule 20.03.2014
comment
@A Должно быть легко адаптировать запрос к вашей новой спецификации. Добавьте id к ORDER BY и др. Не забудьте предоставить всю необходимую информацию в следующий раз, чтобы мы не теряли время зря.   -  person Erwin Brandstetter    schedule 20.03.2014

Ответы (1)


arrow_upward
12
arrow_downward

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

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 000 строк потребовалось только однократное сканирование индекса. Таким образом, должно быть прилично быстро даже с большими столами. Для сравнения, ваш запрос с соединением/различным не завершился через минуту (как и ожидалось).
Даже оптимизированная версия, имеющая дело с одним перекрестным соединением за раз (левое соединение с едва ли ограничивающим условием фактически является ограниченным перекрестным соединением). присоединиться) не закончил через минуту.

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

Как?

  1. В подзапросе sub1 просмотрите событие из предыдущей строки и сохраните его, только если оно изменилось, помечая таким образом первый элемент новой группы. При этом получить id предыдущего и следующего ряда (pre_id, post_id).

  2. В подзапросе sub2, count() подсчитываются только ненулевые значения. Полученный grp помечает одноранговые узлы в блоках последовательных одинаковых событий.

  3. В финальном 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.

person Erwin Brandstetter    schedule 20.03.2014