Data vergelijken

Rapporteren in SQL

Tyler Pernes

Learning & Development Consultant

Vragen om te beantwoorden

Vorige maand vs maand ervoor?
Rolling 7 dagen?

original_table
+------------+----------+
| date       | revenue  |
|------------+----------|
| 2018-01-01 | 400      |
| 2018-01-02 | 380      |
| 2018-01-03 | 625      |
+------------+----------+

Maand-op-maand vergelijking

LAG(value, offset) geeft een value van een aantal rijen (offset) voor de huidige rij.
LEAD(value, offset) geeft een value van een aantal rijen (offset) na de huidige rij.

LAG en LEAD illustratie

Maand-op-maand vergelijking

Stap 1: omzet per maand

SELECT
    DATE_PART('month',date) AS month,
    SUM(revenue) as current_rev
FROM original_table
GROUP BY month;

Stap 2: omzet vorige maand

SELECT
    DATE_PART('month',date) AS month,
    SUM(revenue) as current_rev,
    LAG(SUM(revenue)) OVER (ORDER BY DATE_PART('month',date)) AS prev_rev
FROM original_table
GROUP BY month;

Maand-op-maand vergelijking

Stap 3: procentuele verandering

SELECT
    DATE_PART('month',date) AS month,
    SUM(revenue) as current_rev,
    LAG(SUM(revenue)) OVER (ORDER BY DATE_PART('month',date)) AS prev_rev,
    SUM(revenue)
    /
    LAG(SUM(revenue)) OVER (ORDER BY DATE_PART('month',date))-1 AS perc_change
FROM original_table
GROUP BY month;

Maand-op-maand vergelijking

Stap 3: procentuele verandering

+--------+--------------+-----------+--------------+
| month  | current_rev  | prev_rev  | perc_change  |
|--------+--------------+-----------+--------------|
| 01     | 15000        | null      | null         |
| 02     | 14000        | 15000     | -.06         |
| 03     | 21000        | 14000     | .50          |
+--------+--------------+-----------+--------------+

Rolling-berekeningen

Neem alleen 7 rijen mee

Nieuwe clausule: ROWS BETWEEN

SUM(value) OVER (ORDER BY value ROWS BETWEEN N PRECEDING AND CURRENT ROW)

Rolling-berekeningen

Rolling som-query

SELECT 
    date,
    SUM(SUM(revenue)) OVER 
    (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS weekly_revenue
FROM original_table
GROUP BY date;

Nieuwe tabel: web_data

web_data
+------------+-------------+--------+
| date       | country_id  | view   |
|------------+-------------+--------|
| 2018-01-01 | 1           | 24313  |
| 2018-01-01 | 2           | 3768   |
| 2018-01-01 | 3           | 26817  |
+------------+-------------+--------+

Laten we oefenen!

Rapporteren in SQL