[sql] sub query met multiple rows

[Verwijderd]

Ik heb een tabel met de volgende gegevens:
datum (Ymd), herkost (A,B)

nu wil ik een tabel eruit krijgen die er als volgt uit ziet
datum | A | B
01-12-2009 | 6 | 5
02-12-2009| 0| 11
03-12-2009 | 5 | 0

etc

Alleen lukt het me niet om de query te laten werken, en is het me niet gelukt de oplossing te vinden, dus hopelijk kunnen jullie me verder helpen

SQL: multiquery

SELECT
count_a IN (count(`A`) FROM `table` WHERE `date` < 'startdate' AND `date` > 'enddate' AND `herkomst` = 'A' GROUP BY `date`),
count_b IN (count(`B`) FROM `table` WHERE `date` < 'startdate' AND `date` > 'enddate' AND `herkomst` = 'B' GROUP BY `date`),
date IN (`date` FROM `table` WHERE `date` < 'startdate' AND `date` > 'enddate' GROUP BY `date`)

Het resultaat is dat de kolomnamen de gehele subquery zijn met waarde 0 en het resultaat maar 1 rij groot is.
Ik krijg geen foutmeldingen, en snap feitelijk niet wat ik hier fout doe.

[P_de_B]

Je wilt dus de totalen van A en B per dag, eventueel binnen een bepaalde range?

[Verwijderd]

Ja daar komt het opneer.
Heel simpel, maar ik krijg het er niet uit.

[P_de_B]

Bedoel je trouwens niet SUM? Geef eens een paar regels voorbeeld van de tabel.

[Verwijderd]

SQL:

[b]datum | herkomst[/b]
20091201 | A
20091201 | A
20091201 | B
20091201| A
20091202| B
20091202| B

enz

Met SUM zou ik een numerieke waarde moeten hebben, bijvoorbeeld 1, en dit bijelkaar optellen, maar ik wil de rijen tellen die per datum, per herkomst erin zitten

[ Voor 38% gewijzigd door Verwijderd op 22-12-2009 13:15 ]

[Compuhair]

code:

select distinct
   datum,
   (select count(*) from [table] t2 where herkomst = 'a' and t2.datum = t1.datum),
   (select count(*) from [table] t2 where herkomst = 'b' and t2.datum = t1.datum)
from [table] t1

dit zou moeten werken

[ Voor 4% gewijzigd door Compuhair op 22-12-2009 13:16 ]

[P_de_B]

SQL:

SELECT  datum, SUM(CASE herkomst WHEN 'a' THEN 1 ELSE 0 END) AS CountA, 
        SUM(CASE herkomst WHEN 'b' THEN 1 ELSE 0 END) AS CountB
FROM test
GROUP BY datum

Niet getest op MySQL overigens, maar ik neem aan dat dit moet werken.

[Verwijderd]

Mooi het werkt, dank je.

Als ik de code goed begrijp wekt het zo (anders zit ik hier de volgende keer weer )

De datum (met range) werkt als een standard query, de alias voor de tabel zorgt ervoor dat de subqueries zit richten op deze rij alleen en dat levert het gewenst resultaat op.

Thanks!

[P_de_B]

Compuhair schreef op dinsdag 22 december 2009 @ 13:14:

code:

select distinct
   datum,
   (select count(*) from [table] t2 where herkomst = 'a' and t2.datum = t1.datum),
   (select count(*) from [table] t2 where herkomst = 'b' and t2.datum = t1.datum)
from [table] t1

dit zou moeten werken

Zowel jouw oplossing als die van mij geven dezelfde output. Ik denk echter dat die van mij iets sneller zal zijn. (op MS SQL in ieder geval wel)

[Compuhair]

P_de_B schreef op dinsdag 22 december 2009 @ 13:23:
[...]

Zowel jouw oplossing als die van mij geven dezelfde output. Ik denk echter dat die van mij iets sneller zal zijn. (op MS SQL in ieder geval wel)

daar kan je wel eens gelijk in hebben. mijn query voert er eigenlijk drie uit, en dan nog een distinct er overheen.

[Verwijderd]

P_de_B schreef op dinsdag 22 december 2009 @ 13:19:

SQL:

SELECT  datum, SUM(CASE herkomst WHEN 'a' THEN 1 ELSE 0 END) AS CountA, 
        SUM(CASE herkomst WHEN 'b' THEN 1 ELSE 0 END) AS CountB
FROM test
GROUP BY datum

Niet getest op MySQL overigens, maar ik neem aan dat dit moet werken.

Laadtijd: 0.0009 seconden

Compuhair schreef op dinsdag 22 december 2009 @ 13:14:

code:

select distinct
   datum,
   (select count(*) from [table] t2 where herkomst = 'a' and t2.datum = t1.datum),
   (select count(*) from [table] t2 where herkomst = 'b' and t2.datum = t1.datum)
from [table] t1

dit zou moeten werken

Laadtijd 0.0380 seconden

Ik heb uiteraard voor de snelste gekozen, maar het is altijd mooi als je meerdere oplossing aangereikt krijgt

[Compuhair]

Verwijderd schreef op dinsdag 22 december 2009 @ 13:34:
[...]

Laadtijd: 0.0009 seconden


[...]

Laadtijd 0.0380 seconden

Ik heb uiteraard voor de snelste gekozen, maar het is altijd mooi als je meerdere oplossing aangereikt krijgt

En het lullige is dat je in eerste instantie denkt dat je een goede query hebt geschreven, met 0,038 seconden, maar vervolgens wordt je DB 2 gig, en krijg je 100 requests per seconde en dan merk je ineens het verschil.

[cariolive23]

En je weet zeker dat die 0.0009 sec niet de cache is?

Ga met EXPLAIN aan de slag, daarmee kun je zien hoe een query wordt uitgevoerd door de database en wat sneller is.

[P_de_B]

cariolive23 schreef op dinsdag 22 december 2009 @ 14:27:
En je weet zeker dat die 0.0009 sec niet de cache is?

Ga met EXPLAIN aan de slag, daarmee kun je zien hoe een query wordt uitgevoerd door de database en wat sneller is.

Ik heb net een eplain in MS SQL gedaan en daar is 'mijn' variant ook sneller. Ik vraag me af of hij bij deze twee toch wel verschillende queries een cache hit heeft. SQL Server heeft in ieder geval niet hetzelfde execution plan.

[cariolive23]

Welke database gebruikt de TS? En welke testset gebruikt de TS om te testen? Op een setje van 100 records voert de database andere queryplannen uit dan op een setje van 100.000 records. Althans, PostgreSQL doet dat wel, is slim genoeg om altijd te proberen het meest optimale queryplan op te stellen. Een goede testset is dan ook onmisbaar.

Verder maakt het ook uit hoe de database is geconfigureerd, met een belabberde configuraties dwing je de database al vrij snel om een sequential scan te doen. En als er iets langzaam is...

Er zijn dus vele parameters waar je rekening mee moet houden, je kunt niet zomaar zeggen dat de ene query sneller is dan de andere query. Je moet e.e.a. afstemmen en testen op een omgeving die vergelijkbaar is met de productieomgeving en die al fllink is geoptimaliseerd.

[Bolukan]

Omvang van database zal wel meevallen:
365 dagen per jaar en zo'n 11 records per dag (conform voorbeeld). Ca 4.000 records per jaar. De omvang valt dan wel mee.