[SQL] probleem met AVG functie

[Jabbah]

Ik wil graag van een aantal kolommen de gemiddelde waardes weten, maar de rijen die 0 of NULL zijn mogen niet meegeteld worden. Normaal zou je iets doen zoals:

SQL:

SELECT AVG(kolom1) FROM tabel
WHERE kolom1 > 0 AND kolom1 IS NOT NULL

Nu heb ik een query in de trant van:

SQL:

SELECT
AVG(tabel1.kolom1) * AVG(tabel2.kolom1),
AVG(tabel1.kolom2) * AVG(tabel2.kolom2),
AVG(tabel1.kolom3) * AVG(tabel2.kolom3)
FROM tabel

Ik weet dus niet hoe nu mijn WHERE eruit moet zien. Tot dusver heb ik dit:

SQL:

WHERE
((tabel1.kolom1 > 0 AND tabel1.kolom1 IS NOT NULL
AND tabel2.kolom1 > 0 AND tabel2.kolom1 IS NOT NULL)

AND (tabel1.kolom2 > 0 AND tabel1.kolom2 IS NOT NULL
AND tabel2.kolom2 > 0 AND tabel2.kolom2 IS NOT NULL)

AND (tabel1.kolom3 > 0 AND tabel1.kolom3 IS NOT NULL
AND tabel2.kolom3 > 0 AND tabel2.kolom3 IS NOT NULL))

Dit levert dus niet het juiste resultaat op. Helaas bieden de boeken en google ook geen oplossing.

Wie weet?

PS. Er zitten nog een aantal joins op die ik voor de leesbaarheid maar even heb weggelaten.

[Creepy]

Welk resultaat krijg je dan wel en wat had je precies verwacht?
Daarnaast vraag ik me af waarom je EN checkt of de waarde groter is dan null en daarna nog specifiek checkt of het niet null is..
Daarnaast kan ik me voorstellen dat er nog andere kolommen in de DB staan waar je op zult moeten grouperen (group by).

Daarnaast: welke DB gebruik je nu? MySQL toevallig? Dat is namelijk de enige die het toestaat om aggregated functies te gebruiker zonder of met een incomplete group-by (yes, its a bug, not a feature ).

[Verwijderd]

Jabbah schreef op dinsdag 19 april 2005 @ 16:38:
Ik wil graag van een aantal kolommen de gemiddelde waardes weten, maar de rijen die 0 of NULL zijn mogen niet meegeteld worden. Normaal zou je iets doen zoals:

SQL:

SELECT AVG(kolom1) FROM tabel
WHERE kolom1 > 0 AND kolom1 IS NOT NULL

Weet je dat wel zeker?
- als kolom1 niet is ingevuld (null is) dan wordt je logical AND false omdat hij niet groter dan 0 is
- als kolom1 <= 0 is, is deze dus NOT NULL en levert de logical AND false op
- als kolom1 een waarde groter dan 0 is, dan is deze NOT NULL en levert de waarde true op

Je houdt hier dus geen rekening met negatieve getallen want die worden niet meegenomen door SELECT. Je zal dus, denk ik, dit willen:

SQL:

SELECT AVG(kolom1) FROM tabel
WHERE kolom1

Omdat 0 en NULL false zijn worden alle overige getallen geselecteerd. Of is dit niet de fout? Misschien handig om de output te geven en aan te geven waarom die output niet klopt

[Jabbah]

Ik gebruik SQL Server en er zijn geen negatieve getallen.

Ik doe een GROUP BY van de kolommen die nog meer in de query staan.

Als ik bijv. het 2e en 3e statement in de SELECT weghaal (en dus ook de laatste 4 regels in de WHERE) dan krijg ik een hoger getal voor AVG(tabel1.kolom1) * AVG(tabel2.kolom1) dan wanneer ik de gehele query uitvoer.

Ben nu weg maar ik zal morgen naar de tips kijken en ev. nog wat resultaten posten.

[sig69]

Als je verhaal lees mag 0 of NULL nergens meegeteld worden. In dat geval kan je dit:

code:

WHERE
((tabel1.kolom1 > 0 AND tabel1.kolom1 IS NOT NULL
AND tabel2.kolom1 > 0 AND tabel2.kolom1 IS NOT NULL)
AND (tabel1.kolom2 > 0 AND tabel1.kolom2 IS NOT NULL
AND tabel2.kolom2 > 0 AND tabel2.kolom2 IS NOT NULL)
AND (tabel1.kolom3 > 0 AND tabel1.kolom3 IS NOT NULL
AND tabel2.kolom3 > 0 AND tabel2.kolom3 IS NOT NULL))

al herschrijven naar dit:

code:

ISNULL( tabel1.kolom1, 0 ) > 0 AND
ISNULL( tabel1.kolom2, 0 ) > 0 AND
ISNULL( tabel1.kolom3, 0 ) > 0 AND
ISNULL( tabel2.kolom1, 0 ) > 0 AND
ISNULL( tabel2.kolom2, 0 ) > 0 AND
ISNULL( tabel2.kolom3, 0 ) > 0

Wat er verder moet gebeuren en wat er fout gaat is me ook nog niet helemaal duidelijk...

[ Voor 23% gewijzigd door sig69 op 19-04-2005 17:25 ]

[stp_4]

....

is onzin

[ Voor 91% gewijzigd door stp_4 op 19-04-2005 17:39 ]

[Verwijderd]

Verwijderd schreef op dinsdag 19 april 2005 @ 16:52:
[...]
Weet je dat wel zeker?
- als kolom1 niet is ingevuld (null is) dan wordt je logical AND false omdat hij niet groter dan 0 is
...

Als kolom1 null is levert de hele AND ook null op.
Null = geen waarde. En dan is het niet mogelijk te bepalen of ie dan groter of kleiner is dan 0, want er is geen waarde. Dus geeft ie voor de expressie kolom1>0 ook null terug, onbepaalde waarde.

[ACM]

Verwijderd schreef op dinsdag 19 april 2005 @ 16:52:

SQL:

SELECT AVG(kolom1) FROM tabel
WHERE kolom1

Omdat 0 en NULL false zijn worden alle overige getallen geselecteerd. Of is dit niet de fout? Misschien handig om de output te geven en aan te geven waarom die output niet klopt

Die opmerking vat ik niet helemaal, 0 en NULL zijn niet 'false'. NULL wordt overigens wel genegeerd bij de AVG, 0 niet.

Jabbah schreef op dinsdag 19 april 2005 @ 16:38:
Nu heb ik een query in de trant van:

SQL:

SELECT
AVG(tabel1.kolom1) * AVG(tabel2.kolom1),
AVG(tabel1.kolom2) * AVG(tabel2.kolom2),
AVG(tabel1.kolom3) * AVG(tabel2.kolom3)
FROM tabel

Ik weet dus niet hoe nu mijn WHERE eruit moet zien.

Je hebt geen WHERE nodig, voor zover ik kan zien. NULL wordt genegeerd bij AVG, dus "AVG(1, 2, 3, NULL)" = 2.
Als je handig gebruik maakt van die eigenschap, kan je ervoor zorgen dat als er 0 opgegeven wordt, dat er dan NULL in de AVG terechtkomt en dus de AVG geen rekening houdt met dat veld van dat specifieke record, maar eventueel wel alle andere velden van dat record:

SQL:

SELECT
AVG(CASE tabel1.kolom1 WHEN 0 THEN NULL ELSE tabel1.kolom1) *
   AVG(CASE tabel2.kolom1 WHEN 0 THEN NULL ELSE tabel2.kolom1),
AVG(CASE tabel1.kolom2 WHEN 0 THEN NULL ELSE tabel1.kolom2) *
   AVG(CASE tabel2.kolom2 WHEN 0 THEN NULL ELSE tabel2.kolom2),
AVG(CASE tabel1.kolom3 WHEN 0 THEN NULL ELSE tabel1.kolom3) *
   AVG(CASE tabel2.kolom3 WHEN 0 THEN NULL ELSE tabel2.kolom3)

[Verwijderd]

ACM schreef op dinsdag 19 april 2005 @ 19:09:
[...]

Die opmerking vat ik niet helemaal, 0 en NULL zijn niet 'false'. NULL wordt overigens wel genegeerd bij de AVG, 0 niet.

Ehm ja, hoe moet ik het dan zeggen; 0 en NULL zijn false dus met 'WHERE kolom1' worden alleen de records geselecteerd waarbij kolom1 true is (dus geen 0 en geen NULL). Maar jij bent devver dus jij zal het wel beter weten, alleen wat zeg ik hier nou fout

quote: http://dev.mysql.com/doc/mysql/en/working-with-null.html

Note that in MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean operation is 1.

[Gomez12]

Verwijderd schreef op dinsdag 19 april 2005 @ 20:57:
[...]

Ehm ja, hoe moet ik het dan zeggen; 0 en NULL zijn false dus met 'WHERE kolom1' worden alleen de records geselecteerd waarbij kolom1 true is (dus geen 0 en geen NULL). Maar jij bent devver dus jij zal het wel beter weten, alleen wat zeg ik hier nou fout

Niks als het over mysql zou gaan, maar het gaat over sql en specifieker sql server, dus gelden de rariteiten van Mysql niet meer

[Verwijderd]

Gomez12 schreef op dinsdag 19 april 2005 @ 21:10:
[...]

Niks als het over mysql zou gaan, maar het gaat over sql en specifieker sql server, dus gelden de rariteiten van Mysql niet meer

Ohw shit natuurlijk, ik dacht al

[4VAlien]

waarom niet gewoon HAVING clauses gebruiken?

in de war dit is alleen bij group by.

[ Voor 34% gewijzigd door 4VAlien op 19-04-2005 21:26 ]

[Jabbah]

ACM schreef op dinsdag 19 april 2005 @ 19:09:
Je hebt geen WHERE nodig, voor zover ik kan zien. NULL wordt genegeerd bij AVG, dus "AVG(1, 2, 3, NULL)" = 2.
Als je handig gebruik maakt van die eigenschap, kan je ervoor zorgen dat als er 0 opgegeven wordt, dat er dan NULL in de AVG terechtkomt en dus de AVG geen rekening houdt met dat veld van dat specifieke record, maar eventueel wel alle andere velden van dat record:

SQL:

SELECT
AVG(CASE tabel1.kolom1 WHEN 0 THEN NULL ELSE tabel1.kolom1) *
   AVG(CASE tabel2.kolom1 WHEN 0 THEN NULL ELSE tabel2.kolom1),
AVG(CASE tabel1.kolom2 WHEN 0 THEN NULL ELSE tabel1.kolom2) *
   AVG(CASE tabel2.kolom2 WHEN 0 THEN NULL ELSE tabel2.kolom2),
AVG(CASE tabel1.kolom3 WHEN 0 THEN NULL ELSE tabel1.kolom3) *
   AVG(CASE tabel2.kolom3 WHEN 0 THEN NULL ELSE tabel2.kolom3)

Dit is inderdaad de oplossing voor mijn probleem. Bedankt.

[curry684]

Is je data overigens niet gewoon corrupt als je 0 als de absentie van data beschouwd en er daar dus gewoon NULL in je tabel zou moeten staan?

[Jabbah]

curry684 schreef op woensdag 20 april 2005 @ 10:17:
Is je data overigens niet gewoon corrupt als je 0 als de absentie van data beschouwd en er daar dus gewoon NULL in je tabel zou moeten staan?

Nee.. het gaat hier om prijzen. In dit geval kan een prijs 0 zijn, maar een prijs kan ook niet ingevuld zijn (NULL)

[bigbeng]

Maar als een prijs 0 is dan moet ie toch wel worden meegenomen in het gemiddelde?

[curry684]

Jabbah schreef op woensdag 20 april 2005 @ 13:27:
[...]

Nee.. het gaat hier om prijzen. In dit geval kan een prijs 0 zijn, maar een prijs kan ook niet ingevuld zijn (NULL)

Als de prijs technisch nul is is het product gratis en moet ie meegenomen worden in het gemiddelde. Als de prijs onbekend is (NULL dus) hoort ie niet in het gemiddelde meegenomen te worden.

Je kunt er altijd van uitgaan dat er iets niet in je data klopt zodra je dit soort lelijke stunts in je SQL nodig hebt

[Jabbah]

Het klink gek maar in ons geval willen we een prijs van 0 niet meenemen in ons gemiddelde.

[stp_4]

Jabbah schreef op woensdag 20 april 2005 @ 15:29:
Het klink gek maar in ons geval willen we een prijs van 0 niet meenemen in ons gemiddelde.

Maar hoe komt die 0 dan in de database terrecht? Als het geen prijs moet voorstellen dan hoort er dus niets ingevuld te worden toch?

[curry684]

Jabbah schreef op woensdag 20 april 2005 @ 15:29:
Het klink gek maar in ons geval willen we een prijs van 0 niet meenemen in ons gemiddelde.

Ja dat klinkt gek, want wat is dan het verschil tussen 0 en NULL

[bigbeng]

Nou, dat een gratis artikel wel mag worden "verkocht" en een artikel zonder prijs niet

[curry684]

bigbeng schreef op woensdag 20 april 2005 @ 16:09:
Nou, dat een gratis artikel wel mag worden "verkocht" en een artikel zonder prijs niet

Wat op zich logisch is, maar waarom mogen de gratis producten je gemiddelde verkoopprijs dan niet omlaag trekken en de produkten van 1 cent wel

[stp_4]

Zeg TS, wat moet het gemiddelde volgens jouw (of het bedrijf) voorstellen? Wat willen zij met deze (nog nietszeggende) cijfers doen?

[Jabbah]

Het heeft te maken met het bepalen van prijzen van producten die aan afschrijving onderhevig zijn.
Als we een product willen prijzen dat 5 jaar oud is, dan bepalen we het afschrijvingspercentage voor dat jaar door de gemiddelden van de prijzen in dat zelfde jaar te delen door de gemiddelde van de nieuwprijzen (in een bepaald segment). Om verschillende redenen kan een prijs 0 zijn en deze willen we niet meenemen in de berekening.

Extreem lage of hoge prijzen binnen een segment zijn er niet. Alhoewel er later nog een handmatige correctie volgt zijn de uitkomsten redelijk nauwkeurig en zeker niet nietszeggend voor ons.

[stp_4]

Jabbah schreef op woensdag 20 april 2005 @ 16:53:
Het heeft te maken met het bepalen van prijzen van producten die aan afschrijving onderhevig zijn.
Als we een product willen prijzen dat 5 jaar oud is, dan bepalen we het afschrijvingspercentage voor dat jaar door de gemiddelden van de prijzen in dat zelfde jaar te delen door de gemiddelde van de nieuwprijzen (in een bepaald segment). Om verschillende redenen kan een prijs 0 zijn en deze willen we niet meenemen in de berekening.

Extreem lage of hoge prijzen binnen een segment zijn er niet. Alhoewel er later nog een handmatige correctie volgt zijn de uitkomsten redelijk nauwkeurig en zeker niet nietszeggend voor ons.

Wel jammer dat producten met prijs 0 niet meegerekend worden want deze hebben oorspronkelijk wel een nieuwprijs en zou het afschrijvingspercentage hoger liggen en het product dus ook goedkoper zijn..

[curry684]

Jabbah schreef op woensdag 20 april 2005 @ 16:53:
Om verschillende redenen kan een prijs 0 zijn en deze willen we niet meenemen in de berekening.

Zoals?

Verwijderde produkten moet je met een 'Removed'-date of 'Deleted'-flag uitfilteren. Ik kan geen enkele andere reden om een produkt een prijs van 0 te geven in een goed datamodel. Goed normaliseren vereist dat ieder veld 1 enkele heldere betekenis heeft. De enige reden dat een prijs 0 mag zijn is dat de prijs 0 is. En dan hoort ie dus in het gemiddelde thuis.

[stp_4]

In welke context moet ik prijs 0 zien. Aankoopprijs, verkoopprijs, waarde?