double requete au double limite je sais pas trop

Le Mon, 19 Oct 2009 21:56:47 +0200, WebShaker a écrit:

Pour simplifier disons que j'ai une table commandes table commandes
{
idclient integer,
date_cmd timestamp,
total_ht float

Grave erreur (sans rapport avec votre question, certes).
Les "flottants" introduisent des erreurs d'arrondis, donc votre
comptabilité va pleurer à un moment.
Deux façons de faire :
- utiliser le bon type: numeric(6,2) par exemple
- repasser dans le monde des entiers avec la multiplication qui va bien.

}

On m'a demander d'afficher le nombre de clients actifs dans un tableau
mois par mois.
Donc la définition d'un client actif est un client qui a commandé dans
les 12 mois.

Quelque chose comme :

SELECT idclient,date_trunc('month',date_cmd),SUM(total_ht) AS total FROM
commandes
GROUP BY 1,2
HAVING total > 0

dans un deuxième temps on m'a demander d'afficher les 10 plus gros
clients de chaque mois.
donc cela veut dire qu'en Janvier 2009 je dois afficher les 10 plus gros
client (sommes des commandes sur l'année flottante) qui ont commandés

Pourquoi somme sur l'année ?

sur les 12 mois précédents.

Et la rebelote. Avec 12 requêtes je vois, mais avec une seule...

Ne comprenant pas suffisamment le besoin, je ne peux pas vous donner
d'exemple, même si vous pouvez commencer avec ce qui précède.
Mais c'est un cas typique où vous pouvez vous en sortir, avec une sous-
requêtes ou une auto-jointure, sauf erreur de ma part.

--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
<http://www.dotandco.net/> <http://www.dotandco.com/>
<http://www.dotandco.net/ressources/icann_registrars/prices>
<http://icann-registrars-life.dotandco.net/>

Patrick Mevzek a écrit :

Le Mon, 19 Oct 2009 21:56:47 +0200, WebShaker a écrit:

Pour simplifier disons que j'ai une table commandes table commandes
{
idclient integer,
date_cmd timestamp,
total_ht float

Grave erreur (sans rapport avec votre question, certes).
Les "flottants" introduisent des erreurs d'arrondis, donc votre
comptabilité va pleurer à un moment.
Deux façons de faire :
- utiliser le bon type: numeric(6,2) par exemple
- repasser dans le monde des entiers avec la multiplication qui va bien.

+1 Je crois qu'on insistera jamais assez la-dessus.

}

On m'a demander d'afficher le nombre de clients actifs dans un tableau
mois par mois.
Donc la définition d'un client actif est un client qui a commandé dans
les 12 mois.

Quelque chose comme :

SELECT idclient,date_trunc('month',date_cmd),SUM(total_ht) AS total FROM
commandes
GROUP BY 1,2
HAVING total > 0

Pour ma culture perso, est-ce-que date_trunc n'est pas une fonction
spécifique à postgresql ?

--
Xavier Nayrac
http://personalbugtracker.free.fr

Xavier Nayrac wrote:

Pour ma culture perso, est-ce-que date_trunc n'est pas une fonction
spécifique à postgresql ?

Tout à fait, en MySQL on peut utiliser DATE_FORMAT()


--
Réseau IRC Francophone: http://www.zeolia.net
Aide et astuces : http://www.g33k-zone.org
Communauté Francophone sur les Eggdrops: http://www.eggdrop.fr

Patrick Mevzek a écrit :

Le Mon, 19 Oct 2009 21:56:47 +0200, WebShaker a écrit:

Pour simplifier disons que j'ai une table commandes table commandes
{
idclient integer,
date_cmd timestamp,
total_ht float

Grave erreur (sans rapport avec votre question, certes).
Les "flottants" introduisent des erreurs d'arrondis, donc votre
comptabilité va pleurer à un moment.
Deux façons de faire :
- utiliser le bon type: numeric(6,2) par exemple
- repasser dans le monde des entiers avec la multiplication qui va bien.

J'ai jamais compris pourquoi.
Certes je comprends bien le problème de l'arrondi (que d'ailleurs
j'obtiens régulièrement, mais l'erreur se trouve toujours a une décimal
trèèèèès lointaine.
Je vais par exemple avoir un
1522536.97999999999992

ce qui évidement devrait être un
1522536.98

mais bon un simple arrondi me donne toujours le bon résultat !

SELECT idclient,date_trunc('month',date_cmd),SUM(total_ht) AS total FROM
commandes
GROUP BY 1,2
HAVING total > 0

non ca va me donner le CA par client et par mois.

moi ce qu'il me faut c'est
SELECT 1, count(DISTINCT idclient) AS nbclient FROM commandes WHERE
date_cmd >= '2008-02-01' AND date_cmd < '2009-02-01';

cela me donne ce que je veux plus le mois de janvier

SELECT 2, count(DISTINCT idclient) AS nbclient FROM commandes WHERE
date_cmd >= '2008-03-01' AND date_cmd < '2009-03-01';

et ça pour le mois de février.

moi ce que je voudrai, c'est trouver la requête pour récupérer un
tableau genre
month | nbclient
----------------
1 | 255
2 | 315
...

c'est a dire me renvoyant l'équivalent des 12 requetes. (une pour chaque
mois).

Etienne

Le Tue, 20 Oct 2009 23:35:38 +0200, WebShaker a écrit:

total_ht float

Grave erreur (sans rapport avec votre question, certes). Les
"flottants" introduisent des erreurs d'arrondis, donc votre
comptabilité va pleurer à un moment. Deux façons de faire :
- utiliser le bon type: numeric(6,2) par exemple - repasser dans le
monde des entiers avec la multiplication qui va bien.

J'ai jamais compris pourquoi.

Même sans comprendre, parfois il est bon d'accepter ce que les experts
disent très clairement (surtout quand il n'y a aucune controverse), si je
cite par exemple la documentation de PostgreSQL, ma référence :
The data types real and double precision are inexact, variable-precision
numeric types.
[..]
Inexact means that some values cannot be converted exactly to the
internal format and are stored as approximations, so that storing and
retrieving a value might show slight discrepancies. Managing these errors
and how they propagate through calculations is the subject of an entire
branch of mathematics and computer science and will not be discussed
here, except for the following points:

* If you require exact storage and calculations (such as for monetary
amounts), use the numeric type instead.
[..]
* Comparing two floating-point values for equality might not always
work as expected.


Vous noterez au passage que ce n'est pas qu'un "problème d'arrondi", le
test d'égalité est un gros problème à lui tout seul... mais tout dépend
aussi comment le langage de programmation se comporte, indépendamment de
l'effcicacité du SGBDR.

Certes je comprends bien le problème de l'arrondi (que d'ailleurs
j'obtiens régulièrement,

L'arrondi n'est pas un "problème" c'est une règle de calcul qui doit
venir à la toute fin du résultat, pas en cours de calcul.
Et comme dit plus haut, le problème ici n'est pas limité à l'arrondi.
Dans les opérations arithmétiques en "flottants", vous perdez aussi
l'associativité et la distributivité.
Cf http://en.wikipedia.org/wiki/Floating_point pour tous les détails.

mais l'erreur se trouve toujours a une décimal
trèèèèès lointaine.
Je vais par exemple avoir un
1522536.97999999999992

ce qui évidement devrait être un
1522536.98

mais bon un simple arrondi me donne toujours le bon résultat !

Sauf que les erreurs d'arrondis s'accumulent et plus vous ferez de
calculs plus vous allez au devant de soucis.


Maintenant vous avez le droit de penser "Tout ceci ne me concerne
pas, je ne change rien"
(alors que bon pour le coup les solutions ne sont pas complexes), mais on
aura le droit de vous dire "Vous étiez prévenu" quand vous rencontrerez
le problème, car vous le rencontrerez et les conséquences pourront être
désastreuses (je vous souhaite juste que ce ne soit pas à la hauteur des
500 millions de dollars d'un vol Ariane 5 parti en fumée à cause de
calculs en virgule flottante, ou des pertes humaines d'un missile Patriot
dont le processeur était allumé trop longtemps).

Mais on est HS pour ce point en particulier dans ce groupe.

non ca va me donner le CA par client et par mois.

Oui, désolé, ca me paraissait plus logique, mais j'aurai dû vous relire,
certes.

moi ce que je voudrai, c'est trouver la requête pour récupérer un
tableau genre
month | nbclient
----------------
1 | 255
2 | 315
...

c'est a dire me renvoyant l'équivalent des 12 requetes. (une pour chaque
mois).

Alors :

SELECT date_trunc('month',date_cmd),COUNT(DISTINCT idclient)
FROM commandes
GROUP BY 1

(ce qui n'est pas très différent conceptuellement de la première réponse,
c'est le GROUP BY qui importe pour ce genre de problèmes)

Modulo après les bidouillages de formatage de l'affichage de la date.

La "règle", enfin la façon de penser, est qu'une boucle (dans un langage
procédural) devient, dans le monde rationnel, une jointure ou un
regroupement.

Et pour votre deuxième besoin quelque chose comme (ca doit pouvoir se
simplifier - et y compris avec une vue ou une table temporaire -
et ce n'est qu'une possibilité, une auto-jointure doit pouvoir
faire l'affaire aussi)

SELECT m,c FROM
(SELECT date_trunc('month',date_cmd) AS m,idclient AS c FROM commandes GROUP BY m,idclient) AS l
WHERE l.c IN
(SELECT c FROM
(SELECT date_trunc('month',date_cmd),idclient AS c,SUM(total_ht) FROM commandes WHERE date_trunc('month',date_cmd)=l.m GROUP BY 1,2 ORDER BY 3 DESC LIMIT 10)
AS ll)

(étant entendu que le LIMIT par défaut en SQL ne va pas forcément bien
gérer le cas d'ex-aequo)

ou en simplifiant la deuxième partie :

SELECT m,c FROM
(SELECT date_trunc('month',date_cmd) AS m,idclient AS c FROM commandes GROUP BY m,idclient) AS l
WHERE (l.m,l.c) IN
(SELECT date_trunc('month',date_cmd),idclient AS c FROM commandes WHERE date_trunc('month',date_cmd)=l.m GROUP BY 1,2 ORDER BY SUM(total_ht) DESC LIMIT 10)

Autres possibilités non testées (je n'ai pas eu le temps de jouer encore avec un postgresql 8.4 live) :

WITH cumuls AS (SELECT date_trunc('month',date_cmd) AS m,idclient AS c,SUM(total_ht) AS t FROM commandes GROUP BY m,c)
SELECT * FROM cumuls AS l WHERE (l.m,l.c,l.t) IN (SELECT m,c,t FROM cumuls WHERE m=l.m ORDER BY t DESC LIMIT 10)

ou

SELECT m,c FROM
( SELECT date_trunc('month',date_cmd) AS m,idclient AS c,rank() OVER (PARTITION BY m ORDER BY SUM(total_ht) DESC) AS r FROM commandes ) AS ss
WHERE r<;

--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
<http://www.dotandco.net/> <http://www.dotandco.com/>
<http://www.dotandco.net/ressources/icann_registrars/prices>
<http://icann-registrars-life.dotandco.net/>

Le Thu, 22 Oct 2009 21:43:38 +0200, WebShaker a écrit:

Bon soit!
Ceci dit c'est un peu trop tard dans mon cas.

Trop tard ? Changer le type d'un champ dans votre base c'est trop tard ?
Bon bah... bon courage à votre comptable alors :-) !

Heu nan.
Ca ça va me donner le nombre de client qui ont commandés en Janvier,
fevrier, ...

mois en janvier je veux ce qui ont commander les 12 mois précédents !!!

Bon je laisse tomber, manifestement je n'arrive pas à comprendre votre
besoin après 2 tentatives je vais arrêter de me ridiculiser :-) et
j'espère que d'autres pourront vous aider mieux que moi !

Pour ma culture, je vais essayer ce genre de trucs. je ne connaissais
pas WITH !

Je ne sais pas si c'est du SQLwhatever-compliant, mais c'est dans
PostgreSQL (suffisamment récent), comme ce n'était pas précisé je me suis
lâché avec mon logiciel chouchou :-)

--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
<http://www.dotandco.net/> <http://www.dotandco.com/>
<http://www.dotandco.net/ressources/icann_registrars/prices>
<http://icann-registrars-life.dotandco.net/>

Sur fr.comp.applications.sgbd, Xavier Nayrac disait :

Pour ma culture perso, est-ce-que date_trunc n'est pas une fonction
spécifique à postgresql ?

Si. Par contre on peut arriver quasiment au même résultat avec
EXTRACT, qui est SQL.

Pour info l'équivalent Oracle est TRUNC (comme pour les entiers).

--
Sinon, je suis (toujours) globalement contre, parce que je trouve
le principe amusant.
-- Éric Marillier dans fufe (honteusement coupé) --

Sur fr.comp.applications.sgbd, WebShaker disait :

mois en janvier je veux ce qui ont commander les 12 mois
précédents !!!

Voici ce que je propose pour l'année 2009 :

SELECT t.num, count(*)
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) as t(num)
JOIN (
SELECT
id.client,
EXTRACT(MONTH FROM GREATEST(MIN(date_cmd), '2009-01-01')) as min,
EXTRACT(MONTH FROM (MAX(date_cmd) + '364 days'::Interval)) as max
FROM Commandes
WHERE date_cmd >= '2008-02-01'
GROUP BY id.client
) c ON t.num BETWEEN c.min AND c.max
GROUP BY t.num

J'ai fait ça pour m'amuser. C'est pas testé. Ça ne marche probablement
que sur PostgreSQL (si ça marche).


--
les vrais newsmasters se reposent sur fufa pour une bonne raison : ils
n'ont pas le temps de couper des cheveux en quatre pour finir par se
faire insulter.
-- Erwan David dans fufe --