Requête SQL de dédoublonage

**Sniper69** · 08/01/2024, 17h18

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 :

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
 
 
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.

**escartefigue** · 08/01/2024, 17h37

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 ?

**Sniper69** · 08/01/2024, 17h53

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é

Nom : Sans titre.png
Affichages : 205
Taille : 6,0 Ko

Nom : Sans titre.png
Affichages : 205
Taille : 6,0 Ko

j'espère que c'est clair maintenant

**escartefigue** · 08/01/2024, 17h59

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...

**Sniper69** · 08/01/2024, 18h30

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.

**Waldar** · 08/01/2024, 18h56

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 :

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
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.

**escartefigue** · 09/01/2024, 15h39

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

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).

**vanagreg** · 09/01/2024, 15h50

Bonjour,

Dans Oracle avec le Pattern Matching:

Code :

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

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)
               );

**Waldar** · 09/01/2024, 19h04

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

Code :

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

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.

**SQLpro** · 09/01/2024, 21h57

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 +

**Waldar** · 10/01/2024, 20h17

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.

**LEGALLJJA** · 12/07/2024, 15h10

vous pouvez essayer cela

Code :

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

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')

**SQLpro** · 16/07/2024, 18h09

Celle là est pas mal :

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 
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 +

Requête SQL de dédoublonage

Langage SQL

Discussions similaires

Partager

Partager