Header

Views

Introductie

Met een common table expression (CTE) kun je binnen een query een andere query vanuit een naam gebruiken.

Een CTE is alleen binnen de query bruikbaar waar je het aanmaakt.
Het is dus niet vanuit andere queries bruikbaar.

Als je een query wilt hergebruiken binnen meerdere queries, dan kun je met views werken.

We gaan dit stap voor stap bekijken vanuit een voorbeeld.

1. Voorbeeld views

We gaan met een vraagstuk aan de slag.

Hier gaan we uiteindelijk een view bij gebruiken.

We bouwen het stap voor stap op.

Eerst kijken we naar een voorbeeld met een common table expression.
Daarna herschrijven we het met een view.

1.1. Vraagstelling

Header

In tabel movies hebben we details van een film.
- Bijvoorbeeld het jaar, en de score.
We willen een overzicht van alle films.
- Met daaraan toegevoegd:
  - De gemiddelde score van alle films uit het jaar waarop een film uitkwam.

Hiermee kunnen we zien of een film beter/slechter was dan de gemiddelde film uit dat jaar.

1.2. Oplossing met CTE

Met een common table expression lossen we dit probleem op:

Met de CTE berekenen we met GROUP BY en AVG() de gemiddelde score per jaar.
Dit voegen we met een LEFT JOIN toe aan details van films.

WITH year_details AS (
    SELECT
      year,
      AVG(rank) AS avg_rank_by_year
    FROM
      movies
    GROUP BY
      year
  )
SELECT
  m.*,
  year_details.avg_rank_by_year
FROM
  movies AS m
  LEFT JOIN year_details ON m.year = year_details.year;

id	name	year	rank	avg_rank_by_year
10920	Aliens	1986	8.2	8.2
17173	Animal House	1978	7.5	7.5
18979	Apollo 13	1995	7.5	7.9
...	...	...	...	...

Columns: 5
Rows: 36

1.3. Een view aanmaken

Nu gaan we een view maken maken.

Een view is een query die je opslaat en elders kunt gebruiken.
- Vanuit al je andere queries.
- In tegenstelling tot een CTE:
  - Die is alleen bruikbaar binnen de query waar je het aanmaakt.
Je kunt er informatie uit opvragen, net als uit een tabel.
Een view slaat zelf geen data op zoals een tabel dat doet.
- Het bewaart alleen de query.

We maken een view aan.

Later leggen we de syntax uit.

CREATE VIEW year_details AS
SELECT
  year,
  AVG(rank) AS avg_rank_by_year
FROM
  movies
GROUP BY
  year;

Als we deze query uitvoeren wordt de view met naam year_details aangemaakt.

Query execution started 
Query execution finished

We kunnen nu data uit de view opvragen:

SELECT
  *
FROM
  year_details;

year	avg_rank_by_year
1989	6.949999999999999
1991	7.8
1977	8.8
...	...

Columns: 2
Rows: 20

Je ziet dat hetzelfde werkt als bij data opvragen uit een tabel.

1.4. Oplossing met CTE

We passen de view nu toe om ons vraagstuk op te lossen:

SELECT
  m.*,
  year_details.avg_rank_by_year
FROM
  movies AS m
  LEFT JOIN year_details ON m.year = year_details.year

id	name	year	rank	avg_rank_by_year
10920	Aliens	1986	8.2	8.2
17173	Animal House	1978	7.5	7.5
18979	Apollo 13	1995	7.5	7.9
...	...	...	...	...

Columns: 5
Rows: 36

Je ziet dat dit hetzelfde resultaat geeft als de oplossing met CTE.

Maar dat dit nu een stuk compacter en beter leesbaar is.

2. View syntax

We hebben een voorbeeld van een view bekeken.

Daarin kun je de volgende syntax herkennen:

CREATE VIEW <view_name> AS 
  <view_query>

Je doet hiermee het volgende:

Je maakt een view aan met CREATE en AS.
- Daarbij geef je de view een naam.
  - Met deze naam kun je de query van de view elders oproepen.
Je geeft een query op die onder deze naam uitgevoerd moet worden.

3. View of CTE

Onderstaande punten geven inzicht wanneer je een view of CTE gebruikt:

Ad-hoc queries die je maar eenmalig uitvoert: gebruik een CTE.
Herhaaldelijk hergebruikte queries: gebruik een view.
Gebruikers beperkte toegang geven tot data: view.
- Zo kun je bijvoorbeeld een deel van een tabel afschermen voor bepaalde gebruikers.

Samenvatting

Een view geeft een query een naam.
Met de naam is de query vervolgens elders te gebruiken.
- In alle andere queries.
Voorbeeld:

CREATE VIEW <view_name> AS 
<view_query>

Een view maakt je code beter leesbaar, en kan gebruikt worden om toegang af te schermen.