Discussion :

[lola06]

Bonjour,
je suis sous SQL Server 2005 et je souhaiterai afficher le nombre d'enregistrements dont le champs date est séparé de 30 minutes par Adresse IP.

Voici la requête que j'ai commencé mais elle n'est pas correcte, car elle renvoie la même valeur pour toutes les adresses IP. Et je suppose qu'il serait possible de la simplifier.

Je suis désolée car elle est brouillon, mais c'est la seule manière que j'ai trouvé de me rapprocher de ce que je voulais faire.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT COUNT(DateTime1Min) AS CountOfSession, AdressIP
FROM (
    SELECT DISTINCT MIN(DateTime1) AS DateTime1Min, DateTime2Min, AdressIP
    FROM (
        SELECT DISTINCT DateTime1, MIN(DateTime2) AS DateTime2Min, AdressIP
        FROM (
            SELECT DISTINCT PN1.DateTime AS DateTime1, PN2.DateTime AS DateTime2, dbo.Session.AdressIP
            FROM dbo.PageNavigation AS PN1 INNER JOIN dbo.Session 
                ON PN1.FK_IdSession = dbo.Session.Id_Session CROSS JOIN dbo.Index_PageNavigation AS PN2
            WHERE (DATEDIFF(minute, PN1.DateTime, PN2.DateTime) > 30)
            GROUP BY PN1.DateTime, PN2.DateTime, dbo.Session.AdressIP) AS A
        GROUP BY DateTime1, AdressIP) AS B
    GROUP BY DateTime2Min, AdressIP) AS C
GROUP BY AdressIP

Voici un exemple de table, avec FK_IdSession = IdSession :

Session
--------------------
Date -- FK_IdSession
01/01/01 10:01:01 -- 1
01/01/01 10:01:02 -- 2
01/01/01 10:45:00 -- 3
01/01/01 10:45:54 -- 4

PageNavigation
-----------------------
IdSession -- AdressIP
1 -- 111.111.111.111
2 -- 111.111.111.111
3 -- 111.111.111.111
4 -- 112.112.112.112

En fait avec cet exemple je voudrais le résultat :

Resultat
------------------------
CountOfSession -- AdressIP
2 -- 111.111.111.111
1 -- 112.112.112.112

Si vous avez d'autres questions n'hésitez pas !!
Merci d'avance pour ceux qui voudront bien m'aider.
Lola

[Snipah]

Bonjour,

Il faut pouvoir comparer la valeur de la colonne de date avec la valeur de la ligne suivante, groupé par addresse IP ordonné par date.
Lead et Lag n'existant pas encore sur SQL Server, on va commencer par donner un numéro à chaque ligne, correspondant à sa position.
Avec une auto-jointure, sur position = position -1, on va obtenir sur une même ligne pour une addresse IP, la date et la date suivante.
Restera à compter le nombre de lignes où date < date suivante - (1/48).

J'ai rien pour tester sous la main, donc je garantis pas que ça fonctionne, mais ça ressemblerait à ça :

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
 
WITH tabSessionTemp
AS
(
	SELECT  
		   pn.AdressIP,
		   s.date, 
		   row_number() over (PARTITION BY pn.AdressIP ORDER BY s.date) AS position
	FROM Session s
	JOIN PageNavigation pn
		 ON s.fk_idsession = pn.idsession
)
SELECT 
	   session.AdressIP,
	   SUM(CASE WHEN session.date < session_suivante.date - (1/48) THEN 1 ELSE 0 END) AS CountOfSession
FROM tabSessionTemp session
LEFT JOIN tabSessionTemp session_suivante
	 ON session.AdressIP = session_suivante.AdressIP
	 AND session.position = session_suivante.position-1
GROUP BY session.AdressIP

Soit dit en passant, des noms de tables ou de colonnes comme SESSION ou DATE sont à proscrire, ce sont des mots réservés en SQL.

[lola06]

Merci beaucoup, je vais tester ça et voir si c'est bon.
Quand aux noms des tables je les ai changé avant de les mettre car sinon ce sont des noms à rallonge, j'ai voulu éviter de surcharger une requête déjà lourde...

[Snipah]

J'ai oublié le GROUP BY
Je vais éditer ça.

[lola06]

Bonjour,

Désolée pour le temps de réponse mais une autre affaire a accaparé mon attention. Merci pour la modification mais j'avais eu le temps de remarquer l'oubli. Voici la requête définitive, elle diffère un peu car elle ne marchait pas telle quelle :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
WITH tabSessionTemp AS (SELECT     s.AdressIP, pn.Date, row_number() OVER (PARTITION BY s.AdressIP
                                                           ORDER BY pn.Date) AS position
FROM         [DB_SSIS].[dbo].[Session] s JOIN
                      [DB_SSIS].[dbo].[PageNavigation] pn ON pn.FK_IdSession = s.Id_Session)
    SELECT     session.AdressIP, SUM(CASE WHEN DateDiff(minute, session.Date, session_suivante.Date) >= 30 THEN 1 ELSE 0 END) 
                            + 1 AS CountOfSession
     FROM         tabSessionTemp session LEFT JOIN
                            tabSessionTemp session_suivante ON session.AdressIP = session_suivante.AdressIP AND session.position = session_suivante.position - 1
     GROUP BY session.AdressIP

Merci beaucoup à toi Snipah, tu m'as beaucoup aidée.