Par Xavier le ‣

Dans de nombreuses situations il peut être utile de vouloir lisser dans votre base de données des montants sur une période définie (loyers, salaires, budgets, abonnements annuels, etc.), notamment à des fins de visualisation. S'il peut être plus efficace de le calculer en amont avec d'autres langages, cette opération est totalement faisable en SQL dans BigQuery. Et avec la puissance de calcul d'un Data Warehouse moderne, il n'y a pas de raison de s'en priver !

Code complet 👇 | Explications détaillées plus bas

Ici, il sera recommandé d'utiliser les dates sous un format DATE et non TIMESTAMP. Il est facile de faire la conversion dans BigQuery avec : CAST(nom_variable as DATE) as nom_variable Les variables à remplacer sont :

Code SQL pour BigQuery:

sql
WITH
  table_montant_date AS (
  SELECT *
  FROM `VOTRE_TABLE`  -- La location de votre table d'entrée ici 
  ),

  creation_mois_interval AS (
      SELECT *,
      GENERATE_DATE_ARRAY(dateDebut, LAST_DAY(dateFin), INTERVAL 1 MONTH) AS moisIntervalArray
			FROM  table_montant_date 
  ),

  unnest_mois_interval AS (
      SELECT * EXCEPT(moisIntervalArray,moisIntervalUnnest),
      DATE_TRUNC(moisIntervalUnnest,MONTH) as moisInterval 
			FROM creation_mois_interval, 
      UNNEST(moisIntervalArray) as moisIntervalUnnest
 ),

  jours_interval as (
      SELECT
      *,
      CASE
      WHEN EXTRACT(MONTH FROM dateDebut) = EXTRACT(MONTH FROM dateFin) AND EXTRACT(YEAR FROM dateFin) = EXTRACT(YEAR FROM DateDebut) THEN date_diff(dateFin,DateDebut, DAY) + 1
      WHEN EXTRACT(MONTH FROM moisInterval) = EXTRACT(MONTH FROM dateDebut) AND EXTRACT(YEAR FROM moisInterval) = EXTRACT(YEAR FROM DateDebut) THEN DATE_DIFF(LAST_DAY(moisInterval), DateDebut, DAY) + 1
      WHEN EXTRACT(MONTH FROM moisInterval) = EXTRACT(MONTH FROM dateFin) AND EXTRACT(YEAR FROM moisInterval) = EXTRACT(YEAR FROM dateFin) THEN DATE_DIFF(dateFin,moisInterval, day) + 1
      ELSE EXTRACT (DAY FROM LAST_DAY(moisInterval)) 
      END AS joursDansMois
      FROM
      unnest_mois_interval),

  final as ( 

    SELECT *, 
    ROUND(joursDansMois * Montant/(DATE_DIFF(dateFin, dateDebut, day)+1),2) as montantLisse
    FROM jours_interval
  )

SELECT * FROM final

Explications détaillées :

Le premier CTE (bloc de code) permet de définir la table d'entrée (voir l'annexe pour remplacer les noms de colonnes)

sql
table_montant_date AS (
  SELECT *
  FROM `reporting-digital.Tutoriel_BQ_Xavier.stg_tutoLissage`  
  -- Sélection de la table contenant les données à traiter
  -- Si désiré, renommer et convertir les colonnes dates & montant ici
  )

Le second CTE se base sur les dates de début et de fin pour créer une liste de tous les mois compris dans cet interval

sql

  creation_mois_interval AS (
      SELECT *,
      GENERATE_DATE_ARRAY(dateDebut, LAST_DAY(dateFin), INTERVAL 1 MONTH) AS moisIntervalArray
      -- Génère une liste de TOUS les mois inclus dans l'interval entre dateDebut et dateFin
      -- LAST_DAY(dateFin) est utilisé pour s'assurer d'avoir le dernier mois si l'interval ne correspond pas à un nombre de mois ronds
      FROM  table_montant_date 
  )

Le troisième CTE de passer d'une liste de mois à une ligne pour chaque 1er du mois compris dans l'interval

sql
  unnest_mois_interval AS (
      SELECT * EXCEPT(moisIntervalArray,moisIntervalUnnest),
      DATE_TRUNC(moisIntervalUnnest,MONTH) as moisInterval 
      -- DATE_TRUNC Permet d'harmoniser les date des mois interval, en les affichants au premier du mois
      FROM creation_mois_interval, 
      UNNEST(moisIntervalArray) as moisIntervalUnnest
      -- UNNEST va aplatir la liste afin d'avoir une ligne distincte pour chaque mois d'interval 
  )

Le quatrième CTE est là où sont calculés le nombres de jours sur lequel répartir le montant pour tous les mois dans l'interval

sql
jours_interval as (
      SELECT
      *,
      -- Ce CASE va déterminer si le nombre de jours 'actifs' pour chaque mois dans l'interval, (+ 1 part du principe que un contrat débutant et finissant le même jour compte pour 1 jour et non 0)
      CASE
      WHEN EXTRACT(MONTH FROM dateDebut) = EXTRACT(MONTH FROM dateFin) AND EXTRACT(YEAR FROM dateFin) = EXTRACT(YEAR FROM DateDebut) THEN date_diff(dateFin,DateDebut, DAY) + 1
      -- Si dateDebut et dateFin sont du même mois, alors le nombre de jours prends la différence de jours entre la fin et le début 
      WHEN EXTRACT(MONTH FROM moisInterval) = EXTRACT(MONTH FROM dateDebut) AND EXTRACT(YEAR FROM moisInterval) = EXTRACT(YEAR FROM DateDebut) THEN DATE_DIFF(LAST_DAY(moisInterval), DateDebut, DAY) + 1
      -- Si dateDebut et moisInterval sont du même mois, alors le nombre de jours prends la différence entre le dernier jours du mois et le début
      WHEN EXTRACT(MONTH FROM moisInterval) = EXTRACT(MONTH FROM dateFin) AND EXTRACT(YEAR FROM moisInterval) = EXTRACT(YEAR FROM dateFin) THEN DATE_DIFF(dateFin,moisInterval, day) + 1
      -- Si dateFin et mois interval sont du même mois,alors le nombre de jours prends la différence entre la fin et le début de mois 
      ELSE EXTRACT (DAY FROM LAST_DAY(moisInterval)) 
      -- Sinon pour tous les mois 'a l'intérieur' de l'interval,le nombre de jours prends le total de jours dans le mois

      END AS joursDansMois
      FROM
      unnest_mois_interval)

Le dernier CTE important va ramener le montant à un montant journalier, le multiplier par le nombre de jours adéquat dans le mois et l'arrondir au centime près

sql
  final as ( --prorata_montant

    SELECT *, 
    ROUND(joursDansMois * Montant/(DATE_DIFF(dateFin, dateDebut, day)+1),2) as montantLisse
    -- Calcul du montant moyen journalier multiplié par le nombre de jours 'actifs' dans le mois, arrondi au centime 
    FROM jours_interval
  )

Dans un outil de BI, il conviendra d'utiliser moisInterval en dimension de date (en format ANNÉE-MOIS) et montantLisse pour la métrique du montant (et les renommer si désiré)

Annexe

Code complet commenté :

sql
WITH
  table_montant_date AS (
  SELECT *
  FROM `VOTRE_TABLE`   
  -- Renseignement de la table contenant les données à traiter
  -- Si désiré, renommer et convertir les colonnes dates & montant ici
  ),

  creation_mois_interval AS (
      SELECT *,
      GENERATE_DATE_ARRAY(dateDebut, LAST_DAY(dateFin), INTERVAL 1 MONTH) AS moisIntervalArray
      -- Génère une liste de TOUS les mois inclus dans l'interval entre dateDebut et dateFin
      -- LAST_DAY(dateFin) est utilisé pour s'assurer d'avoir le dernier mois si l'interval ne correspond pas à un nombre de mois ronds
      FROM  table_montant_date 
  ),

  unnest_mois_interval AS (
      SELECT * EXCEPT(moisIntervalArray,moisIntervalUnnest),
      DATE_TRUNC(moisIntervalUnnest,MONTH) as moisInterval 
      -- DATE_TRUNC Permet d'harmoniser les date des mois interval, en les affichants au premier du mois
      FROM creation_mois_interval, 
      UNNEST(moisIntervalArray) as moisIntervalUnnest
      -- UNNEST va aplatir la liste afin d'avoir une ligne distincte pour chaque mois d'interval 
  ),

  jours_interval as (
      SELECT
      *,
      -- Ce CASE va déterminer si le nombre de jours 'actifs' pour chaque mois dans l'interval, (+ 1 part du principe que un contrat débutant et finissant le même jour compte pour 1 jour et non 0)
      CASE
      WHEN EXTRACT(MONTH FROM dateDebut) = EXTRACT(MONTH FROM dateFin) AND EXTRACT(YEAR FROM dateFin) = EXTRACT(YEAR FROM DateDebut) THEN date_diff(dateFin,DateDebut, DAY) + 1
      -- Si dateDebut et dateFin sont du même mois, alors le nombre de jours prends la différence de jours entre la fin et le début 
      WHEN EXTRACT(MONTH FROM moisInterval) = EXTRACT(MONTH FROM dateDebut) AND EXTRACT(YEAR FROM moisInterval) = EXTRACT(YEAR FROM DateDebut) THEN DATE_DIFF(LAST_DAY(moisInterval), DateDebut, DAY) + 1
      -- Si dateDebut et moisInterval sont du même mois, alors le nombre de jours prends la différence entre le dernier jours du mois et le début
      WHEN EXTRACT(MONTH FROM moisInterval) = EXTRACT(MONTH FROM dateFin) AND EXTRACT(YEAR FROM moisInterval) = EXTRACT(YEAR FROM dateFin) THEN DATE_DIFF(dateFin,moisInterval, day) + 1
      -- Si dateFin et mois interval sont du même mois,alors le nombre de jours prends la différence entre la fin et le début de mois 
      ELSE EXTRACT (DAY FROM LAST_DAY(moisInterval)) 
      -- Sinon pour tous les mois 'a l'intérieur' de l'interval,le nombre de jours prends le total de jours dans le mois
      END AS joursDansMois
      FROM
      unnest_mois_interval),

  final as ( 

    SELECT *, 
    ROUND(joursDansMois * Montant/(DATE_DIFF(dateFin, dateDebut, day)+1),2) as montantLisse
    -- Calcul du montant moyen journalier multiplié par le nombre de jours 'actifs' dans le mois, arrondi au centime 
    FROM jours_interval
  )

SELECT * FROM final

Code complet commenté

Table utilisée en entrée :

TutoLissageBQinput

Sheet1 dateFacturation,dateDebut,dateFin,montantFacture 20/10/2021,20/10/2021,19/03/2022,22000 13/11/2021,11/09/2021,26/11/2021,4500 02/01/2022,15/12/2021,15/01/2022,9000

https://docs.google.com/spreadsheets/d/1mCv7FYrshc_kFrd9nSZwxjBIfePTZSo0ro-ZpUZ_ooY/edit?usp=sharing

sql
table_montant_date AS (
  SELECT 
  Montant as Montant,
  dateDebut as dateDebut,
  dateFin as dateFin
  FROM `VOTRE TABLE`
-- Renseignement de la table contenant les données à traiter
-- Si désiré, renommer et convertir les colonnes dates & montant ici
  ),

À des fins de test rapide, il est possible de remplacer le premier CTE par celui là et de remplacer les noms de colonnes avant 'as' par ceux désirés

SQL : Comment lisser des coûts entre 2 dates (sur plusieurs mois) dans BigQuery