[MySQL] count distinct + group by

[Verwijderd]

MySQL versie: 3.23

stats_main_session:
`idvisitor` int(11)
`date` timestamp(14)
`page` varchar(255)
`refferer` varchar(255)

Op verzoek van LuCarD plaats ik ook de andere tabel en een aantal voorbeeld rijen.
Meer tabellen zijn er niet.

stats_main:
- `id` int(11)
- `idwebsite` int(11)
- `date` timestamp(14)
- `refferer` varchar(255)
- `user_ip` varchar(39)
- `user_browser` int(11)
- `user_browserv` varchar(5)
- `user_os` int(11)
- `user_res_width` int(11)
- `user_res_height` int(11)
- `user_lang` varchar(5)
- `search` varchar(255)

De tabel "stats_main" wordt 1 keer gevuld bij het eerst bezoek.
Darna wordt de pagina opgeslagen waar de bezoeker binnenkomt in "stats_main_session".
Vervolgens worden alle bezochte pagina's van deze bezoeker ook opgeslagen in "stats_main_session" en wordt "stats_main" niet meer gebruikt.

Voorbeeld inhoud stats_main_session:

id	idwebsite	date	refferer	user_ip	user_browser
4044	1	20070809162923	http://...	1.1.1.1	9

user_browserv	user_os	user_res_width	user_res_height	user_lang	search
6	1	1280	768	nl	blaat

Voorbeeld inhoud stats_main_session:

idvisitor	date	page	refferer
4044	20070809162923	http://...	http://...
4044	20070809162946	http://...	http://...
4044	20070809162948	http://...	http://...
4044	20070809163003	http://...	http://...
4044	20070809163007	http://...	http://...

Ik probeer met de volgende query de unieke bezoekers en het totaal aantal bezoeken per uur, per dag weer te geven.

SQL:

SELECT  
    DATE_FORMAT(date, '%d-%H') AS selection,
    DAYOFMONTH(date) AS day,
    HOUR(date) AS hour,
    COUNT( idvisitor ) AS tvisitors,
    COUNT(DISTINCT idvisitor) AS uvisitors
FROM 
    stats_main_session                  
WHERE
    YEAR(date) = YEAR(CURDATE()) AND 
    MONTH(date) = MONTH(CURDATE())
GROUP BY 
    selection
ORDER BY
    selection ASC

Nu krijg ik alle gegevens terug die erin staan (3337 resultaten), terwijl het er 193 moeten zijn.

Het rare is, als ik een limit 0, 2000 toevoeg er wel 193 resutaten teruggegeven worden
Als ik de regel "COUNT(DISTINCT idvisitor) AS uvisitors" weghaal, dan krijg ik ook netjes 193 resultaten terug.

Gebruik ik de count(distinct) verkeerd of klopt er ergens anders iets niet?

[ Voor 58% gewijzigd door Verwijderd op 20-08-2007 16:49 ]

[JortK]

wat nou als je COUNT(*) doet, dat is volgens mij accurater dan COUNT(kolomnaam)

[Janoz]

Je gebruikt uberhaupt Group By verkeerd. In de FAQ is een heel stuk over group by opgenomen. Misschien is het handig om dat eens door te nemen. Daarnaast gebruik je inderdaad distinct ook verkeerd.

[Verwijderd]

JortK schreef op maandag 20 augustus 2007 @ 10:43:
wat nou als je COUNT(*) doet, dat is volgens mij accurater dan COUNT(kolomnaam)

Denk je?
Waarom zou dat zijn?

Janoz schreef op maandag 20 augustus 2007 @ 10:45:
Je gebruikt uberhaupt Group By verkeerd. In de FAQ is een heel stuk over group by opgenomen. Misschien is het handig om dat eens door te nemen. Daarnaast gebruik je inderdaad distinct ook verkeerd.

Whoops... dat klinkt niet echt best.
Snel lezen dan maar


Dit verklaard al het een en ander:
"MySQL is een hele brakke database, die deze constructie wel toestaat. En volgens de handleiding is het 'by design' dat je vervolgens random waardes aantreft. Don't do it."

[ Voor 16% gewijzigd door Verwijderd op 20-08-2007 10:56 ]

[JortK]

Verwijderd schreef op maandag 20 augustus 2007 @ 10:51:
[...]

Denk je?
Waarom zou dat zijn?


[...]


Dit verklaard al het een en ander:
"MySQL is een hele brakke database, die deze constructie wel toestaat. En volgens de handleiding is het 'by design' dat je vervolgens random waardes aantreft. Don't do it."

Zie quote

Daarom

[Verwijderd]

Kan je mij misschien een hint geven wat ik niet goed doe?

Ik kan er wel het volgende van maken, maar het resultaat blijft hetzelfde:

SQL:

SELECT  
    DAYOFMONTH(date) AS day,
    HOUR(date) AS hour,
    COUNT( idvisitor ) AS tvisitors,
    COUNT(DISTINCT(idvisitor)) AS uvisitors
FROM 
    stats_main_session                  
WHERE
    YEAR(date) = YEAR(CURDATE()) AND 
    MONTH(date) = MONTH(CURDATE())
GROUP BY 
    DATE_FORMAT(date, '%d-%H')
ORDER BY
    date ASC

[Verwijderd]

JortK schreef op maandag 20 augustus 2007 @ 13:48:
[...]

Zie quote

Daarom

Maar dat werkt niet met distinct?
En die heb ik weer nodig om het aantal unieke bezoekers te bepalen.

[JortK]

Verwijderd schreef op maandag 20 augustus 2007 @ 13:59:
[...]

Maar dat werkt niet met distinct?
En die heb ik weer nodig om het aantal unieke bezoekers te bepalen.

Wat nou als je die distinct weghaalt op die kolom, en daar COUNT(*) van maakt gewoon, en dan die kolom die eerst in de distinct stond gewoon in de group by zet?

[LuCarD]

Verwijderd schreef op maandag 20 augustus 2007 @ 13:52:
Kan je mij misschien een hint geven wat ik niet goed doe?

Ik kan er wel het volgende van maken, maar het resultaat blijft hetzelfde:

SQL:

SELECT  
    DAYOFMONTH(date) AS day,
    HOUR(date) AS hour,
    COUNT( idvisitor ) AS tvisitors,
    COUNT(DISTINCT(idvisitor)) AS uvisitors
FROM 
    stats_main_session                  
WHERE
    YEAR(date) = YEAR(CURDATE()) AND 
    MONTH(date) = MONTH(CURDATE())
GROUP BY 
    DATE_FORMAT(date, '%d-%H')
ORDER BY
    date ASC

Oke een hint

Welke waarde verwacht je in:
DAYOFMONTH(date) AS day,
HOUR(date) AS hour

[Verwijderd]

JortK schreef op maandag 20 augustus 2007 @ 15:01:
[...]

Wat nou als je die distinct weghaalt op die kolom, en daar COUNT(*) van maakt gewoon, en dan die kolom die eerst in de distinct stond gewoon in de group by zet?

Dan krijg ik alle bezochte pagina's per bezoeker, per dag en uur.
Maar niet alle unieke bezoekers en het aantal bezochte pagina's per dag en uur.

[ Voor 49% gewijzigd door Verwijderd op 20-08-2007 15:16 ]

[Verwijderd]

LuCarD schreef op maandag 20 augustus 2007 @ 15:14:
[...]


Oke een hint

Welke waarde verwacht je in:
DAYOFMONTH(date) AS day,
HOUR(date) AS hour

Uhm dagnummer van de maand en het uur van de dag?
Ik snap niet echt waar je heen wilt...

[LuCarD]

Verwijderd schreef op maandag 20 augustus 2007 @ 15:33:
[...]

Uhm dagnummer van de maand en het uur van de dag?
Ik snap niet echt waar je heen wilt...

Om zinnige data te krijgen moeten alle velden in de group by voorkomen of ge-aggregeert worden!

[Verwijderd]

LuCarD schreef op maandag 20 augustus 2007 @ 15:35:
[...]


Om zinnige data te krijgen moeten alle velden in de group by voorkomen of ge-aggregeert worden!

Het veld "date" wordt toch ook gebruikt in de GROUP BY?
Of mag ik daar dan helemaal niks meer mee doen?

Ik kan geen GROUP BY op "date" zelf doen, want dat is een timestamp.
Het moet gegroepeerd worden op dag en uur.

[LuCarD]

Verwijderd schreef op maandag 20 augustus 2007 @ 15:42:
[...]

Het veld "date" wordt toch ook gebruikt in de GROUP BY?
Of mag ik daar dan helemaal niks meer mee doen?

Klopt

DATE_FORMAT(date, '%d-%H') != HOUR(date)

Ik kan geen GROUP BY op "date" zelf doen, want dat is een timestamp.
Het moet gegroepeerd worden op dag en uur.

Nou, dan is het toch simpel? Zet ook de bewerkingen op date in de group by.

Dus

SQL:

SELECT  
    DAYOFMONTH(date) AS day,
    HOUR(date) AS hour,
    COUNT( idvisitor ) AS tvisitors,
    COUNT(DISTINCT idvisitor) AS uvisitors
FROM 
    stats_main_session                  
WHERE
    YEAR(date) = YEAR(CURDATE()) AND 
    MONTH(date) = MONTH(CURDATE())
GROUP BY 
        DAYOFMONTH(date), HOUR(date)

ORDER BY
    date ASC

De functie GROUP DISTINCT is op versie MySQL 3.23.2. toegevoegd. Je hebt toch niet toevallig versie 3.23.1 of 3.23.0 ?

[ Voor 6% gewijzigd door LuCarD op 20-08-2007 15:53 ]

[Verwijderd]

LuCarD schreef op maandag 20 augustus 2007 @ 15:47:
[...]


Klopt

DATE_FORMAT(date, '%d-%H') != HOUR(date)

Dat heb ik inderdaad over het hoofd gezien.
Maar eerst had ik het volgende

SQL:

SELECT      
    DATE_FORMAT(date, '%d-%H') AS selection, 
    DAYOFMONTH(date) AS day, 
    HOUR(date) AS hour, 
....
GROUP BY  
    selection
...

Dus dan kwam de GROUP BY wel overeen.
Is dit ook niet goed?

LuCarD schreef op maandag 20 augustus 2007 @ 15:47:
[...]


Nou, dan is het toch simpel? Zet ook de bewerkingen op date in de group by.

Dus

SQL:

SELECT  
    DAYOFMONTH(date) AS day,
    HOUR(date) AS hour,
    COUNT( idvisitor ) AS tvisitors,
    COUNT(DISTINCT idvisitor) AS uvisitors
FROM 
    stats_main_session                  
WHERE
    YEAR(date) = YEAR(CURDATE()) AND 
    MONTH(date) = MONTH(CURDATE())
GROUP BY 
        DAYOFMONTH(date), HOUR(date)

ORDER BY
    date ASC

Hier krijg ik hetzelfde resultaat mee...

[Janoz]

Verwijderd schreef op maandag 20 augustus 2007 @ 15:55:

SQL:

SELECT      
    DATE_FORMAT(date, '%d-%H') AS selection, 
    DAYOFMONTH(date) AS day, 
    HOUR(date) AS hour, 
....
GROUP BY  
    selection
...

Dus dan kwam de GROUP BY wel overeen.
Is dit ook niet goed?

Nee, dit is niet goed. Hier staan 3 niet geaggregeerde velden terwijl je er maar 1 in je group by hebt genoemd.


Probeer goed te beseffen wat er eigenlijk gebeurt. Het is sowieso niet mogelijk om twee verschillende waarden te krijgen uit twee counts binnen 1 select. Als je beide gegevens wilt hebben zul je dit met twee queries moeten doen.

[ Voor 20% gewijzigd door Janoz op 20-08-2007 16:11 ]

[LuCarD]

Verwijderd schreef op maandag 20 augustus 2007 @ 15:55:
[...]

Hier krijg ik hetzelfde resultaat mee...

Wat is idvisitor eignelijk voor een veld?

Wat staat er in?
En hoe word die gevuld?
Kan je eens een klein gedeelte ( 10 rows of zo ) van de data laten zien?

Janoz schreef op maandag 20 augustus 2007 @ 16:08:


Probeer goed te beseffen wat er eigenlijk gebeurt. Het is sowieso niet mogelijk om twee verschillende waarden te krijgen uit twee counts binnen 1 select. Als je beide gegevens wilt hebben zul je dit met twee queries moeten doen.

Dat is wel mogelijk... ik heb het net hier getest op een database ( MySQL 5 ) van mij. Enige verschil is dat ik geen timestamp veld had maar een datetime veld. Maar daar kan het (IMHO ) niet aan liggen.

[ Voor 48% gewijzigd door LuCarD op 20-08-2007 16:17 ]

[Verwijderd]

Janoz schreef op maandag 20 augustus 2007 @ 16:08:
[...]

Nee, dit is niet goed. Hier staan 3 niet geaggregeerde velden terwijl je er maar 1 in je group by hebt genoemd.

Ok, ik begin het te begrijpen.
Nog 1 vraag hierover

Als ik "GROUP BY date" gebruik, moet ik dan ook exact "date" gebruiken of mag ik daar dan wel bewerkingen mee doen, bv "DATE_FORMAT(date, '%d-%H') AS selection"?

Janoz schreef op maandag 20 augustus 2007 @ 16:08:
Probeer goed te beseffen wat er eigenlijk gebeurt. Het is sowieso niet mogelijk om twee verschillende waarden te krijgen uit twee counts binnen 1 select. Als je beide gegevens wilt hebben zul je dit met twee queries moeten doen.

Dit heb ik bij een aantal andere queries wel gedaan en daar krijg ik de gewenste resultaten mee.
Misschien het foute SQL gebruikt, maar slikt MySQL het wel?

Voorbeeld:

SQL:

SELECT 
    COUNT(id) AS tvisitors, 
    COUNT(DISTINCT user_ip) AS uvisitors, 
    COUNT(IF(refferer != '',1,NULL)) AS trefferer, 
    COUNT(DISTINCT refferer) AS urefferer, 
    COUNT(IF(search != '',1,NULL)) as tsearch, 
    COUNT(DISTINCT search) AS usearch 
FROM 
    stats_main 
WHERE 
    YEAR(date) = YEAR(CURDATE()) AND 
    MONTH(date) = MONTH(CURDATE())

LuCarD schreef op maandag 20 augustus 2007 @ 16:14:
[...]

Wat is idvisitor eignelijk voor een veld?

Wat staat er in?
En hoe word die gevuld?
Kan je eens een klein gedeelte ( 10 rows of zo ) van de data laten zien?

Ik maak gebruik van 2 tabellen.
In de ts staan nu ook de tabellen en een paar voorbeeld rijen.

LuCarD schreef op maandag 20 augustus 2007 @ 16:14:
[...]

Dat is wel mogelijk... ik heb het net hier getest op een database ( MySQL 5 ) van mij. Enige verschil is dat ik geen timestamp veld had maar een datetime veld. Maar daar kan het (IMHO ) niet aan liggen.

Lijkt me ook sterk.

[ Voor 21% gewijzigd door Verwijderd op 20-08-2007 16:50 ]