Willkommen zu SQL für Fortgeschrittene!

Datenbearbeitung in SQL

Mona Khalil

Data Scientist, Greenhouse Software

Themen:

CASE-Anweisungen
Einfache Unterabfragen
Korrelierte Unterabfragen
Fensterfunktionen

Voraussetzungen

Daten auswählen, filtern und gruppieren

SELECT user_id, SUM(sales) 
FROM sales_data
WHERE user_id BETWEEN 300 AND 400
GROUP BY user_id;

Joins anwenden

SELECT c.country, c.team, SUM(m.goals)
FROM countries AS c
LEFT JOIN matches AS m
ON c.team_id = m.home_team_id
WHERE m.year > 1990
GROUP BY c.country, c.team;

SELECT und die europäische Fußballdatenbank

SELECT 
    l.name AS league,
    COUNT(m.country_id) as total_matches
FROM league AS l
LEFT JOIN match AS m
ON l.country_id = m.country_id
GROUP BY l.name;

| league                 | total_matches |
|------------------------|---------------|
| Belgium Jupiler League | 732           |
| England Premier League | 1520          |
| France Ligue 1         | 1520          |
| Germany 1. Bundesliga  | 1224          |

SELECT und die europäische Fußballdatenbank

SELECT 
    date,
    id,
    home_goal,
    away_goal
FROM match
WHERE season = '2013/2014';

| date                | id   | home_goal | away_goal |
|---------------------|------|-----------|-----------|
| 2014-03-29 00:00:00 | 1237 | 2         | 0         |
| 2014-03-29 00:00:00 | 1238 | 0         | 1         |
| 2014-04-05 00:00:00 | 1239 | 1         | 0         |
| 2014-04-05 00:00:00 | 1240 | 0         | 0         |

SELECT und die europäische Fußballdatenbank

SELECT 
    date,
    id,
    home_goal,
    away_goal
FROM match
WHERE season = '2013/2014'
      AND home_team_goal > away_team_goal;

| date                | id   | home_goal | away_goal |
|---------------------|------|-----------|-----------|
| 2014-03-29 00:00:00 | 1237 | 2         | 0         |
| 2014-04-05 00:00:00 | 1239 | 1         | 0         |
| 2014-04-12 00:00:00 | 1241 | 2         | 1         |
| 2014-04-12 00:00:00 | 1242 | 2         | 0         |

CASE-Anweisungen

Enthält die Anweisungen WHEN, THEN und ELSE und schließt mit END

CASE WHEN x = 1 THEN 'a' 
     WHEN x = 2 THEN 'b'
     ELSE 'c' END AS new_column

CASE WHEN

SELECT 
  id,
  home_goal,
  away_goal,
  CASE WHEN home_goal > away_goal THEN 'Home Team Win'
       WHEN home_goal < away_goal THEN 'Away Team Win'
       ELSE 'Tie' END AS outcome
FROM match
WHERE season = '2013/2014';

| id   | home_goal | away_goal | outcome       |
|------|-----------|-----------|---------------|
| 1237 | 2         | 0         | Home Team Win |
| 1238 | 0         | 1         | Away Team Win |
| 1239 | 1         | 0         | Home Team Win |
| 1240 | 0         | 0         | Tie           |

Lass uns üben!

Datenbearbeitung in SQL