Confrontare le date

Reportistica in SQL

Tyler Pernes

Learning & Development Consultant

Domande a cui rispondere

Mese scorso vs mese precedente?
Finestra mobile di 7 giorni?

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

Confronto mese su mese

LAG(value, offset) restituisce il value della riga con offset righe prima di quella corrente.
LEAD(value, offset) restituisce il value della riga con offset righe dopo quella corrente.

Confronto mese su mese

Passo 1: mostra il fatturato per mese

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

Passo 2: fatturato del mese precedente

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;

Confronto mese su mese

Passo 3: calcolo della variazione percentuale

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;

Confronto mese su mese

Passo 3: calcolo della variazione percentuale

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

Calcoli in finestra mobile

Considera solo 7 righe

Nuova clausola: ROWS BETWEEN

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

Calcoli in finestra mobile

Query per somma mobile

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;

Nuova tabella: web_data

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

Esercitiamoci!

Reportistica in SQL