Comment extraire efficacement les dernières activités « A » et « B » suivantes par utilisateur dans PostgreSQL ?

Fonction conditionnelle Lead/Lag dans PostgreSQL

Dans une table PostgreSQL où les activités sont regroupées en types A et B, de telle sorte que les activités B suivent toujours les activités A, les utilisateurs recherchent une solution pour extraire la dernière activité A et l'activité B suivante pour chaque utilisateur. Alors que la fonction lead() semblait au départ comme une approche prometteuse, elle s'est avérée inefficace.

Fonctions de fenêtre conditionnelles

Malheureusement, PostgreSQL ne prend actuellement pas en charge les fonctions de fenêtre conditionnelles. La clause FILTER, qui pourrait fournir un filtrage conditionnel pour les fonctions de fenêtre, n'est disponible que pour les fonctions d'agrégation.

Implication logique et solution

L'idée clé réside dans l'implication logique de l'énoncé du problème : pour chaque utilisateur, il y a au plus une activité B après une ou plusieurs activités A. Cela suggère une solution utilisant une fonction de fenêtre unique avec les instructions DISTINCT ON et CASE.

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;

Considérations sur les performances

Pour un petit nombre d'utilisateurs et d'activités, la requête ci-dessus fonctionnera probablement correctement sans un indice. Toutefois, à mesure que le nombre de lignes et d'utilisateurs augmente, des techniques alternatives peuvent s'avérer nécessaires pour optimiser les performances.

Optimisations potentielles

Pour les données volumineuses, envisagez d'utiliser une approche plus personnalisée :

• Si le temps autorise les valeurs NULL, ajoutez NULLS LAST à la clause ORDER BY.
• Utilisez l'activité d'expression de correspondance de modèles ~ '^[AB]' au lieu d'une activité LIKE 'A%' OR activité LIKE 'B%'.
• Explorez les techniques de sélection de la première ligne de chaque groupe, telles que celle décrite dans cet article : [ Sélectionnez la première ligne de chaque GROUP BY group?](https://stackoverflow.com/questions/18923181/select-first-row-in-each-group-by-group)
• Étudiez des techniques plus avancées pour optimiser les requêtes GROUP BY, en particulier lorsque traiter un nombre élevé de lignes par utilisateur : [Optimiser la requête GROUP BY pour récupérer la dernière ligne par utilisateur utilisateur](https://dba.stackexchange.com/questions/55252/optimize-group-by-query-to-retrieve-latest-row-per-user)

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!