Utilizzare la sintassi delle query con pipe

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

La sintassi con operatore pipe supporta le stesse operazioni della sintassi delle query GoogleSQL esistente, o 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 con operatore 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 operatore pipe vengono prezzate, eseguite e ottimizzate allo stesso modo delle query con sintassi standard equivalenti. Quando scrivi query con la sintassi con operatore 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 con operatore pipe risolve questi problemi:

Sintassi standard Sintassi con operatore pipe
Le clausole devono essere visualizzate in un ordine specifico. Gli operatori pipe possono essere applicati in qualsiasi ordine.
Le query più complesse, come le query con aggregazione multilivello, in genere richiedono CTE o sottoquery nidificate. Le query più complesse vengono in genere 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 con operatore pipe, vedi Analizzare i dati utilizzando la sintassi con operatore pipe. Per tutti i dettagli sulla sintassi, consulta la documentazione di riferimento sulla sintassi delle query con operatore pipe.

Sintassi di base

Nella sintassi con operatore pipe, le query iniziano con una query SQL standard o una clausola FROM. Ad esempio, una clausola FROM autonoma, come FROM MyTable, è una sintassi con operatore pipe valida. Il risultato della query SQL standard o della tabella della FROM clausola può essere passato come input a un simbolo pipe, |>, seguito dal nome di un operatore pipe e da eventuali argomenti per quell'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 controparti della sintassi standard e in genere hanno lo stesso comportamento. La differenza principale tra la sintassi standard e la sintassi con operatore 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 con operatore pipe ha le seguenti caratteristiche principali:

  • Ogni operatore pipe nella sintassi con operatore pipe è costituito dal simbolo pipe, |>, un nome di 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 operatore pipe funziona ovunque sia supportata la sintassi standard: in query, viste, funzioni con valori di tabella e altri contesti.
  • La sintassi con operatore pipe può essere combinata con la sintassi standard nella stessa query. Ad esempio, le sottoquery possono utilizzare una sintassi diversa dalla query principale.
  • Un operatore pipe può visualizzare ogni alias esistente nella tabella che precede il pipe.
  • Una query può iniziare con una clausola FROM e, facoltativamente, gli operatori pipe possono essere aggiunti 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 con operatore pipe valida che mostra come creare una query in sequenza.

Le query possono iniziare con una FROM clausola 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 WHERE operatore pipe:

-- 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'AGGREGATE pipe operatore, 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         |
 +---------+-------------+-----------*/

Supponiamo ora 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 `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 con operatore pipe differisce dalla sintassi standard nei seguenti modi:

  • Le query possono iniziare con una FROM clausola.
  • L'operatore pipe SELECT non esegue l'aggregazione. Devi utilizzare invece l'operatore pipe AGGREGATE.
  • Il filtro viene sempre eseguito con l'WHERE operatore pipe, 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 con operatore 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 pipe |> è una query valida, che ti aiuta a visualizzare i risultati intermedi in una query lunga. I miglioramenti della produttività possono accelerare il processo di sviluppo in tutta l'organizzazione.
  • Analisi dei log: esistono altri tipi di sintassi simili a 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 a Observability Analytics e BigQuery.

Funzionalità aggiuntive nella sintassi con operatore pipe

Con poche eccezioni, la sintassi con operatore pipe supporta tutti gli operatori della sintassi standard con la stessa sintassi. Inoltre, la sintassi con operatore pipe introduce operatori pipe aggiuntivi e utilizza una sintassi modificata per aggregazioni e unioni. 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 EXTENDconsente 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 dei 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 di voler calcolare il punteggio grezzo medio e il punteggio percentuale medio che ogni studente ha ricevuto nel test. Nella sintassi standard, le colonne successive in 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 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 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 di 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 di 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 da 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 con operatore pipe, utilizza l'AGGREGATE operatore pipe, 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 sei pronto a 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 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 completa della tabella, utilizza GROUP BY() o ometti completamente la GROUP BY clausola:

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 pipe ASoperator. Facoltativamente, puoi assegnare un alias alla tabella unita. Puoi fare riferimento a entrambi gli alias seguendo 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 dell'unione non ha visibilità sul lato sinistro dell'unione, il che significa che non puoi unire la tabella corrente a 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'auto-unione 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 di voler conoscere, per ogni stato e tipo di articolo, l'importo medio speso dai clienti abituali. Potresti 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, deve essere letta dall'interno verso l'esterno. Le colonne state e item_type vengono ripetute più volte nelle clausole SELECT e GROUP BY, poi di nuovo nella clausola ORDER BY.

La seguente query equivalente è scritta utilizzando la sintassi con operatore 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 con operatore pipe, puoi scrivere la query in modo da seguire i passaggi logici che potresti pensare di 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 per stato.
  • Conta il numero di ordini per ogni cliente.
  • Limita 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'SELECT istruzione dopo un operatore pipe. Utilizza invece una clausola di privacy differenziale nella sintassi standard e applica gli operatori pipe dopo la query.

Passaggi successivi