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

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 !
Image without caption
Image without caption

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
Schéma de la table utilisée en entrée
Schéma de la table utilisée en entrée

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 )
Image without caption

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 )
Image without caption

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 )
Image without caption

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)
Image without caption

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 )
Image without caption
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
TutoLissageBQinput
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