[MySQL] SUM voor eerste uur in een group by voor een dag?

[LocoShady]

Ik heb een query, waarvan ik me afvraag of het wel mogelijk is?

Het gaat om vier dagen Twitter data (afzonderlijke tweets). Nu wil ik per url_final per dag weten de count(DISTINCT screen_name) maar dan alleen van het eerste uur.

Met andere woorden.. elke row is een tweet en bevat een URL. Ik kan nu al zien wat het totaal is van de hele dag van die waarde (zie onderstaande query), maar daarnaast wil ik ook alleen van het eerste uur weten. is dit mogelijk?

code:

SELECT MIN( created_at ) , url_final, COUNT( id ) AS count_id, COUNT( DISTINCT screen_name ) AS count_unique_screen_name
FROM  `tweets_total` 
GROUP BY DAY( created_at ) , url_final
ORDER BY count_id DESC 
LIMIT 0 , 30

_{Ik vind het een beetje lastig te verwoorden, maar hoop dat jullie me snappen.. zo niet dan probeer ik het nog verder te verduidelijken.}

[ Voor 9% gewijzigd door LocoShady op 02-05-2012 12:00 ]

[Reinier]

In je where een restrictie op created_at

[CT]

where hour(created_at) = 0

[LocoShady]

Reinier schreef op woensdag 02 mei 2012 @ 12:04:
In je where een restrictie op created_at

Maar dan heb ik dus niet meer de SUM van de hele dag. Ik wil dus zowel de SUM van alleen het eerste uur als de SUM van de hele dag.

[LocoShady]

CT schreef op woensdag 02 mei 2012 @ 12:04:
where hour(created_at) = 0

Ah, en het eerste uur nadat het voor het eerst is genoemd Dus dat kan ook halverwege de middag zijn in sommige gevallen.

[CT]

dan kan het opzich wel maar moet je met *uche* subqueries gaan werken
zoiets als

where hour(created_at) between (select etc..) and (select etc..) +1

[tss68nl]

Simpel. Subqueries.

En dat kan op twee manieren:
- In de SELECT: dan gaat hij voor iedere rij je antwoord halen. Dat performt over het algemeen voor geen meter.
- In de FROM: Je bouwt een aantal subqueries die met een group by op url_final de antwoorden ophalen, en joint die met een masterquery aan elkaar op diezelfde url_final sleutel.

Edit: Wat CT zegt is nog een derde plek, in de WHERE. Dat valt onder de categorie in de SELECT list gooien: performt waarschijnlijk erg slecht.

[ Voor 19% gewijzigd door tss68nl op 02-05-2012 12:15 ]

[LocoShady]

tss68nl schreef op woensdag 02 mei 2012 @ 12:13:
Simpel. Subqueries.

En dat kan op twee manieren:
- In de SELECT: dan gaat hij voor iedere rij je antwoord halen. Dat performt over het algemeen voor geen meter.
- In de FROM: Je bouwt een aantal subqueries die met een group by op url_final de antwoorden ophalen, en joint die met een masterquery aan elkaar op diezelfde url_final sleutel.

Edit: Wat CT zegt is nog een derde plek, in de WHERE. Dat valt onder de categorie in de SELECT list gooien: performt waarschijnlijk erg slecht.

De 2e optie lijkt me de dan dus de meest efficiente?

Ik heb tot nu toe dit:

code:

SELECT table2.*
FROM tweets_total table1
LEFT JOIN (SELECT table3.url_final, max(table3.friends_count)
                FROM tweets_total table3 
                 GROUP BY table3.url_final) AS table2 
         ON table1.url_final = table2.url_final
GROUP BY DAY(created_at), url_final

Maar na een half uur nog steeds geen resultaat... (700.000 records in de tabel) In principe is het geen ramp dat de query een keer er heel lang over doet (wil het toch exporteren) maar het maakt het debuggen van m'n query haast niet te doen..

Daarnaast lukt het me nog niet om enkel de eerste occurence ( en een uur erna) te groeperen.. Ik dacht zelf aan zoiets:

code:

SELECT MIN(created_at) as firstoc, 
    url_final, 
    COUNT( DISTINCT screen_name ) AS count_unique_screen_name
FROM tweets_total 
GROUP BY HOUR(created_at), url_final

en vervolgens met min() alleen de laagste te pakken, maar dan groepeert hij ze natuurlijk op het hele uur...

[tss68nl]

Je gooit sowieso een hoop performance weg doordat je nu niet twee subqueries gebruikt maar één. Klinkt raar, maar het is echt zo.

Stel je hebt 1 Mln rijen, niet echt heel veel, en je compressieratio voor je group by is ongeveer 1:10000 (url_final en created_at):
* Met je subquery haal je dan netjes 100 gecalculeerde rijen op...
* Om die vervolgens in je hoofdquery weer te left joinen op 1 Mln rijen...
* Die vervolgens weer door een tweede group by verdicht moeten worden naar 100 rijen....

Beter kan je ieder stuk informatie wat niet uit één query te halen valt in een aparte subquery zetten, en het hoofdresultaat is de join van al deze subqueries.

Daarnaast staat het natuurlijk buiten kijf dat je wel mag zorgen voor een fatsoenlijke index op de kolommen waar de group by op wordt gedaan. En dan het liefst nog een stacked/gecombineerde index die je query-pad ondersteunt.

[tss68nl]

Tip 2:
Ik zie dat je DAY() en HOUR() functies gebruikt in de group by. Niet echt makkelijk als je een index wilt leggen voor performance. Oplossing:
Maak een tijdelijke tabel (hoe tijdelijk mag je zelf weten, zolang het maar een fysieke is), waarin je alle content die je nodig hebt van tweets_total copieert, en twee kolommen toevoegt waarin je DAY(created_at), en HOUR(created_at) voorberekent.

Dat loopt vast even, maar je kan vervolgens wel even een aantal indexes aanmaken, en je group by zal van nature veel sneller lopen. Alternatief is een extra tabel met de primary key van tweets_total en de nieuwe kolommen. Join extra, maar met een index op de extra tabel die ook over de primary key ligt, moet dat bijna net zo snel gaan.

[CT]

maar als je het toch gaat exporteren kan je het misschien beter 'na berekenen' met iets anders (python/java/net/php ofzo) dat werkt denk ik 1000x sneller dan uren klooien met subqueries of nu op 700k records allemaal alters uit te voeren en indexen te plaatsen. Geloof me, je bent dan sneller klaar, en je code kan je ook hergebruiken.

Eigenlijk is de 'vuist regel' een beetje zo; heb je subqueries nodig om je doel te bereiken? -> back to the drawingboard

[tss68nl]

CT schreef op woensdag 02 mei 2012 @ 16:13:
maar als je het toch gaat exporteren kan je het misschien beter 'na berekenen' met iets anders (python/java/net/php ofzo) dat werkt denk ik 1000x sneller dan uren klooien met subqueries of nu op 700k records allemaal alters uit te voeren en indexen te plaatsen. Geloof me, je bent dan sneller klaar, en je code kan je ook hergebruiken.

Eigenlijk is de 'vuist regel' een beetje zo; heb je subqueries nodig om je doel te bereiken? -> back to the drawingboard

Oneens.

Databases zijn mits goed gebruikt vele malen sneller in databewerking/mining dan een programmeertaal zoals PHP, omdat een database set-based werkt ipv row-based zoals in een programmeertaal.

En geen enkele database engine is capabel zonder subquery mogelijkheid. Het probleem is, dat je het snel fout kan doen wat je veel performance kost, maar de voordelen zijn des te groter als je het wel weet te beheersen.

[CT]

Ik zou er ook altijd voor opteren om de data precies 'goed' uit de database te krijgen, en je taalje alleen te laten listen (ipv alles ophalen en dan pas processen).

Maar in dit (ene) geval vermoed ik (weet het overigens niet zeker) dat de persoon deze data niet eeuwig gaat blijven gebruiken? Maar een eenmalige actie? Want dan zou ik pijn en moeite besparen.

Zo niet, en gaat dit serieus een lopend systeem worden, dan zou ik zeker tijd investeren in wat extra werk, en dus verdiepen in indexen e.d.
Maar ik zou toch erg huiverig blijven over subqueries, al zijn er situaties waarin ze onvermijdelijk zijn.
En wellicht wat 'werk' verplaatsen naar het voorwerk, voordat de insert plaatsvindt.

[Knutselsmurf]

Je wilt per dag, per URL het totaal weten, en ook ook hoeveel daarvan in het eerste uur zitten?
Dat kan met een dergelijke constructie (zonder subqueries):

SELECT url,datum,COUNT(ID),SUM(IF(eerste uur,1,0)) FROM tweet GROUP BY url,datum

[tss68nl]

CT schreef op woensdag 02 mei 2012 @ 17:17:
Maar in dit (ene) geval vermoed ik (weet het overigens niet zeker) dat de persoon deze data niet eeuwig gaat blijven gebruiken? Maar een eenmalige actie? Want dan zou ik pijn en moeite besparen.

Het ligt er maar aan wat je pijn en moeite vind denk ik Persoonlijk zou ik altijd voor de SQL oplossing kiezen waar mogelijk, puur omdat me dat makkelijk af gaat. Even een nieuw tabelletje maken is met een 'insert into <tabelnaam> select...' statement bijna hetzelfde als een view/select maken, en een indexje scripten is ook geen straf. Minuutje werk?

@knutsel:
Ik denk dat hem het antwoord niet geeft. De group by moet op dagniveau werken voor de dagtellingen per URL, maar vervolgens wil je het aantal url's in het eerste uur weten uit de select list. De select list wordt geparsed *na* de group by, en krijgt dus enkel reeds geaggregeerde gegevens om op te tellen. Het IF statement zal door de parser ongeldig worden verklaard omdat eerste_uur niet in de group by zit, en IF() geen aggregatiefunctie is.

Oplossing zou een subquery zijn waar je alle items per uur, per url aggregeert, en vervolgens een hoofdquery die deze resultaten aanneemt en verder aggregeert naar dag, url niveau.

Maar goed, gezien TS niet meer reageert, denk ik dat het is opgelost