Discussion :

[fieldset]

Bonjour,

J'ai une requête (postgresql redshift) complexe a écrire et je ne sais absolument pas comment m'en sortir, peut-être que certains d'entre vous sauront m'éclairer.

J'ai une table contenant les enregistrements suivant : (j'ai volontairement simplifié l'exemple)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
MaDate               |    MaPage
 
10h10m 5s     |    page 1
10h15m 5s     |    page 2
10h20m 5s     |    page 3
15h10m 5s     |    page 4

J'aimerais calculer la durée de navigation d'un utilisateur si l'écart entre deux visites n'excède pas 30 minutes (valeur pouvant changer)

J'ai donc pensé à récupérer les lignes dont l'écart entre deux date n'excède pas 30 minutes afin d'obtenir les lignes suivantes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
MaDate               |    MaPage
 
10h10m 5s     |    page 1
10h15m 5s     |    page 2
10h20m 5s     |    page 3

Il me suffira donc de récupérer le MIN(date) et MAX(date) et de faire la différence afin d'obtenir la durée de navigation, mais je ne sais absolument pas comment effectuer la première étape de mon raisonnement.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT DATEDIFF(minutes, MIN(MaDate), MAX(MaDate) FROM MaTable

D'avance, je vous remercie !!!

[punkoff]

bonjour,

Tournez vous du côté des fonctions de fenêtrage et en particulier lead / lag.
http://www.postgresql.org/docs/9.2/s...ns-window.html

[fieldset]

Merci pour votre aide ! Je dois avouer que c'est relativement complexe comme fonctions :s

[aieeeuuuuu]

Bonjour,

comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
WITH CTE(Duree) AS (
  SELECT   
    EXTRACT(EPOCH  FROM  
            LEAD(MaDate) OVER(ORDER BY MaDate) 
            - MaDate
    ) AS Duree
  FROM MaTable
 )
SELECT 
  SUM(CASE WHEN Duree <= 30 * 60 THEN Duree END) As DureeTotaleEnSecondes
FROM CTE

[fieldset]

Merci beaucoup pour votre réponse !

Je viens de me rendre compte que mon exemple était faussé car le jeu de données permettrait de ressortir plusieurs sessions avec des temps différents.

Je vais essayer de reprendre mon exemple précédent en détaillant davantage.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
MaDate      |    MaPage
 
10h10m     |    page 1 \
10h15m     |    page 2  | Session 1 de 10h20 - 10h10 = 10 minutes
10h20m     |    page 3 /
15h05m     |    page 4 \
15h15m     |    page 5  | Session 2 de 15h20 - 15h05 = 15 minutes
15h20m     |    page 6 /
16h05m     |    page 7 \
16h10m     |    page 8  | Session 3 de 16h15 - 16h05 = 10 minutes
16h15m     |    page 9 /

Il peut donc y avoir plusieurs sessions de différentes durées. Pensez-vous que cela est possible avec la requête précédente ?

Merci beaucoup !!

[aieeeuuuuu]

En effet

Plutot comme ceci alors :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
WITH CTE(MaDate,Duree) AS (
  SELECT  
    MaDate
    ,EXTRACT(EPOCH  FROM  
            MaDate 
             - COALESCE(LAG(MaDate) OVER(ORDER BY MaDate) , MaDate)
 
    ) AS Duree
  FROM MaTable
 )
,Session AS (
  SELECT 
    MaDate
    ,Duree
    ,SUM(CASE WHEN Duree> 30 * 60 THEN 1 ELSE 0 END) OVER(ORDER BY MaDate) AS NoSession
  FROM CTE
  )
SELECT 
  MIN(MaDate) AS Debut
  ,MAX(MaDate) AS Fin
  ,SUM(CASE WHEN Duree < 30 * 60 THEN Duree ELSE 0 END) AS Duree
FROM Session
GROUP BY NoSession

[fieldset]

Merci beaucoup pour votre réponse ça fonctionne !!!