SQL : Comment supprimer les doublons d'une liste (array) sur BigQuery

Par Xavier le
Le code ci-dessous permet de simplement retirer les doublons dans une liste sur BigQuery. Plus bas se trouve un cas pratique utilisant ce bout de code
La table en entrée avec des doublons dans la liste (array) ressort sans les doublons
La table en entrée avec des doublons dans la liste (array) ressort sans les doublons

Code SQL👇

sql
SELECT * REPLACE ( (SELECT ARRAY_AGG(agg_liste) FROM ( SELECT DISTINCT * FROM UNNEST(email) AS liste) -- Remplacer email par le nom de la liste à dédupliquer AS agg_liste) AS email ) -- Remplacer email par le nom de la liste à dédupliquer FROM 'TABLE' -- Insérer la source ici
Code permettant d'enlever les doublons présents dans une liste (array)
Il suffit de remplacer les occurrences du mot 'email' par le nom de la colonne sous format array que vous souhaitez dé-dupliquer

Source

Cet article est inspiré de celui-ci :
How to find DISTINCT values in a ARRAY in Bigquery
Often, we have array datatypes and want to remove the duplicates (often called as dedup) and find out the unique values in an array in Bigquery, like shown in the example below: Solution: Much cleaner way is to use SQL UDF to encapsulate dedup logic as in below example and
How to find DISTINCT values in a ARRAY in Bigquery
Avec pour principales différences l'ajout de noms explicites pour mieux comprendre les étapes du code et le retrait de la fonction pour que le code s'exécute sans avoir à la déclarer au préalable.

Annexe (Cas pratique)

Table utilisée en entrée

TutoDedupBQ
Sheet1 id,date,email ...
TutoDedupBQ

Séparation des emails en liste

sql
SELECT * EXCEPT (email), SPLIT(email,',') AS email FROM `TABLE`
Dans la table d'entrée, les emails sont dans un seul champ, séparés par des virgules, ce CTE permet de les séparer en liste

Aplatir la liste

sql
SELECT * EXCEPT (email, liste), liste AS email FROM dedup_mail, UNNEST(email)
Ce CTE placé après peut vous permettre d'aplatir la liste pour avoir une ligne par email. Attention cela va compromettre l'unicité de la ligne id et des éventuels autres KPIs présents

Résultat

Image without caption

Toutes ces étapes utilisées dans une même requête

sql
WITH table_source AS ( SELECT * FROM `TABLE` -- sélection de la table en entrée ), split_mail AS ( SELECT * EXCEPT (email), SPLIT(email,',') AS email FROM table_source ), dedup_mail AS ( SELECT * REPLACE ( (SELECT ARRAY_AGG(agg_liste) FROM ( SELECT DISTINCT * FROM UNNEST(email) AS liste) AS agg_liste) AS email ) FROM split_mail ), final AS ( SELECT * EXCEPT (email, liste), liste AS email FROM dedup_mail,UNNEST(email) ) SELECT * FROM final