Tour d'horizon des fonctions utiles

Analyser des données d'entreprise en SQL

Michel Semaan

Data Scientist

Gérer les dates

DATE_TRUNC('quarter', '2018-08-13') → '2018-07-01 00:00:00+00:00'
'2018-07-01 00:00:00+00:00' :: DATE → '2018-07-01'

Dates dans les rapports

Des dates lisibles sont essentielles dans les rapports
Le format par défaut '2018-08-13' est peu parlant
Comment passer de '2018-08-13' à 'Friday 13, August 2018' ?

Solution

TO_CHAR('2018-08-13', 'FMDay DD, FMMonth YYYY') → 'Friday 13, August 2018'

TO_CHAR()

TO_CHAR(DATE, TEXT) → TEXT (la date mise en forme)
Exemple : Dy → nom abrégé du jour (Mon, Tues, etc.)
- TO_CHAR('2018-06-01', 'Dy') → 'Fri'
- TO_CHAR('2018-06-02', 'Dy') → 'Sat'
Les motifs de la chaîne de format sont remplacés par les éléments de la date; les autres caractères restent tels quels
Exemple : DD → numéro du jour (01 - 31)
- TO_CHAR('2018-06-01', 'Dy - DD') → 'Fri - 01'
- TO_CHAR('2018-06-02', 'Dy - DD') → 'Sat - 02'

Motif	Description
`FMDay`	Nom complet du jour (`Monday`, `Tuesday`, etc.)
`MM`	Mois de l'année (`01` - `12`)
`Mon`	Nom abrégé du mois (`Jan`, `Feb`, etc.)
`FMMonth`	Nom complet du mois (`January`, `February`, etc.)
`YY`	2 derniers chiffres de l'année (`18`, `19`, etc.)
`YYYY`	Année sur 4 chiffres (`2018`, `2019`, etc.)

Documentation : https://www.postgresql.org/docs/9.6/functions-formatting.html

Requête

SELECT DISTINCT
  order_date,
  TO_CHAR(order_date,
          'FMDay DD, FMMonth YYYY') AS format_1,
  TO_CHAR(order_date,
          'Dy DD Mon/YYYY') AS format_2
FROM orders
ORDER BY order_date ASC
LIMIT 3;

Résultat

order_date  format_1                format_2       
----------  ----------------------  ---------------
2018-06-01  Friday 01, June 2018    Fri 01/Jun 2018
2018-06-02  Saturday 02, June 2018  Sat 02/Jun 2018
2018-06-02  Sunday 03, June 2018    Sun 03/Jun 2018

Fonctions de fenêtre : rappel

SUM(...) OVER (...) : calcule le total cumulatif d'une colonne
- Exemple : SUM(registrations) OVER (ORDER BY registration_month) calcule le cumul des inscriptions
LAG(...) OVER (...) : récupère la valeur de la ligne précédente
- Exemple : LAG(mau) OVER (ORDER BY active_month) retourne les utilisateurs actifs du mois précédent (MAU)
RANK() OVER (...) : attribue un rang à chaque ligne selon sa position dans l'ordre trié
- Exemple : RANK() OVER (ORDER BY revenue DESC) classe les utilisateurs, restos ou mois selon les revenus générés

Requête

SELECT
  user_id,
  SUM(meal_price * order_quantity) AS revenue
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
GROUP BY user_id
ORDER BY revenue DESC
LIMIT 3;

Résultat

user_id  revenue
-------  -------
18       626
76       553.25
73       537

Requête

WITH user_revenues AS (
  SELECT
    user_id,
    SUM(meal_price * order_quantity) AS revenue
  FROM meals
  JOIN orders ON meals.meal_id = orders.meal_id
  GROUP BY user_id)

SELECT
  user_id,
  RANK() OVER (ORDER BY revenue DESC)
    AS revenue_rank
FROM user_revenues
ORDER BY revenue_rank DESC
LIMIT 3;

Résultat

user_id  revenue_rank
-------  ------------
18       1
76       2
73       3

Tour d'horizon des fonctions utiles

Analyser des données d'entreprise en SQL