[SQL] Query om meerdere rows te combineren

[Tsurany]

Ik heb wat hulp nodig met SQL. Ik ben er zelf handig genoeg mee om basale dingen te bereiken maar heb nu wat hulp nodig voor een meer geavanceerder vraagstuk. En wellicht is dit zelfs niet iets wat je met SQL op kan lossen, dan hoor ik dat ook graag.

Het gaat om een automatisch proces dat bepaalde stappen rapporteert naar een database toe. Deze database wordt vervolgens uitgelezen door een webtool en als een rapportage pagina verwerkt. Deze tool is alleen vrij complex en maakt het niet makkelijk om rijen te combineren. Tot nu toe voldeed altijd de laatste status en gebruiken we een query die per proces id de laatste status pakte gebaseerd op DateTime.

Voor een nieuw proces moeten we echter data uit diverse tussenstappen gaan combineren.

ID	Date	Status	Opmerking	Onderdeel
1	01-06-2014	Start
1	02-06-2014	Update		1
1	03-06-2014	Update		2
1	09-06-2014	Verzenden	Via brief
1	10-06-2014	Herinnering
1	15-06-2014	Einde
2	01-06-2014	Start
2	02-06-2014	Update		1
2	04-06-2014	Verzenden	Via pakket

Bovenstaande is een voorbeeld van hoe de tabel ingedeeld kan zijn. Wat ik dan wil is een query die mij per ID kan vertellen wat de laatste status is, hoe vaak een update statement uitgevoerd is, hoeveel verschillende unieke onderdelen voorkomen, hoe vaak een verzending per brief en hoe vaak een verzending via pakket gedaan is.

Het voorbeeld is een beetje simpel maar geeft hoop ik wel aan hoe ons proces er ongeveer uit ziet. In de praktijk hebben we ongeveer twintig verschillende statussen waarbij sommige statussen een keer of 20 kunnen voorkomen met diverse opmerkingen en onderdelen.

Ik zoek geen kant en klare query maar ik zoek wat hulp in hoe zo'n query opgesteld zou moeten worden. Ik weet namelijk wel hoe ik de data te voorschijn kan krijgen als ik het ID als vaste waarde in kan geven, echter hoe kan ik een statement maken die per ID werkt?

Het gaat om een SQL Server 2012 opzet.

[Venxir]

Ik meen zoiets ongeveer, ik doe het uit mijn hoofd dus weet niet of het een goede syntax is

Select * from table having MAX(Date) group by ID ?

Dit zou je in de goede richting moeten zetten en je kan hem customizen naar wens.
Ik denk ook wel dat je naar subqueries moet kijken als je alle info per ID in één regel wilt.

[ Voor 38% gewijzigd door Venxir op 04-08-2014 09:06 ]

[frickY]

Met "GROUP BY" kun je rijen groeperen op een bepaalde kolom, en met COUNT() tellen hoeveel resultaten dat oplevert.
Bijvoorbeeld;

SQL:

SELECT Status, COUNT(*) FROM table GROUP BY Status

Zal iets terug geven als

Start	2
Update	3
Verzenden	2
Herinnering	1

Combineer dit nog met een WHERE op het ID en volgens mij kom je dichtbij wat je zoekt.

Als je 1 row terug wilt per rapport waarbij 'start', 'update', 'verzenden' en 'herinnering' kolommen zijn, wordt het een ander verhaal. Dan zul je de mogelijke status-velden moeten hardcoden in de query en krijg je meer zoiets;

SQL:

SELECT 
   s1.*,
  (SELECT COUNT(s2.*) FROM Status as s2 WHERE s2.ID = s1.ID AND s2.Status = 'Start') AS aantalStarts,
  (SELECT COUNT(s2.*) FROM Status as s2 WHERE s2.ID = s1.ID AND s2.Status = 'Update') AS aantalUpdates
FROM
  Status as s1

Maar dit is, mijn inziens, niet verstandig.

[ Voor 48% gewijzigd door frickY op 04-08-2014 09:33 ]

[jbdeiman]

Wat je dus eigenlijk wil is dat de data die verzonden wordt "geclusterd' (totalen) meestuurt met de data.
Het proces rapporteerd op een bepaald moment, geef je aan in je openingspost:
- Wat is dat voor proces en waar/ wanneer in het gehele proces stuur je die gegevens op?
- Wat is de reden waarom je de data al gegroepeerd uit de database wil halen?

Om een antwoord te kunnen geven waar je echt mee geholpen bent is meer informatie nodig.
Stel het volgende:
1 Het gaat om een koop (webwinkel) proces
2 Een (mogelijke) klant start het proces, die krijgt een id
3 Klant doet een tijdje niets of rond het proces af
4 Indien nodig neemt webwinkel proces "over" en stuurt pakket of brief (afhankelijk van de benodigde actie)

Er zijn dan enkele momenten waarop de informatie verzonden kan worden: Wanneer het proces door de klant beëindigd wordt (stap 3) kan je voor dit proces de gegroepeerde gegevens oversturen.
Wanneer de webwinkel een actie onderneemt op ditzelfde proces.

Simpel gezegt ga je min of meer "live" updaten op bepaalde momenten. De query die frickY kan dan prima voldoen, wanneer de tabel goed is opgebouwd, omdat de query dan snel blijft.


Wil je hetzelfde bereiken, maar dan door met een geplande taak ('s nachts bijvoorbeeld) in een keer alle gegevens over te sturen, dan voldoet die query niet meer, omdat die al heel snel traag gaat worden, door alle subquery's die nodig zijn voor de totalen.

[Tsurany]

Venxir schreef op maandag 04 augustus 2014 @ 09:04:
Ik meen zoiets ongeveer, ik doe het uit mijn hoofd dus weet niet of het een goede syntax is

Select * from table having MAX(Date) group by ID ?

Dit zou je in de goede richting moeten zetten en je kan hem customizen naar wens.
Ik denk ook wel dat je naar subqueries moet kijken als je alle info per ID in één regel wilt.

We hebben er nu al eentje die de laatste status geeft maar de subqueries combineren is waar het probleem zit inderdaad.

frickY schreef op maandag 04 augustus 2014 @ 09:29:
Met "GROUP BY" kun je rijen groeperen op een bepaalde kolom, en met COUNT() tellen hoeveel resultaten dat oplevert.
Bijvoorbeeld;

SQL:

SELECT Status, COUNT(*) FROM table GROUP BY Status

Zal iets terug geven als

Start	2
Update	3
Verzenden	2
Herinnering	1

Combineer dit nog met een WHERE op het ID en volgens mij kom je dichtbij wat je zoekt.

Als je 1 row terug wilt per rapport waarbij 'start', 'update', 'verzenden' en 'herinnering' kolommen zijn, wordt het een ander verhaal. Dan zul je de mogelijke status-velden moeten hardcoden in de query en krijg je meer zoiets;

SQL:

SELECT 
   s1.*,
  (SELECT COUNT(s2.*) FROM Status as s2 WHERE s2.ID = s1.ID AND s2.Status = 'Start') AS aantalStarts,
  (SELECT COUNT(s2.*) FROM Status as s2 WHERE s2.ID = s1.ID AND s2.Status = 'Update') AS aantalUpdates
FROM
  Status as s1

Maar dit is, mijn inziens, niet verstandig.

Het tellen hebben we slechts voor een aantal velden nodig. Maar we willen het dan combineren op basis van ID. Dus per ID weten hoeveel verzendingen we hebben gehad en welk type dat was. En dan ook hoeveel unieke onderdelen.

Dus uiteindelijk een row die per ID het volgende geeft:

ID	Aantal brief	Aantal pakket	Aantal update	Unieke onderdelen	Laatste Status
1	1	0	2	2	Einde
2	0	1	1	1	Verzenden

jbdeiman schreef op maandag 04 augustus 2014 @ 09:45:
Wat je dus eigenlijk wil is dat de data die verzonden wordt "geclusterd' (totalen) meestuurt met de data.
Het proces rapporteerd op een bepaald moment, geef je aan in je openingspost:
- Wat is dat voor proces en waar/ wanneer in het gehele proces stuur je die gegevens op?
- Wat is de reden waarom je de data al gegroepeerd uit de database wil halen?

Gaat om een geautomatiseerd proces dat over enkele maanden loopt waarbij er wat klantinteractie is. Dit proces rapporteert zijn statussen naar een database toe en elke stap in het proces is een status rij in een rapportage tabel.
De reden dat we het gegroepeerd op willen halen is dat we zo een overzicht kunnen creëren van de processen en de aantallen interacties per proces.

Om een antwoord te kunnen geven waar je echt mee geholpen bent is meer informatie nodig.
Stel het volgende:
1 Het gaat om een koop (webwinkel) proces
2 Een (mogelijke) klant start het proces, die krijgt een id
3 Klant doet een tijdje niets of rond het proces af
4 Indien nodig neemt webwinkel proces "over" en stuurt pakket of brief (afhankelijk van de benodigde actie)

Er zijn dan enkele momenten waarop de informatie verzonden kan worden: Wanneer het proces door de klant beëindigd wordt (stap 3) kan je voor dit proces de gegroepeerde gegevens oversturen.
Wanneer de webwinkel een actie onderneemt op ditzelfde proces.

Simpel gezegt ga je min of meer "live" updaten op bepaalde momenten. De query die frickY kan dan prima voldoen, wanneer de tabel goed is opgebouwd, omdat de query dan snel blijft.

Wil je hetzelfde bereiken, maar dan door met een geplande taak ('s nachts bijvoorbeeld) in een keer alle gegevens over te sturen, dan voldoet die query niet meer, omdat die al heel snel traag gaat worden, door alle subquery's die nodig zijn voor de totalen.

We gaan live updaten op het moment dat een stap uitgevoerd wordt. Dus elke keer dat een stap uitgevoerd wordt sturen we daarvoor een update naar de database toe. Deze data wordt vervolgens door een reporting tool ingeladen en zal weergegeven worden waarbij een aantal filters gebruikt kunnen worden op basis van de resultaten van de query.


Edit:

Wat ik nu voor elkaar heb is het volgende. Allemaal losse queries die het ID terug geven en een resultaat zoals een count, een laatste status,... en dat vervolgens via left joins aan elkaar geknoopt. Alleen heb je nu wel vier left joins, wat minder netjes vrees ik.

[ Voor 34% gewijzigd door Tsurany op 04-08-2014 10:30 ]

[Rotterdammertje]

Met een beetje creativiteit kan je alle standaard aggregates per ID onderbrengen in 1 query, die je dan kan joinen aan een query om de status behorende bij de meest recente datum op te vragen. Zoiets dus:

SQL:

SELECT * FROM
(SELECT
    ID,
    MAX(LogDate) AS LAST_DATE,
    SUM(CASE WHEN STATUS = 'Update' THEN 1 ELSE 0 END) AS UPDATE_COUNT,
    COUNT(DISTINCT Onderdeel) AS ONDERDELEN_COUNT,
    SUM(CASE WHEN Opmerking = 'Via brief' THEN 1 ELSE 0 END) AS VIA_BRIEF,
    SUM(CASE WHEN Opmerking = 'Via pakket' THEN 1 ELSE 0 END) AS VIA_PAKKET
FROM dbo.ReportTable
GROUP BY ID) A
JOIN (
    SELECT ID, Status, LogDate
    FROM dbo.ReportTable) B
ON (B.ID = A.ID AND B.LogDate = A.LAST_DATE);

Ik slaagde er zo snel niet in om de meest recente status ook in de eerste query te verwerken..

[Tsurany]

Ah thnx! Dat ziet er wel eenvoudiger uit. Ik ga er mee aan de slag, kijken hoe ver ik er mee kom