Comment implémenter des fonctions conditionnelles Lead/Lag dans PostgreSQL ?

Fonction avance/retard conditionnelle Postgres ?

De nombreux utilisateurs sont perplexes quant à la manière d'implémenter des fonctions avance/retard conditionnelles dans Postgres à l'aide de fonctions de fenêtrage. Dans cet exemple, nous avons un tableau avec des données comme ceci :

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

Nous souhaitons transformer ce tableau en un rapport qui montre la prochaine activité de type B pour chaque utilisateur où l'activité précédente était de type A. Plus précisément, nous visons à créer un tableau comme celui-ci :

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

Fonctions de fenêtre conditionnelles

Traditionnellement, les utilisateurs peuvent essayez d'utiliser la fonction lead() pour résoudre ce problème. Cependant, en raison des limitations des fonctions de fenêtre de Postgres, les fonctions conditionnelles ne sont pas directement prises en charge. Plus précisément, la clause FILTER ne peut pas être appliquée à lead() ou lag().

Résoudre le problème

Pour résoudre ce problème, nous devons abandonner la notion d'utilisation une fonction de prospect conditionnelle et utilisez à la place une stratégie de requête plus complexe. Une approche consiste à utiliser les instructions DISTINCT ON et CASE pour obtenir le résultat souhaité. Voici une requête qui illustre cette stratégie :

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;

Explication

• La sous-requête sélectionne des lignes distinctes pour chaque utilisateur, classées par ordre chronologique décroissant.
• La fonction lead() renvoie l'activité suivante dans la séquence, qui est utilisée pour déterminer la prochaine activité de type B pour chaque utilisateur.
• Les instructions CASE sont utilisées pour déterminer l'activité et la prochaine activité en fonction du type d'activité.

Considérations relatives aux performances

Pour les petits ensembles de données, la requête ci-dessus devrait fonctionner suffisamment bien. Cependant, pour les grands ensembles de données, l'optimisation de la requête à l'aide de techniques telles que l'indexation ou les fonctions de fenêtre peut être nécessaire.

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!