Optimisation de requêtes

Version imprimable

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:

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:

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:

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:

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:

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:

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.

23/03/2010, 09h49
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é ?

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:

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

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:

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.

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:

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é ? :calim2:
Je ne sais pas non plus si avec cette étape en plus, j'ai toujours un gain en performance sympa.

Merci à vous

23/03/2010, 21h43
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).
24/03/2010, 08h36
Waldar
Ah si une petite erreur, vous faites le tri sur la représentation littérale de votre date, ce qui va vous donner des résultats bizarre sur certaines périodes.

Modifiez la fin de votre requête ainsi :
Code:

1 2 3 4 5 6 GROUP BY trunc(dh), to_char(dh,'mm-yyyy'), to_char(dh,'dd/mm/yyyy'), pr,vl ORDER BY trunc(dh) ASC, pr ASC;
Le trunc(dh) correspond toujours à la journée, mais reste au format date et est donc triable. Vous n'avez pas besoin de l'avoir dans le select.
24/03/2010, 21h15
Malette

Bonsoir,

Merci Waldar pour le conseil sur le tri.
Quand à la solution trouvée, c'est malheureusement un gouffre en termes de performances, donc je vais rester sur ma solution initiale, mais merci quand même, ca aura au moins le mérite de m'avoir fait réfléchir :mouarf:

A bientôt.

Je ne suis pas vraiment surpris.
Encore une remarque, dans votre solution initiale vous utilisez la fonction RANK.
Si vous avez une égalité parfaite au nombre de ville, ça va fausser vos résultats.
Utilisez row_number() à la place, et également un second critère de tri pour évitez que le résultat de votre requête ne change tous les quatre matins :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH TT AS
(
SELECT 'Bordeaux' vl, 1 pr, 'IN' sn FROM dual union ALL
SELECT 'Bordeaux'   , 1   , 'OUT'   FROM dual union ALL
SELECT 'Talence'    , 1   , 'IN'    FROM dual union ALL
SELECT 'Talence'    , 1   , 'OUT'   FROM dual union ALL
SELECT 'Talence'    , 1   , 'IN'    FROM dual union ALL
SELECT 'Talence'    , 1   , 'OUT'   FROM dual union ALL
SELECT 'Bordeaux'   , 1   , 'IN'    FROM dual union ALL
SELECT 'Bordeaux'   , 1   , 'OUT'   FROM dual
)
  SELECT pr, vl, count(*) as nb_vl,
         rank()       OVER (partition BY pr ORDER BY count(*) DESC        ) AS rk,
         row_number() OVER (partition BY pr ORDER BY count(*) DESC, vl ASC) AS rn
    FROM TT
GROUP BY pr, vl;
 
PR	VL		NB_VL	RK	RN
1	Bordeaux	4	1	1
1	Talence		4	1	2