[MySQL] Reacties per week, ook 'lege' weken

[Fl4sh3r]

Hallo allemaal,

Het is alweer even geleden dat ik hier een vraag gepost heb, maar nu loop ik toch echt weer eens vast met mijn beperkte MySQL kennis.

Het probleem is als volgt:
Voor een website wil ik een leuk grafiekje tekenen met een overzicht van het aantal reacties, dit gaat allemaal redelijk goed. Waar het fout gaat is dat ik geen weken uit mijn query krijg waarin geen enkele reactie is geplaatst.

De huidige query:

SQL:

SELECT
    COUNT(`id`) AS reactions,
    DATE_FORMAT(`datetime`, '%V') AS weeknr,
    DATE_FORMAT(`datetime`, '%X') AS yearnr
FROM `reactions`
WHERE
    `datetime` > DATE_SUB(NOW(), INTERVAL 1 YEAR)
GROUP BY weeknr, yearnr
ORDER BY yearnr ASC, weeknr ASC

Het resultaat hiervan is ongeveer als volgt:

reactions	weeknr	yearnr
4	15	2006
12	16	2006
12	17	2006
7	18	2006
4	19	2006
7	20	2006
14	21	2006
20	22	2006
16	23	2006
25	24	2006
23	25	2006
31	26	2006
41	27	2006
34	28	2006
15	29	2006
4	30	2006
12	31	2006
11	32	2006
23	33	2006
1	46	2006
1	48	2006
4	01	2007

Zoals je ziet ontbreken er weken tussen week 33 - 46, week 47 en week 49 tot en met 53.

Hoe zou ik de query kunnen maken zodat alle weken van een jaar erin zitten, en voor de weken waarin er geen reacties zijn er 0 komt te staan bij aantal reacties?

[P_de_B]

Een tabel maken met alle weeknummers, en een join met deze tabel maken.

SQL:

SELECT [..]
FROM NieuweTabelMetWeeknummers w 
LEFT OUTER JOIN reactions r ON w.WeekNummer = r.Weeknummer

[newpegasus]

Zoek eens op een Outer Join in SQL.

[Woy]

Waar gebruik je het in? Als je het in een php script gebruikt kun je er toch gewoon van uit gaan dat als een week niet voorkomt dat het 0 reacties zijn. Omdat je recordset toch al geordend is op weeknr zal dit waarschijnlijk het makkelijkst zijn.

[Cyphax]

Misschien is dit handiger:

IFNULL()

The Final function we will look at is IFNULL and unsurprisingly this is a very simple syntax that is similar to IF(). The difference is that that instead of there being TRUE and FALSE return values based on a condition, the original value is returned if it is not NULL and a different new value is returned if it is NULL.

Syntax:

IFNULL(original_value, new_value)

To quickly demonstrate test one query with a NULL value and another with a real value.

code:

mysql> SELECT IFNULL(NULL,'The value is Null');
+----------------------------------+
| IFNULL(NULL,'The value is Null') |
+----------------------------------+
| The value is Null                |
+----------------------------------+
1 row in set (0.00 sec)

Bron

Moet je even kijken of je versie die functie ondersteunt. Zo ja, dan is dit ws de makkelijkste manier. Ik heb eenzelfde probleem zo opgelost met SQL Server alleen heet de functie daar IsNull().

Zorg ook dat je NULLs uberhaupt terugkrijgt.. zou moeten lukken als je je where clause aanpast (or datetime is null?)

[ Voor 16% gewijzigd door Cyphax op 15-01-2007 14:46 ]

[xos]

@cyphax, als ik het goed heb begrepen bevat de reactions tabel geen records in de missende weken. Dus dan krijg je ook geen null waarde of iets terug welke je in de ifnull functie kan proppen.

Ik zou het doen zoals P_de_B met het verschil dat ik een right join zou gebruiken in dit geval omdat de reactions tabel "leidend" is. Maar dat is een kwestie van smaak.

code:

select count(*), w.weeknr
  from reactions r
 right join weeknummers w on r.weeknummer = w.weeknr
 group by w.weeknr

[Dido]

offtopic:
Week 53 van 2006 lijkt me niet echt ontbreken, trouwens? Ik heb iig geen week 53 gezien vorig jaar.

[Fl4sh3r]

Ik heb het nu inderdaad maar in mn PHP code opgelost.

De reden dat ik dit niet wilde is het verschil tussen de weeknummers bij MySQL en PHP.
Beide hebben aardig wat manieren om weeknummers op te vragen, weken beginnen op zondag VS weken beginnen op maandag en dat soort dingen.

Ik dacht, als ik nu alles in MySQL doe dan werken ze iig met dezelfde weeknummers.

In MySQL gebruik ik nu '%u' (Week (00..53), where Monday is the first day of the week) en in PHP 'W' (ISO-8601 weeknummer van het jaar, weken beginnend op maandag (toegevoegd in PHP 4.1.0))

Bedankt voor jullie suggesties.
Als iemand nog een MySQL oplossing heeft waarbij ik niet handmatig een tabel met weeknummers hoef te maken heb ik nog wel intresse.

[P_de_B]

Ehm, waarom heb je het nu in PHP opgelost? Alleen omdat je een tabel moet maken? Ik zou nog wel een suggestie uit m'n mouw kunnen schudden zonder gebruik van een extra tabel, maar die zal hoogstwaarschijnlijk een stuk minder goed zijn, dus waarom je dat wil?

Sowieso komt een tabel met oplopende nummers van 1 tot xxxx wel vaker van pas.