Dit artikel is de on-line tegenhanger van de rubriek Puzzelen met SQL die verschijnt in de Optimize, het vakblad voor Oracle ontwikkelaars in Nederland. In dit on-line artikel kunnen alle scripts worden gedownload, zowel de DDL en data-load scripts, als scripts met mogelijke oplossingen. Ook bevat dit on-line artikel enkele extra uitdagingen en verdere uitwerkingen. Bijdragen aan dit artikel zijn geleverd door Lucas Jellema en Anton Scheffer, vaste medewerkers aan de rubriek Puzzelen met SQL.
Als de kat van huis is… Op avonden dat zij alleen thuis is (dan in ieder geval) kijkt Madelon graag naar de televisie. Een uitdaging daarbij is het samenstellen van de ideale televisieavond: waar kan ze het beste naar gaan kijken opdat haar televisie-genot optimaal is.
Hoe moeilijk is het niet om te kiezen wat je gaat kijken op televisie! Als je alleen maar naar de 13 meest voor de hand liggende kanalen kijkt, dan is er keuze uit maar liefst 162 verschillende programma’s. En je kunt er hooguit 5% van kijken! Hoe maak je die keuze?
In deze puzzel gaan we kijken hoe we een SQL Query kunnen ontwikkelen die de keuze voor ons maakt! Wij geven op wat onze programma-smaak is, en de query bepaalt de ideale televisie-avond. Tip voor John de Mol: Misschien een idee voor een leuke nieuwe show: “SQL with the Stars�
Het data model voor deze puzzel bestaat uit een vijftal tabellen, hieronder het datamodel:
Belangrijk om te weten: de voorkeur ligt vast in de tabel PROGRAMMA_VOORKEUR op een schaal van 0 tot en met 5. Hierbij is 0 totaal niet interessant, total waste of time, en is 5 het summum van televisiekijken.
Uitdagingen
1) Schrijf een query die laat zien wat het aanbod is op Nederland 1 vanavond?
select to_char( pga.tijdstip, 'HH24:MI' ) begintijd
, pga.titel
, pga.beschrijving
from programma pga
inner
join zender zdr
on (pga.zdr_id = zdr.id)
where zdr.afkorting = 'NED 1'
and trunc( pga.tijdstip ) = to_date( '13-02-2007' , 'DD-MM-YYYY' )
order by pga.tijdstip
Deze eerste query is redelijk eenvoudig, het enige wat opvalt is de ANSI-join syntax. Met name de ANSI OUTER JOIN maken het schrijven van queries een stuk eenvoudiger. Een goed voorbeeld hiervan is de FULL OUTER JOIN. Voorheen was het alleen met een UNION en een tweetal SELECT statements mogelijk om de data van twee datasets te bekijken, een (+) mag maar op één tabel worden gebruikt. Met een FULL OUTER JOIN is dit een stuk eenvoudiger, er is slecht één SELECT statement voor nodig.
2) Welke programma’s zijn in zijn geheel (start en eind tussen 21.00 en 22.00 uur) te zien tussen 21.00 en 22.00 uur?
Om deze vraag te kunnen beantwoorden, moet je in eerste instantie weten wat de begin- en eindtijden van de programma’s zijn. In de PROGRAMMA-tabel staan echter alleen maar de begintijden. De begintijd van het ene programma is de eindtijd van het vorige programma. De begin- en eindtijden kunnen bepaald worden door gebruik te maken van de Analytische Functie LEAD.
Lead (pga.tijdstip, 1) over (partition by pga.zdr_id order by pga.tijdstip) eindtijd
Met LEAD kunnen we "achteruit" in de resultaat set kijken. Allereerst bepaal je waarnaar je wilt kijken, in ons geval het tijdstip, dit het eerste argument. Als tweede argument kun je aangeven hoeveel rijen je terug wilt kijken, aangezien wij alleen geinteresseerd zijn in de vorige rij is dit 1, dit argument is optioneel. Er is nog een derde optioneel argument waar je een default waarde kan opgeven indien er geen vorige rij is. De PARTITION clause geeft aan hoe de result set is opgedeeld, in ons geval per zender. En de ORDER BY clause geeft, niet verassend, hoe de sortering is binnen de PARTITION. Nu de begin- en eindtijden van de programma’s bekend zijn, is de query eenvoudig te schrijven:
select begintijd
, to_char (eindtijd, 'HH24:MI') eindtijd
, zdr.afkorting zender
, titel programma
from (select to_char (pga.tijdstip, 'HH24:MI') begintijd
, lead (pga.tijdstip, 1) over (partition by pga.zdr_id
order by pga.tijdstip
) eindtijd
, pga.titel
, pga.zdr_id
from programma pga
where pga.tijdstip >= to_date('13-02-2007 21:00', 'DD-MM-YYYY HH24:MI')
)
, zender zdr
where eindtijd
3) Wat is het aanbod aan Speelfilms vanavond?
Hoewel er alleen naar de Speelfilm word gevraagd, hebben we ook de begin- en eindtijd bij gezet. Ook hier is er gebruik gemaakt van de Analytische functie LEAD. De ANSI-join syntax is nu inmiddels een oude bekende.
select *
from (select to_char (pga.tijdstip, 'HH24:MI') begintijd
, to_char (lead (pga.tijdstip,1) over (partition by pga.zdr_id
order by tijdstip
)
,'HH24:MI') eindtijd
, zdr.afkorting zender
, pga.titel
, pte.label programma_type
from programma pga
join zender zdr
on (pga.zdr_id = zdr.id)
join programma_type pte
on (pte.id = pga.pte_id)
where trunc(pga.tijdstip) = to_date('13-02-2007','DD-MM-YYYY')
)
where programma_type = 'Speelfilm'
order by begintijd
4) Hoeveel nieuwsprogramma’s zijn er te zien, per zender en in het totaal?
Om te bepalen hoeveel Nieuws en Actualiteiten programma’s er zijn, inclusief de totalen, kunnen we gebruik maken van ROLLUP. Deze functie maakt het mogelijk om subtotalen te bepalen, inclusief een totaal. Dat maakt deze functie bij uitstek geschikt voor de vraag.
select case grouping (zender)
when 0
then zender
when 1
then 'Totaal:'
end Zender
, count(*) "Aantal Nieuws Programma's"
from (select zdr.afkorting zender
, pte.label programma_type
from programma pga
join zender zdr
on (pga.zdr_id = zdr.id)
join programma_type pte
on (pte.id = pga.pte_id)
where trunc(pga.tijdstip) = to_date('13-02-2007','DD-MM-YYYY')
)
where programma_type = 'Nieuws '||chr(38)||' Actualiteiten'
group by rollup(zender)
Tevens maken we in deze query gebruik van de GROUPING, deze functie laat zien op welk niveau een GROUP BY wordt uitgevoerd. Indien GROUPING een ‘1’ teruggeeft, dan vind er een sommatie plaats op dit niveau. Gebruik makend van een simpel CASE statement, word het overzicht wat inzichtelijker gemaakt.
ZENDER Aantal Nieuws Programma's ---------- ------------------------- BBC 1 4 BBC 2 1 Canvas 1 NED 1 3 NED 2 5 NED 3 2 RTL 4 5 RTL 7 1 SBS 6 3 TV1 8 Totaal: 33
De laatste regel in dit overzicht toont het totaal aantal Nieuws en Actualiteiten programma’s voor deze dag.
5) Wat zijn de langste vijf programma’s van de avond (kijk niet naar programma’s die na middernacht starten)? Toon titel, zender, begintijd en duur van die programma’s.
In het programma overzicht worden alleen de begintijden geregistreerd, dit hebben we al eerder gezien. Om inzicht te krijgen in de verstreken tijd moeten we begintijden van opeenvolgende programma’s van elkaar aftrekken. Als de doorlooptijd voor ieder programma is bepaald kan er een top 5 worden bepaald. In deze query maken we gebruik van ROWNUM. Aangezien ROWNUM bepaald wordt voordat er een ORDER BY word gedaan, is het noodzakelijk om een inline view te gebruiken. Als we dit niet zouden doen, dan worden er willekeurig 5 records gekozen welke vervolgens op doorlooptijd worden gesorteerd, en dat is nu net niet de bedoeling.
select to_char(tijdstip, 'HH24:MI') begintijd
, zender
, titel programma
, to_char(trunc(sysdate) + duur,'HH:MI') duur
from (select pga.tijdstip
, zdr.afkorting zender
, ( lead(pga.tijdstip,1) over (partition by zdr.afkorting order by tijdstip)
- pga.tijdstip
) duur
, pga.titel
from programma pga
join zender zdr
on (pga.zdr_id = zdr.id)
where tijdstip >= to_date('13-02-2007 18:00','DD-MM-YYYY HH24:MI')
order by duur desc
)
where trunc(tijdstip) = to_date('13-02-2007','DD-MM-YYYY')
and rownum
6) Bepaal de ideale avond voor Madelon. Haar programma-voorkeuren liggen vast in de tabel PROGRAMMA_VOORKEUR.
Zij stelt echter wel de volgende randvoorwaarden:
- pas als de kinderen in bed liggen kan ik gaan kijken; dat is vanaf 20.30 uur.
- ik wil om 23.15 uur naar bed; ik wil geen programma bekijken dat ik niet kan af zien.
- ik wil niet aan een programma beginnen als het langer dan 5 minuten bezig is.
Hoe kan je Madelon’s ideale TV-avond bepalen – dat wil zeggen: de combinatie van programma’s waarvoor de optelling van de waarden (lengte van programma * niveau van programma-voorkeur) de grootste opbrengst geeft.
Om dit SQL vraagstuk op te lossen maken we gebruik van Subquery Factoring, zoals dat volgens de documentatie heet. In plaats dat een SQL statement altijd met een SELECT moet beginnen, kan het nu ook met een WITH beginnen. In de WITH clause geef je een naam aan een inline view die je in de rest van het SQL statement kunt gebruiken. Het is ook mogelijk om deze inline view in andere inline views te gebruiken. Het eerste wat we nodig hebben is het totale programma overzicht van de avond, dat kan met behulp van deze query:
with programma_aanbod as
(
select begintijd
, eindtijd
, zender
, titel
, programma_type
, to_char(begintijd, 'HH24:MI') aanvang
, to_char(eindtijd, 'HH24:MI') einde
, pte_id
from (select pga.tijdstip begintijd
, zdr.afkorting zender
, lead(pga.tijdstip,1) over (partition by zdr.afkorting
order by tijdstip
) eindtijd
, pga.titel
, pte.label programma_type
, pte.id pte_id
from programma pga
, zender zdr
, programma_type pte
where pga.zdr_id = zdr.id
and pte.id = pga.pte_id
and tijdstip >= to_date('13-02-2007 20:25','DD-MM-YYYY HH24:MI')
)
where eindtijd
Nu kunnen we in de rest van de query gebruik maken van PROGRAMMA_AANBOD. Als we deze gegevens nu combineren met de voorkeur van Madelon, dan krijg je dit:
select pad.aanvang
, pad.einde
, pad.zender
, pad.titel
, pvr.niveau
from programma_aanbod pad
, programma_voorkeur pvr
, persoon psn
where pad.pte_id = pvr.pte_id
and pvr.psn_id = psn.id
and psn.voornaam ='Madelon'
Per programma is nu bekend wat de voorkeur geniet, dit is af te leiden uit de kolom NIVEAU uit de tabel PROGRAMMA_VOORKEUR. De volgende stap in de query is het combineren van alle mogelijke opeenvolgende televisie programma’s. Dit doen we met behulp van een hierarchische query en de functie SYS_CONNECT_BY_PATH. Met deze functie kan eenvoudig inzichtelijk gemaakt worden welke pad er gevolgd word als je een hierarchische query schrijft. Deze functie gaan we gebruiken om het genot van ieder televisie programma bij elkaar op te tellen. De som van het niveau geeft dan aan hoeveel kijkplezier de gezamelijke programma’s opleveren.
TV_PLEZIER PROGRAMMA ----------- ------------------- ... +3+4+3+3 ##NED 2;20:25-21:20;Netwerk:##NET 5;21:30-22:30;What About Brian##NED 3;22:30-22 ...
Nu gaan we een beetje smokkelen, om de som van het tv_plezier te bepalen moeten we gebruik maken van een PL/SQL functie.
function evaluate_string (p_string in varchar2)
return number
is
l_result number;
begin
execute immediate
'select '||p_string||' from dual'
into l_result;
return l_result;
end evaluate_string;
Nu kunnen we bepalen hoe leuk een avondje televisie kijken kan zijn. De uiteindelijke query wordt dan als volgt:
with programma_aanbod as
(
select begintijd
, eindtijd
, zender
, titel
, programma_type
, to_char (begintijd, 'HH24:MI') aanvang
, to_char (eindtijd, 'HH24:MI') einde
, pte_id
from (select pga.tijdstip begintijd
, zdr.afkorting zender
, lead (pga.tijdstip,1) over (partition by zdr.afkorting
order by tijdstip) eindtijd
, pga.titel
, pte.label programma_type
, pte.id pte_id
from programma pga
, zender zdr
, programma_type pte
where pga.zdr_id = zdr.id
and pte.id = pga.pte_id
and tijdstip >= to_date('13-02-2007 20:25','DD-MM-YYYY HH24:MI')
)
where eindtijd = prior eindtijd - 2/60/24
order by tv_plezier desc
)
, ideaal_programma as
(
select tv_plezier
, programma
from agenda_voorstellen
where rownum =1
) -- now tokenize
select substr (programma_onderdeel
,1
,instr (programma_onderdeel,';',1)-1
) zender
, substr (programma_onderdeel
,instr (programma_onderdeel,';',1)+1
,instr(programma_onderdeel,';',1,2)-1 - instr(programma_onderdeel,';',1)
) tijd
, substr (programma_onderdeel
,instr(programma_onderdeel,';',1,2)+1
) titel
from (select level
, substr (string_to_tokenize
,1+decode( level, 1, 1, instr(string_to_tokenize, delimiter
, 1, level-1)+1)
, instr(string_to_tokenize, delimiter, 1, level)
- decode( level, 1, 1, instr(string_to_tokenize, delimiter
, 1, level-1)+1) -1
) programma_onderdeel
from (select programma||'##' as string_to_tokenize
, '##' as delimiter
from ideaal_programma
)
connect by instr(string_to_tokenize, delimiter, 1, level) > 0
order by level asc
)
/
ZENDER TIJD TITEL
--------------- --------------- ---------------
NET 5 20:30-21:30 Gilmore Girls
Veronica 21:35-23:05 Loaded Weapon.
Dat de query klopt blijkt uit het feit dat Madelon aangaf inderdaad deze programma’s te hebben uitgezocht om te bekijken.
Helaas was zij deze avond niet alleen thuis, en werd er gewoon naar voetbal gekeken.
Wil je zelf met deze puzzel aan de slag, download dan hier de scripts.
