Comment puis-je trouver l'activité suivante du groupe B après les activités du groupe A dans PostgreSQL à l'aide des fonctions de fenêtre ?

Fonction conditionnelle Lead/Lag dans PostgreSQL

Votre tâche consiste à générer une requête qui récupère des séquences d'activités spécifiques pour les utilisateurs à partir d'une table donnée. Vous souhaitez déterminer la prochaine activité du groupe B (qui se produit toujours après une activité du groupe A) pour chaque utilisateur.

Définition du problème

Considérez le tableau suivant :

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

Le résultat souhaité pour ce tableau est :

Name	activity	next_activity
user1	A2	NULL
user2	A3	B1
user3	A1	B2

Solution

Vous pouvez résoudre ce problème en exploitant les instructions DISTINCT ON et CASE en conjonction avec les fonctions de fenêtre :

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 identifie la dernière activité du groupe A et l'activité suivante du groupe B (le cas échéant) pour chaque utilisateur utilisant la fonction DISTINCT ON et la fonction de fenêtre lead() avec un ORDER BY time DESC.
• Les instructions CASE gèrent le résultat souhaité : la dernière activité du groupe A et l'activité suivante du groupe B (si elle existe).

Fenêtre conditionnelle Fonctions

Bien que PostgreSQL ne prenne pas directement en charge les fonctions de fenêtre conditionnelles (par exemple, lead(activity) FILTER (WHERE activité LIKE 'A%')), vous pouvez utiliser la clause FILTER avec des fonctions d'agrégation et les utiliser comme fonctions de fenêtre :

lead(activity) FILTER (WHERE activity LIKE 'A%') OVER () AS activity

Cependant, cette approche est inefficace et peu pratique pour les grands ensembles de données. Au lieu de cela, la solution présentée ci-dessus est recommandée pour les petits et grands ensembles de données.

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!