Условная функция опережения / запаздывания PostgreSQL?

У меня есть такая таблица:

Name   activity  time

user1  A1        12:00
user1  E3        12:01
user1  A2        12:02
user2  A1        10:05
user2  A2        10:06
user2  A3        10:07
user2  M6        10:07
user2  B1        10:08
user3  A1        14:15
user3  B2        14:20
user3  D1        14:25
user3  D2        14:30

Теперь мне нужен такой результат:

Name   activity  next_activity

user1  A2        NULL
user2  A3        B1
user3  A1        B2

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

Как я могу решить свою проблему?


postgresql sql window-functions greatest-n-per-group
person KolM    schedule 22.01.2017    source источник
comment
activity from group B always takes place after activity from group A. Это означает, что у каждого пользователя никогда не бывает двух последовательных действий B? Также: ваши образцы данных показывают, что все действия для каждого пользователя всегда происходят последовательно, то есть A1 - ›A2 -› A3, .. никогда A3 - ›A2. Верный? И: версия Postgres?   -  person Erwin Brandstetter    schedule 23.01.2017
comment
Да, ты прав :)   -  person KolM    schedule 23.01.2017

Ответы (1)


arrow_upward
9
arrow_downward

Ваше определение:

активность из группы B всегда происходит после активности из группы A.

.. логически подразумевает, что для каждого пользователя существует 0 или 1 активность B после 1 или более операций A. Не более 1 млрд действий подряд.

Вы можете заставить его работать с помощью функции единого окна, DISTINCT ON и CASE, что должно быть самым быстрым способом для нескольких строк на пользователя (также см. Ниже):

SELECT name
     , CASE WHEN a2 LIKE 'B%' THEN a1 ELSE a2 END AS activity
     , CASE WHEN a2 LIKE 'B%' THEN a2 END AS next_activity
FROM  (
   SELECT DISTINCT ON (name)
          name
        , lead(activity) OVER (PARTITION BY name ORDER BY time DESC) AS a1
        , activity AS a2
   FROM   t
   WHERE (activity LIKE 'A%' OR activity LIKE 'B%')
   ORDER  BY name, time DESC
   ) sub;

db ‹› fiddle здесь

По умолчанию выражение SQL CASE равно NULL, если ветка ELSE не добавлена, поэтому я остановился на этом кратко.

Предполагая, что time определен NOT NULL. В противном случае вы можете добавить NULLS LAST. Почему?

(activity LIKE 'A%' OR activity LIKE 'B%') более подробный, чем activity ~ '^[AB]', но обычно быстрее в старых версиях Postgres. О сопоставлении с образцом:

Условные оконные функции?

Это действительно возможно. Вы можете комбинировать агрегатное предложение FILTER с предложением OVER оконных функций. Однако:

  1. Само предложение FILTER может работать только со значениями из текущей строки.

  2. Что еще более важно, FILTER не реализован для чистых подлинных функций, таких как lead() или lag() (до Postgres 13) - только для агрегатные функции.

Если вы пытаетесь:

lead(activity) FILTER (WHERE activity LIKE 'A%') OVER () AS activity

Postgres скажет вам:

FILTER is not implemented for non-aggregate window functions

О FILTER:

Представление

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

Для многих пользователей и нескольких строк на пользователя первый запрос выше должен быть самым быстрым. Видеть:

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

person Erwin Brandstetter    schedule 22.01.2017