GROUP BY-probleem
Ik wil met behulp van mijn logfile een overzicht maken van het aantal page-views en het aantal hits.
Elke beweging van een bezoeker wordt vastgelegd.
In het logrecord zit o.m. de volgende info:
- Sessienummer (php-sessie)
- IP-adres
- Datum (datum + tijdstip)
- Robot - Waarde = 1 bij bezoek door robot en die wil ik niet meetellen.
Nu heb ik voor het aantal page-views 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
21
22
23
24
25
26
27
28
29
30
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SELECT
COUNT(IF(HOUR(datum) = 0,'Ja',NULL)) AS van0_1uur,
COUNT(IF(HOUR(datum) = 1,'Ja',NULL)) AS van1_2uur,
COUNT(IF(HOUR(datum) = 2,'Ja',NULL)) AS van2_3uur,
COUNT(IF(HOUR(datum) = 3,'Ja',NULL)) AS van3_4uur,
COUNT(IF(HOUR(datum) = 4,'Ja',NULL)) AS van4_5uur,
COUNT(IF(HOUR(datum) = 5,'Ja',NULL)) AS van5_6uur,
COUNT(IF(HOUR(datum) = 6,'Ja',NULL)) AS van6_7uur,
COUNT(IF(HOUR(datum) = 7,'Ja',NULL)) AS van7_8uur,
COUNT(IF(HOUR(datum) = 8,'Ja',NULL)) AS van8_9uur,
COUNT(IF(HOUR(datum) = 9,'Ja',NULL)) AS van9_10uur,
COUNT(IF(HOUR(datum) = 10,'Ja',NULL)) AS van10_11uur,
COUNT(IF(HOUR(datum) = 11,'Ja',NULL)) AS van11_12uur,
COUNT(IF(HOUR(datum) = 12,'Ja',NULL)) AS van12_13uur,
COUNT(IF(HOUR(datum) = 13,'Ja',NULL)) AS van13_14uur,
COUNT(IF(HOUR(datum) = 14,'Ja',NULL)) AS van14_15uur,
COUNT(IF(HOUR(datum) = 15,'Ja',NULL)) AS van15_16uur,
COUNT(IF(HOUR(datum) = 16,'Ja',NULL)) AS van16_17uur,
COUNT(IF(HOUR(datum) = 17,'Ja',NULL)) AS van17_18uur,
COUNT(IF(HOUR(datum) = 18,'Ja',NULL)) AS van18_19uur,
COUNT(IF(HOUR(datum) = 19,'Ja',NULL)) AS van19_20uur,
COUNT(IF(HOUR(datum) = 20,'Ja',NULL)) AS van20_21uur,
COUNT(IF(HOUR(datum) = 21,'Ja',NULL)) AS van21_22uur,
COUNT(IF(HOUR(datum) = 22,'Ja',NULL)) AS van22_23uur,
COUNT(IF(HOUR(datum) = 23,'Ja',NULL)) AS van23_24uur
FROM
sys__logfile
WHERE
YEAR(datum) = 2015 AND MONTH(datum) = 3 AND DAY(datum) = 18 AND
robot = 0
COUNT(IF(HOUR(datum) = 0,'Ja',NULL)) AS van0_1uur,
COUNT(IF(HOUR(datum) = 1,'Ja',NULL)) AS van1_2uur,
COUNT(IF(HOUR(datum) = 2,'Ja',NULL)) AS van2_3uur,
COUNT(IF(HOUR(datum) = 3,'Ja',NULL)) AS van3_4uur,
COUNT(IF(HOUR(datum) = 4,'Ja',NULL)) AS van4_5uur,
COUNT(IF(HOUR(datum) = 5,'Ja',NULL)) AS van5_6uur,
COUNT(IF(HOUR(datum) = 6,'Ja',NULL)) AS van6_7uur,
COUNT(IF(HOUR(datum) = 7,'Ja',NULL)) AS van7_8uur,
COUNT(IF(HOUR(datum) = 8,'Ja',NULL)) AS van8_9uur,
COUNT(IF(HOUR(datum) = 9,'Ja',NULL)) AS van9_10uur,
COUNT(IF(HOUR(datum) = 10,'Ja',NULL)) AS van10_11uur,
COUNT(IF(HOUR(datum) = 11,'Ja',NULL)) AS van11_12uur,
COUNT(IF(HOUR(datum) = 12,'Ja',NULL)) AS van12_13uur,
COUNT(IF(HOUR(datum) = 13,'Ja',NULL)) AS van13_14uur,
COUNT(IF(HOUR(datum) = 14,'Ja',NULL)) AS van14_15uur,
COUNT(IF(HOUR(datum) = 15,'Ja',NULL)) AS van15_16uur,
COUNT(IF(HOUR(datum) = 16,'Ja',NULL)) AS van16_17uur,
COUNT(IF(HOUR(datum) = 17,'Ja',NULL)) AS van17_18uur,
COUNT(IF(HOUR(datum) = 18,'Ja',NULL)) AS van18_19uur,
COUNT(IF(HOUR(datum) = 19,'Ja',NULL)) AS van19_20uur,
COUNT(IF(HOUR(datum) = 20,'Ja',NULL)) AS van20_21uur,
COUNT(IF(HOUR(datum) = 21,'Ja',NULL)) AS van21_22uur,
COUNT(IF(HOUR(datum) = 22,'Ja',NULL)) AS van22_23uur,
COUNT(IF(HOUR(datum) = 23,'Ja',NULL)) AS van23_24uur
FROM
sys__logfile
WHERE
YEAR(datum) = 2015 AND MONTH(datum) = 3 AND DAY(datum) = 18 AND
robot = 0
Hoe pas ik nu de "GROUP BY" toe opdat dus elk bezoek aan de site (op basis van sessienr??) maar één keer wordt geteld?
George
Toevoeging op 20/03/2015 18:13:36:
Ik heb een oplossing gevonden:
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
26
27
28
29
30
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SELECT
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 0,'Ja',NULL)) AS hits0_1uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 1,'Ja',NULL)) AS hits1_2uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 2,'Ja',NULL)) AS hits2_3uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 3,'Ja',NULL)) AS hits3_4uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 4,'Ja',NULL)) AS hits4_5uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 5,'Ja',NULL)) AS hits5_6uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 6,'Ja',NULL)) AS hits6_7uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 7,'Ja',NULL)) AS hits7_8uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 8,'Ja',NULL)) AS hits8_9uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 9,'Ja',NULL)) AS hits9_10uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 10,'Ja',NULL)) AS hits10_11uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 11,'Ja',NULL)) AS hits11_12uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 12,'Ja',NULL)) AS hits12_13uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 13,'Ja',NULL)) AS hits13_14uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 14,'Ja',NULL)) AS hits14_15uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 15,'Ja',NULL)) AS hits15_16uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 16,'Ja',NULL)) AS hits16_17uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 17,'Ja',NULL)) AS hits17_18uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 18,'Ja',NULL)) AS hits18_19uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 19,'Ja',NULL)) AS hits19_20uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 20,'Ja',NULL)) AS hits20_21uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 21,'Ja',NULL)) AS hits21_22uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 22,'Ja',NULL)) AS hits22_23uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 23,'Ja',NULL)) AS hits23_24uur
FROM
sys__logfile
WHERE
YEAR(datum) = '$nJaar' AND MONTH(datum) = '$nMaand' AND DAY(datum) = '$nDag' AND
robot = 0
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 0,'Ja',NULL)) AS hits0_1uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 1,'Ja',NULL)) AS hits1_2uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 2,'Ja',NULL)) AS hits2_3uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 3,'Ja',NULL)) AS hits3_4uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 4,'Ja',NULL)) AS hits4_5uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 5,'Ja',NULL)) AS hits5_6uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 6,'Ja',NULL)) AS hits6_7uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 7,'Ja',NULL)) AS hits7_8uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 8,'Ja',NULL)) AS hits8_9uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 9,'Ja',NULL)) AS hits9_10uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 10,'Ja',NULL)) AS hits10_11uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 11,'Ja',NULL)) AS hits11_12uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 12,'Ja',NULL)) AS hits12_13uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 13,'Ja',NULL)) AS hits13_14uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 14,'Ja',NULL)) AS hits14_15uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 15,'Ja',NULL)) AS hits15_16uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 16,'Ja',NULL)) AS hits16_17uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 17,'Ja',NULL)) AS hits17_18uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 18,'Ja',NULL)) AS hits18_19uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 19,'Ja',NULL)) AS hits19_20uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 20,'Ja',NULL)) AS hits20_21uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 21,'Ja',NULL)) AS hits21_22uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 22,'Ja',NULL)) AS hits22_23uur,
COUNT(DISTINCT(sessie),IF(HOUR(datum) = 23,'Ja',NULL)) AS hits23_24uur
FROM
sys__logfile
WHERE
YEAR(datum) = '$nJaar' AND MONTH(datum) = '$nMaand' AND DAY(datum) = '$nDag' AND
robot = 0
Er zijn nog geen reacties op dit bericht.