Requête SQL de dédoublonage

Version imprimable

Salut à tous,

J'aurais besoin de votre assistance, s'il vous plaît. Je suis en train de travailler sur une requête de sélection à partir d'une table de pointage des collaborateurs (je travail dans un environnement MS ACCESS 2016), mais le problème est que cette table contient des doublons avec de légères variations dans les horaires.

Un exemple pourrait rendre les choses plus claires.

Table sources contient les données suivants :

EMPLOYEE_ID LoginTime LogoutTime
8858043 2024-01-06 07:58:48.000 2024-01-06 14:03:05.000
8858043 2024-01-06 07:59:47.000 2024-01-06 14:05:05.000
8858043 2024-01-06 15:00:06.000 2024-01-06 18:05:40.000
8858043 2024-01-06 15:00:29.000 2024-01-06 18:01:40.000

Résultats souhaités :

EMPLOYEE_ID LoginTime LogoutTime
8858043 2024-01-06 07:58:48.000 2024-01-06 14:05:05.000
8858043 2024-01-06 15:00:06.000 2024-01-06 18:05:40.000

à l'aide de ChatGPT j'ai obtenu cette requête qui fonctionne presque sauf que ça me donne pour chaque groupe de login/logout la 2ème connexions ey n'ont pas la première.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 SELECT CD1.EMPLOYEE_ID, MIN(CD1.LoginTime) AS FirstLogin, MAX(CD2.LogoutTime) AS LastLogout FROM Telephonie AS CD1 LEFT JOIN Telephonie AS CD2 ON (CD1.LoginTime < CD2.LogoutTime) AND (CD1.EMPLOYEE_ID = CD2.EMPLOYEE_ID) WHERE NOT EXISTS ( SELECT 1 FROM Telephonie AS CD3 WHERE CD3.EMPLOYEE_ID = CD1.EMPLOYEE_ID AND CD3.LoginTime > CD1.LoginTime AND CD3.LoginTime < CD2.LogoutTime ) GROUP BY CD1.EMPLOYEE_ID,CD1.LoginTime
Pouvez-vous m'aider pls ? :)

merci d'avance.

08/01/2024, 16h37
escartefigue

La difficulté ici est de définir pourquoi vous sélectionnez la borne de début de la première ligne avec la borne de fin de la deuxième, plutôt que la borne de fin de la troisième ou de la quatrième...
On comprend intuitivement que quand l'écart est de l'ordre de quelques secondes, on prend la ligne la plus forte, mais en SQL, il faut le traduire par une règle. Quelle est-elle ?
08/01/2024, 16h53
Sniper69

1 pièce(s) jointe(s)

Citation:

Envoyé par escartefigue

La difficulté ici est de définir pourquoi vous sélectionnez la borne de début de la première ligne avec la borne de fin de la deuxième, plutôt que la borne de fin de la troisième ou de la quatrième...
On comprend intuitivement que quand l'écart est de l'ordre de quelques secondes, on prend la ligne la plus forte, mais en SQL, il faut le traduire par une règle. Quelle est-elle ?

L'objectif est de créer une timeline de connexion/déconnexion, d'une façon plus concrète conserver que la première login et la dernière logout de chaque intervalle de connexion/déconnexion

un autre exemple pour simplifier le besoin :

1er tableau = data source
2ème tableau = le résultat souhaité

Pièce jointe 648925

j'espère que c'est clair maintenant :)
08/01/2024, 16h59
escartefigue

C'est clair depuis le départ, mais il faut une règle traduisible en SQL
Là, dans l'exemple, les lignes en jaune sont en doublon ok, mais la couleur jaune ce n'est pas une règle traduisible en SQL
Par exemple, la règle peut être : il considérer qu'il y a doublon quand l'écart est de moins d'une heure, moins de 10 minutes...
08/01/2024, 17h30
Sniper69

Je m'excuse, mais je n'ai pas saisi pleinement ta question. Pour moi, la règle consiste à conserver le (min LoginTime) et le (max LogoutTime) de chaque intervalle chevauché.

Le script que j'ai initialement intégré fonctionne partiellement. Il conserve les intervalles souhaités, mais inclut la dernière connexion plutôt que la première.

Désolé, je suis encore novice dans le langage SQL.

Si votre database n'a pas de fonctions natives pour gérer ceci, la requête SQL peut être complexe et pas très performantes à l'exécution.
Essayez ainsi :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
with cte_data (Employee_Id, LoginTime, LogoutTime) as
(
select 8858043, timestamp '2024-01-06 07:58:48', timestamp '2024-01-06 14:03:05' union all
select 8858043, timestamp '2024-01-06 07:59:47', timestamp '2024-01-06 14:05:05' union all
select 8858043, timestamp '2024-01-06 15:00:06', timestamp '2024-01-06 18:05:40' union all
select 8858043, timestamp '2024-01-06 15:00:29', timestamp '2024-01-06 18:01:40'
)
  ,  cte_LogoutMax (Employee_Id, LoginTime, LogoutTime, LogoutTime_max) as
(
select Employee_Id, LoginTime, LogoutTime
     , max(LogoutTime) over(partition by Employee_Id order by LoginTime, LogoutTime rows unbounded preceding) as LogoutTime_max
  from cte_data
)
  ,  cte_GrpStart (Employee_Id, LoginTime, LogoutTime, GrpStart) as
(
select Employee_Id, LoginTime, LogoutTime
     , case when LoginTime <= lag(LogoutTime_max) over(partition by Employee_Id order by LoginTime, LogoutTime) then 0 else 1 end
  from cte_LogoutMax
)
  ,  cte_GrpId (Employee_Id, LoginTime, LogoutTime, GrpId) as
(
select Employee_Id, LoginTime, LogoutTime
     , sum(GrpStart) over(partition by Employee_Id order by LoginTime rows unbounded preceding)
  from cte_GrpStart
)
  select Employee_Id
       , min(LoginTime)  as LoginTime
       , max(LogoutTime) as LogoutTime
    from cte_GrpId
group by Employee_Id, GrpId
order by Employee_Id, GrpId;
 
Employee_Id  LoginTime            LogoutTime         
-----------  -------------------  -------------------
    8858043  2024-01-06 07:58:48  2024-01-06 14:05:05
    8858043  2024-01-06 15:00:06  2024-01-06 18:05:40

Edit: j'ai corrigé une typo dans le code.

09/01/2024, 14h39
escartefigue

C'est ce que j'ai mis en rouge ci-dessous qui manquait comme information

Citation:

Envoyé par Sniper69

Je m'excuse, mais je n'ai pas saisi pleinement ta question. Pour moi, la règle consiste à conserver le (min LoginTime) et le (max LogoutTime) de chaque intervalle chevauché.

La méthode classique pour satisfaire ce genre de besoins est d'utiliser la méthode dite "Tabibitosan", c'est l'objet de la réponse de Waldar qui précède, il faut pour cela que votre SGBD accepte les fonctions fenêtrés (c'est à dire que ce ne soit pas Access, ou MySQL dans une version antérieure à la V8).

Bonjour,

Dans Oracle avec le Pattern Matching:

Code:

1
2
3
4
5
6
7
8
select * from Telephonie 
match_recognize(partition by employee_id
                order by logintime
                measures min(logintime) as logintime,
                         greatest(max(a.logouttime), next(a.logouttime)) as logouttime
                pattern (a+)
                define A as a.logouttime > next(a.logintime)
               );

Je tente cette version du pattern matching en pure fonctions de fenêtrage :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 with cte_prep as ( select Employee_Id, LoginTime, LogoutTime , lead(LoginTime) over W as LoginTime_next , greatest(max(LogoutTime) over W, lead(LogoutTime) over W) as LogoutTime_max from Telephonie window W as (partition by Employee_Id order by LoginTime) ) select distinct Employee_Id, LoginTime, LogoutTime_max from cte_prep where LogoutTime > LoginTime_next order by Employee_Id, LoginTime;
EDIT : Attention, sur d'autres données cette requête ne donne PAS les bons résultats.
Je la laisse pour l'intégrité de la discussion, mais ne surtout pas utiliser telle quelle.

09/01/2024, 20h57
SQLpro

L'article que j'ai écrit à ce sujet "agrégation des intervalles en SQL" :
https://blog.developpez.com/sqlpro/p...alles_en_sql_1

A +
10/01/2024, 19h17
Waldar

J'avais oublié cet article mais il est un peu daté, beaucoup de jointures, ce qui est ok quand il y a peu de lignes mais qui progresse moins bien avec de la croissance.

Sur mon petit cluster Vertica - 3 nœuds virtuels, 2 vCPU, 16 Go de ram, rien de folichon.

La première solution que j'ai proposée pour 1M de lignes tourne en 200 ms, versus 167 secondes pour la solution 2.
Avec 10M de lignes ça passe à 1600 ms ce qui me paraît bien.

La deuxième solution que j'ai proposée ne donne pas les bons résultats, j'ai édité le post pour l'indiquer.
J'ai également des résultats incohérents avec la solution 4.

jean-jacques

vous pouvez essayer cela

Code:

1
2
3
4
5
6
7
SELECT 
    CD1.EMPLOYEE_ID, to_date(CD1.LoginTime,'DD/MM/YYYY') datejour,
    MIN(CD1.LoginTime) AS FirstLogin, 
    MAX(CD2.LogoutTime) AS LastLogout
FROM 
    Telephonie AS CD1 
group by CD1.EMPLOYEE_ID, to_date(CD1.LoginTime,'DD/MM/YYYY')

Celle là est pas mal :

Code:

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 
C1 AS (SELECT ITV_ITEM, ITV_DEBUT AS ts, +1 AS genre, NULL AS e,
              ROW_NUMBER() OVER(PARTITION BY ITV_ITEM ORDER BY ITV_DEBUT) AS s
       FROM   T_INTERVAL_ITV
       UNION  ALL
       SELECT ITV_ITEM, ITV_FIN AS ts, -1 AS genre, 
              ROW_NUMBER() OVER(PARTITION BY ITV_ITEM ORDER BY ITV_FIN) AS e,
              NULL AS s
       FROM T_INTERVAL_ITV),
C2 AS (SELECT C1.*, ROW_NUMBER() OVER(PARTITION BY ITV_ITEM ORDER BY ts, genre DESC) AS se
       FROM   C1),
C3 AS (SELECT ITV_ITEM, ts, 
              FLOOR((ROW_NUMBER() OVER(PARTITION BY ITV_ITEM ORDER BY ts) - 1) / 2 + 1) AS grpnum
       FROM   C2
       WHERE  COALESCE(s - (se - s) - 1, (se - e) - e) = 0),
C4 AS (SELECT ITV_ITEM, MIN(ts) AS ITV_DEBUT, max(ts) AS ITV_FIN
       FROM C3
       GROUP BY ITV_ITEM, grpnum)
SELECT A.ITV_ITEM, A.ITV_DEBUT, A.ITV_FIN
FROM   (SELECT DISTINCT ITV_ITEM 
        FROM T_INTERVAL_ITV) AS U
       JOIN C4 AS A 
	        ON A.ITV_ITEM = U.ITV_ITEM
ORDER BY ITV_ITEM, ITV_DEBUT, ITV_FIN;

A +