+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + Instructies: + + Onderstaande is nodig om je notebook werkende te krijgen. + + Bij het opstarten van je notebook dien je de chunk van regel 28 t/m 40 en regel 42 t/m 46 te runnen. + + Daarna spring je naar regel 76 waar je je eerste SQL-chunk kan maken. + + + +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Dit is een voorbeeld van hoe je aan de slag zou kunnen gaan met R chunks in een Notebook. Hierin ga je: + opmaak toepassen + functies uit Datacamp toepassen op een “eigen” dataset + oefenen met “knitten” Er is met zogenaamde Markdown allerlei opmaak toegepast op de tekst rondom de chunks. Dit is anders dan hoe het in Word gaat (daar is het meer What You See Is What You Get), het is wel vergelijkbaar met de opmaak van pagina’s in Wikipedia.

Importeren libraries en dataset

Hieronder wordt een library geladen waar functies in zitten die we verderop in het Notebook gaan gebruiken: tidyverse. Daarnaast wordt hier de dataset geïmporteerd zodat we daar vragen aan kunnen stellen.

Setup

UPDATE dataset SET besteldatum = DATE(besteldatum, 'unixepoch'), leverdatum = DATE(leverdatum, 'unixepoch'), betaaldatum = DATE(betaaldatum, 'unixepoch');

Als je bovenstaande code uitvoert door op het groene pijltje rechtsbovenin de chunk te drukken, gebeurt er nog vrij weinig. Als het goed is verschijnt er alleen even een groen balkje naast de code regels. Tenminste, als je het Excel bestand op de juiste plek hebt geüpload. Het laden van de libraries hoeven we maar 1 keer te doen en het is wel zo overzichtelijk om dat allemaal aan het begin van je document te doen voor je de inhoud in duikt.

De dataset is nu opgeslagen in een database waarvoor we een connectie hebben gemaakt die ‘con’ heet. Elke keer als we dus gegevens uit die database willen halen zullen we dus eerst die connectie moeten gaan noemen.

eerste SQL-chunk

In de onderstaande chunk is de meest basale vorm van een query opgesteld. Je kan deze laten uitvoeren door op de groene knop te drukken of door de query-code te selecteren en op Ctrl+Shift+Enter te drukken. Merk ook de eerste regel op waarin ‘connection=con’ staat. Deze is nodig om contact met de database te maken.

/*deze query zoekt voor alle informatie van de familie de Boer en sorteert het op prijs van hoog naar laag. */
  SELECT *
  FROM dataset
  WHERE naam == 'Boer, de' 
  ORDER BY regelOmzet DESC;
Displaying records 1 - 10
regioCode regiomgr mgrnm regelOmzet bestelnr Verkoper besteldatum Jaar_bestel Maand_bestel leverdatum Jaar_leverdatum Maand_leverdaum betaaldatum Jaar_Betaaldum Maand_Betaaldatum klantnr naam postcodehuisnr woonplaats aantal artikelnr omschrijving catomschrijving prijs
GVGH 804 Herman 623.20 1050 901 2012-03-30 2012 3 2012-04-04 2012 4 2012-04-18 2012 4 15 Boer, de 5463ZK1003 Veghel 16 402 Ganzenlever luxe artikelen van de traitteur 38.95
GVGH 805 Gerda 592.00 1421 903 2014-10-29 2014 10 2014-11-03 2014 11 2014-11-21 2014 11 15 Boer, de 5463ZK1003 Veghel 16 402 Ganzenlever luxe artikelen van de traitteur 37.00
GVGH 804 Herman 506.35 1157 901 2012-12-18 2012 12 2012-12-25 2012 12 2013-01-18 2013 1 15 Boer, de 5463ZK1003 Veghel 13 402 Ganzenlever luxe artikelen van de traitteur 38.95
GVGH 802 Bertha 494.50 1193 904 2013-03-31 2013 3 2013-04-01 2013 4 2013-05-01 2013 5 15 Boer, de 5463ZK1003 Veghel 23 401 Kaviaar luxe artikelen van de traitteur 21.50
GVGH 801 Karel 462.00 1250 906 2013-08-14 2013 8 2013-08-19 2013 8 2013-08-29 2013 8 15 Boer, de 5463ZK1003 Veghel 24 403 Vruchtenyoghurt Aardbei zuivelartikelen 19.25
GVGH 805 Gerda 450.00 1421 903 2014-10-29 2014 10 2014-11-03 2014 11 2014-11-21 2014 11 15 Boer, de 5463ZK1003 Veghel 20 410 Soja Vanillevla Biologisch biologische artikelen 22.50
GVGH 804 Herman 408.50 1418 901 2014-10-21 2014 10 2014-10-26 2014 10 2014-11-19 2014 11 15 Boer, de 5463ZK1003 Veghel 19 401 Kaviaar luxe artikelen van de traitteur 21.50
GVGH 804 Herman 405.00 1217 901 2013-06-12 2013 6 2013-06-19 2013 6 2013-07-01 2013 7 15 Boer, de 5463ZK1003 Veghel 18 410 Soja Vanillevla Biologisch biologische artikelen 22.50
GVGH 802 Bertha 373.15 1355 904 2014-05-30 2014 5 2014-06-04 2014 6 2014-06-10 2014 6 15 Boer, de 5463ZK1003 Veghel 17 411 Sojamelk Gezoet Biologisch biologische artikelen 21.95
GVGH 805 Gerda 350.00 1416 903 2014-10-12 2014 10 2014-10-17 2014 10 2014-11-10 2014 11 15 Boer, de 5463ZK1003 Veghel 20 408 Soja Chocolademelk Biologisch biologische artikelen 17.50 
/*Deze query zoekt de naam, woonplaats product, aantal en totale prijs van een klant met klantnummer 16 die producten heeft gekocht in een winkel in eindhoven. Dit wordt gesorteerd op prijs van hoog naar laag.(klantnummer kan eenvoudig worden aangepast voor het zoeken naar een specifieke klant)*/

SELECT regioCode, klantnr, naam, woonplaats, omschrijving AS product, aantal AS Aantal, regelOmzet AS Totale_prijs 

FROM dataset

WHERE woonplaats == 'Eindhoven'

GROUP BY omschrijving

HAVING (klantnr) == 16

ORDER BY regelOmzet desc;
7 records
regioCode klantnr naam woonplaats product Aantal Totale_prijs
REHV 16 Velzenmaker Eindhoven Ganzenlever 20 779.00
REHV 16 Velzenmaker Eindhoven Soja Vanillevla Biologisch 15 315.00
REHV 16 Velzenmaker Eindhoven Vruchtenyoghurt Mango 11 178.75
REHV 16 Velzenmaker Eindhoven Kaviaar 5 107.50
REHV 16 Velzenmaker Eindhoven Magere Kwark 11 98.45
REHV 16 Velzenmaker Eindhoven Soja Chocolademelk Biologisch 6 97.50
REHV 16 Velzenmaker Eindhoven Zwezerik 6 77.70

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + Opdracht + + Bouw een aantal SQL-queries die interessante informatie opvragen uit de data. Je kan denken aan vragen zoals + + - Hoeveel verschillende verkopers zijn er? + + - Hoeveel verkopers per regio zijn er? + + + + Maak zo meer SQL-queries op de gegeven dataset (of een eigen dataset) waarin gebruik gemaakt is van onder andere + + WHERE, ORDER BY, GROUP BY, HAVING en subqueries; + + + +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

/*De query zoekt het aantal verkopers per regio en welke regio dat is.*/

  SELECT regioCode, COUNT(DISTINCT verkoper) AS verkoper_per_regio
  FROM dataset
  GROUP BY regioCode
3 records
regioCode verkoper_per_regio
GVGH 6
REHV 6
RHMD 6

Nieuwe query’s kan je aanroepen door in de balk van dit subscherm op het pijltje naast het groene C-knopje te drukken en te kiezen voor SQL. Vergeet niet de connectie ‘con’ heet en daar aan moet linken, net zoals de bovenstaande query’s.

De resultaten van het notebook kan je opslaan als een HTML-pagina. Om dat te doen klik je in de bovenstaande balk op de pijl naast Preview en selecteer je Knit to HTML. Een andere optie is om Ctrl+Shift+K in te drukken om een HTML bestand te krijgen.

/*De query geeft voor elke manager in welke regio ze werken en hoeveel omzet ze in totaal hebben gemaakt.*/

  SELECT regioCode AS regiocode, mgrnm AS managernaam, SUM(regelOmzet)AS totale_omzet
  FROM dataset
  GROUP BY regioCode, mgrnm;
Displaying records 1 - 10
regiocode managernaam totale_omzet
GVGH Bertha 6790.10
GVGH Gerda 6223.50
GVGH Harrie 5173.35
GVGH Herman 7119.70
GVGH Karel 10045.40
GVGH Klaas 4053.00
REHV Bertha 5977.00
REHV Gerda 11363.20
REHV Harrie 7490.35
REHV Herman 8828.75 
/* Geeft de top 5 best verkochte producten per regio op basis van totale omzet. */

SELECT regioCode, omschrijving AS product, SUM(regelOmzet) AS totale_omzet
FROM dataset
GROUP BY regioCode, omschrijving
ORDER BY regioCode, totale_omzet DESC
LIMIT 5;
5 records
regioCode product totale_omzet
GVGH Ganzenlever 7597.60
GVGH Kaviaar 5074.00
GVGH Soja Vanillevla Biologisch 4594.50
GVGH Soja Chocolademelk Biologisch 3682.35
GVGH Vruchtenyoghurt Mango 3558.75 
/* Geeft de totale omzet per woonplaats. */

SELECT woonplaats, SUM(regelOmzet) AS totale_omzet
FROM dataset
GROUP BY woonplaats
ORDER BY totale_omzet DESC;
6 records
woonplaats totale_omzet
Eindhoven 38033.50
Helmond 26287.65
Veghel 26017.40
Erp 13387.65
Laarbeek 13208.70
Waalre 9907.55 
/* Geeft de totale omzet van klanten die meer hebben uitgegeven dan de gemiddelde klant. */

SELECT klantnr, naam, SUM(regelOmzet) AS totale_omzet
FROM dataset
GROUP BY klantnr
HAVING SUM(regelOmzet) > (SELECT AVG(regelOmzet) FROM dataset)
ORDER BY totale_omzet DESC;
Displaying records 1 - 10
klantnr naam totale_omzet
11 Jansen 14246.30
13 Heiden, van der 13985.60
19 Zuur 13637.25
14 Kraymans 13387.65
18 Bocht 13208.70
12 Rademakers 12556.10
15 Boer, de 12380.15
17 Jansen 12302.05
16 Velzenmaker 11231.10
20 Bakermans 9907.55