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

Date de sortie

08/10/2024

Auteur

Untitled

“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 !

Produits

Découvrir

Ressources