[SQL] waarden zoeken in genormaliseerde tabel

[107mb]

ik heb een tabel:

ID,kenmerk,waarde
1,kleur,blauw
1,vorm,kubus
1,formaat,groot
2,kleur,groen
2,vorm,bol
2,formaat,klein

de sleutel is ID+kenmerk

ik zoek een query die mij de waarde 'blauw' retourneert als aan de volgende voorwaarden voldaan wordt: ID=1 en vorm=kubus en formaat=groen

ik ben al een tijdje aan het zoeken naar de juiste query, maar kom er niet uit. Nu moet ik ook zeggen dat ik moeite heb met het zoeken met de juiste trefwoorden voor deze query .

[kwaakvaak_v2]

huiswerk opdracht? Wat je zoekt is een select met een where

[PolarBear]

ik zoek een query die mij de waarde 'blauw' retourneert als aan de volgende voorwaarden voldaan wordt: ID=1 en vorm=kubus en formaat=groen

Eh, je weet het ID al en je weet dat je kenmerk kleur wil hebben, waarom zou je dan nog fileren om vorm en formaat?

[107mb]

kwaakvaak_v2 schreef op donderdag 12 maart 2020 @ 07:16:
huiswerk opdracht? Wat je zoekt is een select met een where

zeker niet. ben al meer dan 20 jaar geleden afgestudeerd

PolarBear schreef op donderdag 12 maart 2020 @ 07:25:
[...]


Eh, je weet het ID al en je weet dat je kenmerk kleur wil hebben, waarom zou je dan nog fileren om vorm en formaat?

omdat de query niets mag retourneren indien aan vorm en formaat niet voldaan wordt. Dat had ik er niet bij vermeldt.

[PolarBear]

Ok maar dan kan je dat zo oplossen (wel in tikje inefficiënt, er is vast een betere oplossing).

code:

SELECT kenmerk FROM tabel 
WHERE
 id IN (SELECT id FROM tabel WHERE vorm = 'kubus' AND id =1)
AND 
 id IN (SELECT id FROM tabel WHERE formaat = 'groot' AND id =1)

[Nullifiel]

Zoiets?

code:

SELECT tk.kenmerk FROM tabel tk
JOIN tabel tv
ON tk.id = tv.id
AND tv.kenmerk = 'kubus'

JOIN tabel tf
ON tk.id = tf.id
AND tf.kenmerk = 'groen'; -- je bedoelt denk ik groot

[ Voor 2% gewijzigd door Nullifiel op 12-03-2020 08:03 . Reden: tk.kenmerk, niet * :-) ]

[Rannasha]

PolarBear schreef op donderdag 12 maart 2020 @ 07:47:
Ok maar dan kan je dat zo oplossen (wel in tikje inefficiënt, er is vast een betere oplossing).

code:

SELECT kenmerk FROM tabel 
WHERE
 id IN (SELECT id FROM tabel WHERE vorm = 'kubus' AND id =1)
AND 
 id IN (SELECT id FROM tabel WHERE formaat = 'groot' AND id =1)

Dit gaat niet werken want vorm en formaat zijn geen kolommen in de tabel.

Wat wel een oplossing kan zijn is om de tabel met zichzelf te joinen om zo een virtuele tabel te maken waarbij de relevante kenmerken wel een kolom zijn.

Bijvoorbeeld:

code:

SELECT id, tKleur.waarde AS kleur, tVorm.waarde AS vorm, tFormaat.waarde AS formaat FROM tabel tKleur
INNER JOIN (SELECT id, kenmerk, waarde FROM tabel WHERE kenmerk = 'vorm') tVorm ON tKleur.ID = tVorm.ID
INNER JOIN (SELECT id, kenmerk, waarde FROM tabel WHERE kenmerk = 'formaat') tFormaat ON tKleur.ID = tFormaat.ID
WHERE tKleur.kenmerk = 'kleur'

Dit levert een tabel op met velden ID, kleur, vorm en formaat. Hier kun je vervolgens makkelijk filteren op zaken als "kleur = 'blauw'".

Merk op dat deze oplossing uit gaat van de aanname dat iedere ID, kenmerk combinatie een rij heeft in de brontabel. Als dat niet zo is, dan wordt het mogelijk ingewikkelder.

[107mb]

Nullifiel schreef op donderdag 12 maart 2020 @ 07:58:
Zoiets?

code:

SELECT tk.kenmerk FROM tabel tk
JOIN tabel tv
ON tk.id = tv.id
AND tv.kenmerk = 'kubus'

JOIN tabel tf
ON tk.id = tf.id
AND tf.kenmerk = 'groen'; -- je bedoelt denk ik groot

nee. dit werkt niet, want het kenmerk = vorm, en daar hoort een waarde bij.

[PolarBear]

Rannasha schreef op donderdag 12 maart 2020 @ 07:59:
[...]


Dit gaat niet werken want vorm en formaat zijn geen kolommen in de tabel.

Eh sorry het is nog vroeg:

code:

SELECT waarde  FROM tabel 
WHERE
 id IN (SELECT id FROM tabel WHERE  kenmerk = 'vorm' AND  waarde  = 'kubus' AND id =1)
AND 
 id IN (SELECT id FROM tabel WHERE kenmerk =  'formaat' AND waarde  = 'groot' AND id =1)
AND 
  kenmerk = 'kleur'

Zie http://sqlfiddle.com/#!18/47ba6/9

Overigens maakt SQL server hier ook left joins van, de query analyser is best intelligent.

[ Voor 8% gewijzigd door PolarBear op 12-03-2020 08:13 ]

[CyBeRSPiN]

Maak views voor formaat, vorm en kleur. Die dan de query middels joins naar de views.

[Nullifiel]

107mb schreef op donderdag 12 maart 2020 @ 08:06:
[...]


nee. dit werkt niet, want het kenmerk = vorm, en daar hoort een waarde bij.

Verdorie, ik bedoelde niet kenmerk, maar waarde:

code:

SELECT tk.waarde FROM tabel tk
JOIN tabel tv
ON tk.id = tv.id
AND tv.waarde = 'kubus'

JOIN tabel tf
ON tk.id = tf.id
AND tf.waarde = 'groen'; -- je bedoelt denk ik groot

[107mb]

PolarBear schreef op donderdag 12 maart 2020 @ 08:09:
[...]

Eh sorry het is nog vroeg:

code:

SELECT waarde  FROM tabel 
WHERE
 id IN (SELECT id FROM tabel WHERE  kenmerk = 'vorm' AND  waarde  = 'kubus' AND id =1)
AND 
 id IN (SELECT id FROM tabel WHERE kenmerk =  'formaat' AND waarde  = 'groot' AND id =1)
AND 
  kenmerk = 'kleur'

Zie http://sqlfiddle.com/#!18/47ba6/9

Overigens maakt SQL server hier ook left joins van, de query analyser is best intelligent.

dit werkt! toch wel lastig. Dan wordt een database zo veel mogelijk genormaliseerd en dan resulteert het in toch wat complexere queries.

[PolarBear]

107mb schreef op donderdag 12 maart 2020 @ 08:33:
[...]


dit werkt! toch wel lastig. Dan wordt een database zo veel mogelijk genormaliseerd en dan resulteert het in toch wat complexere queries.

Normaliseren is ook een trade-off, je kan ook 'te ver' gaan. De vraag is of je altijd een kleur, vorm en formaat hebt, dan is door normaliseren in de praktijk niet zo handig. Als je een dynamisch aantal kenmerken heb dan is dit een prima tabel maar kunnen sommige query's wel wat complex zijn.

[107mb]

PolarBear schreef op donderdag 12 maart 2020 @ 08:36:
[...]

Normaliseren is ook een trade-off, je kan ook 'te ver' gaan. De vraag is of je altijd een kleur, vorm en formaat hebt, dan is door normaliseren in de praktijk niet zo handig. Als je een dynamisch aantal kenmerken heb dan is dit een prima tabel maar kunnen sommige query's wel wat complex zijn.

er bestaat een groot aantal kenmerken (372 om precies te zijn). Normaliseren is dus wel dé manier.

[KabouterSuper]

107mb schreef op donderdag 12 maart 2020 @ 08:33:
[...]
dit werkt! toch wel lastig. Dan wordt een database zo veel mogelijk genormaliseerd en dan resulteert het in toch wat complexere queries.

Mijn bescheiden mening is dat zwaar genormaliseerde databases heel goed zijn om data op te slaan, maar erg slecht om er informatie uit te halen.Andersom geldt het ook, databases waar je gemakkelijk informatie uit kunt halen, zijn vaak moeilijk te vullen.

Anyway, de oplossing van PolarBear werkt goed.

Als je de vertaalslag tussen "data in" en "informatie uit" op een generiekere manier wilt opzetten, dan zou je een platgeslagen diagonaal query kunnen maken:

code:

select id, max(kleur) kleur, max(vorm) vorm, max(formaat) formaat
from
(
select id, waarde kleur,null vorm,null formaat from <tabel> where kenmerk='kleur'
union all
select id, null kleur,waarde vorm,null formaat from <tabel> where kenmerk=vorm''
union all
select id, null kleur,null vorm, waarde formaat from <tabel> where kenmerk='formaat'
)
group by id

Je kunt nu met deze view/inner query gemakkelijk de benodigde informatie ophalen:

code:

select kleur from <view> where id=1 and vorm='kubus' and formaat='groot'

Met een beetje mazzel wordt de query zo geoptimaliseerd dat je met 3 index scans wegkomt, en dat je geen full table scans doet. Dat zou je uit moeten proberen.Wat elegant is aan deze methode is dat je je filters maar op 1 plaats hebt, in tegenstellling tot bovenstaande oplossingen (waar id=1 op meerdere plaatsen in de query staat).

Een andere alternatieve aanpak is om te gaan tellen:

code:

select waarde from <tabel> 
where id=1 and kenmerk='kleur' 
and 2= (select count(*) from <tabel> where id=1 and ((kenmerk='vorm' and waarde='kubus') or (kenmerk='formaat' and (waarde='groot')))

Deze query zal netjes de pk-index gebruiken en dus geen full-table scans doen.

[Janoz]

Dit lijkt wat mij betreft niet op door normaliseren. Dit lijkt meer op het database in database anti-pattern.

[Matis]

Ik denk ook niet dat een RDBMS en/of SQL database hiervoor de juiste keus is.
Mogelijk kun je kijken naar MongoDB of ElasticSearch.
Wij gebruiken die databases zelf ook voor key/value pairs op te slaan voor producten.

Dat leidt overigens tot veel eenvoudigere queries en de performance is fenomenaal.

[107mb]

de database is de basis waar een volledig geautomatiseerde fabriek op draait. De DB kan dus niet aangepast worden.

[Matis]

De database kan prima aangepast worden, de vraag is alleen of je de businesscase rond krijgt.
Ik geef alleen een suggestie; kijk er eens naar. Je kunt dan altijd nog besluiten er niets mee te doen