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:

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

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!