Wie lassen sich die letzten „A'- und nachfolgenden „B'-Aktivitäten pro Benutzer in PostgreSQL effizient extrahieren?

Bedingte Lead/Lag-Funktion in PostgreSQL

In einer PostgreSQL-Tabelle, in der Aktivitäten in die Typen A und B gruppiert sind, sodass B-Aktivitäten immer auf A-Aktivitäten folgen, suchen Benutzer eine Lösung zum Extrahieren der letzten A-Aktivität und der nachfolgenden B-Aktivität für jeden Benutzer. Obwohl die Funktion „lead()“ zunächst wie ein vielversprechender Ansatz schien, erwies sie sich als unwirksam.

Bedingte Fensterfunktionen

Leider unterstützt PostgreSQL derzeit keine bedingten Fensterfunktionen. Die FILTER-Klausel, die eine bedingte Filterung für Fensterfunktionen bereitstellen könnte, ist nur für Aggregatfunktionen verfügbar.

Logische Implikation und Lösung

Die wichtigste Erkenntnis liegt in der logischen Implikation der Problemstellung: für Für jeden Benutzer gibt es höchstens eine B-Aktivität nach einer oder mehreren A-Aktivitäten. Dies legt eine Lösung nahe, die eine einzelne Fensterfunktion mit DISTINCT ON- und CASE-Anweisungen verwendet.

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;

Überlegungen zur Leistung

Für eine kleine Anzahl von Benutzern und Aktivitäten wird die obige Abfrage wahrscheinlich auch ohne ausreichend funktionieren ein Index. Wenn jedoch die Anzahl der Zeilen und Benutzer zunimmt, sind möglicherweise alternative Techniken erforderlich, um die Leistung zu optimieren.

Potenzielle Optimierungen

Für große Datenmengen sollten Sie einen maßgeschneiderteren Ansatz in Betracht ziehen:

• Wenn die Zeit NULL-Werte zulässt, fügen Sie NULLS LAST zur ORDER BY-Klausel hinzu.
• Verwenden Sie den Mustervergleich Ausdruck Aktivität ~ '^[AB]' anstelle von Aktivität LIKE 'A%' ODER Aktivität LIKE 'B%'.
• Erkunden Sie Techniken zum Auswählen der ersten Zeile in jeder Gruppe, wie beispielsweise die in diesem Artikel beschriebene : [Erste Zeile in jedem GROUP BY auswählen Gruppe?](https://stackoverflow.com/questions/18923181/select-first-row-in-each-group-by-group)
• Untersuchen Sie fortgeschrittenere Techniken zur Optimierung von GROUP BY-Abfragen, insbesondere wenn Umgang mit einer hohen Anzahl von Zeilen pro Benutzer: [GROUP BY-Abfrage optimieren, um die neueste Zeile pro abzurufen Benutzer](https://dba.stackexchange.com/questions/55252/optimize-group-by-query-to-retrieve-latest-row-per-user)

Das obige ist der detaillierte Inhalt vonWie lassen sich die letzten „A'- und nachfolgenden „B'-Aktivitäten pro Benutzer in PostgreSQL effizient extrahieren?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!