sneak peek

• Un champ date (ou datetime, timestamp). Dans mon exemple c’est measured_at.

• En option, on peut avoir des dimensions pour partitioner tout ça (j’ai ajouté name et country)

• En option, des métriques ou dimensions à extrapoler aux dates qui n’existent pas, et ici on en utilise trois: emails_sent, pets, is_employed.

sql
GAP_FILL (
  TABLE time_series_table,
  time_series_column,
  bucket_width,
  [, partitioning_columns=>value]
  [, value_columns=>value ]
  [, origin=>value]
  [, ignore_null_values=>value]
)

1. time_series_table
Ici on a juste à notifier quelle est la table qu’on veut utiliser pour être la source de notre GAP_FILL. Il y a deux possibilités :

sql
TABLE `fake_data_cleaned_gap_fill`

sql
(SELECT * FROM `fake_data_cleaned_gap_fill`)

2. time_series_column
Là on parle de notre champ de date, et pour moi c’est measured_at

sql
ts_column => 'measured_at'

3. bucket_width
Pour spécifier l’interval de temps entre chaque bucket. Là encore ça peut être une date, datetime, ou timestamp. On peut choisir par exemple toutes les 5 minutes. Ici moi j’ai choisi tous les jours.

sql
bucket_width => INTERVAL 1 DAY

4. partitioning_columns (optionnel)
Il peut arriver qu’il y ai plusieurs critères sur lesquels on doive partitioner notre table finale. Ici j’aurais besoin d’avoir une table par jour, par nom, et par pays.

sql
partitioning_columns => ['name', 'country']

5. value_columns (optionnel)
Lorsqu’on choisit d’utiliser des métriques ou dimensions à extrapoler, on doit choisir comment remplir les lignes qui n’existent pas. Il y a trois choix :
• null : si la ligne n’existait pas, la valeur sera null
• linear : si la ligne n’existait pas, une valeur est appliquée suivant la fonction linéaire (marche seulement pour les métriques, et pas les dimensions)
• locf (last observation carried forward): si la ligne n’existait pas, la dernière valeur qui existait est appliquée
Dans mon exemple,
• Pour les emails_sent, on veut qu’ils s’appliquent qu’au jour où ils ont été envoyé
• Pour les pets, on veut répercuter le nombre d’animaux pour les jours où il n’y a pas de données
• Pour le booléen is_employed, on part du principe que tant que le booléen ne change pas d’état, il faut reprendre la valeur précédente

sql
value_columns => [
        ('emails_sent','null'),
        ('pets','locf'),
        ('is_employed','locf')
    ]

6. origin (optionnel)
Cet argument sert à spécifier le moment à partir duquel créer les buckets (date, datetime, ou timestamp, mais doit être du même type que la time_series_column). Comme par défaut la valeur de time_series_column est utilisée et que ça correspond à ce qu’on recherche, je ne la spécifie pas.

7. ignore_null_values (optionnel)
C’est un booléen qui indique si la fonction doit ignorer les valeurs nulles dans la table qu’on lui a donné. Sa valeur par défaut est à vrai, donc on ne va pas le spécifier ici non plus.

sql
SELECT
  *
FROM
  GAP_FILL(
    TABLE `fake_data_cleaned_gap_fill`,
    ts_column => 'measured_at',
    bucket_width => INTERVAL 1 DAY,
    partitioning_columns => ['name', 'country'],
    value_columns => [
        ('emails_sent','null'),
        ('pets','locf'),
        ('is_employed','locf')
    ]
  )
ORDER BY measured_at

output

Time series functions  |  BigQuery  |  Google Cloud

GoogleSQL for BigQuery supports the following time series functions.

https://cloud.google.com/bigquery/docs/reference/standard-sql/time-series-functions#gap_fill

✍️

L’auteure : Eva Despesse

BI Engineer chez UnNest, Eva est en charge des sujets de modélisation de la donnée et du dashboarding, notamment sur Tableau et Power BI.

Ayant commencé dans une start-up, j’ai pu toucher aux flux de données de bout en bout. J’apprécie particulièrement faire les modélisations en bout de chaine avec le dashboarding pour suivre toute la chaine de transformation de données en information.

✉️ Me contacter : eva.despesse@unnest.co

↗️ Me suivre sur LinkedIn