Forums PostgreSQL.fr

palex

Membre

Probleme sur une requete avec distinct et over()

bonjour

je dispose de cette requete:

select
        parutionpr1_.codificationTitre as col_1_0_,
	count(parutionpr1_.codificationTitre) over() as col_2_0_
    from
        public.Produit produitpre0_ 
    left outer join
        public.ParutionPresse parutionpr1_ 
            on produitpre0_.fk_parutionpresse=parutionpr1_.id 
            and (
                parutionpr1_.groupeid=20812
            ) 
   
    where
        produitpre0_.discriminator='PRESSE' 
        and (
            produitpre0_.groupeid in (
                20812
            )
        ) 
        and (
            produitpre0_.datesuppression is null
        ) 
    group by
        produitpre0_.id ,
	parutionpr1_.codificationTitre  
    limit 10

le souci c'est que dans la table parutionpresse je peux avoir

libelle  | codification | numero
TOTO    3456                  1
TOTO    3456                  2
TITI        1234                 6
TITI        1234                 7

donc j'aimerai avoir dans mon "count(parutionpr1_.codificationTitre) over() as col_2_0_"   2 car j'ai bien 4 lignes mais qui corresponde a 2 parutions car la codification est identique.

j'ai tenté de faire un "count(distinct parutionpr1_.codificationTitre) over()  mais ce n'est pas autorisé

je ne peut pas réecire la requete avec un with {} comme j'ai pu le voir sur certain sujet sur le net.

existe t il un autre moyen de le faire car sinon je vais etre obligé de séparer ce count dans une autre requete et ca m'embete un peu, car à l'origine cette requete remonte une 10ene de champs et ensuite plusieurs requete étaient jouées pour realiser les count,  j'ai donc ajouté les count() over() dans la meme requete  pour reussir à tout faire en une seule et unique requete, tout fonctionne sauf ce dernier cas.

merci beaucoup

palex

Membre

Re : Probleme sur une requete avec distinct et over()

j'ai oublié de préciser je suis sur postgres 8.4 en prod et 9.1 sur ma machine, la prod va bientot passer en 9.1 mais peut etre que je devrai trouver une solution pour la 8.4 avant ;(

edlm

Membre

Re : Probleme sur une requete avec distinct et over()

bonjour

je dispose de cette requete:

...

le souci c'est que dans la table parutionpresse je peux avoir

libelle  | codification | numero
TOTO    3456                  1
TOTO    3456                  2
TITI        1234                 6
TITI        1234                 7

donc j'aimerai avoir dans mon "count(parutionpr1_.codificationTitre) over() as col_2_0_"   2 car j'ai bien 4 lignes mais qui corresponde a 2 parutions car la codification est identique.

Le OVER ne comporte pas de clause PARTITION BY donc il exécute le count
sur la totalité des lignes, soit 4 dans ce cas.

Enfin j'imagine car je ne suis pas sûr de bien comprendre l'exposé.

Pour y voir plus clair commencez par enlever le GROUP BY et mettre à la place du over actuel
un OVER(PARTITION BY produitpre0_.id, parutionpr1_.codificationTitre).

---

Éric

palex

Membre

Re : Probleme sur une requete avec distinct et over()

merci pour votre réponse,

le partition by me retourne toujours 1, j'avai deja essayé effectivement cette solution mais si j'ai bien compris le partition by va me retourner ce genre de chose:

titre         codification     numero   paritionBy
TOTO    3456                 1            2
TOTO    3456                 2            2
TITI        1234                 4            3
TITI        1234                 5            3
TITI        1234                 6            3
TATA      11111                1             1

mais moi j'aimerais avoir :

TOTO    3456                 1     3
TOTO    3456                 2     3
TITI        1234                 4     3
TITI        1234                 5     3
TITI        1234                 6     3
TATA      11111                1      3

car a l'affichage de mon resultat sur la page j'ai uniquement besoin d'avoir le nombre de titre unique donc dans mon cas j'ai bien 3456, 1234 et 11111 donc 3 titres et dans le tableau qui est affiché en dessous je dois afficher les 6 lignes car elle dispose d'un numero différent, il ne s'agit pas des memes parutions.

on peut tres bien avoir un tele Z numero 1 et un tele Z numero 2 donc je dois afficher les 2 lignes dans ma liste car je peut tres bien modifier soi l'un soi l'autre mais au niveau stat je doit afficher que je dispose d' un seul et unique titre.

je sais que je retourne sur chaque ligne une information identique mais je prefere cela plutot que de rejouer la requete une autre fois derriere sachant que postgres a deja tout le resultat en memoire donc autant qu il fasse le calcul directement.

mon souci c'est de ne pas avoir de count(distinct codification) over() ou alors peut etre un count(codification) over(group by codification) plutot que partition by je pense.

edlm

Membre

Re : Probleme sur une requete avec distinct et over()

mais moi j'aimerais avoir :

TOTO    3456                 1     3
TOTO    3456                 2     3
TITI        1234                 4     3
TITI        1234                 5     3
TITI        1234                 6     3
TATA      11111                1      3

car a l'affichage de mon resultat sur la page j'ai uniquement besoin d'avoir le nombre de titre unique donc dans mon cas j'ai bien 3456, 1234 et 11111 donc 3 titres et dans le tableau qui est affiché en dessous je dois afficher les 6 lignes car elle dispose d'un numero différent, il ne s'agit pas des memes parutions.

Une solution éventuelle avec les sous-requêtes :

pour une table parution définie comme suit:

\d parution 
            Table « public.parution »
   Colonne    |       Type        | Modificateurs 
--------------+-------------------+---------------
 libelle      | character varying | 
 codification | character varying | 
 numero       | integer           | 

et avec les données suivantes:

SELECT * FROM parution;
 libelle | codification | numero 
---------+--------------+--------
 TOTO    | 3456         |      1
 TOTO    | 3456         |      2
 TITI    | 1234         |      6
 TITI    | 1234         |      7
(4 lignes)

On peut faire ca:

SELECT p.libelle, p.codification, p.numero, s.count FROM parution p INNER JOIN (SELECT codification, count(*) FROM parution GROUP BY codification) s ON (p.codification = s.codification);
 libelle | codification | numero | count 
---------+--------------+--------+-------
 TOTO    | 3456         |      1 |     2
 TOTO    | 3456         |      2 |     2
 TITI    | 1234         |      6 |     2
 TITI    | 1234         |      7 |     2
(4 lignes)

Mais je ne suis toujours pas sûr d'avoir bien compris le problème...

---

Éric

palex

Membre

Re : Probleme sur une requete avec distinct et over()

si tu fait un insert dans parution :

insert into parution values (tata, 5678, 4);

et que tu rejoue la requete tu auras surement une colonne count avec:

 libelle | codification | numero | count 
---------+--------------+--------+-------
 TOTO    | 3456         |      1 |     2
 TOTO    | 3456         |      2 |     2
 TITI    | 1234         |      6 |     2
 TITI    | 1234         |      7 |     2
 TATA    | 5678         |      4 |     1

je n'ai pas refait le test mais je suppose que le resultat sera celui ci car le count retourne uniquement le nombre de parution pour une codification et dans l'example TOTO => 2 et TITI =2 donc ta requete fonctionne mais c'est juste une question de données je pense non?

et moi j'aimerai avoir pour le cas ci dessus :

 libelle | codification | numero | count 
---------+--------------+--------+-------
 TOTO    | 3456         |      1 |     3
 TOTO    | 3456         |      2 |     3
 TITI    | 1234         |      6 |     3
 TITI    | 1234         |      7 |     3
 TATA    | 5678         |      4 |     3

si tu arrive a avoir le resultat voulu alors la je suis prenneur, je test en tout cas ta solution voir si je peux m'en sortir avec au cas ou

Dernière modification par palex (07/05/2012 17:46:19)

palex

Membre

Re : Probleme sur une requete avec distinct et over()

ha peut etre qu'en rajoutant un over() dans la sous requete ca va peut etre fonctionner  :

SELECT p.libelle, p.codification, p.numero, s.count FROM parution p INNER JOIN (SELECT codification, count(*) over() FROM parution GROUP BY codification) s ON (p.codification = s.codification);

palex

Membre

Re : Probleme sur une requete avec distinct et over()

mais malheureusement meme si cela peut fonctionner, ca risque de compliquer les choses car dans la requete principale j'ai des conditions d'exclusion sur plusieurs jointures et si je ne remet pas les memes jointures et conditions dans le sous requete le count(*) over() me fait un count sur toute la table sans tenir compte des conditions d'exclusions mais en ne tenant compte que de la codification

du coup j'ai realisé un test sur mon appli et je n'ai pas les bon resultat, je pense que de rajouter les jointures dans la sous requetes est mission impossible car il s'agit d'une requete généré par un framework écrit en interne qui ajoute les jointures dynamiquement suivant les colonnes affichées et les critères de recherche défini par l'utilisateur, du coup cela me ressort une requete avec le minimum requis et dans la sous requete j'aipeur que de rajouter trop de jointure me plombe le plan d'execution en plus.

en tout cas merci pour la solution s'était dans la bonne direction

edlm

Membre

Re : Probleme sur une requete avec distinct et over()

si tu fait un insert dans parution :

insert into parution values (tata, 5678, 4);

et que tu rejoue la requete tu auras surement une colonne count avec:

 libelle | codification | numero | count 
---------+--------------+--------+-------
 TOTO    | 3456         |      1 |     2
 TOTO    | 3456         |      2 |     2
 TITI    | 1234         |      6 |     2
 TITI    | 1234         |      7 |     2
 TATA    | 5678         |      4 |     1

je n'ai pas refait le test mais je suppose que le resultat sera celui ci car le count retourne uniquement le nombre de parution pour une codification et dans l'example TOTO => 2 et TITI =2 donc ta requete fonctionne mais c'est juste une question de données je pense non?

et moi j'aimerai avoir pour le cas ci dessus :

 libelle | codification | numero | count 
---------+--------------+--------+-------
 TOTO    | 3456         |      1 |     3
 TOTO    | 3456         |      2 |     3
 TITI    | 1234         |      6 |     3
 TITI    | 1234         |      7 |     3
 TATA    | 5678         |      4 |     3

si tu arrive a avoir le resultat voulu alors la je suis prenneur, je test en tout cas ta solution voir si je peux m'en sortir avec au cas ou

Ah je commence à un peu mieux comprendre...

Peut être que cela conviendrait mieux:

SELECT p.libelle, p.codification, p.numero, (SELECT count(DISTINCT codification) FROM parution) FROM parution p;

---

Éric

palex

Membre

Re : Probleme sur une requete avec distinct et over()

presque mais du coup le count(distinct codification) from parution ne tient pas compte des conditions de la requete principal, si je rajotue des jointures

SELECT p.libelle, p.codification, p.numero, (SELECT count(DISTINCT codification) FROM parution) FROM parution p
join majointure1 A
join majointure2 B
join majointure3 C
where
A.datesuppression is null
and B.stock > 0
and C.libelle ilike 'toto';

il va falloir que je rajoute dans la sous requete les memes conditions
mais petit a petit tu arrive exactement a rejoindre mon probleme car ta prochaine idée sera surement de faire comme j'ai fait au debut avec directement :

SELECT p.libelle, p.codification, p.numero,  count(DISTINCT codification)  FROM parution p ....

mais vu qu il y a un limit afin de gérer la pagination et ne pas retourner au client 2000 lignes alors qu'on en veut uniquement 10, j'ai ajouté le mot clé over() pour ne pas s'areter au limit mais dès que je met le mot clé over(), je n'ai plus le droit d'utiliser distinct ou group by, comme je le disais je pense qu il me faudrait :

count(distinct codification) over() ou alors peut etre un count(codification) over(group by codification)  mais je ne crois pas que ce soit possible

palex

Membre

Re : Probleme sur une requete avec distinct et over()

en tout cas merci d'avoir pris le temps de regarder mon problème
j'ai quand meme une solution qui est de revenir à 2 requetes(bon si ya moyen d'en faire qu une si tu trouve je suis prenneur quand meme ), une qui me retourne les données avec le limit qui va bien et la seconde qui me retourne juste les count() pour les meta données sans le limit du coup je n'aurai plus besoin du mot clé over et donc plus de souci de group by ou distinct.

ce qui est dommage c'est que la premiere requete sera identique en terme de jointure et condition donc postgres aura deja fait le travail et je vais devoir lui demander de refaire le meme boulot juste à cause du over qui ne gère pas le disctinct ou group by mais apres il y surement d'autre contrainte qui font que cela n'est pas été implémenté

Dernière modification par palex (07/05/2012 18:38:03)

edlm

Membre

Re : Probleme sur une requete avec distinct et over()

presque mais du coup le count(distinct codification) from parution ne tient pas compte des conditions de la requete principal, si je rajotue des jointures

SELECT p.libelle, p.codification, p.numero, (SELECT count(DISTINCT codification) FROM parution) FROM parution p
join majointure1 A
join majointure2 B
join majointure3 C
where
A.datesuppression is null
and B.stock > 0
and C.libelle ilike 'toto';

il va falloir que je rajoute dans la sous requete les memes conditions
mais petit a petit tu arrive exactement a rejoindre mon probleme car ta prochaine idée sera surement de faire comme j'ai fait au debut avec directement :

SELECT p.libelle, p.codification, p.numero,  count(DISTINCT codification)  FROM parution p ....

mais vu qu il y a un limit afin de gérer la pagination et ne pas retourner au client 2000 lignes alors qu'on en veut uniquement 10, j'ai ajouté le mot clé over() pour ne pas s'areter au limit mais dès que je met le mot clé over(), je n'ai plus le droit d'utiliser distinct ou group by, comme je le disais je pense qu il me faudrait :

count(distinct codification) over() ou alors peut etre un count(codification) over(group by codification)  mais je ne crois pas que ce soit possible

Dans ce cas là pourquoi ne pas factoriser en utilisant  la clause WITH:

WITH ma_table AS(
    SELECT libelle, codification, numero
    FROM parution JOIN... JOIN... WHERE...
)
SELECT libelle, codification, numero, (SELECT count(DISTINCT codification) FROM ma_table) FROM ma_table;

---

Éric

palex

Membre

Re : Probleme sur une requete avec distinct et over()

bonjour

ce serait effectivement uine solution je pense, en fait comme je le disait precedement on utilise un framework ecri en interne pour générer nos requete de grille et pour le moment il repond à 99% des cas (le 1% etant mon cas à moi qui peut etre resolu en 2 requetes), du coup il faudrait soit faire une evolution du framework pour y inclure la gestion de la clause with soit passer en 2 requetes et le moins couteux et le plus rapide pour le moment est la 2eme solution, si j'ai du temps je ferais evoluer le framework effectivement en attendant "peut etre un jour" un  group by ou distinct dans les fonctions window .

merci beaucoup