[MySQL] rows met elkaar mergen

[smokalot]

Ik heb een lastig probleem waar ik niet uit kom.

Ik heb één tabel, daarin staan een soort meetwaarden, met soms een volgnummer. Nou moet ik die meetwaardes met elkaar vergelijken, dus moeten er meerdere rows gecombineerd worden tot één. Of ze bij elkaar horen wordt bepaald door twee values in die row.

Dus:

Identifier 1	Identifier 2	Volgnr	Waarde
15	9	0	foo
13	9	0	tekst
13	9	1	text

daarvan zou ie zoiets moeten maken:

Identifier 1	Identifier 2	Waarde1	Waarde2
15	9	foo
13	9	tekst	text

Het dichtst bij ben ik nu gekomen met een dergelijke query:

SQL:

SELECT A.id_1, A.id_2, A.waarde AS Waarde_1, B.waarde AS Waarde_2
FROM Tabel A, Tabel B
WHERE
    A.id_1 = B.id_1
    AND A.id_2 = B.id_2
    AND A.volgnr = 0
    AND B.volgnr = 1

Maar dan laat ie alleen de rijen zien waarbij er een record met volgnr 1 bestaat, terwijl ik wil dat ie NULL is als ie niet bestaat.

Nu kan ik dit natuurlijk wel met een java/php programmaatje oplossen, maar dan moet ik die iedere keer draaien als er weer records bijgezet zijn in de tabel. Heeft iemand een idee hoe ik dit aan kan pakken in SQL?

[Tharulerz]

Edit: foute query

Toch moet het mogelijk zijn met een LEFT Self join..

[ Voor 100% gewijzigd door Tharulerz op 23-06-2010 20:40 ]

[Gomez12]

Toch zou ik dit in php/java oplossen. Juist in sql moet je steeds een aanpassing doen als je een kolom erbij wilt hebben ( weer een left selfjoin erbij dat gaat niet echt fantastisch werken imho ).

In php / java is het enkel een while loopje eroverheen. Of als je met een fixed file format werkt zou je eerst een query ala : select max(count(volgnr)) kunnen doen om het maximale aantal rijen in gebruik te bepalen.

[smokalot]

Ik geloof dat ik m nu (bijna?) heb!

Ik had uiteraard al wel joins geprobeerd, en zelfs left joins, maar het probleem was dat ik dan ook rijen kreeg die ik niet wilde. Ik heb nu een LEFT JOIN met een GROUP BY, en die lijkt het gewenste resultaat te hebben.

Zoiets dus:

SQL:

SELECT A.id_1, A.id_2, A.waarde AS Waarde_1, B.waarde AS Waarde_2, C.waarde AS Waarde_3 
FROM
    Tabel A 
        LEFT JOIN Tabel B 
            ON A.id_1=B.id_1 
            AND A.id_2=B.id_2
            AND B.volgnr = 1
        LEFT JOIN Tabel C
            ON A.id_1=C.id_1 
            AND A.id_2=C.id_2
            AND C.volgnr = 2
WHERE A.volgnr = 0
GROUP BY A.id_1, A.id_2;

[RobIII]

Hoe werkt dat GROUP BY nu eigenlijk?

[NMe]

Waarom heb je überhaupt een group by gekozen? Je maakt niet eens gebruik van een aggregate function, dus die group by voegt niet veel toe.

[smokalot]

NMe schreef op woensdag 23 juni 2010 @ 23:37:
Waarom heb je überhaupt een group by gekozen? Je maakt niet eens gebruik van een aggregate function, dus die group by voegt niet veel toe.

Mmm, die GROUP BY had ik gedaan omdat in een eerdere query sommige rijen er dubbel inkwamen. de GROUP BY loste dat op, maar nu doet ie inderdaad niets meer.

[NMe]

smokalot schreef op donderdag 24 juni 2010 @ 00:01:
[...]

Mmm, die GROUP BY had ik gedaan omdat in een eerdere query sommige rijen er dubbel inkwamen. de GROUP BY loste dat op, maar nu doet ie inderdaad niets meer.

Group by heeft niks te maken met het filteren van dubbele rijen; het enige waar je dat fatsoenlijk mee kan afvangen is distinct, maar die kun je beter ook maar mijden in de meeste gevallen.

't Komt een beetje over alsof je maar wat in elkaar geprakt hebt zonder te weten wat bepaalde clauses in je query doen. Da's niet handig debuggen natuurlijk, dus je zou er goed aan doen om even na te pluizen wat een group by ís voordat je hem gebruikt.

[CH4OS]

NMe schreef op donderdag 24 juni 2010 @ 00:13:
't Komt een beetje over alsof je maar wat in elkaar geprakt hebt zonder te weten wat bepaalde clauses in je query doen. Da's niet handig debuggen natuurlijk, dus je zou er goed aan doen om even na te pluizen wat een group by ís voordat je hem gebruikt.

Desalniettemin vraag ik me af of de database die gebruikt word genormaliseerd is. Daarmee zijn problemen als dit imo ook te voorkomen of in ieder geval de kans hierop aanzienlijk te verkleinen.

[ Voor 5% gewijzigd door CH4OS op 24-06-2010 00:29 ]

[smokalot]

NMe schreef op donderdag 24 juni 2010 @ 00:13:
[...]

Group by heeft niks te maken met het filteren van dubbele rijen; het enige waar je dat fatsoenlijk mee kan afvangen is distinct, maar die kun je beter ook maar mijden in de meeste gevallen.

De rijen waren niet helemaal dubbel, probleem was dat ie een combinatie id_1/id_2 meerdere keren terug gaf als er meerdere volgnrs waren, eerst n-1 keren met ongewenste waarden voor Waarde_2 en hoger, en de laatste rij was zoals ik m wilde hebben. Een GROUP BY geeft dan alleen die waarden terug.

't Komt een beetje over alsof je maar wat in elkaar geprakt hebt zonder te weten wat bepaalde clauses in je query doen. Da's niet handig debuggen natuurlijk, dus je zou er goed aan doen om even na te pluizen wat een group by ís voordat je hem gebruikt.

Er is een hoop wat ik nog bij kan leren van SQL, maar het is niet zo dat ik geen idee heb wat een GROUP BY doet. In de query die ik dus eerder had loste de GROUP BY dus wel degelijk een van mijn problemen op, en daarna was ik vergeten m weg te halen.

CptChaos schreef op donderdag 24 juni 2010 @ 00:29:
[...]
Desalniettemin vraag ik me af of de database die gebruikt word genormaliseerd is. Daarmee zijn problemen als dit imo ook te voorkomen of in ieder geval de kans hierop aanzienlijk te verkleinen.

Ik heb er over nagedacht, maar ik kon geen manier bedenken om een handigere tabelstructuur te maken. Volgens mij kan ik m niet verder normaliseren. Wat zou je voorstellen dan?

Ik kan niet vantevoren weten hoeveel volgnrs er bij een bepaalde id_1/id_2 combinatie zitten. Ik wil ze wel allemaal in de database hebben, maar voor wat ik nu wil bereiken hoef ik alleen de eerste vijf ofzo te hebben.

[NMe]

smokalot schreef op donderdag 24 juni 2010 @ 01:51:
[...]

Er is een hoop wat ik nog bij kan leren van SQL, maar het is niet zo dat ik geen idee heb wat een GROUP BY doet. In de query die ik dus eerder had loste de GROUP BY dus wel degelijk een van mijn problemen op, en daarna was ik vergeten m weg te halen.

Juist met dit antwoord laat je merken dat je niet weet wat group by eigenlijk precies doet. Door aan te geven dát je wil groeperen maar niet aan te geven wát je wil groeperen is hetgeen je groepeert in MySQL eigenlijk complete willekeur. Je kreeg puur bij toeval^* net die records terug die je wilde, en programmeren op toevalligheden kan ik niet bepaald aanraden. Het is niet voor niets onmogelijk in de meeste andere DBMS'en om dit soort constructies te bouwen.

* Helemaal toevallig is het niet, aangezien het met de insert-volgorde te maken zal hebben.

[smokalot]

NMe schreef op donderdag 24 juni 2010 @ 12:47:
[...]

Juist met dit antwoord laat je merken dat je niet weet wat group by eigenlijk precies doet. Door aan te geven dát je wil groeperen maar niet aan te geven wát je wil groeperen is hetgeen je groepeert in MySQL eigenlijk complete willekeur. Je kreeg puur bij toeval^* net die records terug die je wilde, en programmeren op toevalligheden kan ik niet bepaald aanraden. Het is niet voor niets onmogelijk in de meeste andere DBMS'en om dit soort constructies te bouwen.

* Helemaal toevallig is het niet, aangezien het met de insert-volgorde te maken zal hebben.

Ik snap niet wat je bedoelt met "wát je wil groeperen", ik geef toch aan dat ik op id_1, id_2 wil groeperen? Ik wil namelijk maar één rij per combinatie.

Ik weet dat de GROUP BY constructie misgaat als de rijen in een andere volgorde staan, maar omdat ik zeker weet dat een hoger volgnr ook later in de tabel staat dacht ik van de (niet gedocumenteerde) eigenschap van GROUP BY dat deze altijd de laatste waardes van de groep teruggeeft gebruik te maken. Ik was daar dus ook niet blij mee, en vandaar dat ik er een topic van heb gemaakt.


Ik ben hier niet om mijn SQL vaardigheden te verdedigen, maar om te begrijpen wat de beste oplossing is van mijn probleem. Ik weet dat ik nog veel kan leren van SQL en datamodelleren, maar zo dramatisch als jij het stelt is het ook weer niet.


Hoe kun je dan, zonder GROUP BY, alleen de rij returnen met het hoogste volgnr van een tabel als

id_1	id_2	Volgnr	Waarde 1	Waarde 2
2	3	0	foo	niet_bar
2	3	1	foo	bar
2	4	0	text	tekst

En ik ben ook wel benieuwd hoe ik dan een betere tabelstructuur had kunnen maken, zodat dit makkelijker was geweest. id_1 verwijst naar een persoon in een andere tabel. id_2 verwijst naar een opdracht. Ik heb tientallen proefpersonen en honderden opdrachten. De tabel waar ik het over heb geeft aan hoe een persoon op een opdracht heeft gepresteerd. Als deze prestatie niet naar wens was, had de proefpersoon de mogelijkheid om het nog een keer te doen. Er is vantevoren geen maximum aantal pogingen vastgesteld.

Ik wil bekijken of de prestaties in latere pogingen beter zijn dan in de eerste poging, om de prestaties tussen de pogingen te kunnen vergelijken met SQL moet ik alles op één rij zien te krijgen, en daar is deze query voor.

[RobIII]

Heb je dit nou eigenlijk al eens gelezen?

RobIII schreef op woensdag 23 juni 2010 @ 23:29:
Hoe werkt dat GROUP BY nu eigenlijk?

En dan met name het stukje boven (en tot en met):

MySQL is een hele brakke database, die deze laatste constructie wel toestaat. En volgens de handleiding is het 'by design' dat je vervolgens random waardes in kolom B aantreft. Don't do it.

M.a.w.: Je begrijpt GROUP BY helemaal niet (zo goed als je denkt)

smokalot schreef op donderdag 24 juni 2010 @ 14:09:
Ik weet dat de GROUP BY constructie misgaat als de rijen in een andere volgorde staan

Since when? Sowieso bedoel je kolommen en geen rijen en zelfs dan boeit het nog niet in welke volgorde ze staan (behalve voor de group zelf natuurlijk)

smokalot schreef op donderdag 24 juni 2010 @ 14:09:

Ik ben hier niet om mijn SQL vaardigheden te verdedigen, maar om te begrijpen wat de beste oplossing is van mijn probleem. Ik weet dat ik nog veel kan leren van SQL en datamodelleren, maar zo dramatisch als jij het stelt is het ook weer niet.

Dramatisch wellicht niet, maar je doet het niet goed.

Voor wat betreft je probleem: ik zie niet waarom je dit in SQL wil oplossen en dit niet gewoon in je presentatielaag afhandelt. (Of ik mis iets...)


Edit:

smokalot schreef op donderdag 24 juni 2010 @ 14:09:
Ik snap niet wat je bedoelt met "wát je wil groeperen", ik geef toch aan dat ik op id_1, id_2 wil groeperen?

En de rest van de velden in je select dan? Waar staan die in je GROUP BY?

quote: http://gathering.tweakers..._messages/1105378#groupby

Waarom zegt ie dat? Kijk eens mee naar de tabel waar we deze query op doen. We vragen hem te groeperen op identieke waarden in kolom A. Echter, als we dat doen, welke waarde van B moeten we dan selecteren bij waarde 1 in A? Daar kan je zowel 1 als 2 bij verwachten in kolom B, en omdat dat niet gespecificeerd is staat de database het terecht niet toe.

^ En dan het stukje over MySQL dat ik aan 't begin van deze post aanhaal.

[ Voor 65% gewijzigd door RobIII op 24-06-2010 16:14 ]

[smokalot]

RobIII schreef op donderdag 24 juni 2010 @ 16:07:
Heb je dit nou eigenlijk al eens gelezen?

Ja, ik wist niet dat alleen MySQL zo'n query toe zou laten, maar ik snap wel waarom.
[quote]
Since when? Sowieso bedoel je kolommen en geen rijen en zelfs dan boeit het nog niet in welke volgorde ze staan (behalve voor de group zelf natuurlijk)
[quote]
Nee hoor, ik bedoel echt rijen. Bij kolommen maakt het uiteraard niet uit in welke volgorde ze staan.

Voor wat betreft je probleem: ik zie niet waarom je dit in SQL wil oplossen en dit niet gewoon in je presentatielaag afhandelt. (Of ik mis iets...)

Ik moet nog meer queries draaien op de tabel die uit die query komt.

[RobIII]

smokalot schreef op donderdag 24 juni 2010 @ 16:54:
Nee hoor, ik bedoel echt rijen. Bij kolommen maakt het uiteraard niet uit in welke volgorde ze staan.

Rijen (records) hébben geen (gegarandeerde) "volgorde" in een tabel. Dus het is me nu al helemaal een raadsel waar je op doelt.

[KabouterSuper]

Je kunt de groepsfunctie prima gebruiken om de tabel op z'n kant te krijgen:

SQL:

select id1,id2,
max(case when Volgnr=1 then Waarde else NULL end) as Waarde1,
max(case when Volgnr=2 then Waarde else NULL end) as Waarde2,
max(case when Volgnr=3 then Waarde else NULL end) as Waarde3
FROM tabel_A
group by id1,id2

[NMe]

RobIII schreef op donderdag 24 juni 2010 @ 16:07:
Heb je dit nou eigenlijk al eens gelezen?

[...]

En dan met name het stukje boven (en tot en met):

[...]

M.a.w.: Je begrijpt GROUP BY helemaal niet (zo goed als je denkt)

Mwah, die tekst mogen we intussen wel een heel klein beetje nuanceren aangezien MySQL juist zo'n beetje de enige database is die hier de standaard volgt; je hoeft niet te groupen op velden die functioneel afhankelijk zijn van de velden waar je wél op groupt. Maar dat is zo'n vaag principe dat ik het heel verdedigbaar vind om het te mijden als de pest.

[smokalot]

RobIII schreef op donderdag 24 juni 2010 @ 16:59:
[...]

Rijen (records) hébben geen (gegarandeerde) "volgorde" in een tabel. Dus het is me nu al helemaal een raadsel waar je op doelt.

Op de niet gegarandeerde volgorde dus.

En het verhaal wordt toch niet anders als je selecteert uit een subquery met een ORDER BY (of view met ORDER BY, in een DBMS die dat honoreert)?