[SQL] Trend uitrekenen?

[Dontwan]

Hallo allemaal,

Zelf ben ik "nog" geen superheld in SQL maar ik begin het steeds meer te leren. Echter kom ik er maar niet uit bij één query. Misschien dat jullie mij kunnen helpen.

Ik ben bezig met het maken van een grafiek in PHP met combinatie van een SQL database.
In deze grafiek wil ik elke week het gemiddelde weergeven met een trendlijn, het gemiddelde lukt me, echter kom ik er maar niet uit hoe je de trend kan berekenen in SQL.

Mijn tabel heet dbo.waardes_chart_data en ziet er als volgt uit:

Weeknummer	Trend	Gemiddelde
1		1
2		1
3		2
4		5
5		1

etc. tot week 53

Het tabel zal er uiteindelijk als volgt uit moeten komen te zien:

Weeknummer	Trend	Gemiddelde
1	1	1
2	1	1
3	1,33	2
4	2,25	5
5	2	1

Is er iemand die mij hiermee kan helpen?
Alvast bedankt!

[Knutselsmurf]

Hoe zou je, zonder SQL, de trend uitrekenen? Als je die berekening weet, kun je stappen nemen om dat om te zetten naar een SQL-query

[Woy]

In deze case lijkt het mij makkelijker om de trend pas aan de consumerende kant van de data met procedurele code op te lossen.

Het lijkt erop dat je nu gewoon alle voorgaande elementen sommeert, en deelt door het aantal elementen.

Als je het perse in SQL wilt doen zul je iets met een correlated sub-query moeten doen.

[Dontwan]

Knutselsmurf schreef op maandag 21 december 2015 @ 15:43:
Hoe zou je, zonder SQL, de trend uitrekenen? Als je die berekening weet, kun je stappen nemen om dat om te zetten naar een SQL-query

Zonder SQL zal ik de berekening zo uitvoeren:

Week 1: Gemiddelde van week 1 / 1
Week 2: (Gemiddelde van week 1 + Gemiddelde van week 2) / 2
Week 3: (Gemiddelde van week 1 + Gemiddelde van week 2 + Gemiddelde van week 3) / 3

etc.
Als ik dit omzet naar een query, dan ben ik 53 regels verder.. er moet een makkelijker manier zijn.

[Janoz]

Het lijkt me makkelijker om de trend gewoon in php te berekenen. Databases zijn goed in het opslaan en teruggeven van relationele data met daarbij mogelijk een stuk aggregatie. Een trend berekening is echter niet een aggregatie. Het is veel simpeler om die berekening te doen op het moment dat je je resultset aan het uitlezen bent. Dan is het slechts 1 of 2 regeltjes code ipv een onbegrijpelijke bak SQL.

[Rannasha]

Ik zou dit overigens niet echt een trend willen noemen. Bij een trend denk ik eerder aan iets als een meebewegend gemiddelde (moving average). Dit is eerder een year-to-date waarde van het gemiddelde.

Verder ben ik het met Janoz eens dat dit waarschijnlijk makkelijker te doen is buiten de database om.

[pedorus]

De antwoorden hierboven kloppen, maar mocht het perse in SQL moeten dan kan het met een zogenaamde self join. Iets als

SQL:

select a.weeknummer, avg(b.gemiddelde) as trend, a.gemiddelde from dbo.waardes_chart_data as a 
inner join dbo.waardes_chart_data as b on b.weeknummer <= a.weeknummer 
group by a.weeknummer, a.gemiddelde

", a.gemiddelde" op het einde kan weg als a.weeknummer een key is, afhankelijk van het dialect.

[Gomez12]

pedorus schreef op maandag 21 december 2015 @ 19:53:
De antwoorden hierboven kloppen, maar mocht het perse in SQL moeten dan kan het met een zogenaamde self join.

Hmm, altijd leuk om te zien hoe mensen het kunnen oplossen.

Maar ik zou zeggen werk hem eens voor 53 weken uit en het is redelijk evident waarom het afgeraden wordt.

Mocht het echt op de dbase server moeten gebeuren dan moet je alsnog geen sql gebruiken, maar simpelweg gaan kijken naar t-sql of het oracle/postgresql equivalent daarvan. Die zijn juist gemaakt voor dit soort dingen (loopjes over sql-code)

[pedorus]

Als je een rolling average van 53 weken bedoelt dan wordt dat vervelend ja, als we data hebben over slechts 1 jaar dan werkt die query gewoon. Maar eigenlijk geeft het datamodel te denken als je er maar 1 jaar in kan opslaan (tenzij dit een view is, maar dat zal wel niet).

Ik kijk de OP net even door en zie dat er PHP genoemd wordt. Dit is dan natuurlijk de plek waarin het moet worden opgelost.

Als ik die query tegenkwam zou ik hem dus om twee redenen afwijzen

[jlrensen]

Als je mssql 2012 of recenter gebruikt, kun je ook kijken naar de "lag" functie. Daarmee kun je een waarde uit het bovenliggende record halen. Dan sla je bij trend het totaal op, en deel je door het weeknummer in de query. De nieuwe totaalwaarde wordt dan je weekwaarde plus de totaalwaarde van het vorige record.

[4Real]

Ik zou al geen gemiddelde opslaan, want je kunt nooit terug naar de individuele meetpunten. Daarnaast moet je jezelf de volgende vragen eens stellen: Hoe ga je om met trendanalyses over verschillende tijd-periodes? Of trendanalyses met meerdere dimensies (verkopen over x weken waarbij provincies y en z met elkaar worden vergeleken? Wat als je data een niet linieare trend volgt?

[Croga]

4Real schreef op vrijdag 25 december 2015 @ 09:18:
Ik zou al geen gemiddelde opslaan, want je kunt nooit terug naar de individuele meetpunten.

^^ dit.
Dit is geen vraagstuk voor een relationele database, dit is een BI vraagstuk. Zet er dan ook een bijbehorende BI database achter zodat je analyse tool met de juiste gegevens dit soort informatie kan ophoesten.

[MSalters]

Gomez12 schreef op maandag 21 december 2015 @ 21:40:
[...]

Maar ik zou zeggen werk hem eens voor 53 weken uit en het is redelijk evident waarom het afgeraden wordt.

(52 × trend van vorige week + nieuwe waarde )/ 53. Simpel.

De echte truc is een week 0 invoegen met trend 0 zodat week 1 dezelfde formule kan gebruiken

[Morrar]

Waarom sla je niet iedere week ook het cumulatieve gemiddelde op? Dus bij week 2 het (cumulatieve) gemiddelde van week 1 + het gemiddelde van week 2. Bij week 3 het cumulatieve gemiddelde van week 2 + het gemiddelde van week 3. Enzovoorts. De tabel wordt dan:

Weeknummer	Gemiddelde	Cum. Gemiddelde
1	1	1
2	1	2
3	2	4
4	5	9
5	1	10

De trend kun je vervolgens heel simpel berekenen door het cumulatieve gemiddelde van een week te delen door het weeknummer. Die hoef je dus niet op te slaan:

SELECT cum_gemiddelde / weeknummer AS trend FROM table;

Overigens weet ik niet wat je met de trend zou willen; zou je niet een time frame erbij nemen zodat het een echt moving average wordt of wil je echt alle gemiddelden even zwaar laten meewegen?

En zo ja, is het niet beter om echt een linear model te fitten door alle gemiddelden? Bij veel data zou je in dat geval eens naar Spark kunnen kijken, kun je het in een cluster doen waardoor het lineair schaalt.

[ Voor 25% gewijzigd door Morrar op 26-12-2015 21:49 ]

[Fiander]

Sorry dat ik nu pas reageer, maar het is vrijdag middag, en ik verveelde me en keek voor het eerst in maanden weer eens hier op het forum.

Geen idee ofdat je MSSQL2012 of nieuwe gebruikt, daar is nog geen antwoord op geweest, maar in dat geval heb je de beschikking over het OVER commando (windowed functie werd al eerder genoemt)

CREATE TABLE #T (Id INT, Gemmidelde FLOAT);
INSERT INTO #T ( Id, Gemmidelde ) VALUES ( 1,1),(2,1),(3,2),(4,5),(6,1);

SELECT
Id,
Gemmidelde,
AVG(Gemmidelde)
OVER (
ORDER BY Id
ROWS BETWEEN 53 PRECEDING AND CURRENT ROW
) AS Trend
FROM #T;

DROP TABLE #t;

Success, en sry voor het grafgraven en oprakelen oude topic

[ Voor 0% gewijzigd door Fiander op 19-02-2016 19:27 . Reden: typo ]