Comment puis-je trouver l'activité suivante du groupe B après la dernière activité du groupe A dans PostgreSQL ?

Fonction Lead/Lag conditionnelle dans PostgreSQL

Vous disposez d'un tableau contenant des activités pour différents utilisateurs et des horodatages. Vous souhaitez identifier la dernière activité du groupe A pour chaque utilisateur, puis déterminer l'activité ultérieure du groupe B, le cas échéant.

Utilisation de la fonction Lead

Au départ, vous a tenté d'utiliser la fonction lead() pour résoudre ce problème. Cependant, la fonction lead() à elle seule n'est pas suffisante pour cette tâche car elle récupère simplement la valeur suivante dans l'ordre, quelles que soient les conditions.

Fonctions de fenêtre conditionnelles (FILTER)

Pour appliquer efficacement des conditions aux fonctions de fenêtre, PostgreSQL propose la clause FILTER. Malheureusement, cette clause n'est actuellement pas implémentée pour les véritables fonctions de fenêtre comme lead() et lag(), car elle n'est disponible que pour les fonctions d'agrégation.

Solution alternative utilisant DISTINCT ON

Pour relever ce défi sans FILTER, nous pouvons utiliser une combinaison d'instructions DISTINCT ON et CASE ainsi qu'une sous-requête :

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;

Ceci Cette approche exploite la clause DISTINCT ON pour regrouper les lignes par utilisateur et sélectionner la dernière activité éligible du groupe A pour chaque utilisateur. Les instructions CASE sont utilisées pour déterminer l'activité appropriée et l'activité ultérieure en fonction des conditions spécifiées.

Solution optimale pour les grands ensembles de données

Pour les cas impliquant un grand nombre de lignes par utilisateur, des techniques alternatives peuvent être plus efficaces. Ces techniques impliquent généralement la création de tables temporaires ou l'utilisation de stratégies d'indexation avancées. Cependant, l'approche optimale peut varier en fonction des caractéristiques spécifiques de vos données et de votre charge de travail.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!