Utilizzare la sintassi delle query con pipe

La sintassi delle query con pipe è un'estensione di GoogleSQL che supporta una struttura di query lineare progettata per semplificare la lettura, la scrittura e la manutenzione delle query. Puoi utilizzare la sintassi pipe ovunque scrivi GoogleSQL.

La sintassi con pipe supporta le stesse operazioni della sintassi delle query GoogleSQL esistente o della sintassi standard, ad esempio selezione, aggregazione e raggruppamento, unione e filtro, ma le operazioni possono essere applicate in qualsiasi ordine e un numero qualsiasi di volte. La struttura lineare della sintassi pipe ti consente di scrivere query in modo che l'ordine della sintassi della query corrisponda all'ordine dei passaggi logici eseguiti per creare la tabella dei risultati.

Le query che utilizzano la sintassi con pipe vengono fatturate, eseguite e ottimizzate allo stesso modo delle query con sintassi standard equivalenti. Quando scrivi query con la sintassi pipe, segui le linee guida per stimare i costi e ottimizzare il calcolo delle query.

La sintassi standard presenta problemi che possono renderla difficile da leggere, scrivere e gestire. La seguente tabella mostra in che modo la sintassi pipe risolve questi problemi:

Sintassi standard Sintassi pipe
Le clausole devono essere visualizzate in un ordine specifico. Gli operatori pipe possono essere applicati in qualsiasi ordine.
Le query più complesse, come quelle con aggregazione multilivello, di solito richiedono CTE o sottoquery nidificate. Le query più complesse vengono solitamente espresse aggiungendo operatori pipe alla fine della query.
Durante l'aggregazione, le colonne vengono ripetute nelle clausole SELECT, GROUP BY e ORDER BY. Le colonne possono essere elencate una sola volta per aggregazione.

Per creare una query complessa passo passo nella sintassi pipe, consulta Analizza i dati utilizzando la sintassi pipe. Per i dettagli completi sulla sintassi, consulta la documentazione di riferimento sulla sintassi delle query con pipe.

Sintassi di base

Nella sintassi pipe, le query iniziano con una query SQL standard o una clausola FROM. Ad esempio, una clausola FROM autonoma, come FROM MyTable, è una sintassi pipe valida. Il risultato della query SQL standard o della tabella della clausola FROM può quindi essere passato come input a un simbolo pipe, |>, seguito dal nome di un operatore pipe e da eventuali argomenti per l'operatore. L'operatore pipe trasforma la tabella in qualche modo e il risultato di questa trasformazione può essere passato a un altro operatore pipe.

Puoi utilizzare un numero qualsiasi di operatori pipe nella query per eseguire operazioni come selezionare, ordinare, filtrare, unire o aggregare colonne. I nomi degli operatori pipe corrispondono alle loro controparti di sintassi standard e generalmente hanno lo stesso comportamento. La differenza principale tra la sintassi standard e la sintassi con pipe è il modo in cui strutturi la query. Man mano che la logica espressa dalla query diventa più complessa, la query può comunque essere espressa come una sequenza lineare di operatori pipe, senza utilizzare subquery con nidificazione profonda, il che la rende più facile da leggere e comprendere.

La sintassi Pipe ha le seguenti caratteristiche principali:

  • Ogni operatore pipe nella sintassi pipe è costituito dal simbolo pipe, |>, dal nome di un operatore e da eventuali argomenti:
    |> operator_name argument_list
  • Gli operatori pipe possono essere aggiunti alla fine di qualsiasi query valida.
  • Gli operatori pipe possono essere applicati in qualsiasi ordine e un numero qualsiasi di volte.
  • La sintassi con pipe funziona ovunque sia supportata la sintassi standard: nelle query, nelle viste, nelle funzioni con valori di tabella e in altri contesti.
  • La sintassi con pipe può essere combinata con la sintassi standard nella stessa query. Ad esempio, le sottoquery possono utilizzare una sintassi diversa da quella della query principale.
  • Un operatore pipe può visualizzare ogni alias esistente nella tabella che precede la pipe.
  • Una query può iniziare con una clausola FROM e gli operatori pipe possono essere aggiunti facoltativamente dopo la clausola FROM.

Considera la seguente tabella:

CREATE OR REPLACE TABLE mydataset.Produce AS (
  SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
  UNION ALL
  SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);

Le seguenti query contengono ciascuna una sintassi pipe valida che mostra come creare una query in sequenza.

Le query possono iniziare con una clausola FROM e non devono contenere un simbolo pipe:

-- View the table.
FROM mydataset.Produce;

/*---------+-------+-----------+
 | item    | sales | category  |
 +---------+-------+-----------+
 | apples  | 7     | fruit     |
 | apples  | 2     | fruit     |
 | carrots | 0     | vegetable |
 | bananas | 15    | fruit     |
 +---------+-------+-----------*/

Puoi filtrare con un operatore pipe WHERE:

-- Filter items with no sales.
FROM mydataset.Produce
|> WHERE sales > 0;

/*---------+-------+-----------+
 | item    | sales | category  |
 +---------+-------+-----------+
 | apples  | 7     | fruit     |
 | apples  | 2     | fruit     |
 | bananas | 15    | fruit     |
 +---------+-------+-----------*/

Per eseguire l'aggregazione, utilizza l'operatore pipe AGGREGATE, seguito da un numero qualsiasi di funzioni aggregate, seguite da una clausola GROUP BY. La clausola GROUP BY fa parte dell'operatore pipe AGGREGATE e non è separata da un simbolo pipe (|>).

-- Compute total sales by item.
FROM mydataset.Produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY item;

/*---------+-------------+-----------+
 | item    | total_sales | num_sales |
 +---------+-------------+-----------+
 | apples  | 9           | 2         |
 | bananas | 15          | 1         |
 +---------+-------------+-----------*/

Ora supponiamo di avere la seguente tabella che contiene un ID per ogni articolo:

CREATE OR REPLACE TABLE mydataset.ItemData AS (
  SELECT 'apples' AS item, '123' AS id
  UNION ALL
  SELECT 'bananas' AS item, '456' AS id
  UNION ALL
  SELECT 'carrots' AS item, '789' AS id
);

Puoi utilizzare l'operatore pipe JOIN per unire i risultati della query precedente a questa tabella per includere l'ID di ogni articolo:

FROM mydataset.Produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY item
|> JOIN mydataset.ItemData USING(item);

/*---------+-------------+-----------+-----+
 | item    | total_sales | num_sales | id  |
 +---------+-------------+-----------+-----+
 | apples  | 9           | 2         | 123 |
 | bananas | 15          | 1         | 456 |
 +---------+-------------+-----------+-----*/

Differenze principali rispetto alla sintassi standard

La sintassi pipe differisce dalla sintassi standard per i seguenti aspetti:

  • Le query possono iniziare con una clausola FROM.
  • L'operatore pipe SELECT non esegue l'aggregazione. Devi invece utilizzare l'operatore pipe AGGREGATE.
  • Il filtro viene sempre eseguito con l'operatore pipe WHERE, che può essere applicato ovunque. L'operatore pipe WHERE, che sostituisce HAVING e QUALIFY, può filtrare i risultati delle funzioni di aggregazione o finestra.

Per maggiori dettagli, consulta l'elenco completo degli operatori pipe.

Casi d'uso

I casi d'uso comuni per la sintassi pipe includono:

  • Analisi ad hoc e creazione di query incrementali: l'ordine logico delle operazioni semplifica la scrittura e il debug delle query. Il prefisso di qualsiasi query fino a un simbolo di pipe |> è una query valida, che ti aiuta a visualizzare i risultati intermedi in una query lunga. Gli incrementi di produttività possono accelerare il processo di sviluppo in tutta l'organizzazione.
  • Analisi dei log: esistono altri tipi di sintassi simili alle pipe che sono popolari tra gli utenti di analisi dei log. La sintassi con operatore pipe fornisce una struttura familiare che semplifica l'onboarding di questi utenti in Observability Analytics e BigQuery.

Funzionalità aggiuntive nella sintassi pipe

Con poche eccezioni, la sintassi pipe supporta tutti gli operatori supportati dalla sintassi standard con la stessa sintassi. Inoltre, la sintassi pipe introduce operatori pipe aggiuntivi e utilizza una sintassi modificata per aggregazioni e join. Le sezioni seguenti spiegano alcuni di questi operatori. Per tutti gli operatori supportati, consulta l'elenco completo degli operatori pipe.

Operatore pipe EXTEND

L'operatore pipe EXTEND consente di aggiungere colonne calcolate alla tabella corrente. L'operatore pipe EXTEND è simile all'istruzione SELECT *, new_column, ma offre una maggiore flessibilità nel riferimento agli alias delle colonne.

Considera la seguente tabella che contiene due punteggi di test per ogni persona:

CREATE OR REPLACE TABLE mydataset.Scores AS (
  SELECT 'Alex' AS student, 9 AS score1, 10 AS score2, 10 AS points_possible
  UNION ALL
  SELECT 'Dana' AS student, 5 AS score1, 7 AS score2, 10 AS points_possible);

/*---------+--------+--------+-----------------+
 | student | score1 | score2 | points_possible |
 +---------+--------+--------+-----------------+
 | Alex    | 9      | 10     | 10              |
 | Dana    | 5      | 7      | 10              |
 +---------+--------+--------+-----------------*/

Supponiamo che tu voglia calcolare il punteggio grezzo medio e il punteggio percentuale medio che ogni studente ha ricevuto nel test. Nella sintassi standard, le colonne successive di un'istruzione SELECT non hanno visibilità sugli alias precedenti. Per evitare una sottoquery, devi ripetere l'espressione per la media:

SELECT student,
  (score1 + score2) / 2 AS average_score,
  (score1 + score2) / 2 / points_possible AS average_percent
FROM mydataset.Scores;

L'operatore pipe EXTEND può fare riferimento ad alias utilizzati in precedenza, rendendo la query più facile da leggere e meno soggetta a errori:

FROM mydataset.Scores
|> EXTEND (score1 + score2) / 2 AS average_score
|> EXTEND average_score / points_possible AS average_percent
|> SELECT student, average_score, average_percent;

/*---------+---------------+-----------------+
 | student | average_score | average_percent |
 +---------+---------------+-----------------+
 | Alex    | 9.5           | .95             |
 | Dana    | 6.0           | 0.6             |
 +---------+---------------+-----------------*/

Operatore pipe SET

L'operatore pipe SET consente di sostituire il valore delle colonne nella tabella corrente. L'operatore pipe SET è simile all'istruzione SELECT * REPLACE (expression AS column). Puoi fare riferimento al valore originale qualificando il nome della colonna con un alias della tabella.

FROM (SELECT 3 AS x, 5 AS y)
|> SET x = 2 * x;

/*---+---+
 | x | y |
 +---+---+
 | 6 | 5 |
 +---+---*/

Operatore pipe DROP

L'operatore pipe DROP consente di rimuovere le colonne dalla tabella corrente. L'operatore pipe DROP è simile all'istruzione SELECT * EXCEPT(column). Dopo aver eliminato una colonna, puoi comunque fare riferimento al valore originale qualificando il nome della colonna con un alias della tabella.

FROM (SELECT 1 AS x, 2 AS y) AS t
|> DROP x;

/*---+
 | y |
 +---+
 | 2 |
 +---*/

Operatore pipe RENAME

L'operatore pipe RENAME consente di rinominare le colonne della tabella corrente. L'operatore pipe RENAME è simile all'istruzione SELECT * EXCEPT(old_column), old_column AS new_column.

FROM (SELECT 1 AS x, 2 AS y, 3 AS z) AS t
|> RENAME y AS w;

/*---+---+---+
 | x | w | z |
 +---+---+---+
 | 1 | 2 | 3 |
 +---+---+---*/

Operatore pipe AGGREGATE

Per eseguire l'aggregazione nella sintassi pipe, utilizza l'operatore pipe AGGREGATE, seguito da un numero qualsiasi di funzioni aggregate, seguito da una clausola GROUP BY. Non è necessario ripetere le colonne in una clausola SELECT.

Gli esempi in questa sezione utilizzano la tabella Produce:

CREATE OR REPLACE TABLE mydataset.Produce AS (
  SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
  UNION ALL
  SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);

/*---------+-------+-----------+
 | item    | sales | category  |
 +---------+-------+-----------+
 | apples  | 7     | fruit     |
 | apples  | 2     | fruit     |
 | carrots | 0     | vegetable |
 | bananas | 15    | fruit     |
 +---------+-------+-----------*/

FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP BY item, category;

/*---------+-----------+-------+-------------+
 | item    | category  | total | num_records |
 +---------+-----------+-------+-------------+
 | apples  | fruit     | 9     | 2           |
 | carrots | vegetable | 0     | 1           |
 | bananas | fruit     | 15    | 1           |
 +---------+-----------+-------+-------------*/

Se vuoi ordinare i risultati immediatamente dopo l'aggregazione, puoi contrassegnare le colonne nella clausola GROUP BY che vuoi ordinare con ASC o DESC. Le colonne non contrassegnate non sono ordinate.

Se vuoi ordinare tutte le colonne, puoi sostituire la clausola GROUP BY con una clausola GROUP AND ORDER BY, che ordina ogni colonna in ordine crescente per impostazione predefinita. Puoi specificare DESC dopo le colonne che vuoi ordinare in ordine decrescente. Ad esempio, le seguenti tre query sono equivalenti:

-- Use a separate ORDER BY clause.
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP BY category, item
|> ORDER BY category DESC, item;

-- Explicitly mark how to order columns in the GROUP BY clause.
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP BY category DESC, item ASC;

-- Only mark descending columns in the GROUP AND ORDER BY clause.
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP AND ORDER BY category DESC, item;

Il vantaggio di utilizzare una clausola GROUP AND ORDER BY è che non devi ripetere i nomi delle colonne in due posizioni.

Per eseguire l'aggregazione dell'intera tabella, utilizza GROUP BY() o ometti completamente la clausola GROUP BY:

FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records;

/*-------+-------------+
 | total | num_records |
 +-------+-------------+
 | 24    | 4           |
 +-------+-------------*/

Operatore pipe JOIN

L'operatore pipe JOIN consente di unire la tabella corrente a un'altra tabella e supporta le operazioni di join standard, tra cui CROSS, INNER, LEFT, RIGHT e FULL.

Gli esempi seguenti fanno riferimento alle tabelle Produce e ItemData:

CREATE OR REPLACE TABLE mydataset.Produce AS (
  SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
  UNION ALL
  SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);

CREATE OR REPLACE TABLE mydataset.ItemData AS (
  SELECT 'apples' AS item, '123' AS id
  UNION ALL
  SELECT 'bananas' AS item, '456' AS id
  UNION ALL
  SELECT 'carrots' AS item, '789' AS id
);

L'esempio seguente utilizza una clausola USING ed evita l'ambiguità delle colonne:

FROM mydataset.Produce
|> JOIN mydataset.ItemData USING(item)
|> WHERE item = 'apples';

/*--------+-------+----------+-----+
 | item   | sales | category | id  |
 +--------+-------+----------+-----+
 | apples | 2     | fruit    | 123 |
 | apples | 7     | fruit    | 123 |
 +--------+-------+----------+-----*/

Per fare riferimento alle colonne nella tabella corrente, ad esempio per eliminare l'ambiguità delle colonne in una clausola ON, devi assegnare un alias alla tabella corrente utilizzando l'operatore pipe AS. Se vuoi, puoi assegnare un alias alla tabella unita. Puoi fare riferimento a entrambi gli alias dopo gli operatori pipe successivi:

FROM mydataset.Produce
|> AS produce_table
|> JOIN mydataset.ItemData AS item_table
   ON produce_table.item = item_table.item
|> WHERE produce_table.item = 'bananas'
|> SELECT item_table.item, sales, id;

/*---------+-------+-----+
 | item    | sales | id  |
 +---------+-------+-----+
 | bananas | 15    | 123 |
 +---------+-------+-----*/

Il lato destro del join non ha visibilità sul lato sinistro del join, il che significa che non puoi unire la tabella corrente con se stessa. Ad esempio, la seguente query non va a buon fine:

-- This query doesn't work.
FROM mydataset.Produce
|> AS produce_table
|> JOIN produce_table AS produce_table_2 USING(item);

Per eseguire un self-join con una tabella modificata, puoi utilizzare un'espressione di tabella comune (CTE) all'interno di una clausola WITH.

WITH cte_table AS (
  FROM mydataset.Produce
  |> WHERE item = 'carrots'
)
FROM cte_table
|> JOIN cte_table AS cte_table_2 USING(item);

Esempio

Considera la seguente tabella con le informazioni sugli ordini dei clienti:

CREATE OR REPLACE TABLE mydataset.CustomerOrders AS (
  SELECT 1 AS customer_id, 100 AS order_id, 'WA' AS state, 5 AS cost, 'clothing' AS item_type
  UNION ALL
  SELECT 1 AS customer_id, 101 AS order_id, 'WA' AS state, 20 AS cost, 'clothing' AS item_type
  UNION ALL
  SELECT 1 AS customer_id, 102 AS order_id, 'WA' AS state, 3 AS cost, 'food' AS item_type
  UNION ALL
  SELECT 2 AS customer_id, 103 AS order_id, 'NY' AS state, 16 AS cost, 'clothing' AS item_type
  UNION ALL
  SELECT 2 AS customer_id, 104 AS order_id, 'NY' AS state, 22 AS cost, 'housewares' AS item_type
  UNION ALL
  SELECT 2 AS customer_id, 104 AS order_id, 'WA' AS state, 45 AS cost, 'clothing' AS item_type
  UNION ALL
  SELECT 3 AS customer_id, 105 AS order_id, 'MI' AS state, 29 AS cost, 'clothing' AS item_type);

Supponiamo che tu voglia conoscere, per ogni stato e tipo di articolo, l'importo medio speso dai clienti abituali. Puoi scrivere la query nel seguente modo:

SELECT state, item_type, AVG(total_cost) AS average
FROM
  (
    SELECT
      SUM(cost) AS total_cost,
      customer_id,
      state,
      item_type,
      COUNT(*) OVER (PARTITION BY customer_id) AS num_orders
    FROM mydataset.CustomerOrders
    GROUP BY customer_id, state, item_type
    QUALIFY num_orders > 1
  )
GROUP BY state, item_type
ORDER BY state DESC, item_type ASC;

Se leggi la query dall'alto verso il basso, trovi la colonna total_cost prima che sia stata definita. Anche all'interno della sottoquery, i nomi delle colonne vengono letti prima di vedere da quale tabella provengono.

Per comprendere questa query, è necessario leggerla dall'interno verso l'esterno. Le colonne state e item_type vengono ripetute più volte nelle clausole SELECT e GROUP BY, quindi di nuovo nella clausola ORDER BY.

La seguente query equivalente è scritta utilizzando la sintassi pipe:

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

/*-------+------------+---------+
 | state | item_type  | average |
 +-------+------------+---------+
 | WA    | clothing   | 35.0    |
 | WA    | food       | 3.0     |
 | NY    | clothing   | 16.0    |
 | NY    | housewares | 22.0    |
 +-------+------------+---------*/

Con la sintassi pipe, puoi scrivere la query in modo che segua i passaggi logici che potresti seguire per risolvere il problema originale. Le righe di sintassi nella query corrispondono ai seguenti passaggi logici:

  • Inizia con la tabella degli ordini dei clienti.
  • Scopri quanto ha speso ogni cliente per ogni tipo di articolo in base allo stato.
  • Conta il numero di ordini per ogni cliente.
  • Limitare i risultati ai clienti abituali.
  • Trova l'importo medio speso dai clienti abituali per ogni stato e tipo di articolo.

Limitazioni

  • Non puoi includere una clausola di privacy differenziale in un'istruzione SELECT che segue un operatore pipe. Utilizza invece una clausola di privacy differenziale nella sintassi standard e applica gli operatori pipe dopo la query.

Passaggi successivi