[SQL] Uniek aantal dagen tellen

[Reveller]

Ik ben nu bezig met script om wat statistieken op mijn website te verzamelen. Ik wil onder andere weten op hoeveel unieke dagen er activiteit gemeten is op de site. Onderstaande kolom, "created" (type DATETIME) staat in een tabel met statistieken genaamd "stats":

code:

2002-10-04 22:56:45
2003-11-04 22:56:49
2004-11-04 22:56:51
2004-12-15 22:57:31
2004-12-17 23:21:04
2005-12-05 23:21:20
2005-12-05 23:21:23
2005-12-06 23:21:33

Ik probeer een query te verzinnen welke het unieke aantal dagen telt (6 in dit geval), maar heb moeite met een goede formulering. Volgens mij ben ik met onderstaande niet op de goede weg:

SQL:

SELECT COUNT(DISTINCT(DAYOFMONTH(created))) FROM stats

Wie kan mij in de goede richting wijzen?

[ Voor 5% gewijzigd door Reveller op 06-12-2005 20:01 ]

[P_de_B]

Je zult het datumtijd formaat moeten omzetten naar een datum formaat. Hoe dit precies moet is afhankelijk van de RDBMS. Voor MS SQL server kun je o.a. het volgende doen:

SQL:

select count(distinct (convert(varchar(10), datum,105) ) ) from ...

[Gé Brander]

Of met behulp van LEFT(dateofmonth, 10) alleen de datum selecteren. Of is dat 'duurder' in database land ten opzichte van de convert zoals P_de_B voorsteld.

[ Voor 61% gewijzigd door Gé Brander op 06-12-2005 20:11 ]

[Reveller]

Ik gebruik overigens MySQL. Ik vraag mij af of bovenstaande manieren werken, omdat de dagen 1 t/m 31 in 12 verschillende maanden in meerdere jaren voorkomen...

[kenneth]

Dan moet je alleen DAYOFMONTH en de equivalent voor dag gebruiken, toch?

[Gé Brander]

Reveller schreef op dinsdag 06 december 2005 @ 20:31:
Ik gebruik overigens MySQL. Ik vraag mij af of bovenstaande manieren werken, omdat de dagen 1 t/m 31 in 12 verschillende maanden in meerdere jaren voorkomen...

Ik ken MySQL niet, maar is er een LEFT functie? Dan is met elke eerste 10 karakters toch een unieke combinatie te maken? Dan heb je toch niets met 1-31, 1-12 te maken in verschillende jaren?

[kenneth]

Dan neem je ook jaar mee, en dat is niet de bedoeling.

[Gé Brander]

kenneth schreef op dinsdag 06 december 2005 @ 20:40:
Dan neem je ook jaar mee, en dat is niet de bedoeling.

hmm. Dat lees ik nergens, of ik begrijp het verkeerd.

[P_de_B]

kenneth schreef op dinsdag 06 december 2005 @ 20:40:
Dan neem je ook jaar mee, en dat is niet de bedoeling.

Volgens mij wel, hij wil gewoon unieke dagen.

In MySQL kun je dus de date functie gebruiken. DATE(een_datum_tijd) geeft alleen de datum, zonder de tijd.In combinatie met mijn eerste voorbeeld moet je er wel mee komen denk ik.

[djluc]

LEFT weet ik niet in MySQL. Ik heb dit ook eens opgelost met SUBSTRING(created, 0, 10)

[kenneth]

Reveller schreef op dinsdag 06 december 2005 @ 20:31:
Ik gebruik overigens MySQL. Ik vraag mij af of bovenstaande manieren werken, omdat de dagen 1 t/m 31 in 12 verschillende maanden in meerdere jaren voorkomen...

Hieruit (en uit de testgegevens) trek ik de conclusie dat het jaar irrelevant is: 2003-11-04 en 2004-11-04 tellen als dezelfde dag.

[Gé Brander]

Kort door de bocht conclusie naar mijn mening, maar je kan het zo opvatten. Echter logisch vind ik het niet.
Een unieke dag is een unieke dag, en niet een dag die in meerdere jaren voorkomt.

[ Voor 28% gewijzigd door Gé Brander op 06-12-2005 20:47 ]

[P_de_B]

kenneth schreef op dinsdag 06 december 2005 @ 20:45:
[...]
Hieruit (en uit de testgegevens) trek ik de conclusie dat het jaar irrelevant is: 2003-11-04 en 2004-11-04 tellen als dezelfde dag.

Dat is volgens mij omdat hij twijfelt of DAYOFMONTH wel de goede functie is, maar dit blijft een beetje gokwerk;

@TS: 2004-12-05 en 2005-12-05, moet dat als 1 of als 2 geteld worden?

[kenneth]

Kijk naar de testset, en zijn aanname dat er zes uit moet komen ... dat kan alleen als je 4 november als 1 dag telt.

[P_de_B]

kenneth schreef op dinsdag 06 december 2005 @ 20:51:
Kijk naar de testset, en zijn aanname dat er zes uit moet komen ... dat kan alleen als je 4 november als 1 dag telt.

Je hebt gelijk, ik had niet gezien dat 4 november in zowel 2003 als 2004 in het voorbeeld stond

[edeboeck]

IMHO is deze code zowel de meest correcte (datumfunctie gebruiken voor datumfunctionaliteit, geen stringfunctie) als de meest performante (omdat je de functie gebruikt waarvoor hij gemaakt is).

SQL:

SELECT COUNT(DISTINCT(DATE_FORMAT(created,"%c-%e"))) AS [Aantal unieke dagen] FROM stats

Unieke dagen zijn unieke dagen, dus het jaar speelt wel degelijk een rol ... als TS enkel het huidige jaar wil, kan hij de query aanpassen als volgt (analoog voor jaar en/ofmaand):

SQL:

SELECT COUNT(DISTINCT(DATE_FORMAT(created,"%c-%e"))) AS [Aantal unieke dagen] FROM stats
WHERE YEAR(created)=intJaar

(intJaar is een integer variabele die het gewenste jaar bevat)

Meer info rond datum-/tijdfuncties: http://dev.mysql.com/doc/...e-and-time-functions.html

[ Voor 2% gewijzigd door edeboeck op 06-12-2005 21:09 . Reden: *oeps* nu pas testset [b]grondig[/b] bekeken |:( ]

[P_de_B]

edeboeck schreef op dinsdag 06 december 2005 @ 21:02:
IMHO is deze code zowel de meest correcte (datumfunctie gebruiken voor datumfunctionaliteit, geen stringfunctie) als de meest performante (omdat je de functie gebruikt waarvoor hij gemaakt is).

SQL:

SELECT COUNT(DISTINCT(DATE(created))) AS [Aantal unieke dagen] FROM stats

Unieke dagen zijn unieke dagen, dus het jaar speelt wel degelijk een rol ... als TS enkel het huidige jaar wil, kan hij de query aanpassen als volgt (analoog voor jaar en/ofmaand):

SQL:

SELECT COUNT(DISTINCT(DATE(created))) AS [Aantal unieke dagen] FROM stats
WHERE YEAR(created)=intJaar

(intJaar is een integer variabele die het gewenste jaar bevat)

Meer info rond datum-/tijdfuncties: http://dev.mysql.com/doc/...e-and-time-functions.html

Ik begreep het net als jij, en gaf date ook al als voorbeeld maar als je de opmerkingen van kenneth en de voorbeeld data bekijkt zie je dat dit niet is wat TS vraagt.

[Reveller]

P_de_B schreef op dinsdag 06 december 2005 @ 20:50:
[...]
@TS: 2004-12-05 en 2005-12-05, moet dat als 1 of als 2 geteld worden?

Misschien moet ik het beter uitleggen: ik wil in de tabel "stats" kijken van welke dagen in statistieken heb. Dat wil zeggen: als ik 35 records heb van de dag 5 december 2004, dan is 5 december 2004 een "unieke" dag. Als ik records heb staan met als datum 5 december 2005, dan is ook dat een "unieke" dag

De reden hiervoor is dat ik bijvoorbeeld een "gemiddeld aantal pageviews per dag" wil kunnen berekenen. Ik tel dan alle pageviews bij elkaar op, en deel deze door het aantal dagen waarvan ik statistieken. Vervolgens kun je staatjes weergeven als "vandaag 232 pageviews; dat is 12,5% boven het dagelijks gemiddelde over het afgelopen jaar".

[ Voor 60% gewijzigd door Reveller op 06-12-2005 21:08 ]

[P_de_B]

Ok, dan had ik toch gelijk

Dit is een compleet voorbeeld: [rml]edeboeck in "[ SQL] Uniek aantal dagen tellen"[/rml]

[edeboeck]

*knip*

[ Voor 100% gewijzigd door edeboeck op 06-12-2005 21:15 . Reden: Overbodig door post P_de_B ]

[Maxxi]

$aantaldagen = 1;

mysql_query = SELECT * FROM stats;

while ($row blbabla){

if ($row[datum] != $temp){
$aantaldagen ++;
}
$temp = $row[datum];

[ Voor 16% gewijzigd door Maxxi op 06-12-2005 21:21 ]

[kenneth]

Maxxi schreef op dinsdag 06 december 2005 @ 21:20:

Dat meen je niet?

[Reveller]

P_de_B schreef op dinsdag 06 december 2005 @ 21:14:
Ok, dan had ik toch gelijk

Dit is een compleet voorbeeld: [rml]edeboeck in "[ SQL] Uniek aantal dagen tellen"[/rml]

Ontzettend bedankt, P_de_B en edeboeck, het werkt perfect. Ik ga nu wel de MySQL manual dieper bestuderen, want hier was ik zelf niet snel opgekomen

[ Voor 4% gewijzigd door Reveller op 07-12-2005 14:21 ]

[Verwijderd]

kenneth schreef op dinsdag 06 december 2005 @ 21:35:
[...]
Dat meen je niet?

Heheh lekkere query inderdaad, zelfde als dat ik ooit heb gezien waar geen like werd gebruikt, maar alle gegevens opgehaald en met een regex worden gecontroleert