Self-join; gegevens ophalen waarbij 2 kolommen exact hetzelfde zijn.
Ik heb een tabel waarin lessen staan van een schoolrooster.
lessons
id
studentnumber
week
day
hour
teacher
subject
week = week van het jaar, deze week dus 21
day = 0 (maandag) t/m 5 (vrijdag)
hour = 1 t/m 8
Voorbeeld data:
Code (php)
1
2
3
4
2
3
4
1 3206343 22 1 8 KOR10 NETL
2 2342342 22 1 8 KOR10 NETL
3 3206343 22 1 7 KEH10 INF
4 2342342 22 1 7 KEH10 INF
2 2342342 22 1 8 KOR10 NETL
3 3206343 22 1 7 KEH10 INF
4 2342342 22 1 7 KEH10 INF
Nu wil ik van de docenten KOR10 en KEH10 (dit zijn hun afkortingen die ook worden gebruikt in de kolom teacher) weten wanneer ze in week 22 tegelijk op school zijn. Met andere woorden: ik wil weten in week 22 wanneer zowel de week, day en hour bij de docenten gelijk zijn en ze dus tegelijk op school aanwezig zijn.
Ik heb van alles geprobeerd (met bijv. een self-join), maar ik krijg steeds rijen terug waarbij 1 docent les geeft op een moment dat de andere het niet doet.
Heeft iemand enig idee?
Gewijzigd op 21/05/2014 23:19:18 door P-ter AA
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
SELECT DISTINCT a.week, a.day, a.hour, a.teacher, b.teacher
FROM lessons a
INNER JOIN lessons b ON (
a.week = b.week
AND a.day = b.day
AND a.hour = b.hour
AND a.teacher = 'KOR10'
AND b.teacher = 'KEH10'
)
WHERE a.week = 22
FROM lessons a
INNER JOIN lessons b ON (
a.week = b.week
AND a.day = b.day
AND a.hour = b.hour
AND a.teacher = 'KOR10'
AND b.teacher = 'KEH10'
)
WHERE a.week = 22
Dit zou bij een grote tabel alleen nog wel eens traag kunnen worden, omdat je voor elke student zo te zien een rij hebt en er dus vele combinaties mogelijk zijn, die hetzelfde resultaat opleveren. Als dat het geval is moet je even voor beide tabellen een subquery maken die alle dubbele eruit haalt, voor je de join maakt.
Gewijzigd op 22/05/2014 08:32:59 door Erwin H
Erwin H op 22/05/2014 08:32:35:
Werkt een self join op deze manier niet?
Dit zou bij een grote tabel alleen nog wel eens traag kunnen worden, omdat je voor elke student zo te zien een rij hebt en er dus vele combinaties mogelijk zijn, die hetzelfde resultaat opleveren. Als dat het geval is moet je even voor beide tabellen een subquery maken die alle dubbele eruit haalt, voor je de join maakt.
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
SELECT DISTINCT a.week, a.day, a.hour, a.teacher, b.teacher
FROM lessons a
INNER JOIN lessons b ON (
a.week = b.week
AND a.day = b.day
AND a.hour = b.hour
AND a.teacher = 'KOR10'
AND b.teacher = 'KEH10'
)
WHERE a.week = 22
FROM lessons a
INNER JOIN lessons b ON (
a.week = b.week
AND a.day = b.day
AND a.hour = b.hour
AND a.teacher = 'KOR10'
AND b.teacher = 'KEH10'
)
WHERE a.week = 22
Dit zou bij een grote tabel alleen nog wel eens traag kunnen worden, omdat je voor elke student zo te zien een rij hebt en er dus vele combinaties mogelijk zijn, die hetzelfde resultaat opleveren. Als dat het geval is moet je even voor beide tabellen een subquery maken die alle dubbele eruit haalt, voor je de join maakt.
Wauw super! Ik zat er heel dicht bij in de buurt. Ik was " a.week = b.week" vergeten, vandaar dat ik verkeerde resultaten kreeg. Heel erg bedankt! Nu even uitzoeken hoe ik dit subqueries kan doen, want het klopt inderdaad dat iedere student een eigen regel heeft.
Erwin H op 22/05/2014 08:32:35:
Dit zou bij een grote tabel alleen nog wel eens traag kunnen worden, omdat je voor elke student zo te zien een rij hebt en er dus vele combinaties mogelijk zijn, die hetzelfde resultaat opleveren. Als dat het geval is moet je even voor beide tabellen een subquery maken die alle dubbele eruit haalt, voor je de join maakt.
Een subquerie is water naar de zee dragen in dit geval.
Dan krijg je immers ook een SELECT DISTINCT subquery ...
en je krijgt een tabel meer in de explain!
Gewijzigd op 22/05/2014 12:29:50 door Ger van Steenderen
Omdat er voor elke leerling een record in de tabel zit, zijn er bijvoorbeeld 10 records voor leraar a op tijdstip x en ook 10 records voor leraar b op tijdstip x. Dat betekent dus dat er 100 resultaten uit de join komen. Pas daarover wordt de distinct genomen.
Als je nu eerst de resultaten verkleint door in zowel eerste, als de tweede eerst te ontdubbelen, krijg je niet 100 resultaten, maar maar 1. Volgens mij zou dat in de meeste gevallen een snellere query kunnen opleveren. Ik bedoel dus dit:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT a.week, a.day, a.hour, a.teacher, b.teacher
FROM (
SELECT DISTINT week, day, hour, teacher
FROM lessons
WHERE teacher = 'KOR10'
AND week = 22
) a
INNER JOIN (
SELECT DISTINT week, day, hour, teacher
FROM lessons
WHERE teacher = 'KEH10'
AND week = 22
) b ON (
a.week = b.week
AND a.day = b.day
AND a.hour = b.hour
)
FROM (
SELECT DISTINT week, day, hour, teacher
FROM lessons
WHERE teacher = 'KOR10'
AND week = 22
) a
INNER JOIN (
SELECT DISTINT week, day, hour, teacher
FROM lessons
WHERE teacher = 'KEH10'
AND week = 22
) b ON (
a.week = b.week
AND a.day = b.day
AND a.hour = b.hour
)
Uiteraard heb ik dit niet getest (ik heb niet even zo'n gevulde tabel liggen) en is mijn ervaring met dit soort queries dat het soms het beste via trial and error te vinden is. Aan de andere kant is mijn ervaring ook dat het snel verkleinen van je dataset vaak hele grote versnellingen kan geven. Daarop is mijn suggestie gebaseerd.
Gewijzigd op 22/05/2014 12:38:24 door Erwin H
Quote:
Omdat er voor elke leerling een record in de tabel zit, zijn er bijvoorbeeld 10 records voor leraar a op tijdstip x en ook 10 records voor leraar b op tijdstip x. Dat betekent dus dat er 100 resultaten uit de join komen. Pas daarover wordt de distinct genomen.
Dit geldt dan ook voor de subqueries
Inderdaad kan je in een aantal gevallen de boel versnellen door met een subquery aan het begin de dataset te verkleinen.
Maar een subquery achter een inner join heeft meestal alleen maar een vertragend effect.
Je kan veel meer winnen door goed te indexen.
Ger van Steenderen op 22/05/2014 12:54:15:
Dit geldt dan ook voor de subqueries
Dat is niet waar. Voor de subqueries geldt dat je twee keer 10 records hebt. Dat is 20, geen 100.
Ger van Steenderen op 22/05/2014 12:54:15:
Inderdaad kan je in een aantal gevallen de boel versnellen door met een subquery aan het begin de dataset te verkleinen.
Maar een subquery achter een inner join heeft meestal alleen maar een vertragend effect.
Maar een subquery achter een inner join heeft meestal alleen maar een vertragend effect.
Dat kan ik niet beoordelen en in dit geval is het dus alleen maar testen welke optie sneller is.
Ger van Steenderen op 22/05/2014 12:54:15:
Je kan veel meer winnen door goed te indexen.
Niet altijd. Ja, goede indices helpen een heleboel queries te versnellen en zonder indices is het vaak een hopeloze zaak. Maar indices alleen zijn niet altijd genoeg en mijn ervaring is dat het verkleinen van je dataset in sommige situaties net zo goed kunnen helpen als een goed index dat in een andere situatie doet.
Stel jouw voorbeeld:
de derived table levert tien resultaten op
de lessons in de subquery ook
hetzelfde geldt voor de tweede subquery
Cartesian product: 10.000 (ipv 100)
Gewijzigd op 22/05/2014 13:09:17 door Ger van Steenderen
Maar om je eerlijk te zijn heb ik op dit moment geen zin om het uit te leggen omdat de oorspronkelijke vraag allang beantwoord is. Ik houd het hier dus bij.
Quote:
Because type is ALL for each table, this output indicates that MySQL is generating a Cartesian product of all the tables; that is, every combination of rows. This takes quite a long time, because the product of the number of rows in each table must be examined. For the case at hand, this product is 74 × 2135 × 74 × 3872 = 45,268,558,720 rows. If the tables were bigger, you can only imagine how long it would take.
Bron (bijna onderaan)
En jij weet net zo goed als ik dat een subquery altijd een (derived) tabel extra geeft in de explain.
Dus is, nogmaals met de juiste indexen, de eerste query de beste.
Gewijzigd op 22/05/2014 15:31:56 door Ger van Steenderen
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
id student week day hour docent
1 1111111 22 1 8 KOR10
2 2222222 22 1 8 KOR10
3 3333333 22 1 8 KOR10
4 1111111 22 1 8 KEH10
5 2222222 22 1 8 KEH10
6 3333333 22 1 8 KEH10
1 1111111 22 1 8 KOR10
2 2222222 22 1 8 KOR10
3 3333333 22 1 8 KOR10
4 1111111 22 1 8 KEH10
5 2222222 22 1 8 KEH10
6 3333333 22 1 8 KEH10
Eerste query, even zoder DISTINCT, met het resultaat:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT a.week, a.day, a.hour, a.teacher, b.teacher
FROM lessons a
INNER JOIN lessons b ON (
a.week = b.week
AND a.day = b.day
AND a.hour = b.hour
AND a.teacher = 'KOR10'
AND b.teacher = 'KEH10'
)
WHERE a.week = 22
//resultaat:
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
FROM lessons a
INNER JOIN lessons b ON (
a.week = b.week
AND a.day = b.day
AND a.hour = b.hour
AND a.teacher = 'KOR10'
AND b.teacher = 'KEH10'
)
WHERE a.week = 22
//resultaat:
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
22 1 8 KOR10 KEH10
Omdat je 3 rijen hebt voor KOR10 en drie rijen voor KEH10, wordt elke rij op elke rij gejoined en krijg je dus 9 rijen terug. Identieke rijen, dus na de distinct houd je er 1 over.
Tweede query:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT a.week, a.day, a.hour, a.teacher, b.teacher
FROM (
SELECT DISTINT week, day, hour, teacher
FROM lessons
WHERE teacher = 'KOR10'
AND week = 22
) a
INNER JOIN (
SELECT DISTINT week, day, hour, teacher
FROM lessons
WHERE teacher = 'KEH10'
AND week = 22
) b ON (
a.week = b.week
AND a.day = b.day
AND a.hour = b.hour
)
FROM (
SELECT DISTINT week, day, hour, teacher
FROM lessons
WHERE teacher = 'KOR10'
AND week = 22
) a
INNER JOIN (
SELECT DISTINT week, day, hour, teacher
FROM lessons
WHERE teacher = 'KEH10'
AND week = 22
) b ON (
a.week = b.week
AND a.day = b.day
AND a.hour = b.hour
)
Nu, uit de eerste subquery komt 1 rij terug:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT DISTINT week, day, hour, teacher
FROM lessons
WHERE teacher = 'KOR10'
AND week = 22
//resultaat:
22 1 8 KOR10
FROM lessons
WHERE teacher = 'KOR10'
AND week = 22
//resultaat:
22 1 8 KOR10
Uit de tweede subquery komt ook 1 rij terug:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT DISTINT week, day, hour, teacher
FROM lessons
WHERE teacher = 'KEH10'
AND week = 22
//resultaat:
22 1 8 KEH10
FROM lessons
WHERE teacher = 'KEH10'
AND week = 22
//resultaat:
22 1 8 KEH10
De join is dus maar 1 rij op 1 rij. Dat is dus een stuk kleinere tussen resultset dan een resultset van 9 rijen.
Gewijzigd op 22/05/2014 16:19:59 door Erwin H
Je moet een EXPLAIN voor de query zetten, dan weet je hoeveel rijen er intern afgelopen moeten worden.
Als er voor een SELECT DISTINCT in de subquery dus eerst 3 rijen moeten worden afgelopen om uiteindelijk één rij op te leveren, moet die combinatie dus worden nagegaan.
Dus in het gunstigste geval (1x3x3x1) blijft het gelijk.
Daarnaast is het zo als die kolommen waarop je filtert niet geindexed zijn, je gewoon een full table scan krijgt, dan toch liever twee full table scans dan vier.
Explain verkaart een hoop ;-)
Gewijzigd op 22/05/2014 16:48:17 door Ger van Steenderen