Utilizzare la sintassi delle query con pipe

La sintassi delle query 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 della pipe ovunque scrivi GoogleSQL.

La sintassi pipe supporta le stesse operazioni della sintassi di query GoogleSQL esistente o della sintassi standard, ad esempio selezione, aggregazione e raggruppamento, unione e filtraggio, 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 pipe vengono prezzate, 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 rendere difficile la lettura, la scrittura e la manutenzione. La seguente tabella mostra come la sintassi della barra verticale risolve questi problemi:

Sintassi standard Sintassi pipe
Le clausole devono essere visualizzate in un ordine particolare. Gli operatori pipe possono essere applicati in qualsiasi ordine.
Le query più complesse, come quelle con aggregazione multilivello, di solito richiedono CTE o subquery nidificate. Le query più complesse vengono 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 dopo passo nella sintassi pipe, vedi Analizzare i dati utilizzando la sintassi pipe. Per tutti i dettagli 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 di pipe, |>, seguito da un nome di operatore di 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 sottoquery nidificate in profondità, il che la rende più facile da leggere e comprendere.

La sintassi della barra verticale ha le seguenti caratteristiche principali:

  • Ogni operatore pipe nella sintassi pipe è costituito dal simbolo pipe, |>, da un nome 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 pipe funziona ovunque sia supportata la sintassi standard: in query, viste, funzioni con valori di tabella e altri contesti.
  • La sintassi della barra verticale 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 puoi creare una query in sequenza.

Le query possono iniziare con una clausola FROM e non devono contenere un simbolo di 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, seguito 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 supponi di avere la seguente tabella che contiene un ID per ogni elemento:

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 elemento:

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 da quella standard per i seguenti motivi:

  • Le query possono iniziare con una FROMclausola.
  • 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 della barra verticale 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. L'aumento della produttività può accelerare il processo di sviluppo in tutta l'organizzazione.
  • Analisi dei log: esistono altri tipi di sintassi simili a pipe molto diffusi tra gli utenti dell'analisi dei log. La sintassi con operatore pipe fornisce una struttura familiare che semplifica l'onboarding di questi utenti in Analisi dei log e BigQuery.

Funzionalità aggiuntive nella sintassi pipe

Con poche eccezioni, la sintassi pipe supporta tutti gli operatori della sintassi standard con la stessa sintassi. Inoltre, la sintassi della pipe introduce ulteriori operatori pipe e utilizza una sintassi modificata per aggregazioni e join. Le sezioni seguenti spiegano 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 maggiore flessibilità nel fare riferimento agli alias delle colonne.

Considera la seguente tabella che contiene due punteggi del 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 la percentuale media che ogni studente ha ottenuto nel test. Nella sintassi standard, le colonne successive di un'istruzione SELECT non hanno visibilità sugli alias precedenti. Per evitare una query secondaria, 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 agli 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 ti 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 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 ti 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 subito dopo l'aggregazione, puoi contrassegnare le colonne nella clausola GROUP BY che vuoi ordinare con ASC o DESC. Le colonne non contrassegnate non vengono 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 unione standard, tra cui CROSS, INNER, LEFT, RIGHT e FULL.

I seguenti esempi 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 della tabella corrente, ad esempio per disambiguare le colonne in una clausola ON, devi assegnare un alias alla tabella corrente utilizzando l'operatore AS pipe. Facoltativamente, 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, il che significa che non puoi unire la tabella corrente con se stessa. Ad esempio, la seguente query non riesce:

-- 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 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 sapere, 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, incontri la colonna total_cost prima che sia stata definita. Anche all'interno della sottoquery, leggi i nomi delle colonne prima di vedere da quale tabella provengono.

Per dare un senso a questa query, è necessario leggerla dall'interno verso l'esterno. Le colonne state e item_type vengono ripetute numerose volte nelle clausole SELECT e GROUP BY, poi di nuovo nella clausola ORDER BY.

La seguente query equivalente è scritta utilizzando la sintassi della 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 della 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 ciascun cliente.
  • Limita i risultati ai clienti abituali.
  • Trova l'importo medio speso dai clienti di ritorno per ogni stato e tipo di articolo.

Limitazioni

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

Passaggi successivi