[MYSQL] stats query

[bRight]

Voor een extranetgedeelte van een website wil ik statistieken bijhouden van het surfgedrag van de users.

De gevens worden opgeslagen in een tabel met de volgende structuur:

code:

+---------+--------+---------+---------+---------+
| user_id | maand  | pagina1 | pagina2 | pagina3 |
+---------+--------+---------+---------+---------+
| 1       | 200306 | 12      | 14      | 10      |
+---------+--------+---------+---------+---------+
| 2       | 200306 | 6       | 4       | 7       |
+---------+--------+---------+---------+---------+
| 1       | 200307 | 5       | 8       | 3       |
+---------+--------+---------+---------+---------+
| 2       | 200307 | 11      | 5       | 6       |
+---------+--------+---------+---------+---------+
| 3       | 200307 | 10      | 5       | 15      |
+---------+--------+---------+---------+---------+

De hits worden dus voor elke user per pagina per maand geregistreerd.
Je kan nu bijv een vergelijkingsoverzicht opvragen van de pagina's onderling voor juni:

SELECT SUM(pagina1), SUM(pagina2), SUM(pagina3) FROM stats WHERE maand = '200306'

Maar is het ook mogelijk om de query zo te verbouwen dat de hits gelijk gesorteerd worden van hoog naar laag?

Sorry voor deze ontzettende n00bvraag maar ik kom er ff niet uit..

[Evilbee]

Zet dit er eens achter

code:

order by `columnname`

[bRight]

hmm tis iets complexer dan dat hoor.. ik wil de resultaten van de SUM()'s sorteren.. en die staan in verschillende kolommen..

[gorgi_19]

SELECT partid, SUM(qty)
FROM orderpart
GROUP BY partid
ORDER BY SUM(qty) DESC

Nee, het blijft eenvoudig.

[ACM]

doe dan iets netter:
SELECT .... SUM(qty) as sum
...
ORDER BY sum DESC

Dan heb je iig niet een dubbele-sum erin (mocht dat niet weggeoptimaliseerd worden).

[bRight]

das in ieder geval een stap in de goede richting

maar het punt is dat je vantevoren niet weet welke SUM() de hoogste waarde gaat opleveren.. de kolommen moeten tenopzichte van elkaar gesort worden, dus niet de records die binnen een kolom vallen..

Misschien dat ik de tablestructuur helemaal verkeerd heb gekozen, maar weet ff niet hoe ik het anders zou moeten doen..

[mjax]

ACM schreef op 25 July 2003 @ 23:03:
doe dan iets netter:
SELECT .... SUM(qty) as sum
...
ORDER BY sum DESC

Dan heb je iig niet een dubbele-sum erin (mocht dat niet weggeoptimaliseerd worden).

Ik vind het niet echt netjes om een reserved keyword (SUM) te gebruiken als alias...

[alienfruit]

Waarom sla je niet meteen gewoon de hele datum op? Die twee tekens... Kun je later meteen nog leuke trucjes uithalen! Bijv. aantal bezoekers op je verjaardag van de laatste x maanden.

[ACM]

mjax schreef op 26 July 2003 @ 00:05:
Ik vind het niet echt netjes om een reserved keyword (SUM) te gebruiken als alias...

Op die plek is ie niet eens reserved blijkbaar, anders zou mysql het niet accepteren

Aangezien er aan de syntax van de query afgelezen kan worden welke er bedoeld wordt, is het imho totaal niet fout om te doen. SUM is trouwens geen reserved keyword, maar een naam van een aggregatie-functie (en evt daardoor als reserved keyword te markeren).
De parser van de query weet echt wel wat ik bedoel hoor...

[justmental]

AArsgier schreef op 25 juli 2003 @ 23:04:
Misschien dat ik de tablestructuur helemaal verkeerd heb gekozen, maar weet ff niet hoe ik het anders zou moeten doen..

Op het moment dat je kolommen gaat benoemen als 'pagina1','pagina2',etc heb je een hele goede hint dat het beter kan

Om eenvoudig een beter datamodel te maken kun je bedenken wat voor verschillende 'dingen' je gaat opslaan. Die komen vaak erg overeen met hoe je ze in taal zou benoemen.
Wat je hier op wilt slaan zijn de hits per pagina per maand per gebruiker.
Zo heb je hier dus te maken met 'pagina', 'gebruiker' en 'hits/statistieken' (een tabel met maanden is niet zo zinnig).
Als je hier losse tabellen van maakt ben je flexibeler voor als je nog eens een keer wat anders met de data wilt doen.

[KompjoeFriek]

simpel op te lossen toch? (de structuur dan)

code:

+---------+--------+--------+------+
| user_id | maand  | pagina | hits |
+---------+--------+--------+------+
| 1       | 200306 | 1      | 14   |
+---------+--------+--------+------+
| 1       | 200306 | 2      | 4    |
+---------+--------+--------+------+
| 2       | 200306 | 1      | 8    |
+---------+--------+--------+------+
| 2       | 200306 | 2      | 5    |
+---------+--------+--------+------+
| 1       | 200307 | 1      | 5    |
+---------+--------+--------+------+

zo heb je per user,maand en pagina het aantal hits

[Verwijderd]

Misschien snap ik de vraag niet helemaal, maar is dit niet iets?

SQL:

SELECT
  user_id,
  SUM(pagina1) AS pagina1, 
  SUM(pagina2) AS pagina2, 
  SUM(pagina3) AS pagina3,
  SUM(pagina1 + pagina2 + pagina3) AS totaal
FROM stats 
WHERE maand = '200306'
GROUP BY user_id
ORDER BY totaal DESC

't Wordt wat lastiger als je later ook nog 's pagina's wilt gaan toevoegen oid, en dan voldoet KompjoeFriek's datamodel een stuk beter. Maar ja, de queries die je daarop moet loslaten zijn ook weer een stukje pittiger...

[ Voor 7% gewijzigd door Verwijderd op 26-07-2003 13:13 ]

[bRight]

Das inderdaad niet wat ik wil.. ik wil de pagina's sorteren op basis van aantal hits..

alienfruit schreef op 26 July 2003 @ 00:15:
Waarom sla je niet meteen gewoon de hele datum op? Die twee tekens... Kun je later meteen nog leuke trucjes uithalen! Bijv. aantal bezoekers op je verjaardag van de laatste x maanden.

Is wel leuk, maar dan groeit de database wat te hard..
Niet alleen 2 karakters verschil, maar ook 30x zoveel data

KompjoeFriek schreef op 26 July 2003 @ 12:05:
simpel op te lossen toch? (de structuur dan)

code:

+---------+--------+--------+------+
| user_id | maand  | pagina | hits |
+---------+--------+--------+------+
| 1       | 200306 | 1      | 14   |
+---------+--------+--------+------+
| 1       | 200306 | 2      | 4    |
+---------+--------+--------+------+
| 2       | 200306 | 1      | 8    |
+---------+--------+--------+------+
| 2       | 200306 | 2      | 5    |
+---------+--------+--------+------+
| 1       | 200307 | 1      | 5    |
+---------+--------+--------+------+

zo heb je per user,maand en pagina het aantal hits

Klopt. Ik denk dat ik het op deze manier ga doen.
Het kost alleen wel veel meer ruimte in de database (vooral met veel pagina's..)

Volgens mij heb ik hem door:

SELECT pagina, SUM(hits) AS totaal FROM stats WHERE maand = '200306' GROUP BY pagina ORDER BY totaal DESC

code:

+--------+--------+
| pagina | totaal |
+--------+--------+
| 1      | 22     |
+--------+--------+
| 2      | 9      |
+--------+--------+

Dit is precies wat ik nodig heb!
Iedereen bedankt!

[ Voor 4% gewijzigd door bRight op 26-07-2003 13:43 . Reden: typo ]

[mjax]

ACM schreef op 26 July 2003 @ 11:38:
[...]

Op die plek is ie niet eens reserved blijkbaar, anders zou mysql het niet accepteren

Aangezien er aan de syntax van de query afgelezen kan worden welke er bedoeld wordt, is het imho totaal niet fout om te doen. SUM is trouwens geen reserved keyword, maar een naam van een aggregatie-functie (en evt daardoor als reserved keyword te markeren).
De parser van de query weet echt wel wat ik bedoel hoor...

Met het risico om zwaar offtopic te gaan EN ook nog eens tegen een mod in:

Het feit dat de MySQL parser SUM als alias accepteert, maakt dit nog geen nette oplossing. Het gebruik van gereserveerde namen/keywords/functienamen/etc. in andere context dan waar deze voor bedoeld is, is gewoon ranzig en geen nette manier van programmeren. De kans is groot dat wanneer je deze "techniek" toepast op een ander RDBMS, het wel als een syntax fout wordt gezien.

Ik zal hier verder niet op doorgaan, omdat het mierenneukerij is, maar aangezien dit forum ook een soort opvoedkundige waarde t.o.v. andere ontwikkelaars heeft, lijkt het me toch verstandig aan te geven dat dit soort constructies niet "the-way-to-go" zijn.

[Bosmonster]

alienfruit schreef op 26 July 2003 @ 00:15:
Waarom sla je niet meteen gewoon de hele datum op? Die twee tekens... Kun je later meteen nog leuke trucjes uithalen! Bijv. aantal bezoekers op je verjaardag van de laatste x maanden.

Als je het ook al per dag wilt sla dan een exactere datum in een los veld in. Aangezien die MySQL gebruikt en je dus geen functionele indices bij kune houden zouden je queries extreem traag worden als je bijvoorbeeld op maand wilt gaan groupen.

Stats opslaan in een DB als het erg veel verkeer is is sowieso niet zo handig imho.. dat loopt als een gek vol en de queries worden dramatisch traag (door de SUM's enzo).