[SQL] gemiddelde over een rij berekenen

[Verwijderd]

De volgende gegevens hebben we:

SELECT zone1, zone2, zone3, zone4, (zone1+zone2+zone3+zone4)/4 AS gemiddelde
FROM Vochtgegevens;

Deze SQL-query berekend het gemiddelde van een rij van 4 zones in een tabel.

MAAR... Het enige wat nog ontbreekt is dat deze query alle negatieve waarden niet meeneemt bij het berekenen van het gemiddelde.

De AVG functie kunnen we niet gebruiken, omdat deze alleen het gemiddelde over een kolom berekend.


Wie weet hier meer over....
alsvast bedankt

[gorgi_19]

Misschien een beter datamodel nemen, want ik heb zo de indruk van een brak datamodel.

[whoami]

Hoezo hij neemt de negatieve waarden niet mee ? Wil je misschien met de absolute waarde werken dan? Er is wel een SQL functie die je de absolute waarde teruggeeft, waarmee je dan aan de slag kunt.

Maarreh, gorgi_19 heeft wel gelijk wb dat datamodel.

[0528973]

Ik heb hier een klein sql voorbeeld waar die dat wel doet

code:

query: SELECT (  `1`  +  `2`  +  `3`  +  `4`  ) /4 FROM tmp;

#
# Tabel structuur voor tabel `tmp`
#

CREATE TABLE `tmp` (
  `1` tinyint(4) NOT NULL default '0',
  `2` tinyint(4) NOT NULL default '0',
  `3` tinyint(4) NOT NULL default '0',
  `4` tinyint(4) NOT NULL default '0'
) TYPE=MyISAM;

#
# Gegevens worden uitgevoerd voor tabel `tmp`
#

INSERT INTO `tmp` VALUES (1, 2, 3, 4);
INSERT INTO `tmp` VALUES (5, 6, 7, 8);
INSERT INTO `tmp` VALUES (-1, -2, 3, 4);

uitvoer query in phpmyadmin:
( `1` + `2` + `3` + `4` ) /4    
2.50    
6.50    
1.00

[0528973]

Maarreh, gorgi_19 heeft wel gelijk wb dat datamodel.

yup, dat denk ik ook

[Verwijderd]

Aan dat datamodel kunnen we niks veranderen...:(

Tis de bedoeling dat ie t gemiddelde uitrekent van de 4 zones, maar bij deze berekening moet ie alleen de waardes > 0 meenemen.

[faabman]

Verwijderd schreef op 17 maart 2004 @ 14:39:
Aan dat datamodel kunnen we niks veranderen...:(

Tis de bedoeling dat ie t gemiddelde uitrekent van de 4 zones, maar bij deze berekening moet ie alleen de waardes > 0 meenemen.

dan voeg je toeg gewoon een WHERE naam_veld > 0 toe aan je query

[P_de_B]

faabman schreef op 17 maart 2004 @ 14:41:
[...]


dan voeg je toeg gewoon een WHERE naam_veld > 0 toe aan je query

WHERE heeft betrekking op rijen, dit gaat om kolommen In een goed datamodel zou je gelijk hebben.

[Verwijderd]

faabman schreef op 17 maart 2004 @ 14:41:
[...]


dan voeg je toeg gewoon een WHERE naam_veld > 0 toe aan je query

Probleem: dan berekent hij het gemiddelde waar zone 1 groter dan 0 is, OF waar zone2 groter dan 0 is enz...

Zone1 Zone2 Zone3 Zone4 Gem
-12 -23 24 24 24

Hij moet dus de negatiefe waardes niet meenemen in zijn berekening, dus hierboven alleen zone 3 en 4 voor de berekening gebruiken

[Verwijderd]

where zone1 > 0 and zone2 > 0 and zone3 > 0 and zone4 > 0... of denk ik nu te simpel?

[gorgi_19]

Verwijderd schreef op 17 maart 2004 @ 14:46:
[...]


Probleem: dan berekent hij het gemiddelde waar zone 1 groter dan 0 is, OF waar zone2 groter dan 0 is enz...

Zone1 Zone2 Zone3 Zone4 Gem
-12 -23 24 24 24

Hij moet dus de negatiefe waardes niet meenemen in zijn berekening, dus hierboven alleen zone 3 en 4 voor de berekening gebruiken

Een database heeft toch wel een case, iif of iets vergelijkbaars?

Iif(veld <0;0;veld) as veld1

[justmental]

code:

SELECT zone1, zone2, zone3, zone4
, (greatest(zone1,0)+greatest(zone2,0)+greatest(zone3,0)+greatest(zone4,0))/4 AS gemiddelde
FROM Vochtgegevens;

[Freee!!]

Verwijderd schreef op 17 maart 2004 @ 14:50:
where zone1 > 0 and zone2 > 0 and zone3 > 0 and zone4 > 0... of denk ik nu te simpel?

Ja, je denkt te simpel, lees dit nog eens:

Verwijderd schreef op 17 maart 2004 @ 14:46:
[...]
Probleem: dan berekent hij het gemiddelde waar zone 1 groter dan 0 is, OF waar zone2 groter dan 0 is enz...

Zone1 Zone2 Zone3 Zone4 Gem
-12 -23 24 24 24

Hij moet dus de negatieve waardes niet meenemen in zijn berekening, dus hierboven alleen zone 3 en 4 voor de berekening gebruiken

[whoami]

gorgi_19 schreef op 17 maart 2004 @ 14:51:
[...]

Een database heeft toch wel een case, iif of iets vergelijkbaars?

Iif(veld <0;0;veld) as veld1

Dan klopt je gemiddelde niet meer:

4 + 2 + 0 + 0 / 4
is niet hetzelfde als
4 + 2 / 2

_{Ik ga er dus vanuit dat je moet delen door het aantal waarden die je ook daadwerkelijk in beschouwing neemt}

[ Voor 17% gewijzigd door whoami op 17-03-2004 14:54 ]

[P_de_B]

Verwijderd schreef op 17 maart 2004 @ 14:50:
where zone1 > 0 and zone2 > 0 and zone3 > 0 and zone4 > 0... of denk ik nu te simpel?

Nee, je denkt niet te simpel. Ik denk niet ver genoeg

edit: toch niet, zie Mr. Liu's reply hierboven

[ Voor 15% gewijzigd door P_de_B op 17-03-2004 14:56 ]

[Verwijderd]

Hhhm, ja de vraagstelling wordt me steeds minder duidelijk
Maarre, gorgi_19, volgens mij moet het zoiets worden. -->

[ Voor 97% gewijzigd door Verwijderd op 17-03-2004 14:56 ]

[gorgi_19]

whoami schreef op 17 maart 2004 @ 14:52:
[...]


Dan klopt je gemiddelde niet meer:

4 + 2 + 0 + 0 / 4
is niet hetzelfde als
4 + 2 / 2

_{Ik ga er dus vanuit dat je moet delen door het aantal waarden die je ook daadwerkelijk in beschouwing neemt}

Dan neem je die iif functie ook mee onder de deelstreep.. Als we toch al inefficient bezig zijn..

[Verwijderd]

heeft iemand al een idee, betreft deze ingewikkeld maar tog zo intersante ontwikkeling binne het sql van ms access.

modbreak:
Je hoeft zo snel je topic niet te kicken. Ieder topic is even belangrijk. Kicken is pas na 24h toegestaan.
Daarnaast kan je ook eens de tips van gorgi_19 ivm die IIF lezen.

[ Voor 48% gewijzigd door whoami op 17-03-2004 15:12 ]

[Grijze Vos]

Om eerlijk te zijn vind ik het niet echt een interessant topic. Het betreft een query die met een goed datamodel triviaal was om te implementeren. (1-M relaties, anyone?)

Maar goed. Wat ik zou doen. Gebruik je niet nog een laagje om die SQL heen? PHP/APS/VB of whatever? Ik zou daar gewoon het gemiddelde in uitrekenen als ik jou was.

[Verwijderd]

Verwijderd schreef op 17 maart 2004 @ 14:15:
De volgende gegevens hebben we:

SELECT zone1, zone2, zone3, zone4, (zone1+zone2+zone3+zone4)/4 AS gemiddelde
FROM Vochtgegevens;

Deze SQL-query berekend het gemiddelde van een rij van 4 zones in een tabel.

MAAR... Het enige wat nog ontbreekt is dat deze query alle negatieve waarden niet meeneemt bij het berekenen van het gemiddelde.

Heeft een record ook een Id?
Goochel eens met subqueries...

Let op: Dat een goed DBMS die zich aan de SQL-x specificatie houdt, alleen hele getallen teruggeeft als gemiddelde!

[ Voor 11% gewijzigd door Verwijderd op 17-03-2004 21:14 . Reden: Noot toegevoegd. ]

[JaQ]

hier is maar 1 heel eenvoudig antwoord op:
DIT KAN NIET IN SQL.

wat je namelijk wil, is dat je divisor (dus die delen door 4), lager wordt, als je een negatieve waarde tegenkomt.

dus:
4 , 3, 5, 4 = 14 14/ 4 = 4
4, -1, 5, 3 = 12 (want -1 neem je niet mee) 12 / 3 = 4 ( <-- delen door 3 dus! )

de oplossing met greatest is goed voor het optellen van je waardes, maar je zal nog een of andere onwijs complexe decode truc oid moeten uithalen voor je divisor. Volgens mij kan dat helemaal niet in standaard ANSI-SQL. Een vorm van PL is dus nodig.

[curry684]

DrFrankenstoner schreef op 18 maart 2004 @ 12:47:
hier is maar 1 heel eenvoudig antwoord op:
DIT KAN NIET IN SQL.

Nou het kan wel vanzelfsprekend, maar met enorm ranzige code. En dat komt door het enorm ranzige datamodel

Iets in deze richting:

SQL:

SELECT Veld1, Veld2, Veld3, Veld4, 
       (greatest(Veld1, 0)+greatest(Veld2, 0)+greatest(Veld3, 0)+greatest(Veld4, 0)) /
       (convert(int, Veld1 > 0) + convert(int, Veld2 > 0) + convert(int, Veld3 > 0) +
        convert(int, Veld4 > 0))) as gemiddelde from Vochtgegevens;

Uit de losse pols.... maar ik vermoed dat het wel werkt

[JaQ]

curry684 schreef op 18 maart 2004 @ 12:56:
[...]

Nou het kan wel vanzelfsprekend, maar met enorm ranzige code. En dat komt door het enorm ranzige datamodel

Iets in deze richting:

SQL:

SELECT Veld1, Veld2, Veld3, Veld4, 
       (greatest(Veld1, 0)+greatest(Veld2, 0)+greatest(Veld3, 0)+greatest(Veld4, 0)) /
       (convert(int, Veld1 > 0) + convert(int, Veld2 > 0) + convert(int, Veld3 > 0) +
        convert(int, Veld4 > 0))) as gemiddelde from Vochtgegevens;

Uit de losse pols.... maar ik vermoed dat het wel werkt

zulke ranzige code is toch hetzelfde als niet kunnen Stel je voor dat je dat op heel veel gegevens gaat loslaten.. volgens mij gaat je database dan doen...

[Verwijderd]

Verwijderd schreef op 17 maart 2004 @ 14:46:
[...]


Probleem: dan berekent hij het gemiddelde waar zone 1 groter dan 0 is, OF waar zone2 groter dan 0 is enz...

Zone1 Zone2 Zone3 Zone4 Gem
-12 -23 24 24 24

Hij moet dus de negatiefe waardes niet meenemen in zijn berekening, dus hierboven alleen zone 3 en 4 voor de berekening gebruiken

Moet hier gedeeld worden door 2 of door 4?

[Annie]

Verwijderd schreef op 18 maart 2004 @ 21:08:
[...]

Moet hier gedeeld worden door 2 of door 4?

Wat denk je zelf als je naar de uitkomst van het voorbeeld kijkt?
_{tip: (24 + 24) / 4 != 24}

[Verwijderd]

code:

SELECT zone1
, zone2
, zone3
, zone4
, (((zone1+ABS(zone1)+zone2+ABS(zone2)+zone3+ABS(zone3)+zone4+ABS(zone4))/2) /
((zone1+ABS(zone1)/2)/ABS(zone1)) + ((zone2+ABS(zone2)/2)/ABS(zone2)) + 
((zone3+ABS(zone3)/2)/ABS(zone3)) + ((zone4+ABS(zone4)/2)/ABS(zone4))  )
AS gemiddelde
FROM Vochtgegevens;

Enige voorwaarde is dat de gegevens niet 0 mogen zijn, i.v.m. deling door 0.

[ Voor 39% gewijzigd door Verwijderd op 19-03-2004 01:47 . Reden: voorwaarde toegevoegd. ]

[Verwijderd]

Verwijderd schreef op 19 maart 2004 @ 01:31:
......... slimme code

Alleen die beperking is een groot nadeel

Heb nog een andere oplossing geprobeerd, maar moet toegeven dat het datamodel gammel is.

TS?? Om hoeveel records gaat het (orde van grootte??), als je de volgende query op 10000 records loslaat staat ie toch ff te rekeken.........

code:

SELECT IIf([Zone1]>0,[Zone1],0) AS W1, 
IIf([Zone2]>0,[Zone2],0) AS W2, 
IIf([Zone3]>0,[Zone3],0) AS W3, 
IIf([Zone4]>0,[Zone4],0) AS W4, 
IIf([W1]=0,0,1) AS T1, 
IIf([W2]=0,0,1) AS T2, 
IIf([W3]=0,0,1) AS T3, 
IIf([W4]=0,0,1) AS T4, 
([W1]+[W2]+[W3]+[W4])/([T1]+[T2]+[T3]+[T4]) AS Gemiddeld
FROM TEST;

[SuperRembo]

In een soortgelijk geval (10x3 kolommen met meetwaarden, spelfouten in de kolomnamen ) heb ik een view gemaakt die er met behulp van unions iets bruikbaars van maakt. Hier zou je zoiets krijgen:

code:

SELECT id, Count(nummer) AS aantal, AVG(waarde) 
FROM (
    SELECT id, 1 as nummer, zone1 AS waarde FROM VochtGegevens WHERE zone1>0
    UNION SELECT id, 2, zone2 FROM VochtGegevens WHERE zone2>0
    UNION SELECT id, 3, zone3 FROM VochtGegevens WHERE zone3>0
    UNION SELECT id, 4, zone4 FROM VochtGegevens WHERE zone4>0
) sub
GROUP BY id