Wie implementiert man bedingte Lead/Lag-Funktionen in PostgreSQL?

Bedingte Lead/Lag-Funktion Postgres?

Viele Benutzer sind ratlos darüber, wie sie bedingte Lead/Lag-Funktionen in Postgres mithilfe von Fensterfunktionen implementieren können. In diesem Beispiel haben wir eine Tabelle mit Daten wie diesen:

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

Wir möchten diese Tabelle in einen Bericht umwandeln, der die nächste Aktivität vom Typ B für jeden Benutzer zeigt, bei dem die vorherige Aktivität Typ A war. Konkret möchten wir eine Tabelle wie diese erstellen:

Name	Activity	Next Activity
user1	A2	NULL
user2	A3	B1
user3	A1	B2

Bedingte Fensterfunktionen

Traditionell Benutzer Möglicherweise wird versucht, die Funktion „lead()“ zu verwenden, um dieses Problem zu lösen. Aufgrund der Einschränkungen der Fensterfunktionen von Postgres werden bedingte Funktionen jedoch nicht direkt unterstützt. Insbesondere kann die FILTER-Klausel nicht auf „lead()“ oder „lag()“ angewendet werden.

Lösung des Problems

Um dieses Problem zu lösen, müssen wir den Gedanken der Verwendung aufgeben eine bedingte Lead-Funktion und verwenden Sie stattdessen eine komplexere Abfragestrategie. Ein Ansatz besteht darin, DISTINCT ON- und CASE-Anweisungen zu verwenden, um das gewünschte Ergebnis zu erzielen. Hier ist eine Abfrage, die diese Strategie demonstriert:

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 wählt für jeden Benutzer unterschiedliche Zeilen aus, sortiert nach absteigender Zeit.
• Die Funktion „lead()“ gibt die nächste Aktivität in der Sequenz zurück, die verwendet wird, um für jede die nächste Aktivität vom Typ B zu bestimmen Benutzer.
• Die CASE-Anweisungen werden verwendet, um die Aktivität und die nächste Aktivität basierend auf der Art der Aktivität zu bestimmen.

Leistungsüberlegungen

Bei kleinen Datensätzen sollte die obige Abfrage ausreichend gut funktionieren. Bei großen Datenmengen kann es jedoch erforderlich sein, die Abfrage mithilfe von Techniken wie Indizierung oder Fensterfunktionen zu optimieren.

Das obige ist der detaillierte Inhalt vonWie implementiert man bedingte Lead/Lag-Funktionen in PostgreSQL?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!