wake-up-neo.com

SQL-Fensterfunktion mit einer where-Klausel?

Ich versuche, zwei Arten von Ereignissen für Benutzer zu korrelieren. Ich möchte alle "B" -Ereignisse zusammen mit dem letzten "A" -Ereignis für diesen Benutzer vor dem "A" -Ereignis sehen. Wie würde man das erreichen? Insbesondere versuche ich dies in Postgres.

Ich hatte gehofft, dass es möglich ist, eine "where" -Klausel in einer Fensterfunktion zu verwenden. In diesem Fall könnte ich im Wesentlichen eine LAG () mit einem "where event = 'A'" erstellen, aber das scheint nicht möglich zu sein.

Irgendwelche Empfehlungen?

Datenbeispiel:

|user |time|event|
|-----|----|-----|
|Alice|1   |A    |
|Bob  |2   |A    |
|Alice|3   |A    |
|Alice|4   |B    |
|Bob  |5   |B    |
|Alice|6   |B    |

Erwünschtes Ergebnis:

|user |event_b_time|last_event_a_time|
|-----|------------|-----------------|
|Alice|4           |3                |
|Bob  |5           |2                |
|Alice|6           |3                |
8
MJ.

Ich habe gerade Gordons Ansatz mit PostgreSQL 9.5.4 ausprobiert, und das hat sich beklagt

FILTER ist nicht für nicht aggregierte Fensterfunktionen implementiert

dies bedeutet, dass die Verwendung von lag() mit FILTER nicht zulässig ist. Also modifizierte ich Gordons Anfrage mit max(), einem anderen Fensterrahmen und CTE:

WITH subq AS (
  SELECT
    "user", event, time as event_b_time,
    max(time) FILTER (WHERE event = 'A') OVER (
      PARTITION BY "user"
      ORDER BY time
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS last_event_a_time
  FROM events
  ORDER BY time
)
SELECT
  "user", event_b_time, last_event_a_time
FROM subq
WHERE event = 'B';

Verifiziert, dass dies mit PostgreSQL 9.5.4 funktioniert.

Danke an Gordon für den FILTER-Trick!

13
Cheng Lian

Hier ist eine Methode:

select t.*
from (select t.*,
             lag(time) filter (where event = 'A') (partition by user order by time)
      from t
     ) t
where event = 'B';

Es ist möglich, dass die korrelierte Unterabfrage/Querverbindung eine bessere Leistung aufweist.

4
Gordon Linoff

Fensterfunktionen sind hier nicht erforderlich. Suchen Sie einfach alle B-Ereignisse und finden Sie für jedes Ereignis die aktuellste A desselben Benutzers über eine Unterabfrage. So etwas sollte es tun:

SELECT
    "user",
    time AS event_b_time,
    (SELECT time AS last_event_a_time
     FROM t t1
     WHERE "user"=t.user AND event='A' AND time<t.time
     ORDER BY time DESC LIMIT 1)
FROM t
WHERE event='B';

Ich gehe davon aus, dass die Tabelle t heißt (ich habe sie zweimal verwendet).

1
redneb