Skip to content

Instantly share code, notes, and snippets.

@ahmed-bhs

ahmed-bhs/pipe-sytaxe.md

Last active October 15, 2024 13:06
Show Gist options
  • Save ahmed-bhs/c539de8820941b1d219c21dbcf7c5e2a to your computer and use it in GitHub Desktop.
Save ahmed-bhs/c539de8820941b1d219c21dbcf7c5e2a to your computer and use it in GitHub Desktop.
Download ZIP
Raw
pipe-sytaxe.md

PIPE Syntaxe |>

La syntaxe Pipe est une extension de GoogleSQL qui permet d'écrire des requêtes de manière linéaire, rendant ainsi les requêtes plus faciles à lire, écrire et maintenir. Elle offre une flexibilité accrue en permettant d'appliquer les opérations dans n'importe quel ordre et autant de fois que nécessaire.

Principales caractéristiques :

  • Structure linéaire des requêtes : Les requêtes commencent par une clause FROM et utilisent |> pour appliquer des transformations.

  • Exemple :

    FROM mydataset.sales
|> AGGREGATE SUM(revenue) AS total_revenue

  • Résultat :

    total_revenue
    150000

  • Exemple 1 :

    FROM mydataset.sales
|> WHERE product_type = 'Electronics'
|> AGGREGATE SUM(revenue) AS total_revenue_electronics

  • Résultat :

    total_revenue_electronics
    90000

  • Exemple 2 :

    FROM mydataset.sales
|> WHERE region = 'North America'
|> AGGREGATE SUM(revenue) AS total_revenue_by_region
|> EXTEND avg_revenue = total_revenue / COUNT(DISTINCT customer_id) OVER ()

  • Résultat :

    total_revenue_by_region avg_revenue
    80000 400

  • Opérateurs Pipe : Les noms des opérateurs correspondent à ceux de la syntaxe standard, mais ils peuvent être appliqués dans n'importe quel ordre.

  • Exemple :

    FROM mydataset.orders
|> WHERE status = 'completed'
|> AGGREGATE COUNT(*) AS order_count

  • Résultat :

    order_count
    200

  • Exemple 1 :

    FROM mydataset.orders
|> WHERE status = 'pending'
|> AGGREGATE COUNT(*) AS pending_order_count

  • Résultat :

    pending_order_count
    150

  • Exemple 2 :

    FROM mydataset.orders
|> WHERE status = 'completed'
|> AGGREGATE COUNT(*) AS completed_orders
|> EXTEND avg_order_value = AVG(order_value) OVER ()

  • Résultat :

    completed_orders avg_order_value
    200 75

  • Flexibilité : Les opérateurs peuvent être combinés sans nécessiter de sous-requêtes.

  • Exemple :

    FROM mydataset.employees
|> EXTEND year_of_birth = EXTRACT(YEAR FROM birth_date)
|> WHERE year_of_birth < 1980

  • Résultat :

    year_of_birth
    1975
    1968

  • Exemple 1 :

    FROM mydataset.employees
|> EXTEND year_of_birth = EXTRACT(YEAR FROM birth_date)
|> WHERE year_of_birth < 1980
|> AGGREGATE COUNT(*) AS count_old_employees GROUP BY department

  • Résultat :

    department count_old_employees
    HR 5
    Engineering 10

  • Exemple 2 :

    FROM mydataset.employees
|> EXTEND year_of_birth = EXTRACT(YEAR FROM birth_date)
|> WHERE year_of_birth < 1980
|> AGGREGATE COUNT(*) AS count_old_employees GROUP BY department

  • Résultat :

    department count_old_employees
    HR 5
    Engineering 10

  • Visibilité des alias : Chaque opérateur pipe a accès aux alias définis précédemment.

  • Exemple :

    FROM mydataset.products
|> AGGREGATE AVG(price) AS avg_price
|> WHERE avg_price > 100

  • Résultat :

    avg_price
    120

  • Exemple 1 :

    FROM mydataset.products
|> AGGREGATE AVG(price) AS avg_price
|> WHERE avg_price < 150
|> EXTEND price_range = MAX(price) - MIN(price) OVER ()

  • Résultat :

    avg_price price_range
    120 50

  • Exemple 2 :

    FROM mydataset.products
|> AGGREGATE AVG(price) AS avg_price
|> WHERE avg_price > 100
|> EXTEND price_difference = MAX(price) - MIN(price) OVER ()

  • Résultat :

    avg_price price_difference
    120 300

Différences clés avec la syntaxe standard :

  • Ordre des clauses : La syntaxe Pipe permet d'appliquer les opérateurs dans l'ordre logique.

  • Exemple :

    FROM mydataset.transactions
|> WHERE amount > 1000
|> AGGREGATE SUM(amount) AS total_large_transactions

  • Résultat :

    total_large_transactions
    50000

  • Exemple 1 :

    FROM mydataset.transactions
|> WHERE amount BETWEEN 500 AND 1000
|> AGGREGATE SUM(amount) AS medium_transactions

  • Résultat :

    medium_transactions
    25000

  • Exemple 2 :

    FROM mydataset.transactions
|> WHERE amount > 1000
|> AGGREGATE COUNT(*) AS count_large_transactions
|> EXTEND avg_transaction_value = SUM(amount) OVER () / count_large_transactions

  • Résultat :

    count_large_transactions avg_transaction_value
    50 1200

  • Agrégation : L'opérateur AGGREGATE est utilisé pour les opérations d'agrégation, suivi d'une clause GROUP BY.

  • Exemple :

    FROM mydataset.sales
|> AGGREGATE SUM(total) AS total_sales GROUP BY store_id

  • Résultat :

    store_id total_sales
    1 30000
    2 50000

  • Exemple 1 :

    FROM mydataset.sales
|> AGGREGATE AVG(total) AS avg_sales GROUP BY store_id

  • Résultat :

    store_id avg_sales
    1 15000
    2 25000

  • Exemple 2 :

    FROM mydataset.sales
|> AGGREGATE SUM(total) AS total_sales GROUP BY store_id
|> EXTEND avg_sales = AVG(total_sales) OVER ()
|> WHERE total_sales > avg_sales

  • Résultat :

    store_id total_sales avg_sales
    2 50000 40000

  • Filtrage : L'opérateur WHERE est utilisé pour le filtrage à tout moment.

  • Exemple :

    FROM mydataset.inventory
|> WHERE quantity < 10

  • Résultat :

    item_id quantity
    101 5
    102 2

  • Exemple 1 :

    FROM mydataset.inventory
|> WHERE quantity BETWEEN 1 AND 5
|> AGGREGATE COUNT(*) AS critical_stock_items

  • Résultat :

    critical_stock_items
    2

  • Exemple 2 :

    FROM mydataset.inventory
|> WHERE quantity < 10
|> AGGREGATE COUNT(*) AS low_stock_items GROUP BY item_type

  • Résultat :

    item_type low_stock_items
    Electronics 3
    Clothing 5

Opérateurs supplémentaires :

  • EXTEND : Ajoute des colonnes calculées.

  • Exemple :

    FROM mydataset.orders
|> EXTEND total_cost = price * quantity

  • Résultat :

    order_id total_cost
    1 150
    2 300

  • Exemple 1 :

    FROM mydataset.orders
|> EXTEND discount = CASE WHEN quantity > 10 THEN 0.1 ELSE 0 END
|> EXTEND total_cost = (price * quantity) * (1 - discount)

  • Résultat :

    order_id total_cost
    1 135
    2 300

  • Exemple 2 :

    FROM mydataset.orders
|> EXTEND total_cost = price * quantity
|> AGGREGATE SUM(total_cost) AS grand_total GROUP BY customer_id

  • Résultat :

    customer_id grand_total
    1 450
    2 600

  • SET : Remplace les valeurs des colonnes existantes.

  • Exemple :

    FROM mydataset.customers
|> SET status = 'inactive' WHERE last_order_date < '2020-01-01'

  • Résultat :

    customer_id status
    1 inactive
    2 active

  • Exemple 1 :

  • FROM mydataset.orders o
JOIN mydataset.customers c ON o.customer_id = c.customer_id
|> SET c.status = 'inactive'
WHERE o.order_date < '2020-01-01' AND c.status = 'active'

  • Résultat :

    customer_id status
    1 inactive
    3 inactive
    5 inactive

Explication de l'exemple :

  1. On part de la table orders (commandes) et on effectue une jointure avec la table customers (clients) sur la colonne customer_id.
  2. On modifie le statut des clients dont la dernière commande a été passée avant le 1er janvier 2020 et qui sont actuellement actifs.
  3. Le résultat montre les customer_id des clients qui ont été mis à jour vers un statut inactive.

  • Exemple 2 :

    FROM mydataset.customers
|> SET status = 'inactive' WHERE last_order_date < '2020-01-01'
|> AGGREGATE COUNT(*) AS inactive_customers

  • Résultat :

    inactive_customers
    30

  • DROP : Supprime des colonnes de la table actuelle.

  • Exemple :

    FROM mydataset.users
|> DROP email

  • Résultat :

    user_id name
    1 Alice
    2 Bob

  • Exemple 1 :

    FROM mydataset.users
|> DROP email

  • Résultat :

    user_id name
    1 Alice
    2 Bob

  • Exemple 2 :

    FROM mydataset.users
|> DROP email
|> EXTEND user_info = CONCAT(name, ' (', user_id, ')')

  • Résultat :

    user_id name user_info
    1 Alice Alice (1)
    2 Bob Bob (2)

  • RENAME :

Renomme des colonnes existantes.

  • Exemple :

    FROM mydataset.products
|> RENAME old_name AS new_name

  • Résultat :

    new_name
    Widget A
    Widget B

  • Exemple 1 :

    FROM mydataset.books
|> RENAME author TO writer

  • Résultat :

    book_id writer
    1 John Doe
    2 Jane Doe

Exemple 2 :

FROM mydataset.books
|> RENAME author TO writer
|> EXTEND book_summary = CONCAT(title, ' by ', writer)
  • Résultat :
    book_id writer book_summary
    1 John Doe Example Book by John Doe
    2 Jane Doe Another Book by Jane Doe

Exemple d'utilisation :

La syntaxe Pipe permet de réécrire des requêtes complexes de manière plus lisible.

  • Exemple :

    FROM mydataset.customer_orders
|> AGGREGATE SUM(cost) AS total_cost GROUP BY customer_id
|> EXTEND COUNT(*) OVER (PARTITION BY customer_id) AS num_orders
|> WHERE num_orders > 1
|> AGGREGATE AVG(total_cost) AS average GROUP BY state DESC

  • Résultat :

    state average
    CA 250.00
    NY 300.00

Limitations :

  • Confidentialité différentielle : Ne peut pas être incluse dans une instruction SELECT suivant un opérateur pipe.
  • Fenêtres nommées : Ne sont pas prises en charge dans la syntaxe Pipe.

Cas d'utilisation recommandés :

  • Analyse ad hoc : La syntaxe Pipe facilite l'écriture et le débogage des requêtes.

  • Exemple :

    FROM mydataset.log_data
|> WHERE log_level = 'ERROR'
|> AGGREGATE COUNT(*) AS error_count

  • Résultat :

    error_count
    50

  • Analyse des journaux : Offre une syntaxe familière pour l'analyse des logs.

  • Exemple :

    FROM mydataset.access_logs
|> WHERE response_time > 500
|> AGGREGATE COUNT(*) AS slow_requests

  • Résultat :

    slow_requests
    25

En résumé, la syntaxe Pipe de BigQuery offre une alternative plus flexible et lisible à la syntaxe SQL standard, améliorant la productivité et facilitant la maintenance des requêtes complexes.

Voici les points essentiels à retenir concernant les avantages de la syntaxe Pipe dans BigQuery :

  1. Lisibilité : Les requêtes sont plus claires et faciles à suivre grâce à leur structure linéaire.

  2. Ordre logique : Les opérations peuvent être appliquées dans un ordre qui a du sens, simplifiant la compréhension du flux de données.

  3. Flexibilité : Les utilisateurs peuvent combiner et appliquer des opérations sans nécessiter de sous-requêtes, ce qui simplifie la rédaction de requêtes complexes.

  4. Accès aux alias : Les alias définis dans une étape sont accessibles dans les étapes suivantes, évitant la redondance.

  5. Réduction de la complexité : Les requêtes sont moins complexes, facilitant le débogage et la maintenance.

  6. Efficacité des opérations : L'utilisation des opérateurs est intuitive et cohérente, rendant le travail avec les données plus efficace.

  7. Analyse ad hoc facilitée : Écriture et débogage de requêtes simplifiés pour des analyses rapides.

  8. Productivité accrue : Réduction du temps nécessaire pour créer et maintenir des requêtes, améliorant ainsi la productivité.

  9. Gestion des erreurs améliorée : Identification et correction des erreurs facilitées grâce à une structure claire.

En résumé, la syntaxe Pipe dans BigQuery rend l'écriture de requêtes plus intuitive, flexible et efficace, ce qui se traduit par une meilleure expérience pour les utilisateurs.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment