[sql] groeperen, maar met behoud waarden

[Dennis]

Tweakers,

Ik heb een SQL query en die moet het volgende uit gaan voeren. Ik werk bij een laboratorium en wij voeren testen uit op monsters. Elke test heeft een datum dat het resultaat is ingevoerd. Nu wil ik per monster de test weten waarvan het resultaat als laatste is ingevoerd. Uiteindelijk wil ik dat dan weer gaan aggregeren.

De volgende resultaten krijg ik nu:

code:

monster testcode        datum
226496  as5i        02-09-08
226496  cd5i        02-09-08
226496  cr5i        02-09-08
226496  cu5i        02-09-08
226496  hg5s        05-09-08
226496  ir5m        27-08-08
226496  me5m        28-08-08
226496  me5o        28-08-08
226496  ni5i        02-09-08
226496  pb5i        02-09-08
226496  zn5i        02-09-08
226496  eox5e       09-09-08
226496  eox5o       04-09-08
226496  pak5o       04-09-08
226496  pem5m       28-08-08
226496  btex5u      03-09-08
226496  grir5m      28-08-08
226496  minol5g     10-09-08
226496  minol5o     04-09-08
226496  pakvrom5h   08-09-08

Dit hierboven is even een voorbeeld van 1 monster (uiteindelijk zijn dat er honderden). Het resultaat van hierboven zou moeten worden:

code:

226496  minol5g     10-09-08

omdat dat de hoogste datum is.

Als ik een GROUP BY uitvoer op monster dan blijven óf alle testen staan, of ik zie ze niet meer. Maar ik wil juist van elk monster weten welke test nou de hoogste datum heeft. Aan een HAVING clausule heb ik volgens mij niets, maar is dit op een andere manier op te lossen??

De enige oplossing die anders overblijft is met subqueries weer met datums vergelijken, maar daar wordt de performance zoals jullie begrijpen niet echt beter van.

Alvast bedankt! .

[BtM909]

Welke query gebruik je nu?

[Face_-_LeSS]

Wat jij zoekt is de MAX functie denk ik.

(Is dit wat: [SQL] Max en Datum bij een GROUP BY)

[ Voor 50% gewijzigd door Face_-_LeSS op 02-12-2008 11:39 ]

[Dennis]

BtM909 schreef op dinsdag 02 december 2008 @ 11:32:
Welke query gebruik je nu?

Dit is hem, ik hoop dat hij een beetje begrijpbaar is:

SQL:

        SELECT
          s.code samplecode, 
          st.code sampletestcode, 
          max(stsh.fromdate) resultdate
        FROM
          sample s
        LEFT JOIN
          sample_fl sfl ON
          sfl.id = s.id
        LEFT JOIN
          sampletest st ON
          st.sample_id = s.id
        LEFT JOIN
          sampleteststathist stsh ON
          stsh.smptst_id = st.id
        WHERE
          s.status = 300 AND
          s.isctrlsample = 0 AND
          sfl.receiptdate >= to_date('2008-07-01', 'yyyy-MM-dd') AND
          s.prioactualdate > s.priofinishdate AND
          stsh.status = 
          (
            SELECT
              MIN(ststathist.status)
            FROM
              sampleteststathist ststathist
            WHERE
              ststathist.smptst_id = st.id AND
              ststathist.status >= 240
          )
        GROUP BY
          s.code, 
          st.code

En nog even verduidelijkt, maar dit moet er dus gebeuren:

code:

van

monster test    datum
228598  rap10x  26-11-08
228598  rap20x  20-10-08
228598  rap30x  13-11-08
228599  rap10x  26-11-08
228599  rap20x  20-10-08
228599  rap30x  13-11-08
228600  rap10x  26-11-08
228600  rap20x  30-11-08
228600  rap30x  13-11-08

naar

monster test    datum
228598  rap10x  26-11-08
228599  rap10x  26-11-08
228600  rap20x  30-11-08

naar

test    aantal
rap10x  2
rap20x  1

.

[P_de_B]

Met een subquery:

SQL:

SELECT MonsterId, Waarde, Datum
FROM Tabel T1
WHERE Datum = (SELECT MAX(Datum) FROM Tabel WHERE MonsterId = T1.MonsterId)

edit: oh, dat had je al bedacht. Weet je zeker dat de performance zo slecht is? Met 'enkele honderden' monsters is dat echt niet het geval.

[ Voor 31% gewijzigd door P_de_B op 02-12-2008 11:43 ]

[Confusion]

Je hebt een subquery nodig, om eerst de max(fromdate) per sample te bepalen en daarna de gegevens van de test op die datum op te halen. Je krijgt bijvoorbeeld een constructie als:

SQL:

WHERE stsh.fromdate = (select max(fromdate) from stsh where <en dan weer op de samplecode en sampletestcode matchen) 
 

[Janoz]

Bedenk trouwens dat je meerdere tests bij een monster terug krijgt wanneer ze op dezelfde datum plaats vonden. Dat is vanzelfsprekend vanuit het oogpunt van de database (welke test zou hij immers moeten kiezen?), maar het zou best wel eens kunnen zijn dat dat niet is wat je verwacht.

[Dennis]

Allereerst bedankt voor jullie supersnelle berichten allemaal! .

P_de_B schreef op dinsdag 02 december 2008 @ 11:41:
Met een subquery:

SQL:

SELECT MonsterId, Waarde, Datum
FROM Tabel T1
WHERE Datum = (SELECT MAX(Datum) FROM Tabel WHERE MonsterId = T1.MonsterId)

edit: oh, dat had je al bedacht. Weet je zeker dat de performance zo slecht is? Met 'enkele honderden' monsters is dat echt niet het geval.

Ja nou, eigenlijk zijn het dus al duizenden monsters met daaraan dan ook zo'n tien testen per monster gekoppeld. Dus dat zijn toch wel aardig wat rijtjes. Ik haal het ook niet op uit 1 tabel, dan was het nog makkelijk geweest.

Probleem met de subqueries is dat ik dan dezelfde tabel zou moeten joinen. Ik wil nog heel veel criteria flexibel houden, dus daar kan ik ook niet al te veel mee doen. Daarom ga ik denk ik maar een view maken, met nog wat velden erbij waar ik in de toekomst ook op wil filteren en ga ik daar de subquery op loslaten. Die blijft dan iig. leesbaar.

Janoz schreef op dinsdag 02 december 2008 @ 11:45:
Bedenk trouwens dat je meerdere tests bij een monster terug krijgt wanneer ze op dezelfde datum plaats vonden. Dat is vanzelfsprekend vanuit het oogpunt van de database (welke test zou hij immers moeten kiezen?), maar het zou best wel eens kunnen zijn dat dat niet is wat je verwacht.

Goede opmerking. Ik ben me daar van bewust maar die kans is zo verdomde klein (gaat op de seconde) dat dat voor het resultaat niet uit maakt. Stel dat het bij 1% voor zou komen, dan zouden mijn analyse in grote lijnen nog steeds kloppend zijn, en in dit geval is dat voldoende .

[Confusion]

Dennis schreef op dinsdag 02 december 2008 @ 11:56:
Probleem met de subqueries is dat ik dan dezelfde tabel zou moeten joinen.

Dat is geen probleem, dat is een oplossing .

[P_de_B]

Ik begrijp ook niet precies waarom dat een probleem zou zijn.

Ik verwacht overigens geen performanceverschil tussen joinen met een view of joinen met dezelfde tabel. Ook in het geval van een subquery is de kans erg groot dat het executieplan hetzelfde zal zijn. Het enige voordeel zou een materialized view kunnen zijn, maar dan heb je veelal wel een dure versie van je databasesoftware nodig.

[Verwijderd]

Dennis schreef op dinsdag 02 december 2008 @ 11:56:
Probleem met de subqueries is dat ik dan dezelfde tabel zou moeten joinen. Ik wil nog heel veel criteria flexibel houden, dus daar kan ik ook niet al te veel mee doen. Daarom ga ik denk ik maar een view maken, met nog wat velden erbij waar ik in de toekomst ook op wil filteren en ga ik daar de subquery op loslaten. Die blijft dan iig. leesbaar.

Een VIEW maken kan inderdaad erg nuttig zijn voor de leesbaarheid, maar let op: het helpt absoluut niet voor de snelheid van de query. Een VIEW gaat intern de originele query uitvoeren, dus daarmee kun je geen snelheidswinst behalen (tenzij je de originele query optimaliseert natuurlijk).

Edit: een materialized view zou wel kunnen helpen inderdaad, maar die mogelijkheid heb je niet altijd

[ Voor 6% gewijzigd door Verwijderd op 02-12-2008 12:40 ]

[Dennis]

Nou, we gebruiken wel een dure versie van Oracle, dus misschien dat we er nog iets aan hebben. Inmiddels heb ik de boel werkend gekregen, met leuke queries (kolom en viewnamen moet ik nog even ééntalig maken).

De view:

SQL:

        SELECT
          c.code klantcode, 
          p.code projectcode, 
          r.code opdrachtcode, 
          s.code samplecode, 
          st.code sampletestcode, 
          max(stsh.fromdate) resultdate
        FROM
          sample s
        LEFT JOIN
          sample_fl sfl ON
          sfl.id = s.id
        LEFT JOIN
          sampletest st ON
          st.sample_id = s.id
        LEFT JOIN
          sampleteststathist stsh ON
          stsh.smptst_id = st.id
        LEFT JOIN
          request r ON
          r.id = s.request_id
        LEFT JOIN
          project p ON
          p.id = s.proj_id
        LEFT JOIN
          customer c ON
          c.id = p.cust_id
        WHERE
          s.status = 300 AND
          s.isctrlsample = 0 AND
          sfl.receiptdate >= to_date('2008-07-01', 'yyyy-MM-dd') AND
          s.prioactualdate > s.priofinishdate AND
          stsh.status = 
          (
            SELECT
              MIN(ststathist.status)
            FROM
              sampleteststathist ststathist
            WHERE
              ststathist.smptst_id = st.id AND
              ststathist.status >= 240
          )
        GROUP BY
          c.code, 
          p.code, 
          r.code, 
          s.code, 
          st.code
        ORDER BY
          s.code, 
          st.code

De query:

SQL:

SELECT
  *
FROM
  (
    SELECT
      count(x.monstercode) aantal, 
      x.testcode testcode, 
      tvfl.anal_group analysegroep
    FROM
      (
        SELECT
          vlb.samplecode monstercode, 
          MAX(vlb.sampletestcode) testcode
        FROM
          v_levertijden_brondata vlb
        WHERE
          vlb.resultdate = 
          (
            SELECT 
              MAX(resultdate) 
            FROM 
              v_levertijden_brondata vlbr 
            WHERE 
              vlbr.samplecode = vlb.samplecode AND 
              vlbr.sampletestcode = vlbr.sampletestcode
          )
        GROUP BY
          vlb.samplecode, 
          vlb.resultdate
      ) x
    LEFT JOIN
      testversion tv ON
      tv.code = x.testcode
    LEFT JOIN
      testversion_fl tvfl ON
      tvfl.id = tv.id
    GROUP BY
      x.testcode, 
      tvfl.anal_group
  ) y
ORDER BY
  y.aantal DESC

De query doet er nu 142 seconden over. Nog redelijk acceptabel, maar ik ga het zoekbare gebied nog uitbreiden van 1 juli naar 1 januari, dus ik verwacht dan een verdubbeling van de benodigde tijd.

@Janoz, uiteindelijk waren die dubbele records tóch veelvoorkomend en een probleem, en daarom heb ik maar een group by met max gezet op de tekstwaarde. Dat maakt voor het beeld niet zoveel uit, omdat ik, zoals je misschien ziet, de testen groepeer in voorgedefinieerde groepen en die dubbele betreffen altijd testen uit één groep.

.

[Verwijderd]

Dennis schreef op dinsdag 02 december 2008 @ 13:12:
De query doet er nu 142 seconden over. Nog redelijk acceptabel, maar ik ga het zoekbare gebied nog uitbreiden van 1 juli naar 1 januari, dus ik verwacht dan een verdubbeling van de benodigde tijd.

Of dat acceptabel is, is natuurlijk nog maar de vraag... In jullie geval zal dat vast goedkomen, maar voor een webapplicatie zou je hier heel verdrietig van worden

[Plekuz]

Dennis schreef op dinsdag 02 december 2008 @ 13:12:
De query doet er nu 142 seconden over. Nog redelijk acceptabel, maar ik ga het zoekbare gebied nog uitbreiden van 1 juli naar 1 januari, dus ik verwacht dan een verdubbeling van de benodigde tijd.

Wellicht kun je de query enorm versnellen met een paar slimme indexen erbij. Ik weet alleen niet of je op dat niveau de database mag/kunt aanpassen en welke tools Oracle biedt om achter de meest ideale indexing te komen.

[Dennis]

Verwijderd schreef op dinsdag 02 december 2008 @ 13:23:
Of dat acceptabel is, is natuurlijk nog maar de vraag... In jullie geval zal dat vast goedkomen, maar voor een webapplicatie zou je hier heel verdrietig van worden

Klopt ja. Voorlopig zal deze query denk ik maar één keer per maand worden gebruikt, en dan vooral in managementoverzichten.

Weakling schreef op dinsdag 02 december 2008 @ 13:29:
Wellicht kun je de query enorm versnellen met een paar slimme indexen erbij. Ik weet alleen niet of je op dat niveau de database mag/kunt aanpassen en welke tools Oracle biedt om achter de meest ideale indexing te komen.

Ik mag gelukkig indexes aanmaken, dat heb ik ook al gedaan maar blijkbaar niet op de juiste plaatsen want de performance van de view is al crap. Ik heb zelf ook geen idee welke tools er beschikbaar zijn voor optimalisatie, maar onze ICT wordt door één van onze moedermaatschappijen verzorgd en daar werken twee Oracle DBA's. Dus wellicht ga ik daar de vraag eens uitzetten .

[Aetos]

Ik vraag me af waarom je left joins gebruikt? Is hier een reden voor? Ik weet niet hoe het bij oracle zit, maar bij de dbms die ik gebruik, betekent left join eigenlijk left outer join.

Je gebruikt duidelijk het feit dat het een outer join is niet. In de view wordt er van de rechter zijde van de outer joins geeist dat ze een andere invulling dan null hebben.

Misschien dat je dbms door het vervangen van de outer joins door inner joins nog iets kan optimaliseren. Hoewel je dbms zelf kan detecteren dat het geen outer joins zijn.

[Verwijderd]

Dennis schreef op dinsdag 02 december 2008 @ 13:12:
Nou, we gebruiken wel een dure versie van Oracle

Zeg dat dan meteen

code:

select * from (
select my_table.*, row_number() over(partition by test order by datum desc, monster desc) rn from my_table)
) where rn = 1

[curry684]

Aetos schreef op dinsdag 02 december 2008 @ 13:43:
Ik vraag me af waarom je left joins gebruikt? Is hier een reden voor? Ik weet niet hoe het bij oracle zit, maar bij de dbms die ik gebruik, betekent left join eigenlijk left outer join.

Kun je me eens uitleggen wat een left inner join zou moeten doen dan?

In iedere SQL-compliant DB is 'left join' shorthand voor 'left outer join'.

[Aetos]

Hetzelfde als een right inner join? De reden dat left of right bij een inner join niet voorkomen in de implementaties die ik wel ken?

[Dennis]

De reden dat ik left joins gebruik is dat ze precies doen wat ik wil .

@mark platvoet: van je voorbeeld begrijp ik niets, niet eens wat het doet. Ik kan het natuurlijk wel opzoeken, maar kun je een korte toelichting geven voor nitwit's zoals ik? .

Verder ben ik totaal in mijn nopjes met de oplossing die ik nu (mede dankzij jullie) heb. Nu kan ik eindelijk zien welke personenafdelingen nou verantwoordelijk zijn voor de te late levering aan onze klanten .

[Aetos]

Kan je dan aangeven waarom ze precies doen wat je wilt? Waar heb je het nodig dat een left join doet wat een left join doet t.o.v. een inner join?

In je select query doe je het volgende:

select a.a2, b.b3, c.c4 from a left join b left join c where b.b1 = 300 and c.c2 > '2008-10-10'.

Dit zal je b.v. niet de garantie geven dat je query over alle elementen van a resultaten op gaat leveren.

[Dennis]

Aetos schreef op dinsdag 02 december 2008 @ 15:52:
Kan je dan aangeven waarom ze precies doen wat je wilt? Waar heb je het nodig dat een left join doet wat een left join doet t.o.v. een inner join?

In je select query doe je het volgende:

select a.a2, b.b3, c.c4 from a left join b left join c where b.b1 = 300 and c.c2 > '2008-10-10'.

Dit zal je b.v. niet de garantie geven dat je query over alle elementen van a resultaten op gaat leveren.

Nou ben ik even niet zo snel bekend met de precieze verschillen, maar als dat is dat je met INNER JOIN wél de garantie hebt alleen complete recordsets te ontvangen dan heb je gelijk en kan ik beter dát gebruiken .

[Verwijderd]

[b][message=3114026
@mark platvoet: van je voorbeeld begrijp ik niets, niet eens wat het doet. Ik kan het natuurlijk wel opzoeken, maar kun je een korte toelichting geven voor nitwit's zoals ik? .

Wat ik doe is gebruik maken van de analitische functies van Oracle. Door een rij nummer toe te wijzen aan een gepartitioneerde set data (te vergelijken met een group by clause) kun je vrij eenvoudig het eerste element opvragen van een partitie.


Dus zorg eerst dat je een rij nummer op de juiste manier toewijst. Onderstaand is uiteraard pseudo, je zult zelf de juiste kolomnamen moeten gebruiken.

Partitioneren (groeperen) wil je vast op 'monster'.
Sorteren wil je vast op 'test_datum' aflopend.
Tabel is 'my_tabel':

code:

select
  my_table.*,
  row_number() over(partition by monster order by test_datum desc) rn
from
  my_table

Je krijgt nu iets als:
[code]
226496 minol5g 10-09-08 1
226496 eox5e 09-09-08 2
226496 pakvrom5h 08-09-08 3
226496 minol5o 04-09-08 4
[code]

Vervolgens hoef je enkel van alle gepartioneerde setjes de rijen met rijnummer 1 op te vragen:

code:

select * from (
select
  my_table.*,
  row_number() over(partition by monster order by test_datum desc) rn
from
  my_table
) where rn = 1