UnNest - Data Consulting
UnNest - Data Consulting
🤌

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

Twitter LinkedIn Nos services

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

Par Xavier le Dec 13, 2021 6:06 PM (GMT+1)

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👇


    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 :

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

Séparation des emails en liste 

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 

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

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

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

Tous les articles concernant Google BigQuery