Datayoka
Datayoka
SQL - C’est quoi ARRAY_LENGTH() sur BigQuery ?

SQL - C’est quoi ARRAY_LENGTH() sur BigQuery ?

Date de sortie
October 8, 2024
Auteur
Bertrand Fabre
“Hey, j’ai une table de commande et dedans j’ai un ARRAY STRUCT avec le détail des produits commandés. Comment je peux faire pour compter le nombre de produits par commande sans faire de UNNEST ?”

Sur BigQuery, il existe une fonction afin de compter le nombre d’éléments d’un tableau, c’est ARRAY_LENGTH().

Syntaxe

ARRAY_LENGTH(il_attend_donc_un_array)

Pas grande chose à décrire sur cette syntaxe 😅.

Néanmoins, un exemple sera toujours plus parlant.

🤧 Les commandes de ventes de mouchoirs

WITH orders AS (
    SELECT "CMD001" AS order_id, "CLT001" AS client_id, DATE("2024-10-01") AS order_date,
        [
            STRUCT("Mouchoirs en papier standard" AS tissue_type, 5 AS quantity, 1.5 AS unit_price),
            STRUCT("Mouchoirs hypoallergéniques" AS tissue_type, 2 AS quantity, 2.0 AS unit_price),
            STRUCT("Mouchoirs mentholés" AS tissue_type, 1 AS quantity, 2.5 AS unit_price)
        ] AS products
    UNION ALL
    SELECT "CMD002", "CLT002", DATE("2024-10-02"),
        [
            STRUCT("Mouchoirs en papier recyclé", 10, 1.2),
            STRUCT("Mouchoirs parfumés", 3, 2.8)
        ]
    UNION ALL
    SELECT "CMD003", "CLT003", DATE("2024-10-03"),
        [
            STRUCT("Mouchoirs en papier standard", 7, 1.5),
            STRUCT("Mouchoirs ultra-doux", 4, 3.0)
        ]
)

SELECT *
FROM orders;

Résultat :

Ligne
order_id
client_id
order_date
products.tissue_type
products.quantity
products.unit_price
1
CMD001
CLT001
2024-10-01
Mouchoirs en papier standard
5
1.5
Mouchoirs hypoallergéniques
2
2.0
Mouchoirs mentholés
1
2.5
2
CMD002
CLT002
2024-10-02
Mouchoirs en papier recyclé
10
1.2
Mouchoirs parfumés
3
2.8
3
CMD003
CLT003
2024-10-03
Mouchoirs en papier standard
7
1.5
Mouchoirs ultra-doux
4
3.0

Nombre de produits par commande

WITH orders AS (
    SELECT "CMD001" AS order_id, "CLT001" AS client_id, DATE("2024-10-01") AS order_date,
        [
            STRUCT("Mouchoirs en papier standard" AS tissue_type, 5 AS quantity, 1.5 AS unit_price),
            STRUCT("Mouchoirs hypoallergéniques" AS tissue_type, 2 AS quantity, 2.0 AS unit_price),
            STRUCT("Mouchoirs mentholés" AS tissue_type, 1 AS quantity, 2.5 AS unit_price)
        ] AS products
    UNION ALL
    SELECT "CMD002", "CLT002", DATE("2024-10-02"),
        [
            STRUCT("Mouchoirs en papier recyclé", 10, 1.2),
            STRUCT("Mouchoirs parfumés", 3, 2.8)
        ]
    UNION ALL
    SELECT "CMD003", "CLT003", DATE("2024-10-03"),
        [
            STRUCT("Mouchoirs en papier standard", 7, 1.5),
            STRUCT("Mouchoirs ultra-doux", 4, 3.0)
        ]
)

SELECT order_id, ARRAY_LENGTH(products) AS nbProducts
FROM orders;

Résultat :

Ligne
order_id
nbProducts
1
CMD001
3
2
CMD002
2
3
CMD003
2

Filtrage des données

Maintenant, on voudrait obtenir tout le détail des données mais seulement pour les commandes qui contiennent plus de deux produits.

WITH orders AS (
    SELECT "CMD001" AS order_id, "CLT001" AS client_id, DATE("2024-10-01") AS order_date,
        [
            STRUCT("Mouchoirs en papier standard" AS tissue_type, 5 AS quantity, 1.5 AS unit_price),
            STRUCT("Mouchoirs hypoallergéniques" AS tissue_type, 2 AS quantity, 2.0 AS unit_price),
            STRUCT("Mouchoirs mentholés" AS tissue_type, 1 AS quantity, 2.5 AS unit_price)
        ] AS products
    UNION ALL
    SELECT "CMD002", "CLT002", DATE("2024-10-02"),
        [
            STRUCT("Mouchoirs en papier recyclé", 10, 1.2),
            STRUCT("Mouchoirs parfumés", 3, 2.8)
        ]
    UNION ALL
    SELECT "CMD003", "CLT003", DATE("2024-10-03"),
        [
            STRUCT("Mouchoirs en papier standard", 7, 1.5),
            STRUCT("Mouchoirs ultra-doux", 4, 3.0)
        ]
)

SELECT *
FROM orders
WHERE ARRAY_LENGTH(products) > 2
;

Résultat :

Ligne
order_id
client_id
order_date
products.tissue_type
products.quantity
products.unit_price
1
CMD001
CLT001
2024-10-01
Mouchoirs en papier standard
5
1.5
Mouchoirs hypoallergéniques
2
2.0
Mouchoirs mentholés
1
2.5

Points importants à noter

  • Valeurs NULL : si le tableau est NULL, ARRAY_LENGTH() retournera NULL ;
  • Tableaux vides : si le tableau est vide (c'est-à-dire []), la fonction retournera 0 ;
  • Éléments NULL dans le tableau : les éléments NULL à l'intérieur du tableau sont comptés. Par exemple, pour [1, NULL, 3], ARRAY_LENGTH() retournera 3.

Merci de votre lecture !