Vous n'êtes pas identifié(e).
Pages : 1
Justement c'est bien ça mon souci, c'est de :
il vous faut récupérer tous les élements de chaque tableau, de ne conserver qu'une valeur distincte puis de faire la somme pour chaque élément distinct.
même en plusieurs requêtes je suis un peu dans le flou.
Merci d'avance
Bonjour,
Je souhaite réaliser une requête qui me calcul le nombre d'IPCA(technicien) par typologie pour une date donnée.
Le problème c'est que un IPCA peux avoir plusieurs typologie pour la même date c'est à dire que voici le schéma de ma table en question :
CREATE TABLE ticket_enoff
(
teo_date character varying(12),
teo_ipca character varying(4),
teo_typo integer[]
)Voici un exemple de résultat dans la table :
teo_date teo_ipca teo_typo
20091115 1314 {1}
20091115 2514 {1,3}
20091115 3214 {2}
Donc le résultat de la requête devrait me donner :
typologie nombre_ipca
{1} 2
{2} 1
{3} 1
Voici ma requête actuel :
SELECT teo_typo, COUNT(teo_ipca) AS nb_ipca
FROM ticket_enoff
WHERE teo_date = '20091115'
GROUP BY teo_typoj'obtiens comme résultat :
typologie nombre_ipca
{1} 1
{1,3} 1
{2} 1
Comment puis-je faire s'il vous plais car je voit pas du tout??
Merci par avance.
Bonjour,
Merci beaucoup pour votre aide.
En fait j'ai trouvé une autre solution qui fonctionne bien. En fait, j'ai ajouté un champ 'suivi_bot' dans ma table intervention et dés qu'une intervention est suivi je lui affecte '1' dans le champ 'suivi_bot' et pour l'affichage je fais appel aux interventions qui ont 1 dans le champ 'suivi_bot'.
En tout cas merci d'avoir pris de votre temps.
Cordialement.
Bonjour,
But du projet : Mon projet c'est de faire un outil permettant de suivre les interventions que réalise les techniciens sélectionnés auparavant.
Objectif de la requête : Afficher la liste des interventions prise en charge par les IPCA concerné par le suivi. Ces interventions sont validés par des hotliner si tout est OK(cette validation est enregistrée dans la base de données, lorsquelle est validée on ne l'affiche plus dans la liste). Ces interventions doivent être suivi la semaine après la sélection de l'IPCA mais également après la semaine si cette dernière (l'intervention) n'a pas été validé par le BOT.
Problème : Si cette intervention n'a pas été validé alors qu'on lance une autre sélection (c'est à dire à la fin de la semaine du suivi) le numéro d'intervention est enregistrée dans la même table que la validation (mais sans date de validation) et je voit pas comment faire pour afficher les interventions qui ont une date de validation vide, et d'afficher également les interventions des nouveaux techniciens sélectionnés et ne pas afficher celle qui ont une date de validation.
Table intervention :
CREATE TABLE intervention
(
id_ca character varying(9) NOT NULL, -- numéro intervention
int_refinst character varying(4), -- numéro ipca(tech)
CONSTRAINT intervention_pkey PRIMARY KEY (id_ca)
)Table histo_suivi_bot (table pour l'historisation des interventions surveillées) :
CREATE TABLE histo_suivi_bot
(
hsb_inter character varying(9) NOT NULL,
hsb_date_validation character varying(14),
hsb_ipca character varying(4),
hsb_tech integer,
hsb_tech_bot integer,
CONSTRAINT histo_suivi_bot_pkey PRIMARY KEY (hsb_inter)
)Table ticket_enoff (table dans laquelle j'enregistre la liste des tchniciens(IPCA) qui sont sélectionnés) :
CREATE TABLE ticket_enoff
(
teo_date character varying(12),
teo_ipca character varying(4),
teo_typo integer[]
)
WITH (OIDS=TRUE);
ALTER TABLE ticket_enoff OWNER TO postgres;Donc j'ai essayer de faire ceci :
SELECT int_refinst AS ipca, int_ig, int_refinst_tech AS ipca_tech, id_ca, int_statut, histo_pec_dt AS dt_pec,
int_date_rdv AS dt_planif, int_date_debut AS dt_cloture, teo_typo AS typologie
FROM intervention AS inter
JOIN ticket_enoff AS teo ON teo.teo_ipca = inter.int_refinst
JOIN histo_pec AS hpec ON hpec.histo_pec_id = inter.id_ca
WHERE to_date(histo_pec_dt, 'DD/MM/YYYY') BETWEEN '20091220' AND '20091226'
AND histo_pec_action = '1'
AND teo.teo_date = '20091220'
AND (id_ca NOT IN (SELECT hsb_inter FROM histo_suivi_bot WHERE hsb_date_validation IS NOT NULL)
AND id_ca IN (SELECT hsb_inter FROM histo_suivi_bot WHERE hsb_date_validation IS NULL)
)
ORDER BY int_refinstEn gros, dans l'exemple de la requête :
de la semaine du 20/12/2009 au 26/12/2009, j'ai 1 interventions à afficher(interventions de la nouvelle semaine donc des nouveaux techniciens sélectionnées) et j'ai 42 interventions de la semaine précédente qui sont dans la table 'histo_suivi_bot' sans date de validation donc je devrais obtenir 43 lignes mais avec ma requête, j'en obtiens aucune.
Comment puis-je faire??
Merci d'avance.
Ok merci beaucoup pour votre aide en tout cas.
Cordialement.
Bonjour,
En fait c'est pas grave si elle prend pas en compte l'index, la requête s'exécute assez rapidement.
J'ai retiré la clause where car tu avait dit :
On ne pourrait pas remplacer la clause where par des jointures de type FULL OUTER JOIN entre A et B , et entre C et D, et une INNER JOIN entre le résultat?
Enfin, c'est pas grave je pense l'a laisser comme ça.
Cordialement.
Réponse à gleu : Si par Analyse tu parle d'un Explain Analyse sur la requête, oui je l'est effectué.
Réponse à flo :
1) Effectivement lorsque je remplace la table intervention qui apparait en premier dans la requête par la table correspondant aux technicien (table 'presta') la requête s'exécute beaucoup plus rapidement (250ms)
Voici la requête modifié :
SELECT DISTINCT presta.pre_id, ((coalesce(change_box_ig,0))+(coalesce(change_box_sav,0)))AS nb_box, (coalesce(nb_inter_sav,0)+coalesce(nb_inter_ig,0))AS nb_inter
FROM presta LEFT JOIN
(SELECT int_refinst, COUNT(*) AS change_box_ig
FROM intervention JOIN conclusion_ig ON id_ca = conclu_ig_id
WHERE conclu_ig_date BETWEEN '20091214' AND '20091219'
AND conclu_ig_anc_box <>''
GROUP BY int_refinst) as tab_ig ON tab_ig.int_refinst = presta.pre_id
LEFT JOIN
(SELECT int_refinst, COUNT(*) AS change_box_sav
FROM intervention JOIN conclusion_sav ON id_ca = conclu_sav_id
WHERE conclu_sav_date BETWEEN '20091214' AND '20091219'
AND conclu_sav_anc_box <>''
GROUP BY int_refinst) as tab_sav ON tab_sav.int_refinst = presta.pre_id
LEFT JOIN
(SELECT int_refinst,COUNT(*) AS nb_inter_sav
FROM intervention JOIN conclusion_sav ON id_ca = conclu_sav_id
WHERE conclu_sav_date BETWEEN '20091214' AND '20091219'
GROUP BY int_refinst
HAVING COUNT(*) > 10
) as tab_nb_inter_sav on tab_nb_inter_sav.int_refinst = presta.pre_id
LEFT JOIN
(SELECT int_refinst,COUNT(*) AS nb_inter_ig
FROM intervention JOIN conclusion_ig ON id_ca = conclu_ig_id
WHERE conclu_ig_date BETWEEN '20091214' AND '20091219'
GROUP BY int_refinst
HAVING COUNT(*) > 10
) as tab_nb_inter_ig on tab_nb_inter_ig.int_refinst = presta.pre_id
WHERE (nb_inter_sav IS NOT NULL OR nb_inter_ig IS NOT NULL)
AND (change_box_ig IS NOT NULL OR change_box_sav IS NOT NULL)et son explain analyze :
"Unique (cost=22773.45..22787.22 rows=1377 width=40) (actual time=158.923..159.139 rows=42 loops=1)"
" -> Sort (cost=22773.45..22776.90 rows=1377 width=40) (actual time=158.916..158.982 rows=42 loops=1)"
" Sort Key: presta.pre_id, (COALESCE(tab_ig.change_box_ig, 0::bigint) + COALESCE(tab_sav.change_box_sav, 0::bigint)), (COALESCE(tab_nb_inter_sav.nb_inter_sav, 0::bigint) + COALESCE(tab_nb_inter_ig.nb_inter_ig, 0::bigint))"
" -> Hash Left Join (cost=22279.89..22701.66 rows=1377 width=40) (actual time=146.337..158.735 rows=42 loops=1)"
" Hash Cond: ((presta.pre_id)::text = (tab_nb_inter_ig.int_refinst)::text)"
" Filter: ((tab_nb_inter_sav.nb_inter_sav IS NOT NULL) OR (tab_nb_inter_ig.nb_inter_ig IS NOT NULL))"
" -> Hash Left Join (cost=18710.06..19063.25 rows=1377 width=32) (actual time=100.332..112.152 rows=270 loops=1)"
" Hash Cond: ((presta.pre_id)::text = (tab_nb_inter_sav.int_refinst)::text)"
" -> Hash Left Join (cost=5492.94..5695.14 rows=1377 width=24) (actual time=28.342..39.252 rows=270 loops=1)"
" Hash Cond: ((presta.pre_id)::text = (tab_sav.int_refinst)::text)"
" Filter: ((tab_ig.change_box_ig IS NOT NULL) OR (tab_sav.change_box_sav IS NOT NULL))"
" -> Hash Left Join (cost=1151.31..1306.40 rows=1377 width=16) (actual time=7.886..15.771 rows=1367 loops=1)"
" Hash Cond: ((presta.pre_id)::text = (tab_ig.int_refinst)::text)"
" -> Seq Scan on presta (cost=0.00..149.77 rows=1377 width=8) (actual time=0.017..3.124 rows=1367 loops=1)"
" -> Hash (cost=1151.11..1151.11 rows=16 width=16) (actual time=7.835..7.835 rows=61 loops=1)"
" -> Subquery Scan tab_ig (cost=1150.75..1151.11 rows=16 width=16) (actual time=7.412..7.705 rows=61 loops=1)"
" -> HashAggregate (cost=1150.75..1150.95 rows=16 width=7) (actual time=7.407..7.513 rows=61 loops=1)"
" -> Nested Loop (cost=11.44..1150.67 rows=16 width=7) (actual time=1.330..7.194 rows=71 loops=1)"
" -> Bitmap Heap Scan on conclusion_ig (cost=11.44..1017.10 rows=16 width=13) (actual time=1.262..5.019 rows=71 loops=1)"
" Recheck Cond: (((conclu_ig_date)::text >= '20091214'::text) AND ((conclu_ig_date)::text <= '20091219'::text))"
" Filter: ((conclu_ig_anc_box)::text <> ''::text)"
" -> Bitmap Index Scan on i_conclu_ig_date (cost=0.00..11.44 rows=317 width=0) (actual time=1.008..1.008 rows=1662 loops=1)"
" Index Cond: (((conclu_ig_date)::text >= '20091214'::text) AND ((conclu_ig_date)::text <= '20091219'::text))"
" -> Index Scan using intervention_pkey on intervention (cost=0.00..8.34 rows=1 width=20) (actual time=0.017..0.019 rows=1 loops=71)"
" Index Cond: ((intervention.id_ca)::text = (conclusion_ig.conclu_ig_id)::text)"
" -> Hash (cost=4338.68..4338.68 rows=236 width=16) (actual time=20.374..20.374 rows=234 loops=1)"
" -> Subquery Scan tab_sav (cost=4333.37..4338.68 rows=236 width=16) (actual time=18.675..19.854 rows=234 loops=1)"
" -> HashAggregate (cost=4333.37..4336.32 rows=236 width=7) (actual time=18.670..19.083 rows=234 loops=1)"
" -> Nested Loop (cost=39.22..4332.19 rows=236 width=7) (actual time=1.760..17.773 rows=335 loops=1)"
" -> Bitmap Heap Scan on conclusion_sav (cost=39.22..2422.01 rows=236 width=13) (actual time=1.733..8.873 rows=335 loops=1)"
" Recheck Cond: (((conclu_sav_date)::text >= '20091214'::text) AND ((conclu_sav_date)::text <= '20091219'::text))"
" Filter: ((conclu_sav_anc_box)::text <> ''::text)"
" -> Bitmap Index Scan on i_conclu_sav_date (cost=0.00..39.16 rows=1490 width=0) (actual time=1.528..1.528 rows=2537 loops=1)"
" Index Cond: (((conclu_sav_date)::text >= '20091214'::text) AND ((conclu_sav_date)::text <= '20091219'::text))"
" -> Index Scan using intervention_pkey on intervention (cost=0.00..8.08 rows=1 width=20) (actual time=0.013..0.015 rows=1 loops=335)"
" Index Cond: ((intervention.id_ca)::text = (conclusion_sav.conclu_sav_id)::text)"
" -> Hash (cost=13206.98..13206.98 rows=812 width=16) (actual time=71.947..71.947 rows=44 loops=1)"
" -> Subquery Scan tab_nb_inter_sav (cost=13184.65..13206.98 rows=812 width=16) (actual time=71.516..71.838 rows=44 loops=1)"
" -> HashAggregate (cost=13184.65..13198.86 rows=812 width=7) (actual time=71.510..71.693 rows=44 loops=1)"
" Filter: (count(*) > 10)"
" -> Nested Loop (cost=39.53..13173.47 rows=1490 width=7) (actual time=1.693..65.881 rows=2537 loops=1)"
" -> Bitmap Heap Scan on conclusion_sav (cost=39.53..2418.59 rows=1490 width=13) (actual time=1.657..8.509 rows=2537 loops=1)"
" Recheck Cond: (((conclu_sav_date)::text >= '20091214'::text) AND ((conclu_sav_date)::text <= '20091219'::text))"
" -> Bitmap Index Scan on i_conclu_sav_date (cost=0.00..39.16 rows=1490 width=0) (actual time=1.413..1.413 rows=2537 loops=1)"
" Index Cond: (((conclu_sav_date)::text >= '20091214'::text) AND ((conclu_sav_date)::text <= '20091219'::text))"
" -> Index Scan using intervention_pkey on intervention (cost=0.00..7.21 rows=1 width=20) (actual time=0.010..0.012 rows=1 loops=2537)"
" Index Cond: ((intervention.id_ca)::text = (conclusion_sav.conclu_sav_id)::text)"
" -> Hash (cost=3565.87..3565.87 rows=317 width=16) (actual time=45.870..45.870 rows=10 loops=1)"
" -> Subquery Scan tab_nb_inter_ig (cost=3557.15..3565.87 rows=317 width=16) (actual time=45.679..45.837 rows=10 loops=1)"
" -> HashAggregate (cost=3557.15..3562.70 rows=317 width=7) (actual time=45.673..45.799 rows=10 loops=1)"
" Filter: (count(*) > 10)"
" -> Nested Loop (cost=11.52..3554.77 rows=317 width=7) (actual time=1.175..41.830 rows=1662 loops=1)"
" -> Bitmap Heap Scan on conclusion_ig (cost=11.52..1016.38 rows=317 width=13) (actual time=1.142..5.472 rows=1662 loops=1)"
" Recheck Cond: (((conclu_ig_date)::text >= '20091214'::text) AND ((conclu_ig_date)::text <= '20091219'::text))"
" -> Bitmap Index Scan on i_conclu_ig_date (cost=0.00..11.44 rows=317 width=0) (actual time=0.995..0.995 rows=1662 loops=1)"
" Index Cond: (((conclu_ig_date)::text >= '20091214'::text) AND ((conclu_ig_date)::text <= '20091219'::text))"
" -> Index Scan using intervention_pkey on intervention (cost=0.00..8.00 rows=1 width=20) (actual time=0.009..0.011 rows=1 loops=1662)"
" Index Cond: ((intervention.id_ca)::text = (conclusion_ig.conclu_ig_id)::text)"
"Total runtime: 160.808 ms"
2) Ensuite j'ai essayé la même requête avec FULL OUTER JOIN et INNER JOIN comme voici :
SELECT DISTINCT presta.pre_id, ((coalesce(change_box_ig,0))+(coalesce(change_box_sav,0)))AS nb_box, (coalesce(nb_inter_sav,0)+coalesce(nb_inter_ig,0))AS nb_inter
FROM presta LEFT JOIN
(SELECT int_refinst, COUNT(*) AS change_box_ig
FROM intervention JOIN conclusion_ig ON id_ca = conclu_ig_id
WHERE conclu_ig_date BETWEEN '20091214' AND '20091219'
AND conclu_ig_anc_box <>''
GROUP BY int_refinst) as tab_ig ON tab_ig.int_refinst = presta.pre_id
FULL OUTER JOIN
(SELECT int_refinst, COUNT(*) AS change_box_sav
FROM intervention JOIN conclusion_sav ON id_ca = conclu_sav_id
WHERE conclu_sav_date BETWEEN '20091214' AND '20091219'
AND conclu_sav_anc_box <>''
GROUP BY int_refinst) as tab_sav ON tab_sav.int_refinst = presta.pre_id
INNER JOIN
(SELECT int_refinst,COUNT(*) AS nb_inter_sav
FROM intervention JOIN conclusion_sav ON id_ca = conclu_sav_id
WHERE conclu_sav_date BETWEEN '20091214' AND '20091219'
GROUP BY int_refinst
HAVING COUNT(*) > 10
) as tab_nb_inter_sav on tab_nb_inter_sav.int_refinst = presta.pre_id
FULL OUTER JOIN
(SELECT int_refinst,COUNT(*) AS nb_inter_ig
FROM intervention JOIN conclusion_ig ON id_ca = conclu_ig_id
WHERE conclu_ig_date BETWEEN '20091214' AND '20091219'
GROUP BY int_refinst
HAVING COUNT(*) > 10
) as tab_nb_inter_ig on tab_nb_inter_ig.int_refinst = presta.pre_idmais la requête me renvoit également les IPCA lorsque le nombre de box est à 0 donc ce n'est pas correct.
Merci beaucou pour votre aide.
Cordialement.
Ok.
Voici l'explain analyse :
"Unique (cost=173444.01..176724.45 rows=328044 width=39) (actual time=31535.927..31767.122 rows=42 loops=1)"
" -> Sort (cost=173444.01..174264.12 rows=328044 width=39) (actual time=31535.918..31659.499 rows=51648 loops=1)"
" Sort Key: intervention.int_refinst, (COALESCE(tab_ig.change_box_ig, 0::bigint) + COALESCE(tab_sav.change_box_sav, 0::bigint)), (COALESCE(tab_nb_inter_sav.nb_inter_sav, 0::bigint) + COALESCE(tab_nb_inter_ig.nb_inter_ig, 0::bigint))"
" -> Merge Left Join (cost=109091.46..123201.39 rows=328044 width=39) (actual time=26475.724..31148.898 rows=51648 loops=1)"
" Merge Cond: ((intervention.int_refinst)::text = "inner"."?column3?")"
" Filter: ((tab_nb_inter_sav.nb_inter_sav IS NOT NULL) OR (tab_nb_inter_ig.nb_inter_ig IS NOT NULL))"
" -> Merge Left Join (cost=105512.43..115240.95 rows=328052 width=31) (actual time=25837.823..30189.183 rows=159847 loops=1)"
" Merge Cond: ((intervention.int_refinst)::text = "inner"."?column3?")"
" -> Merge Left Join (cost=92266.21..96253.83 rows=328052 width=23) (actual time=25216.883..28833.303 rows=159847 loops=1)"
" Merge Cond: ((intervention.int_refinst)::text = "inner"."?column3?")"
" Filter: ((tab_ig.change_box_ig IS NOT NULL) OR (tab_sav.change_box_sav IS NOT NULL))"
" -> Merge Left Join (cost=87918.23..89655.49 rows=328060 width=15) (actual time=23303.638..25571.859 rows=328176 loops=1)"
" Merge Cond: ("outer"."?column2?" = "inner"."?column3?")"
" -> Sort (cost=86766.80..87586.95 rows=328060 width=7) (actual time=22867.116..23822.785 rows=328176 loops=1)"
" Sort Key: (intervention.int_refinst)::text"
" -> Seq Scan on intervention (cost=0.00..45496.60 rows=328060 width=7) (actual time=0.088..20296.449 rows=328176 loops=1)"
" -> Sort (cost=1151.43..1151.47 rows=16 width=16) (actual time=436.485..501.501 rows=37475 loops=1)"
" Sort Key: (tab_ig.int_refinst)::text"
" -> Subquery Scan tab_ig (cost=1150.75..1151.11 rows=16 width=16) (actual time=435.723..436.103 rows=61 loops=1)"
" -> HashAggregate (cost=1150.75..1150.95 rows=16 width=7) (actual time=435.712..435.840 rows=61 loops=1)"
" -> Nested Loop (cost=11.44..1150.67 rows=16 width=7) (actual time=87.043..435.247 rows=71 loops=1)"
" -> Bitmap Heap Scan on conclusion_ig (cost=11.44..1017.10 rows=16 width=13) (actual time=54.592..277.920 rows=71 loops=1)"
" Recheck Cond: (((conclu_ig_date)::text >= '20091214'::text) AND ((conclu_ig_date)::text <= '20091219'::text))"
" Filter: ((conclu_ig_anc_box)::text <> ''::text)"
" -> Bitmap Index Scan on i_conclu_ig_date (cost=0.00..11.44 rows=317 width=0) (actual time=33.812..33.812 rows=1662 loops=1)"
" Index Cond: (((conclu_ig_date)::text >= '20091214'::text) AND ((conclu_ig_date)::text <= '20091219'::text))"
" -> Index Scan using intervention_pkey on intervention (cost=0.00..8.34 rows=1 width=20) (actual time=2.198..2.201 rows=1 loops=71)"
" Index Cond: ((intervention.id_ca)::text = (conclusion_ig.conclu_ig_id)::text)"
" -> Sort (cost=4347.98..4348.57 rows=236 width=16) (actual time=1910.099..2127.287 rows=135117 loops=1)"
" Sort Key: (tab_sav.int_refinst)::text"
" -> Subquery Scan tab_sav (cost=4333.37..4338.68 rows=236 width=16) (actual time=1907.907..1909.190 rows=234 loops=1)"
" -> HashAggregate (cost=4333.37..4336.32 rows=236 width=7) (actual time=1907.880..1908.345 rows=234 loops=1)"
" -> Nested Loop (cost=39.22..4332.19 rows=236 width=7) (actual time=36.562..1906.342 rows=335 loops=1)"
" -> Bitmap Heap Scan on conclusion_sav (cost=39.22..2422.01 rows=236 width=13) (actual time=36.521..1447.509 rows=335 loops=1)"
" Recheck Cond: (((conclu_sav_date)::text >= '20091214'::text) AND ((conclu_sav_date)::text <= '20091219'::text))"
" Filter: ((conclu_sav_anc_box)::text <> ''::text)"
" -> Bitmap Index Scan on i_conclu_sav_date (cost=0.00..39.16 rows=1490 width=0) (actual time=29.216..29.216 rows=2537 loops=1)"
" Index Cond: (((conclu_sav_date)::text >= '20091214'::text) AND ((conclu_sav_date)::text <= '20091219'::text))"
" -> Index Scan using intervention_pkey on intervention (cost=0.00..8.08 rows=1 width=20) (actual time=1.354..1.356 rows=1 loops=335)"
" Index Cond: ((intervention.id_ca)::text = (conclusion_sav.conclu_sav_id)::text)"
" -> Sort (cost=13246.22..13248.25 rows=812 width=16) (actual time=620.924..698.282 rows=47831 loops=1)"
" Sort Key: (tab_nb_inter_sav.int_refinst)::text"
" -> Subquery Scan tab_nb_inter_sav (cost=13184.65..13206.98 rows=812 width=16) (actual time=620.415..620.749 rows=44 loops=1)"
" -> HashAggregate (cost=13184.65..13198.86 rows=812 width=7) (actual time=620.407..620.604 rows=44 loops=1)"
" Filter: (count(*) > 10)"
" -> Nested Loop (cost=39.53..13173.47 rows=1490 width=7) (actual time=1.796..613.332 rows=2537 loops=1)"
" -> Bitmap Heap Scan on conclusion_sav (cost=39.53..2418.59 rows=1490 width=13) (actual time=1.763..8.966 rows=2537 loops=1)"
" Recheck Cond: (((conclu_sav_date)::text >= '20091214'::text) AND ((conclu_sav_date)::text <= '20091219'::text))"
" -> Bitmap Index Scan on i_conclu_sav_date (cost=0.00..39.16 rows=1490 width=0) (actual time=1.531..1.531 rows=2537 loops=1)"
" Index Cond: (((conclu_sav_date)::text >= '20091214'::text) AND ((conclu_sav_date)::text <= '20091219'::text))"
" -> Index Scan using intervention_pkey on intervention (cost=0.00..7.21 rows=1 width=20) (actual time=0.225..0.227 rows=1 loops=2537)"
" Index Cond: ((intervention.id_ca)::text = (conclusion_sav.conclu_sav_id)::text)"
" -> Sort (cost=3579.04..3579.83 rows=317 width=16) (actual time=469.721..494.556 rows=14564 loops=1)"
" Sort Key: (tab_nb_inter_ig.int_refinst)::text"
" -> Subquery Scan tab_nb_inter_ig (cost=3557.15..3565.87 rows=317 width=16) (actual time=469.385..469.538 rows=10 loops=1)"
" -> HashAggregate (cost=3557.15..3562.70 rows=317 width=7) (actual time=469.377..469.497 rows=10 loops=1)"
" Filter: (count(*) > 10)"
" -> Nested Loop (cost=11.52..3554.77 rows=317 width=7) (actual time=1.203..464.529 rows=1662 loops=1)"
" -> Bitmap Heap Scan on conclusion_ig (cost=11.52..1016.38 rows=317 width=13) (actual time=1.166..6.200 rows=1662 loops=1)"
" Recheck Cond: (((conclu_ig_date)::text >= '20091214'::text) AND ((conclu_ig_date)::text <= '20091219'::text))"
" -> Bitmap Index Scan on i_conclu_ig_date (cost=0.00..11.44 rows=317 width=0) (actual time=0.983..0.983 rows=1662 loops=1)"
" Index Cond: (((conclu_ig_date)::text >= '20091214'::text) AND ((conclu_ig_date)::text <= '20091219'::text))"
" -> Index Scan using intervention_pkey on intervention (cost=0.00..8.00 rows=1 width=20) (actual time=0.263..0.265 rows=1 loops=1662)"
" Index Cond: ((intervention.id_ca)::text = (conclusion_ig.conclu_ig_id)::text)"
"Total runtime: 31792.560 ms"
J'ai l'impression qu'il passe jamais dans l'index 'i_int_refinst' (cette index existait déjà à la base, je m'en suit aperçut tout à l'heure.
Voici le code de sa création :
CREATE INDEX i_int_refinst
ON intervention
USING btree
(int_refinst);Cordialement
Ok. Merci quand même pour votre aide.
Par contre, je peux quand même utiliser l'index?? Si oui, comment je doit faire pour l'utiliser dans ma requête?? Car je n'en est jamais utilisé et je voit pas trop.
Cordialement.
Oui l'index est créé : i_int_refinst.
Voici la nouvelle requête :
SELECT tab_ig.i_int_refinst, ((coalesce(change_box_ig,0))+(coalesce(change_box_sav,0)))AS nb_box, (coalesce(nb_inter_sav,0)+coalesce(nb_inter_ig,0))AS nb_inter
FROM
(SELECT i_int_refinst, COUNT(*) AS change_box_ig
FROM intervention JOIN conclusion_ig ON id_ca = conclu_ig_id
WHERE conclu_ig_date BETWEEN '20091214' AND '20091219'
AND conclu_ig_anc_box <>''
GROUP BY i_int_refinst) as tab_ig
LEFT JOIN
(SELECT i_int_refinst, COUNT(*) AS change_box_sav
FROM intervention JOIN conclusion_sav ON id_ca = conclu_sav_id
WHERE conclu_sav_date BETWEEN '20091214' AND '20091219'
AND conclu_sav_anc_box <>''
GROUP BY i_int_refinst) as tab_sav
LEFT JOIN
(SELECT i_int_refinst,COUNT(*) AS nb_inter_sav
FROM intervention JOIN conclusion_sav ON id_ca = conclu_sav_id
WHERE conclu_sav_date BETWEEN '20091214' AND '20091219'
GROUP BY i_int_refinst
HAVING COUNT(*) > 10
) as tab_nb_inter_sav
LEFT JOIN
(SELECT i_int_refinst,COUNT(*) AS nb_inter_ig
FROM intervention JOIN conclusion_ig ON id_ca = conclu_ig_id
WHERE conclu_ig_date BETWEEN '20091214' AND '20091219'
GROUP BY i_int_refinst
HAVING COUNT(*) > 10
) as tab_nb_inter_ig
WHERE (nb_inter_sav IS NOT NULL OR nb_inter_ig IS NOT NULL)
AND (change_box_ig IS NOT NULL OR change_box_sav IS NOT NULL)Mais le problème c'est qu'elle ne fonctionne plus, elle m'affiche le message d'erreur suivant :
ERROR: syntax error at or near "WHERE"
LINE 28: WHERE (nb_inter_sav IS NOT NULL OR nb_inter_ig IS NOT NULL)
^********** Erreur **********
ERROR: syntax error at or near "WHERE"
État SQL :42601
Caractère : 1133
Cordialement.
int_refinst comme c'est indiqué correspond au numéro du technicien qui à fait cette intervention donc la réponse à "y a-t-il plusieurs id_ca pour le même int_refinst " est oui.
Conclusion_ig est une table qui permet d'obtenir les renseignements lorsque l'intervention est clôturé(la date de la clôture, le changement de box....) par le technicien. Donc le champ conclusion_ig_id correspond à un numéro d'intervention qui est bien entendu un des "id_ca" de la table intervention.
J'espère avoir été clair dans mes réponses.
Merci d'avance.
Bonjour,
Voici la structure de chaque table :
1) Table Intervention :
CREATE TABLE intervention
(
id_ca character varying(9) NOT NULL, -- numéro intervention
int_refinst character varying(4), -- numéro ipca(tech)
int_ig integer DEFAULT 0, - 1 si intervention de type "Installaton Garantie" et 2 pour "SAV"
CONSTRAINT intervention_pkey PRIMARY KEY (id_ca)
)2) Table Conclusion_IG :
CREATE TABLE conclusion_ig
(
conclu_ig_id character varying(9) NOT NULL, -- numéro intervention
conclu_ig_date character varying(8), -- Date de cloture
conclu_ig_anc_box character varying(18), -- Num série ancienne box
conclu_ig_nvl_box character varying(18), -- Num série nouvelle box
CONSTRAINT conclu_ig_pkey PRIMARY KEY (conclu_ig_id)
)3) Table Conclusion_SAV :
CREATE TABLE conclusion_sav
(
conclu_sav_id character varying(9) NOT NULL, -- numéro intervention
conclu_sav_date character varying(8), -- Date de cloture
conclu_sav_anc_box character varying(18), -- Num série ancienne box
conclu_sav_nvl_box character varying(18), -- Num série nouvelle box
CONSTRAINT conclu_sav_pkey PRIMARY KEY (conclu_sav_id)
)J'ai fais la jointure avec la table Intervention dans chacune des sous-requête car il faut que je récupère 'int_refinst' qui se situe dans la table Intervention.
Les 2 première sous-requêtes me permette de récupérer le nombre de box changé lors des interventions pour la période données et les 2 dernière sous-requêtes me permettent de récupérer le nombre d'intervention en tout pour la période données.
Cordialement.
Ok désolé je savait pas.
Donc lorsque je fais un "EXPLAIN ANALYSE" de la requête, voici ce que j'obtiens :
"Unique (cost=173444.01..176724.45 rows=328044 width=39) (actual time=30211.200..30485.622 rows=42 loops=1)"
" -> Sort (cost=173444.01..174264.12 rows=328044 width=39) (actual time=30211.190..30379.267 rows=51648 loops=1)"
" Sort Key: intervention.int_refinst, (COALESCE(tab_ig.change_box_ig, 0::bigint) + COALESCE(tab_sav.change_box_sav, 0::bigint)), (COALESCE(tab_nb_inter_sav.nb_inter_sav, 0::bigint) + COALESCE(tab_nb_inter_ig.nb_inter_ig, 0::bigint))"
" -> Merge Left Join (cost=109091.46..123201.39 rows=328044 width=39) (actual time=24299.792..29755.457 rows=51648 loops=1)"
" Merge Cond: ((intervention.int_refinst)::text = "inner"."?column3?")"
" Filter: ((tab_nb_inter_sav.nb_inter_sav IS NOT NULL) OR (tab_nb_inter_ig.nb_inter_ig IS NOT NULL))"
" -> Merge Left Join (cost=105512.43..115240.95 rows=328052 width=31) (actual time=23956.297..29057.165 rows=159847 loops=1)"
" Merge Cond: ((intervention.int_refinst)::text = "inner"."?column3?")"
" -> Merge Left Join (cost=92266.21..96253.83 rows=328052 width=23) (actual time=23528.958..27760.144 rows=159847 loops=1)"
" Merge Cond: ((intervention.int_refinst)::text = "inner"."?column3?")"
" Filter: ((tab_ig.change_box_ig IS NOT NULL) OR (tab_sav.change_box_sav IS NOT NULL))"
" -> Merge Left Join (cost=87918.23..89655.49 rows=328060 width=15) (actual time=21173.121..23803.847 rows=328176 loops=1)"
" Merge Cond: ("outer"."?column2?" = "inner"."?column3?")"
" -> Sort (cost=86766.80..87586.95 rows=328060 width=7) (actual time=20580.848..21730.980 rows=328176 loops=1)"
" Sort Key: (intervention.int_refinst)::text"
" -> Seq Scan on intervention (cost=0.00..45496.60 rows=328060 width=7) (actual time=0.141..18121.620 rows=328176 loops=1)"
" -> Sort (cost=1151.43..1151.47 rows=16 width=16) (actual time=592.237..661.458 rows=37475 loops=1)"
" Sort Key: (tab_ig.int_refinst)::text"
" -> Subquery Scan tab_ig (cost=1150.75..1151.11 rows=16 width=16) (actual time=591.482..591.866 rows=61 loops=1)"
" -> HashAggregate (cost=1150.75..1150.95 rows=16 width=7) (actual time=591.475..591.616 rows=61 loops=1)"
" -> Nested Loop (cost=11.44..1150.67 rows=16 width=7) (actual time=112.406..591.005 rows=71 loops=1)"
" -> Bitmap Heap Scan on conclusion_ig (cost=11.44..1017.10 rows=16 width=13) (actual time=94.867..447.570 rows=71 loops=1)"
" Recheck Cond: (((conclu_ig_date)::text >= '20091214'::text) AND ((conclu_ig_date)::text <= '20091219'::text))"
" Filter: ((conclu_ig_anc_box)::text <> ''::text)"
" -> Bitmap Index Scan on i_conclu_ig_date (cost=0.00..11.44 rows=317 width=0) (actual time=63.304..63.304 rows=1662 loops=1)"
" Index Cond: (((conclu_ig_date)::text >= '20091214'::text) AND ((conclu_ig_date)::text <= '20091219'::text))"
" -> Index Scan using intervention_pkey on intervention (cost=0.00..8.34 rows=1 width=20) (actual time=2.002..2.005 rows=1 loops=71)"
" Index Cond: ((intervention.id_ca)::text = (conclusion_ig.conclu_ig_id)::text)"
" -> Sort (cost=4347.98..4348.57 rows=236 width=16) (actual time=2352.667..2618.697 rows=135117 loops=1)"
" Sort Key: (tab_sav.int_refinst)::text"
" -> Subquery Scan tab_sav (cost=4333.37..4338.68 rows=236 width=16) (actual time=2350.652..2351.831 rows=234 loops=1)"
" -> HashAggregate (cost=4333.37..4336.32 rows=236 width=7) (actual time=2350.644..2351.095 rows=234 loops=1)"
" -> Nested Loop (cost=39.22..4332.19 rows=236 width=7) (actual time=48.143..2349.002 rows=335 loops=1)"
" -> Bitmap Heap Scan on conclusion_sav (cost=39.22..2422.01 rows=236 width=13) (actual time=48.108..1826.013 rows=335 loops=1)"
" Recheck Cond: (((conclu_sav_date)::text >= '20091214'::text) AND ((conclu_sav_date)::text <= '20091219'::text))"
" Filter: ((conclu_sav_anc_box)::text <> ''::text)"
" -> Bitmap Index Scan on i_conclu_sav_date (cost=0.00..39.16 rows=1490 width=0) (actual time=42.640..42.640 rows=2537 loops=1)"
" Index Cond: (((conclu_sav_date)::text >= '20091214'::text) AND ((conclu_sav_date)::text <= '20091219'::text))"
" -> Index Scan using intervention_pkey on intervention (cost=0.00..8.08 rows=1 width=20) (actual time=1.545..1.547 rows=1 loops=335)"
" Index Cond: ((intervention.id_ca)::text = (conclusion_sav.conclu_sav_id)::text)"
" -> Sort (cost=13246.22..13248.25 rows=812 width=16) (actual time=427.323..528.795 rows=47831 loops=1)"
" Sort Key: (tab_nb_inter_sav.int_refinst)::text"
" -> Subquery Scan tab_nb_inter_sav (cost=13184.65..13206.98 rows=812 width=16) (actual time=426.784..427.128 rows=44 loops=1)"
" -> HashAggregate (cost=13184.65..13198.86 rows=812 width=7) (actual time=426.776..426.981 rows=44 loops=1)"
" Filter: (count(*) > 10)"
" -> Nested Loop (cost=39.53..13173.47 rows=1490 width=7) (actual time=1.821..419.583 rows=2537 loops=1)"
" -> Bitmap Heap Scan on conclusion_sav (cost=39.53..2418.59 rows=1490 width=13) (actual time=1.797..8.798 rows=2537 loops=1)"
" Recheck Cond: (((conclu_sav_date)::text >= '20091214'::text) AND ((conclu_sav_date)::text <= '20091219'::text))"
" -> Bitmap Index Scan on i_conclu_sav_date (cost=0.00..39.16 rows=1490 width=0) (actual time=1.557..1.557 rows=2537 loops=1)"
" Index Cond: (((conclu_sav_date)::text >= '20091214'::text) AND ((conclu_sav_date)::text <= '20091219'::text))"
" -> Index Scan using intervention_pkey on intervention (cost=0.00..7.21 rows=1 width=20) (actual time=0.148..0.150 rows=1 loops=2537)"
" Index Cond: ((intervention.id_ca)::text = (conclusion_sav.conclu_sav_id)::text)"
" -> Sort (cost=3579.04..3579.83 rows=317 width=16) (actual time=174.523..199.498 rows=14564 loops=1)"
" Sort Key: (tab_nb_inter_ig.int_refinst)::text"
" -> Subquery Scan tab_nb_inter_ig (cost=3557.15..3565.87 rows=317 width=16) (actual time=174.263..174.387 rows=10 loops=1)"
" -> HashAggregate (cost=3557.15..3562.70 rows=317 width=7) (actual time=174.255..174.349 rows=10 loops=1)"
" Filter: (count(*) > 10)"
" -> Nested Loop (cost=11.52..3554.77 rows=317 width=7) (actual time=1.442..169.784 rows=1662 loops=1)"
" -> Bitmap Heap Scan on conclusion_ig (cost=11.52..1016.38 rows=317 width=13) (actual time=1.153..5.966 rows=1662 loops=1)"
" Recheck Cond: (((conclu_ig_date)::text >= '20091214'::text) AND ((conclu_ig_date)::text <= '20091219'::text))"
" -> Bitmap Index Scan on i_conclu_ig_date (cost=0.00..11.44 rows=317 width=0) (actual time=0.955..0.955 rows=1662 loops=1)"
" Index Cond: (((conclu_ig_date)::text >= '20091214'::text) AND ((conclu_ig_date)::text <= '20091219'::text))"
" -> Index Scan using intervention_pkey on intervention (cost=0.00..8.00 rows=1 width=20) (actual time=0.085..0.087 rows=1 loops=1662)"
" Index Cond: ((intervention.id_ca)::text = (conclusion_ig.conclu_ig_id)::text)"
"Total runtime: 30496.614 ms"
En espérant que cette fois-ci ce sera bon 
Merci d'avance.
Ok.
Lorsque je vais dans l'onglet "Messages", j'obtiens :
Durée totale d'exécution de la requête :13796 ms.
42 lignes récupérées.
Par contre quand je vais dans l'onglet "Expliquer(Explain)", je n'obtiens aucune information.
Cordialement.
Ok mais as-tu une idée pour mieux l'optimiser s'il te plais car je voit pas du tout??
Cordialement.
Ok merci pour la réponse clair et précise.
Je n'est pas le choix, je suis au travail et je ne peux pas changer la version des serveurs de PostgreSQL.
Donc j'ai aucune solution pour optimiser ma requête??
Cordialement
En fait sous PgAdmin, j'ai 2 serveur :
Le premier qui me permet de faire des tests -> "PostgreSQL 8.2.5 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)"
Le second qui est la partie production -> "PostgreSQL 8.0.2 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)"
Donc je commence sur ma première et ensuite une fois que cela fonctionnera, je migrerait mon projet sous l'autre serveur.
Cordialement.
Bonjour,
Je suppose que oui car lorsque je vais dans Aide -> A propos je vois écris "versions 1.8.4".
Merci d'avance.
Bonjour,
J'ai une requête (qui permet de récupérer le nombre de box changé par chaque technicien lors d'intervention pour une période données ainsi que le nombre totale d'intervention pour la période données pour chaque IPCA) qui est très lourde et donc je souhaiterais l'optimiser.
J'ai trouvé sur internet, un site qui parle de fonctions fenêtrées (http://sqlpro.developpez.com/article/ol … se-window/) et ça à l'air d'être pas mal pour optimiser la requête.
Voici ma requête de base :
SELECT DISTINCT intervention.int_refinst, ((coalesce(change_box_ig,0))+(coalesce(change_box_sav,0)))AS nb_box, (coalesce(nb_inter_sav,0)+coalesce(nb_inter_ig,0))AS nb_inter
FROM intervention LEFT JOIN
(SELECT int_refinst, COUNT(*) AS change_box_ig
FROM intervention JOIN conclusion_ig ON id_ca = conclu_ig_id
WHERE conclu_ig_date BETWEEN '20091214' AND '20091219'
AND conclu_ig_anc_box <>''
GROUP BY int_refinst) as tab_ig ON tab_ig.int_refinst = intervention.int_refinst
LEFT JOIN
(SELECT int_refinst, COUNT(*) AS change_box_sav
FROM intervention JOIN conclusion_sav ON id_ca = conclu_sav_id
WHERE conclu_sav_date BETWEEN '20091214' AND '20091219'
AND conclu_sav_anc_box <>''
GROUP BY int_refinst) as tab_sav ON tab_sav.int_refinst = intervention.int_refinst
LEFT JOIN
(SELECT int_refinst,COUNT(*) AS nb_inter_sav
FROM intervention JOIN conclusion_sav ON id_ca = conclu_sav_id
WHERE conclu_sav_date BETWEEN '20091214' AND '20091219'
GROUP BY int_refinst
HAVING COUNT(*) > 10
) as tab_nb_inter_sav on tab_nb_inter_sav.int_refinst = intervention.int_refinst
LEFT JOIN
(SELECT int_refinst,COUNT(*) AS nb_inter_ig
FROM intervention JOIN conclusion_ig ON id_ca = conclu_ig_id
WHERE conclu_ig_date BETWEEN '20091214' AND '20091219'
GROUP BY int_refinst
HAVING COUNT(*) > 10
) as tab_nb_inter_ig on tab_nb_inter_ig.int_refinst = intervention.int_refinst
WHERE (nb_inter_sav IS NOT NULL OR nb_inter_ig IS NOT NULL)
AND (change_box_ig IS NOT NULL OR change_box_sav IS NOT NULL)Pour essayer... j'ai essayer de réaliser cette requête :
SELECT int_refinst, COUNT(*) OVER(PARTITION BY int_refinst) FROM intervention;Mais j'obtiens l'erreur suivante :
ERROR: syntax error at or near "OVER"
LINE 1: SELECT int_refinst, COUNT(*) OVER(PARTITION BY int_refinst) ...
^
********** Erreur **********
ERROR: syntax error at or near "OVER"
État SQL :42601
Caractère : 30
J'ai deux questions :
1) Quelqu'un as-t'il une idée pour régler mon erreur??
2) Quelqu'un pourrais-t'il m'aider pour re-construire ma requête grâce au fonction fenêtrées??
Merci d'avance
Pages : 1