Supposons que j'ai une table avec le format

user, fav_colour, date

Avec des exemples de données:

a, green, 2015-10-1
a, green, 2016-10-1
a, yellow, 2016-12-1
b, red, 2015-10-1
b, red, 2016-10-1
b, red, 2017-10-1
c, black, 2014-10-1
c, black, 2015-10-1
c, blue, 2016-02-1

Comment exécuter une requête qui renvoie des utilisateurs, où la dernière fav_colour est différente de la précédente fav_colour et le décalage horaire est d'au moins 3 mois

Donc la requête retournerait

c

A n'est pas inclus car le jaune ne suit le vert que deux mois plus tard

1
Abe 3 août 2017 à 08:58

2 réponses

Meilleure réponse

Vous pouvez utiliser la fonction de fenêtre lag pour rechercher la ligne précédente de chaque ligne. Notez, cependant, que vous ne pouvez pas l'utiliser dans une clause where, vous devrez donc utiliser une sous-requête, ou quelque chose d'équivalent:

SELECT username
FROM   (SELECT username,
               fav_colour,
               LAG(fav_colour) OVER (PARTITION BY username ORDER BY date) AS
                  prev_fav_colour,
               date,
               LAG(date) OVER (PARTITION BY username ORDER BY date) AS
                   prev_date
        FROM   colours) c
WHERE  fav_colour != prev_fav_colour AND
       AGE(date, prev_date) >= INTERVAL '3 MONTHS';
1
Mureinik 3 août 2017 à 06:17

Une méthode différente de moi, mais j'espère donne la même réponse.

1) J'ai créé une expression de table commune (CTE) qui ajoute un numéro de ligne pour classer les fav_colour de chaque utilisateur par date ascendante.

2) Je me suis ensuite auto-joint à ce CTE sur user et row_number +1 afin que chaque enregistrement soit mis en correspondance avec l'enregistrement suivant pour cet utilisateur.

3) Ensuite, dans la clause WHERE, j'ai les critères de 3 mois d'intervalle et une couleur différente

J'espère que cela t'aides!

WITH CTE
AS
(
SELECT 
user, 
fav_colour, 
date,
ROW_NUMBER() OVER (PARTITION BY user ORDER BY date ASC) as rn
)

SELECT 
CTE1.user
FROM CTE AS CTE1
INNER JOIN CTE AS CTE2 CTE1.user=CTE2.user ON CTE1.rn=(CTE2.rn+1)
WHERE DATEDIFF(mm,CTE1.date,CTE2.date) >=3
AND CTE1.fav_colour<>CTE2.fav_colour;
1
WJS 3 août 2017 à 07:07