Functie AVG met weeknummers
Pagina: « vorige 1 2 3 4 volgende »
Pas nadat Pg Vincent er een opmerking over had gemaakt.
Het is overigens geen 'aanval' mijnerzijds, maar meer bedoeld als stimulans om soms even verder te vragen op het moment dat een vraagstelling riekt naar een vreemde achterliggende gedachte.
Database voorbeeld
Week1 Datum1 Jaar1 Week2 Datum2 KlantID
201640 2016-10-05 2016 201702 2017-01-11 2
201640 2016-10-05 2016 201602 2016-01-13 2
SELECT avg(week(datum1)),avg(week(datum2)) WHERE Jaar1 = '2016' AND KlantID='2' GROUP BY Jaar1
Wat de bedoeling is om het 'gemiddelde' (misschien is gemiddelde niet het juiste woord maar hoe zou je het anders moeten noemen) te krijgen van de week 1 velden en de week 2 velden, maar wel over het jaar genomen. Ik krijg nu een gemiddelde waarde maar ik wil een 'jaar weeknummer' (201605) terug krijgen.
er zitten dus ook nog 2 datums/weken in 1 record. En daarvan wil je het gemiddelde hebben?
SELECT ROUND((Week1 + Week2) /2) FROM tabel WHERE YEAR(datum1) = 2016 AND klantID = 2
Niet dat ik snap op wat voor manier dit getal bruikbaar is.
201601 + 201603 = 403204
dat delen door 2 levert 201602
Maar
201552 + 201601 levert 403153
delen door 2 levert je 201577
Als jij er iets mee kunt, prima.
Maar zoals al gesteld: het zou handig zijn om voor de reageerders op z'n minst een poging te doen om te antwoorden op de vraag wat je hier in hemelsnaam mee moet....
Gewijzigd op 17/10/2016 10:22:37 door Ivo P
:-) gewoon doen, anders krijg je nooit een goed antwoord.
"Wat de bedoeling is om het 'gemiddelde' (misschien is gemiddelde niet het juiste woord maar hoe zou je het anders moeten noemen)"
En daar zit het probleem; ik kan me geen beeld vormen van wat je bedoelt met "gemiddelde week". Een weeknummer is een datum en zoals anderen al hebben uitgelegd is een gemiddelde datum feitelijk een loze kreet. Je vraagt immers eigenlijk gewoon wat het gemiddelde is van "maandag,woensdag en donderdag", of "wat is het gemiddelde van parijs en londen"? Je kunt wel vragen om het gemiddelde aantal inwoners van prijs en londen, en je kunt vragen om het gemiddelde aantal verkochtte bioscoopkaartjes op maandag, woensdag en donderdag, maar "het gemiddelde van twee weken" zegt mij (en de rest) niets. En daarom vermoed ik dat je ergens een denkfoutje maakt...
Kun je een concreet voorbeeld geven van de brondata die je hebt en de uitslag die je wilt krijgen? (mag verzonnen data zijn uiteraard, als het maar de vorm heeft die je gebruikt) en dan ook graag een uitleg van waar je die data voor gebruikt?
----
Zo maar een idee he.
Stel dat het achterliggende probleem is, dat je een oplossing zoekt voor "op welke datum stuur ik een mailtje aan de klant dat het project halverwege is?"
Dan zou je moeten weten wat de lengte van de periode is tussen de 2 datums (in dagen.
Deel die waarde door 2 en tel dat op bij de begindatum.
Dan heb je een nieuwe datum. En daarvan bepaal je het weeknummer.
Dus
Code (php)
1
2
3
4
5
2
3
4
5
SELECT ROUND(DATEDIFF(datum1, datum2) /2); -- midden van de periode
SELECT datum1 + INTERVAL ROUND(DATEDIFF(datum1, datum2) /2) DAY ; -- de datum van die dag
SELECT YEARWEEK(datum1 + INTERVAL ROUND(DATEDIFF(datum1, datum2) /2) DAY); -- het weeknummer van die datum
SELECT datum1 + INTERVAL ROUND(DATEDIFF(datum1, datum2) /2) DAY ; -- de datum van die dag
SELECT YEARWEEK(datum1 + INTERVAL ROUND(DATEDIFF(datum1, datum2) /2) DAY); -- het weeknummer van die datum
Database voorbeeld
Week1 Datum1 Jaar1 Week2 Datum2 KlantID
201601 2016-01-06 2016 201623 2016-06-08 28
201602 2016-01-13 2016 201627 2016-07-06 28
Stap 1
GEM (2016-01-06+2016-01-13)2016-01-9.5 --> gemiddelde datum1 --> 2016-01-09 (1 september 2016 =201635)
Stap 2
GEM (2016-06-08+2016-07-06)= 2016-06-12 --> gemiddelde datum2 12 juni 2016 = 201623 (deze datum is even gegokt.
Het gaat er dus om dat de query een 'gemiddelde' datum1 terug geeft van datum1. En voorkeur in de vorm van een weeknummer.
Hetzelfde was de bedoeling van datum2.
De gegevens groepeer ik overigens via een group by. Zie mijn eerdere antwoorden
Gewijzigd op 17/10/2016 12:27:32 door Piet Knaap
Bedenk verder dat het verschil tussen twee datums geen datum is, maar een interval of een periode in ISO-termen. Denk bij de vorige gevallen bijvoorbeeld aan een betaaltermijn en een levertermijn. Dit verschil zie je in PHP bijvoorbeeld terug in de klassen DateTime en DateInterval. Dat komt misschien van pas als je niet de "gemiddelde datum" maar meer de gemiddelde afwijking van een datum zoekt.
Leg eens in Jip-en-Janneke-taal uit wat datum 1 en datum 2 zijn?
Wat gebeurt er op datum 1, en wat gebeurt er op datum 2?
En wat gebeurt en volgens jou in die "gemiddelde week" die jij probeert uit te rekenen?
Datum 2 is de afleverdag/afleverweek
Wat we willen is de gemiddelde inkoopdag (dus alle dagen opgeteld en dan het gemiddelde hieruit teruggeven in de SQL query) en exact hetzelfde met de gemiddelde afleverdag.
En dan uitrekenen wat het verschil is (in weken). Ze kunnen dan zeggen in periode 1 duurt het gemiddeld 20 weken maar in periode 2 duurt het gemiddeld 14 weken. En over een jaar genomen duurt het gemiddeld 18 weken
Ze werken bij ons met de tijdnotitie 201601 als week 1 of 201640 als week 40 van het jaar 2016.
Dan lijkt het me eenvoudiger om eerst per record te bepalen wat de leverduur is.
Dat is bijvoorbeeld 15 dagen. DATEDIFF(leverdatum, besteldatum)
Dan neem je over een bepaalde periode (jaar) al die leverduren. En daar kun je een gemiddelde van bepalen.
Toevoeging op 17/10/2016 13:36:07:
En die query kun je dan voor zomerperiode (20 juni / 20 sept of zo) of kerstdrukte (1 dec/31 dec) aanpassen.
Ik zou zeker voor de berekeningen de weeknotaties loslaten. Hooguit in je rapport gebruiken.
Maar een bestelling op zondagavond, geleverd om maandagochten 10:30 zou anders een verschil van 1 week opleveren. (of je moet het over leveringen vanuit China per boot hebben natuurlijk)
>> Wat we willen is de gemiddelde inkoopdag (dus alle dagen opgeteld en dan het gemiddelde hieruit teruggeven in de SQL query) en exact hetzelfde met de gemiddelde afleverdag.
Dit lijkt me niet correct. Een gemiddelde inkoopdag wil zeggen een dag waarop gemiddeld alles wordt ingekocht. Zoiets bestaat niet.
Ik neem aan dat wat je wilt weten de gemiddelde tijdsduur is tussen het moment van inkoop en aflevering. Anders gezegd, je wilt weten hoeveel weken het gemiddeld duurt vanaf het moment van aankoop tot het moment van levering. Correct?
Ze willen als uitkomt van deze twee waarde 201609 terug hebben
201606 + 201612 = 201609
Gewijzigd op 17/10/2016 13:39:43 door Piet Knaap
"En dan uitrekenen wat het verschil is (in weken). Ze kunnen dan zeggen in periode 1 duurt het gemiddeld 20 weken maar in periode 2 duurt het gemiddeld 14 weken. En over een jaar genomen duurt het gemiddeld 18 weken "
De gemiddelde levertijd is het gemiddelde van de levertijden, niet de tijd tussen twee gemiddelde bestel- en leverdatums. Dat is wiskundig echt heel wat anders.
Gelukkig kan SQL dit helemaal zelf, je kunt datums gewoon van elkaar aftrekken en daar het gemiddelde van nemen:
SELECT AVG(leverdatum - besteldatum)
FROM tabel
WHERE besteldatum BETWEEN x AND y;
De BETWEEN bepaalt dan hoe groot de periode moet zijn waarover je rapporteert.
201606 + 201612 = 201609
of nog leuker
201652 + 201702 = 201701
Gewijzigd op 17/10/2016 13:49:30 door Piet Knaap
>> 201606 + 201612 = 201609
Ik weet niet wie "ze" zijn, maar dit klopt helemaal niet. Dit slaat nergens op.
Ik bestel in juni. Ik krijg geleverd in december. Gemiddeld heb ik het product in september gekregen???
* koekoek *
Terug naar de tekentafel :-)
Ozzie PHP op 17/10/2016 13:50:01:
>> Ze willen als uitkomt van deze twee waarde 201609 terug hebben
>> 201606 + 201612 = 201609
Ik weet niet wie "ze" zijn, maar dit klopt helemaal niet. Dit slaat nergens op.
Ik bestel in juni. Ik krijg geleverd in december. Gemiddeld heb ik het product in september gekregen???
* koekoek *
Terug naar de tekentafel :-)
>> 201606 + 201612 = 201609
Ik weet niet wie "ze" zijn, maar dit klopt helemaal niet. Dit slaat nergens op.
Ik bestel in juni. Ik krijg geleverd in december. Gemiddeld heb ik het product in september gekregen???
* koekoek *
Terug naar de tekentafel :-)
Het gaat om levende producten :) Koekoek is toch een vogel. Het gaat erom dat ze dan weten wat de meest ideale week is om de koekoek te gaan produceren
Code (php)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
SELECT
DATE_FORMAT(inkoopdatum, '%Y%u') AS jaar_en_weeknummer,
AVG(DATEDIFF(afleverdatum, inkoopdatum) / 7) AS gemiddelde_levertijd_in_weken
FROM
inkooporders
WHERE
inkoopdatum IS NOT NULL
AND
afleverdatum IS NOT NULL
GROUP BY
jaar_en_weeknummer
DATE_FORMAT(inkoopdatum, '%Y%u') AS jaar_en_weeknummer,
AVG(DATEDIFF(afleverdatum, inkoopdatum) / 7) AS gemiddelde_levertijd_in_weken
FROM
inkooporders
WHERE
inkoopdatum IS NOT NULL
AND
afleverdatum IS NOT NULL
GROUP BY
jaar_en_weeknummer
Ozzie PHP op 17/10/2016 13:54:50:
Leg het nu eens uit met een reëel concreet voorbeeld, want ik snap nu echt niet wat je wilt bereiken.
duidelijker dan wat is het gemiddelde van 201606 + 201612 of 201652 + 201702 zou ik niet meer weten
Pagina: « vorige 1 2 3 4 volgende »
Dit topic is gesloten.