Utilizzare la sintassi delle query con pipe

Per registrare un progetto nell'anteprima della sintassi delle pipe, compila il modulo di registrazione per la sintassi delle 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 gestione delle query. Puoi utilizzare la sintassi della barra ovunque scrivi GoogleSQL.

La sintassi delle pipe supporta le stesse operazioni della sintassi delle query GoogleSQL esistente o della sintassi standard, ad esempio selezione, aggregazione e raggruppamento, unione e filtri, ma le operazioni possono essere applicate in qualsiasi ordine e un numero qualsiasi di volte. La struttura lineare della sintassi della barra 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 calcolate, eseguite e ottimizzate nello stesso modo delle query con sintassi standard equivalenti. Quando scrivi query con la sintassi della canalizzazione, segui le linee guida per stimare i costi e ottimizzare il calcolo delle query.

La sintassi standard presenta problemi che possono complicare la lettura, la scrittura e la manutenzione. La seguente tabella mostra come la sintassi della barra consente di risolvere questi problemi:

Sintassi standard Sintassi delle pipe
Le clausole devono essere visualizzate in un ordine specifico. Gli operatori di pipe possono essere applicati in qualsiasi ordine.
Le query più complesse, ad esempio quelle con aggregazione a più livelli, solitamente richiedono CTE o sottoquery nidificate. Le query più complesse vengono solitamente espresse aggiungendo operatori di 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 informazioni dettagliate sulla sintassi, consulta la documentazione di riferimento sulla sintassi delle query con pipe.

Sintassi di base

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

Puoi utilizzare un numero qualsiasi di operatori di pipe nella query per eseguire operazioni come selezionare, ordinare, filtrare, unire o aggregare colonne. I nomi degli operatori di pipe corrispondono alle loro controparti di sintassi standard e in genere hanno lo stesso comportamento. La differenza principale tra la sintassi standard e la sintassi con barra è il modo in cui strutturare 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 di pipe, senza utilizzare sottoquery nidificate in modo complesso, il che la rende più facile da leggere e comprendere.

La sintassi della pipe ha le seguenti caratteristiche principali:

  • Ogni operatore pipe nella sintassi della pipeline è costituito dal simbolo della pipeline, |>, da un nome operatore e da eventuali argomenti:
    |> operator_name argument_list
  • Gli operatori di pipe possono essere aggiunti alla fine di qualsiasi query valida.
  • Gli operatori di 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 tabella e altri contesti.
  • La sintassi delle barre può essere combinata con la sintassi standard nella stessa query. Ad esempio, le sottoquery possono utilizzare una sintassi diversa dalla query principale.
  • Un operatore di pipe può vedere tutti gli alias esistenti nella tabella che precede la pipe.
  • Una query può iniziare con una clausola FROM e gli operatori di pipe possono essere facoltativamente aggiunti dopo la clausola FROM.

Considera la seguente tabella:

CREATE OR REPLACE TABLE 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 di pipe valida che mostra come puoi costruire una query in sequenza.

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

-- View the table.
FROM 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 Produce
|> WHERE sales > 0;

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

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

-- Compute total sales by item.
FROM 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 contenente un ID per ogni articolo:

CREATE OR REPLACE TABLE 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 di canalizzazione JOIN per unire i risultati della query precedente a questa tabella in modo da includere l'ID di ogni elemento:

FROM Produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY item
|> JOIN 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 è diversa da quella standard nei seguenti modi:

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

Casi d'uso

Ecco alcuni casi d'uso comuni per la sintassi della pipe:

  • Analisi ad hoc e creazione di query incrementali: l'ordine logico delle operazioni simplifica la scrittura e il debug delle query. Il prefisso di qualsiasi query fino a un simbolo di tubo |> è una query valida, che ti consente di 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 alle barre verticali che sono molto apprezzate dagli utenti di analisi dei log. La sintassi delle pipe fornisce una struttura familiare che semplifica l'onboarding per gli utenti che devono eseguire e .

Funzionalità aggiuntive nella sintassi pipe

Con poche eccezioni, la sintassi delle pipe supporta tutti gli operatori supportati dalla sintassi standard con la stessa sintassi. Inoltre, la sintassi pipe introduce i seguenti operatori pipe.

EXTEND operatore pipe

L'operatore pipe EXTEND ti consente di accodare le colonne calcolate alla tabella corrente. L'operatore di canalizzazione EXTEND è simile all'istruzione SELECT *, new_column, ma offre maggiore flessibilità per fare riferimento agli alias di colonna.

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

CREATE OR REPLACE TABLE 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 medio non elaborato e il punteggio medio in percentuale che ogni studente ha ricevuto al test. Nella sintassi standard, le colonne successive di un'istruzione SELECT non hanno visibilità agli alias precedenti. Per evitare una subquery, devi ripetere l'espressione per la media:

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

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

FROM 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             |
 +---------+---------------+-----------------*/

SET operatore pipe

L'operatore di canalizzazione 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 specificando il nome della colonna con un alias tabella.

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

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

DROP operatore pipe

L'operatore di canalizzazione 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 specificando il nome della colonna con un alias tabella.

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

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

RENAME operatore pipe

L'operatore di canalizzazione 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 |
 +---+---+---*/

AGGREGATE operatore pipe

Per eseguire l'aggregazione nella sintassi della pipeline, utilizza l'operatore di pipeline AGGREGATE, seguito da un numero qualsiasi di funzioni di aggregazione, 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 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 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 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 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 Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP AND ORDER BY category DESC, item;

Il vantaggio dell'utilizzo di una clausola GROUP AND ORDER BY è che non devi ripetere i nomi delle colonne in due punti.

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

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

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

JOIN operatore pipe

L'operatore di canalizzazione JOIN ti consente di unire la tabella corrente con 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 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 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 Produce
|> JOIN `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 distinguere le colonne in una clausola ON, devi creare un alias per la tabella corrente utilizzando l'operatore di canalizzazione AS. Se vuoi, puoi assegnare un alias alla tabella unita. Puoi fare riferimento a entrambi gli alias dopo gli operatori di pipe successivi:

FROM Produce
|> AS produce_table
|> JOIN `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 della congiunzione non ha visibilità sul lato sinistro della congiunzione, 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 Produce
|> AS produce_table
|> JOIN produce_table AS produce_table_2 USING(item);

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

WITH cte_table AS (
  FROM 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 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. 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 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 comprendere questa query, deve essere letta 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 barra verticale:

FROM 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 barra, puoi scrivere la query in modo che segua i passaggi logici che potresti pensare di svolgere 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 che segue un operatore di canalizzazione. Utilizza invece una clausola di privacy differenziale nella sintassi standard e applica gli operatori di pipe dopo la query.
  • Non puoi utilizzare una finestra denominata nella sintassi della barra verticale.