Comparer des dates

Création de rapports en SQL

Tyler Pernes

Learning & Development Consultant

Questions à résoudre

Dernier mois ou mois précédent ?
Roulement sur 7 jours ?

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

Comparaison d'un mois à l'autre

LAG(value, offset) retourne la value d'un nombre de lignes offset avant la ligne courante.
LEAD(value, offset) retourne la value d'un nombre de lignes offset après la ligne courante.

Comparaison d'un mois à l'autre

Étape 1 : afficher les revenus par mois

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

Étape 2 : revenus du mois précédent

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;

Comparaison d'un mois à l'autre

Étape 3 : calcul du pourcentage de changement

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;

Comparaison d'un mois à l'autre

Étape 3 : calcul du pourcentage de changement

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

Calculs roulants

Ne tenir compte que de 7 lignes

Nouvelle clause : ROWS BETWEEN

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

Calculs roulants

Requête de somme roulante

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;

Nouvelle table : web_data

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

Passons à la pratique !

Création de rapports en SQL