aantal rijen per week tellen
Voor een online boekingssysteem ben ik een grafiek aan het maken met het aantal boekingen per jaar. Hierdoor kan inzicht verkregen worden of de boekingen van het nieuwe jaar op schema liggen.
Nu vraagt de grafiek om een xml formaat waar ik per week het aantal boekingen wil 'invullen'. ue.datum is opgeslagen als DATETIME formaat.
Per week draai ik de volgende query:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?php
$weken = range(1,53);
$jaar = 2013;
$aantal = 0;
foreach($weken as $week):
$sql = '
SELECT
COUNT(ue.id) AS aantal
FROM
user_event AS ue
INNER JOIN
event AS e ON ue.event = e.id
WHERE
(DATE_FORMAT(e.start, \'%Y\') = '.$jaar.')
AND
(DATE_FORMAT(ue.datum, \'%V\') = '.$week.')';
//query uitvoeren
//row ophalen en bij aantal optellen dus bijvoorbeeld: $aantal = $aantal + $row;
endforeach;
?>
$weken = range(1,53);
$jaar = 2013;
$aantal = 0;
foreach($weken as $week):
$sql = '
SELECT
COUNT(ue.id) AS aantal
FROM
user_event AS ue
INNER JOIN
event AS e ON ue.event = e.id
WHERE
(DATE_FORMAT(e.start, \'%Y\') = '.$jaar.')
AND
(DATE_FORMAT(ue.datum, \'%V\') = '.$week.')';
//query uitvoeren
//row ophalen en bij aantal optellen dus bijvoorbeeld: $aantal = $aantal + $row;
endforeach;
?>
Als ik ter controle het volgende doe krijg ik net iets meer resultaten.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
<?php
$sql = '
SELECT
COUNT(ue.id) AS aantal
FROM
user_event AS ue
INNER JOIN
event AS e ON ue.event = e.id
WHERE
(DATE_FORMAT(e.start, \'%Y\') = '.$jaar.')';
?>
$sql = '
SELECT
COUNT(ue.id) AS aantal
FROM
user_event AS ue
INNER JOIN
event AS e ON ue.event = e.id
WHERE
(DATE_FORMAT(e.start, \'%Y\') = '.$jaar.')';
?>
En dat kan niet de bedoeling zijn, op beide methoden moet ik evenveel boekingen kunnen behalen in dat bepaalde jaar. Kan iemand misschien een tip geven waarom ik cumulatief per week minder resultaten heb dan wanneer ik het allemaal in één keer selecteer? Ik ben aardig door het ISO date format gestruind en dan zie je dat ze een week 53 gebruiken. Verder zie ik geen bijzonderheden. Iemand een tip voor een andere aanpak of een effectieve methode?
Crispijn
edit
Ik bouw het hele project in Zend Framework en het totaal beslaat aardig wat code dat hier niet relevant is volgens mij, dit is de basis opzet die volgens mij ergens fout gaat. Mocht er behoefte zijn aan meer broncode dan hoor ik het graag!
Gewijzigd op 16/09/2013 20:32:15 door Crispijn -
waarom gebruik je niet gewoon WEEK(ue.datum) in plaats van DATE_FORMAT(ue.datum, \'%V\')?
en zo ook:
YEAR(e.start) in plaats van DATE_FORMAT(e.start, \'%Y\')
Toevoeging op 16/09/2013 20:46:40:
wat is het verschil tussen ue.datum en e.start?
waarschijnlijk zit daar het probleem in bovenste query. het lijkt mij consequenter als je twee keer dezelfde gebruikt.
Gewijzigd op 16/09/2013 20:48:03 door Frank Nietbelangrijk
Code (php)
1
2
3
4
2
3
4
SELECT WEEK(e.start) AS weeknummer, COUNT(e.start) AS aantal
FROM user_events
GROUP BY WEEK(e.start)
ORDER BY WEEK(e.start) ASC
FROM user_events
GROUP BY WEEK(e.start)
ORDER BY WEEK(e.start) ASC
Dus een COUNT op de week en dan een GROUP BY op dezelfde dag/week.
Ik weet niet of het gelijk zo werkt, maar 't is een opzetje.
Toevoeging op 16/09/2013 20:52:38:
Code (php)
1
2
3
4
2
3
4
SELECT WEEK(e.start) AS weeknummer, COUNT(e.start) AS aantal
FROM user_events
GROUP BY WEEK(e.start)
ORDER BY WEEK(e.start) ASC
FROM user_events
GROUP BY WEEK(e.start)
ORDER BY WEEK(e.start) ASC
Dus een COUNT op de week en dan een GROUP BY op dezelfde dag/week.
Ik weet niet of het gelijk zo werkt, maar 't is een opzetje.
Goede tip van het dateformat, die gebruik ik eigenlijk altijd, ik zal er in de toekomst op letten.
e.start is de start van het event
ue.datum is de datum van de boeking (relatie user_event)
Men kan zich ook vanaf week 40 in het jaar daarvoor opgeven dus mijn voorbeeld was niet helemaal compleet.
Dit is dan meer volgens mijn echte broncode
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?php
$weken = range(-12,40);
$jaar = 2013;
$aantal = 0;
foreach($weken as $key):
$week = ($key < 1) ? 53 + $key : $key;
$boekjaar = ($key < 1) ? $jaar - 1 : $jaar;
$sql = '
SELECT
COUNT(ue.id) AS aantal
FROM
user_event AS ue
INNER JOIN
event AS e ON ue.event = e.id
WHERE
(YEAR(e.start) = '.$jaar.')
AND
(WEEK(ue.datum) = '.$week.')
AND
(YEAR(ue.datum) = '. $boekjaar.')';
//query uitvoeren
//row ophalen en bij aantal optellen dus bijvoorbeeld: $aantal = $aantal + $row;
endforeach;
?>
$weken = range(-12,40);
$jaar = 2013;
$aantal = 0;
foreach($weken as $key):
$week = ($key < 1) ? 53 + $key : $key;
$boekjaar = ($key < 1) ? $jaar - 1 : $jaar;
$sql = '
SELECT
COUNT(ue.id) AS aantal
FROM
user_event AS ue
INNER JOIN
event AS e ON ue.event = e.id
WHERE
(YEAR(e.start) = '.$jaar.')
AND
(WEEK(ue.datum) = '.$week.')
AND
(YEAR(ue.datum) = '. $boekjaar.')';
//query uitvoeren
//row ophalen en bij aantal optellen dus bijvoorbeeld: $aantal = $aantal + $row;
endforeach;
?>
Mijn controle query is dan deze:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
<?php
$sql = '
SELECT
COUNT(ue.id) AS aantal
FROM
user_event AS ue
INNER JOIN
event AS e ON ue.event = e.id
WHERE
(YEAR(e.start) = '.$jaar.')';
?>
$sql = '
SELECT
COUNT(ue.id) AS aantal
FROM
user_event AS ue
INNER JOIN
event AS e ON ue.event = e.id
WHERE
(YEAR(e.start) = '.$jaar.')';
?>
Gewijzigd op 16/09/2013 21:02:12 door Crispijn -
Als ik jouw vraag goed begrepen heb wil je een rijtje van 53 weken van één kalenderjaar waarin per week staat hoeveel accommodaties? er verhuurd zijn.
Als dat klopt dan mis ik eigenlijk nog een einddatum, iets als e.end. want als je dat er bij betrekt kun je zoiets maken als Eddy aangeeft en kun je ook de de accommodaties meetellen die nog in het vorige kalenderjaar gestart zijn met de verhuur. (immers je hebt dan ook een einddatum).
Gewijzigd op 16/09/2013 21:18:07 door Frank Nietbelangrijk
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
w.weeknr,
COUNT(e.id) AS aantal
FROM
(SELECT
@weeknr := @weeknr + 1 weeknr
FROM
table_with_more_than_53_rows, (SELECT @weeknr:= 0) uservar
LIMIT 53
) w
LEFT JOIN
events e
ON WEEK(e.datum, 3) = w.weeknr
WHERE
e.datum BETWEEN '2013-01-01' AND '2013-12-31'
AND w.weeknr <= WEEK('2013-12-31', 3)
GROUP BY w.weeknr
w.weeknr,
COUNT(e.id) AS aantal
FROM
(SELECT
@weeknr := @weeknr + 1 weeknr
FROM
table_with_more_than_53_rows, (SELECT @weeknr:= 0) uservar
LIMIT 53
) w
LEFT JOIN
events e
ON WEEK(e.datum, 3) = w.weeknr
WHERE
e.datum BETWEEN '2013-01-01' AND '2013-12-31'
AND w.weeknr <= WEEK('2013-12-31', 3)
GROUP BY w.weeknr
Gewijzigd op 16/09/2013 21:30:38 door Ger van Steenderen
Ik ben een klein stukje verder met de opzet van jou die wel erg charmant is. Momenteel zie ik het nog niet helemaal maar heb ik dit:
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT YEAR( ue.datum ) AS jaarnummer, WEEK( ue.datum ) AS weeknummer, COUNT( ue.id ) AS aantal
FROM user_event AS ue
WHERE YEAR( ue.datum )
IN ( 2012, 2013 )
GROUP BY YEAR( ue.datum ) , WEEK( ue.datum )
ORDER BY YEAR( ue.datum ) , WEEK( ue.datum ) ASC
FROM user_event AS ue
WHERE YEAR( ue.datum )
IN ( 2012, 2013 )
GROUP BY YEAR( ue.datum ) , WEEK( ue.datum )
ORDER BY YEAR( ue.datum ) , WEEK( ue.datum ) ASC
In phpmyadmin zie ik dat ik dan ook een nulde week heb in 2013, wel vreemd niet?
Ik zie dat dit klopt! Super, nu alleen even fixen dat de weken die 'ontbreken' 0 geven en dan kan ik een csv file genereren en de grafiek maken.
Bedankt voor jullie expertise heren!
Crispijn
Toevoeging op 16/09/2013 21:41:48:
@Ger,
De query die je schrijft snap ik niet helemaal. Maak je nu een virtuele tabel aan met meer dan 53 rijen? Kan je een kleine toelichting geven?
Gewijzigd op 16/09/2013 21:32:17 door Crispijn -
Dat is dan een (derived) tabel met 1 kolom.
Daarna ga je die weeknrs op je events joinen op het weeknr.
Vanwege de left join krijgen de weken waarin geen event plaats vindt 0 als aantal, want ik tel op e.id en lege kolommen worden niet mee geteld.
Gewijzigd op 16/09/2013 21:58:10 door Ger van Steenderen
Wat doen die apenstaartjes in je tabel? Dit is echt compleet nieuw voor me, ben alleen maar erg bekend met een hele (basic) basis zo te merken...
Deze uservars bestaan in het geheugen van de database server zolang de connectie actief is, en zijn per connectie.
In mijn eerdere voorbeeld wordt die geset via de
Code (SQL)
1
.. FROM ....(SELECT @weeknr:= 0) uservar
Daarna wordt met:
Code (SQL)
1
2
3
2
3
SELECT
@weeknr := @weeknr + 1 weeknr
FROM ...
@weeknr := @weeknr + 1 weeknr
FROM ...
de waarde elke keer met 1 opgehoogd zolang de tabel in de FROM rijen heeft, en daarom moeten er minimaal 53 in die tabel zijn. Dat kan dus elke tabel zijn als ie maar min. 53 rijen heeft.
Gewijzigd op 17/09/2013 20:32:24 door Ger van Steenderen
Gek genoeg als ik er deze query van maak zou ik resultaten met aantal = 0 terug moeten krijgen. Dat is echter niet het geval...
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT YEAR( ue.datum ) AS jaarnr, w.weeknr, COUNT( ue.id ) AS aantal
FROM (
SELECT @weeknr := @weeknr +1 weeknr
FROM user, (
SELECT @weeknr :=0
)uservar
LIMIT 53
)w
LEFT JOIN user_event AS ue ON WEEK( ue.datum, 3 ) = w.weeknr
LEFT JOIN event AS e ON ue.event = e.id
WHERE YEAR( ue.datum )
IN ( 2012, 2013 )
AND YEAR( e.start ) =2013
GROUP BY w.weeknr
ORDER BY w.weeknr
FROM (
SELECT @weeknr := @weeknr +1 weeknr
FROM user, (
SELECT @weeknr :=0
)uservar
LIMIT 53
)w
LEFT JOIN user_event AS ue ON WEEK( ue.datum, 3 ) = w.weeknr
LEFT JOIN event AS e ON ue.event = e.id
WHERE YEAR( ue.datum )
IN ( 2012, 2013 )
AND YEAR( e.start ) =2013
GROUP BY w.weeknr
ORDER BY w.weeknr
Enig idee wat er fout gaat? Ook als ik de query helemaal uitkleed geeft hij dit resultaat (zonder de join event)?
Gewijzigd op 19/09/2013 08:40:32 door Crispijn -
Code (SQL)
1
WHERE ue.datum BETWEEN "2013-01-01" AND "2013-12-31"
Ik weet niet welke data er in de gejoinde tabellen staat dus het is een beetje lastig te beoordelen waar het fout gaat.
ue.datum is namelijk NULL als niet aan de join voorwaarde wordt voldaan.
Dus je moet met een subquery een inline view maken:
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
...
) w
LEFT JOIN
(SELECT WEEK(ev.datum) weeknr, YEAR(ev.datum) jaarnr
FROM events e
INNER JOIN user_event ev ON e.id = ev.event
WHERE ev.datum BETWEEN "2013-01-01" AND "2013-12-31") ue
USING (weeknr)
) w
LEFT JOIN
(SELECT WEEK(ev.datum) weeknr, YEAR(ev.datum) jaarnr
FROM events e
INNER JOIN user_event ev ON e.id = ev.event
WHERE ev.datum BETWEEN "2013-01-01" AND "2013-12-31") ue
USING (weeknr)
Gewijzigd op 20/09/2013 09:07:20 door Ger van Steenderen
Het is geven geleden maar het werkt nog steeds niet zoals ik wil. De weken die niks zouden moeten hebben geven tot een 1 weer als resultaat, dat is niet de bedoeling natuurlijk. Ik heb het volgende:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT w.weeknr, COUNT(*) AS aantal
FROM (
SELECT @weeknr := @weeknr +1 weeknr
FROM user, (
SELECT @weeknr :=-1
)uservar
LIMIT 54
)w
LEFT JOIN
(SELECT WEEK(ue.datum) weeknr, YEAR(ue.datum) jaarnr
FROM event e
INNER JOIN user_event ue ON e.id = ue.event
WHERE ue.datum BETWEEN "2012-01-01" AND "2013-12-31"
AND e.type = 'kamp'
AND YEAR(e.start) = 2013) boe
USING (weeknr)
GROUP BY w.weeknr
ORDER BY w.weeknr
FROM (
SELECT @weeknr := @weeknr +1 weeknr
FROM user, (
SELECT @weeknr :=-1
)uservar
LIMIT 54
)w
LEFT JOIN
(SELECT WEEK(ue.datum) weeknr, YEAR(ue.datum) jaarnr
FROM event e
INNER JOIN user_event ue ON e.id = ue.event
WHERE ue.datum BETWEEN "2012-01-01" AND "2013-12-31"
AND e.type = 'kamp'
AND YEAR(e.start) = 2013) boe
USING (weeknr)
GROUP BY w.weeknr
ORDER BY w.weeknr
Kan je me het laatste zetje geven? Ik zie door de bomen het bos niet meer...