[MySQL] Average van 10 laatste entries

[Cavorka]

Ik ben nu al een tijdje bezig om een query in elkaar te zetten die, naar ik dacht, iets heel simpels moet doen: het gemiddelde berekenen van de 10 rijen uit een table.

Maar, volgens mij kan wat ik wil niet, want welke combinatie van AVG, SUM en COUNT ik ook probeer er komt niet uit wat ik wil.

Tabel:

code:

StatisticsID            int(12)
StatisticsDate          date
StatisticsZoneID        int(9)
StatisticsZoneViews     int(9)
StatisticsZoneClicks    int(9)

Niets bijzonders, het is een tabel AdServer die voor de publicatie van mijn banners op mijn websites zorgt, en dit is dus de tabel die het aantal impressions en click bijhoudt.

Stel dat ik de data van StatisticsZoneID = 9 wil zien dan doe ik:

MySQL:

SELECT *
FROM `statisticszones`
WHERE `StatisticsZoneID` =9
LIMIT 0 , 30

en komt er dit uit

code:

StatisticsID, StatisticsDate, StatisticsZoneID, StatisticsZoneViews, StatisticsZoneClicks
31  2006-09-14  9   65  0
42  2006-09-15  9   9707    0
48  2006-09-16  9   746     0
65  2006-09-17  9   524     0
77  2006-09-18  9   356     0
89  2006-09-19  9   234     0
100     2006-09-20  9   257     0
112     2006-09-21  9   161     0
124     2006-09-22  9   193     0
136     2006-09-23  9   325     0
148     2006-09-24  9   202     0
160     2006-09-25  9   71  0

Hiep hoi, allemaal leuk en aardig. Maar wat moet ik nou doen als ik bijvoorbeeld het gemiddelde van de laatste 5 rijen wil hebben door middel van een query?

Ik dacht dit:

MySQL:

SELECT 
    DAY( `StatisticsDate` ) AS DAY , AVG(`StatisticsZoneViews`) 
FROM 
    `statisticszones`
WHERE 
    `StatisticsZoneID` = 9
GROUP BY 
    Day DESC
ORDER BY 
    `StatisticsID`
LIMIT 
    0 , 5

Maar dan krijg ik gewoon de laatste 5 rijen terug met een gemiddelde van 1 rij en dus gewoon de waarde.

Dit dan:

MySQL:

SELECT 
    DAY( `StatisticsDate` ) AS Day , AVG(`StatisticsZoneViews`)
FROM 
    `statisticszones`
WHERE   
    `StatisticsZoneID` = 9
GROUP BY 
    `StatisticsZoneID` DESC
ORDER BY 
    `StatisticsID`
LIMIT 
    0, 5

Dat levert tenminste wel 1 rij op, maar dat is het gemiddelde van alle rijen en niet van de laatste 5.

Dus: kan het wel wat ik wil? Of zit ik in de verkeerde hoek en moet ik toch met SUM's en COUNT(*)'s gaan klooien? Ik zie het even niet meer, na 1.5 uur gepruts.

PS: Ik kan het natuurlijk wel in PHP gaan doen, maar het lijkt me dat dit toch wel mogelijk moet zijn met MySQL.

[Zyppora]

Misschien zit ik er wel helemaal naast hoor, maar moet je niet gewoon ORDER BY StatisticsDate DESC LIMIT 0, 5 doen?

code:

SELECT
  AVG(StatisticsZoneViews)
FROM
  statisticszones
WHERE
  StatisticsZoneID = 9
ORDER BY
  StatisticsDate
DESC LIMIT 0, 5

Volgens mij ben je het jezelf onnodig moeilijk aan het maken met GROUP BY constructies.

[Cavorka]

Nope, dan krijg je weer het gemiddelde van alle rijen.

Het probleem wat ik ook al tegen kwam is dat die LIMIT helemaal niet gebruikt wordt als er maar 1 resultaat (1 rij) uit komt en je dus waarschijnlijk geen subselecties kan maken, maar alleen over het geheel een AVG kan berekenen.

Zie ik daar subqueries in de verte aan komen fietsen?

*huivert*

[ Voor 11% gewijzigd door Cavorka op 25-09-2006 17:35 ]

[Verwijderd]

Geen flauw idee waarom je 'GROUP BY column DESC' zou willen gebruiken (en ook geen flauw idee waarom MySQL 't pikt, want 't heeft niks met standaard SQL syntax te maken).
Maar in je ORDER BY kun je ook gewoon het kolomnummer opgeven waarop je wilt sorteren. Als je dus wilt sorteren op AVG(`StatisticsZoneViews`), dan wordt dat ORDER BY 2. Daar kun je dan wel eventueel DESC aan toevoegen, en is 't nog zinvol (en valid SQL) ook...

[Annie]

Cavorka schreef op maandag 25 september 2006 @ 17:34:
Zie ik daar subqueries in de verte aan komen fietsen?

In MSSQL zou je bijvoorbeeld iets als onderstaande kunnen doen:

SQL:

SELECT AVG(StatisticsZoneViews) 
FROM (
      SELECT TOP 2 StatisticsZoneViews 
      FROM statisticszones 
      ORDER BY StatisticsID DESC
     ) AS t

[Janoz]

Zolang je niet een subquery kunt gebruiken zoals in Annie's post zul je het gewoon op moeten lossen in je buisness logic (je php bijvoorbeeld). Met een gemiddelde over 10 resultaten moet dan absoluut geen probleem op leveren lijkt me.

[Annie]

Een andere optie is nog werken met een temporary table. Maar dan kan je inderdaad net zo goed het gemiddelde in PHP uitrekenen.

[Cavorka]

Verwijderd schreef op maandag 25 september 2006 @ 21:16:
Geen flauw idee waarom je 'GROUP BY column DESC' zou willen gebruiken (en ook geen flauw idee waarom MySQL 't pikt, want 't heeft niks met standaard SQL syntax te maken).
Maar in je ORDER BY kun je ook gewoon het kolomnummer opgeven waarop je wilt sorteren. Als je dus wilt sorteren op AVG(`StatisticsZoneViews`), dan wordt dat ORDER BY 2. Daar kun je dan wel eventueel DESC aan toevoegen, en is 't nog zinvol (en valid SQL) ook...

Dat was een typo, ik wilde de ordening op de dag hebben uiteraard of de ID, zodat ik de laatste 10 had. Maar indeed: waarom MySQL dat pakt? Geen idee.

Het kan dus niet met een 'normale' query; helaas.

Thanks voor de subquery, Annie. Deze werkt met een kleine mutatie naar MySQL query code perfect.

@Janoz + Annie: Het is uiteraard geen probleem om het op te lossen met PHP, maar ik dacht dat het toch met een (non-sub) query zou kunnen. Bah!

Thanks voor het meedenken, allen.