Wie kann ich mithilfe von Fensterfunktionen die nächste Aktivität aus Gruppe B finden, die den Aktivitäten der Gruppe A in PostgreSQL folgt?

Bedingte Lead/Lag-Funktion in PostgreSQL

Ihre Aufgabe besteht darin, eine Abfrage zu generieren, die bestimmte Aktivitätssequenzen für Benutzer aus einer bestimmten Tabelle abruft. Sie möchten für jeden Benutzer die nächste Aktivität aus Gruppe B (die immer nach einer Aktivität aus Gruppe A auftritt) ermitteln.

Problemdefinition

Beachten Sie die folgende Tabelle:

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

Die gewünschte Ausgabe für diese Tabelle ist:

Name	activity	next_activity
user1	A2	NULL
user2	A3	B1
user3	A1	B2

Lösung

Sie können dieses Problem lösen, indem Sie die Anweisungen DISTINCT ON und CASE in Verbindung mit Fensterfunktionen nutzen:

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;

Erklärung

• Die Unterabfrage identifiziert die neueste Aktivität aus Gruppe A und die folgende Aktivität aus Gruppe B (falls vorhanden) für jeden Benutzer unter Verwendung der DISTINCT ON- und Fensterfunktion „lead()“ mit einem ORDER BY time DESC.
• Die CASE-Anweisungen verarbeiten die gewünschte Ausgabe: die Neueste Aktivität von Gruppe A und die nächste Aktivität von Gruppe B (falls vorhanden).

Bedingtes Fenster Funktionen

Während PostgreSQL bedingte Fensterfunktionen nicht direkt unterstützt (z. B. Lead(aktivität) FILTER (WHERE Aktivität LIKE 'A%')), können Sie die FILTER-Klausel mit Aggregatfunktionen verwenden und diese verwenden als Fensterfunktionen:

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

Dieser Ansatz ist jedoch für große Datensätze ineffizient und unpraktisch. Stattdessen empfiehlt sich die oben vorgestellte Lösung sowohl für kleine als auch für große Datensätze.

Das obige ist der detaillierte Inhalt vonWie kann ich mithilfe von Fensterfunktionen die nächste Aktivität aus Gruppe B finden, die den Aktivitäten der Gruppe A in PostgreSQL folgt?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!