Discussion :

[Malette]

Bonsoir,
je viens vous demander votre aide car j'ai un petit problème de performance sur une de mes requêtes, j'ai la table suivante qui représente une badgeuse:

Date_heure, Ville, id_personne, sens

Code :

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

1
2
3
4
5
6
7
8
9
10
11
19/03/2010 08:00:53, 'Bordeaux', 1, 'IN'
19/03/2010 10:00:53, 'Bordeaux', 1, 'OUT'
 
19/03/2010 11:00:53, 'Talence', 1, 'IN'
19/03/2010 20:00:53, 'Talence', 1, 'OUT'
 
20/03/2010 08:00:53, 'Bordeaux', 1, 'IN'
20/03/2010 10:00:53, 'Bordeaux', 1, 'OUT'
 
20/03/2010 11:00:53, 'Bordeaux', 1, 'IN'
20/03/2010 20:00:53, 'Bordeaux', 1, 'OUT'

Pour identifier les premiers et derniers évènements (sans se soucier de la ville sur laquelle la personne pointe), j'ai la requête suivante:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
select to_char(date_heure,'mm-yyyy') as months,to_char(date_heure,'dd/mm/yyyy') as horo,id_personne,
min(CASE WHEN sens= 'IN' THEN to_char
(date_heure,'dd/mm/yyyy hh24:mi:ss') ELSE NULL END) as day_first_entry,
max(CASE WHEN sens= 'IN' THEN to_char
(date_heure,'dd/mm/yyyy hh24:mi:ss') ELSE NULL END) as day_last_entry,
min(CASE WHEN sens= 'OUT' THEN to_char
(date_heure,'dd/mm/yyyy hh24:mi:ss') ELSE NULL END) as day_first_exit,
max(CASE WHEN sens= 'OUT' THEN to_char
(date_heure,'dd/mm/yyyy hh24:mi:ss') ELSE NULL END) as day_last_exit
from test
group by to_char(date_heure,'mm-yyyy'), to_char(date_heure,'dd/mm/yyyy'), id_personne;

qui me retourne le résultat:

Code :

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

1
2
month, date_heure, id_personne, day_first_entry,day_last_entry, day_first_exit, day_last_exit
03-2010, 19/03/2010 , 1, 19/03/2010 08:00:53, 19/03/2010 11:00:53, 19/03/2010 10:00:53, 19/03/2010 20:00:53

Ce résultat je l'utilise ensuite dans une sur-requête qui compte le nombre d'heures de présences pour chaque personne par jour.

De l'autre côté, pour des besoins statistiques, je dois rattacher chaque personne à une ville de référence en se basant sur le nombre max d'occurrences d'évenements par personne, avec la requête suivante:

Code :

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

1
2
3
4
5
6
7
          select pers,ville
          from
          (
            select id_personne AS pers,ville,count(id_personne), rank() OVER (partition by id_personne order by count(id_personne) DESC ) as rank
            from test
          ) 
          where rank=1;

qui me renvoie le résultat suivant:

Code :

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

1
2
PERS                   VILLE                          
1                      Bordeaux

Actuellement je fais donc un inner join de cette requête avec l'autre pour obtenir la ville de référence pour la personne.

Mon problème, par rapport à cette approche est qu'il faut faire 2 passage sur la table qui sont très consommateur en terme de temps sur cette même table pour obtenir 2 types d'informations différentes.

En fait, je souhaiterais diminuer au maximum le nombre de passage sur la table mais j'ai comme toujours un problème conceptuel car dans la première requête je ne me soucis pas de la ville, et dans la deuxième requête je compte les nombres d'occurrences par ville, donc 2 approches différentes.

Le résultat attendu serait

Code :

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

1
2
19/03/2010 , 1, 19/03/2010 08:00:53, 19/03/2010 11:00:53, 19/03/2010 10:00:53, 19/03/2010 20:00:53, Bordeaux
20/03/2010 , 1, 20/03/2010 08:00:53, 20/03/2010 11:00:53, 20/03/2010 10:00:53, 20/03/2010 20:00:53, Bordeaux

Pourquoi Bordeaux, car il y a 4 évènements pour cette personne et pour la ville de Bordeaux par rapport à Talence qui n'en n'a que 2.

Auriez-vous éventuellement des suggestions pour simplifier les choses ?

Merci.

[Waldar]

En fait vous voulez, par jour et par utilisateur connaître le premier couple (dt_entrée, dt_sortie), puis le dernier et la ville où il a passé le plus de temps toutes journées confondues ?

Que se passe-t'il en cas d'égalité ?

[Waldar]

En suspension au cas où le nombre de ville serait à égalité, voici une solution avec un seul table scan.

J'ai renommé / comprimé quelques données pour réduire le visuel du jeu de test :

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
With TT AS
(
select to_date('19/03/2010 08', 'dd/mm/yyyy hh24') dh, 'Bordeaux' vl, 1 pr, 'IN' sn from dual union all
select to_date('19/03/2010 10', 'dd/mm/yyyy hh24')   , 'Bordeaux'   , 1   , 'OUT'   from dual union all
select to_date('19/03/2010 11', 'dd/mm/yyyy hh24')   , 'Talence'    , 1   , 'IN'    from dual union all
select to_date('19/03/2010 20', 'dd/mm/yyyy hh24')   , 'Talence'    , 1   , 'OUT'   from dual union all
select to_date('20/03/2010 08', 'dd/mm/yyyy hh24')   , 'Bordeaux'   , 1   , 'IN'    from dual union all
select to_date('20/03/2010 10', 'dd/mm/yyyy hh24')   , 'Bordeaux'   , 1   , 'OUT'   from dual union all
select to_date('20/03/2010 11', 'dd/mm/yyyy hh24')   , 'Bordeaux'   , 1   , 'IN'    from dual union all
select to_date('20/03/2010 20', 'dd/mm/yyyy hh24')   , 'Bordeaux'   , 1   , 'OUT'   from dual
)
  ,  SR1 AS
(
  SELECT dh, pr, sn, vl,
         count(*) over(partition by pr, vl) as cnt_vl
    FROM TT
)
  SELECT to_char(dh,'mm-yyyy')    AS months,
         to_char(dh,'dd/mm/yyyy') AS horo,
         pr,
         min(CASE sn WHEN 'IN'  THEN dh END) AS day_first_entry,
         max(CASE sn WHEN 'IN'  THEN dh END) AS day_last_entry,
         min(CASE sn WHEN 'OUT' THEN dh END) AS day_first_exit,
         max(CASE sn WHEN 'OUT' THEN dh END) AS day_last_exit,
         max(vl) keep (dense_rank first order by cnt_vl desc) as vl
    from sr1
GROUP BY to_char(dh,'mm-yyyy'),
         to_char(dh,'dd/mm/yyyy'),
         pr
ORDER BY to_char(dh,'dd/mm/yyyy') asc,
         pr asc;
 
MONTHS	HORO		PR	DAY_FIRST_ENTRY		DAY_LAST_ENTRY		DAY_FIRST_EXIT		DAY_LAST_EXIT		VL
03-2010	19/03/2010	1	19/03/2010 08:00:00	19/03/2010 11:00:00	19/03/2010 10:00:00	19/03/2010 20:00:00	Bordeaux
03-2010	20/03/2010	1	20/03/2010 08:00:00	20/03/2010 11:00:00	20/03/2010 10:00:00	20/03/2010 20:00:00	Bordeaux

[Malette]

Merci pour ta réponse, en fait si il y'a un doublon, cela importe peu, on prend la première ville.

Par contre je suis en train de faire un autre jeu de test, et avec ta réponse j'obtiens la meilleure ville par jour ce qui n'est pas en conformité avec le jeu de test que tu as effectué, je suis donc en train de creuser la question pour savoir d'ou vient le problème.

Merci, je te tiens au courant.

Edit: voila le jeu de test pour reproduire:

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
WITH TT AS
(
SELECT to_date('17/08/2009 09:56:26', 'dd/mm/yyyy hh24:mi:ss') dh, 'Talence' vl, 1 pr, 'IN' sn FROM dual union ALL
SELECT to_date('17/08/2009 09:57:17', 'dd/mm/yyyy hh24:mi:ss')   , 'Talence'  , 1   , 'OUT'   FROM dual union ALL
SELECT to_date('17/08/2009 10:22:54', 'dd/mm/yyyy hh24:mi:ss')   , 'Talence'    , 1   , 'IN'    FROM dual union ALL
SELECT to_date('17/08/2009 10:32:27', 'dd/mm/yyyy hh24:mi:ss')   , 'Talence'    , 1   , 'IN'    FROM dual union ALL
SELECT to_date('17/08/2009 12:05:47', 'dd/mm/yyyy hh24:mi:ss')   , 'Talence'    , 1   , 'OUT'   FROM dual union ALL
SELECT to_date('18/08/2009 06:57:08', 'dd/mm/yyyy hh24:mi:ss')   , 'Bordeaux'   , 1   , 'IN'    FROM dual union ALL
SELECT to_date('18/08/2009 14:59:28', 'dd/mm/yyyy hh24:mi:ss')   , 'Bordeaux'   , 1   , 'OUT'   FROM dual 
)
  ,  SR1 AS
(
  SELECT dh, pr, sn, vl,
         count(*) over(partition BY pr,vl) AS cnt_vl
    FROM TT
)
  SELECT to_char(dh,'mm-yyyy')    AS months,
         to_char(dh,'dd/mm/yyyy') AS horo,
         pr,
         min(CASE sn WHEN 'IN'  THEN dh END) AS day_first_entry,
         max(CASE sn WHEN 'IN'  THEN dh END) AS day_last_entry,
         min(CASE sn WHEN 'OUT' THEN dh END) AS day_first_exit,
         max(CASE sn WHEN 'OUT' THEN dh END) AS day_last_exit,
         max(vl) keep (dense_rank first ORDER BY cnt_vl DESC) AS vl
    FROM sr1
GROUP BY to_char(dh,'mm-yyyy'),
         to_char(dh,'dd/mm/yyyy'),
         pr
ORDER BY to_char(dh,'dd/mm/yyyy') ASC,
         pr ASC;

Cela me renvoi une ville différente pour les deux dates, ce qui me paraît assez bizarre, peut-être avez-vous une idée ?

Merci.

[Malette]

En fait, après réflexion cela paraît logique, puisque avec cette requête, il affiche pour chaque jour la meilleure ville basé sur la présence par jour.

Dans ce cas particulier là, comme la personne n'est pas sur Bordeaux le 18, cette ville ne peut pas être sélectionnée comme la meilleure.

Par contre, j'ai essayé de bidouiller le code, j'obtiendrais le bon résultat avec cette requête:

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
WITH TT AS
(
SELECT to_date('17/08/2009 09:56:26', 'dd/mm/yyyy hh24:mi:ss') dh, 'Talence' vl, 1 pr, 'IN' sn FROM dual union ALL
SELECT to_date('17/08/2009 09:57:17', 'dd/mm/yyyy hh24:mi:ss')   , 'Talence'  , 1   , 'OUT'   FROM dual union ALL
SELECT to_date('17/08/2009 10:22:54', 'dd/mm/yyyy hh24:mi:ss')   , 'Talence'    , 1   , 'IN'    FROM dual union ALL
SELECT to_date('17/08/2009 10:32:27', 'dd/mm/yyyy hh24:mi:ss')   , 'Talence'    , 1   , 'IN'    FROM dual union ALL
SELECT to_date('17/08/2009 12:05:47', 'dd/mm/yyyy hh24:mi:ss')   , 'Talence'    , 1   , 'OUT'   FROM dual union ALL
SELECT to_date('18/08/2009 06:57:08', 'dd/mm/yyyy hh24:mi:ss')   , 'Bordeaux'   , 1   , 'IN'    FROM dual union ALL
SELECT to_date('18/08/2009 14:59:28', 'dd/mm/yyyy hh24:mi:ss')   , 'Bordeaux'   , 1   , 'OUT'   FROM dual 
)
  ,  SR1 AS
(
  SELECT dh, pr, sn, vl,
         count(*) over(partition BY pr,vl) AS cnt_vl
    FROM TT
), SR2 AS
(
SELECT dh,pr, sn, max(vl) keep (dense_rank first ORDER BY cnt_vl DESC) over (partition by pr) AS vl 
from SR1
)
  SELECT to_char(dh,'mm-yyyy')    AS months,
         to_char(dh,'dd/mm/yyyy') AS horo,
         pr,
         min(CASE sn WHEN 'IN'  THEN dh END) AS day_first_entry,
         max(CASE sn WHEN 'IN'  THEN dh END) AS day_last_entry,
         min(CASE sn WHEN 'OUT' THEN dh END) AS day_first_exit,
         max(CASE sn WHEN 'OUT' THEN dh END) AS day_last_exit,
         vl
    FROM sr2
GROUP BY to_char(dh,'mm-yyyy'),
         to_char(dh,'dd/mm/yyyy'),
         pr,vl
ORDER BY to_char(dh,'dd/mm/yyyy') ASC,
         pr ASC;

Mais un avis expert pourrait me dire si je me suis trompé ?
Je ne sais pas non plus si avec cette étape en plus, j'ai toujours un gain en performance sympa.

Merci à vous

[Waldar]

Ça m'a l'air correct, mais je n'ai rien sous la main pour tester.
Bien vu pour la correction, keep first dépendait du group by et donc du jour de présence.
Pour tester la performance, il faut essayer, je ne garanti rien.

Pour être honnête, j'aurai naturellement codé votre solution actuelle (en utilisant un keep first pour éviter une sous-requête).