Sintassi delle pipeline

Anteprima

Questa funzionalità è soggetta ai "Termini delle Offerte pre-GA" nella sezione Termini generali del servizio dei Termini specifici dei servizi. Le funzionalità pre-GA sono disponibili "così come sono" e potrebbero avere un supporto limitato. Per saperne di più, consulta le descrizioni della fase di lancio.

Nota: per richiedere feedback o assistenza per questa funzionalità, invia un'email a [e-mail].

La sintassi delle pipeline è un'estensione di GoogleSQL che supporta una query lineare strutturata per semplificare la lettura, la scrittura e la gestione delle query.

Per registrare un progetto nell'anteprima della sintassi barra verticale, compila il Modulo di registrazione per la sintassi della barra verticale BigQuery.

Panoramica

Puoi utilizzare la sintassi della barra ovunque scrivi GoogleSQL. La sintassi delle pipeline supporta le stesse operazioni della sintassi GoogleSQL esistente, o la sintassi standard, ad esempio selezione, aggregazione e raggruppamento, unione ma le operazioni possono essere applicate in qualsiasi ordine e in qualsiasi numero volte. La struttura lineare della sintassi delle barre 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 barre vengono calcolate, eseguite e ottimizzate nello stesso modo delle query con sintassi standard equivalenti. Quando scrivi query con la barra verticale seguire le linee guida per stima i costi e ottimizzare il calcolo delle query.

La sintassi standard presenta problemi che possono complicare la lettura, la scrittura e la manutenzione. La tabella seguente mostra come vengono usati risolvere questi problemi:

Sintassi standard	Sintassi delle pipeline
Le clausole devono essere visualizzate in un ordine specifico.	Gli operatori tubo 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 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.

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 mydataset.mytable, è una sintassi di pipe valida. Il risultato della query SQL standard o della tabella della clausola FROM può essere poi passato come input a un simbolo di canalizzazione, |>, seguito dal nome di un operatore di canalizzazione e da eventuali argomenti per quell'operatore. L'operatore di pipe trasforma la tabella in qualche modo e il risultato della trasformazione può essere passato a un altro operatore di pipe.

Puoi utilizzare un numero qualsiasi di operatori pipe nella query per eseguire operazioni quali 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ò essere ancora espressa come una sequenza lineare di operatori di pipe, senza utilizzare sottoquery nidificate in modo approfondito, il che semplifica la lettura e la comprensione.

Considera la seguente tabella:

CREATE 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 per creare una query in sequenza.

Le query possono iniziare con una clausola FROM e non devono necessariamente contenere il simbolo di una barra verticale:

-- 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 Operatore pipe AGGREGATE, seguito da un numero qualsiasi di di aggregazione, seguite da una clausola GROUP BY. La clausola GROUP BY fa parte dell'operatore pipe AGGREGATE e non è separata da una barra (|>).

-- 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 TABLE mydataset.item_data 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 lo Operatore pipe JOIN per unire i risultati della query precedente a questa tabella in modo da includere ogni ID elemento:

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

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

La sintassi della pipe ha le seguenti caratteristiche principali:

• Gli operatori di pipe possono essere applicati in qualsiasi ordine e un numero qualsiasi di volte.
• La sintassi delle pipeline funziona ovunque sia supportata la sintassi standard: query, viste, funzioni con valori di tabella e altri contesti.
• La sintassi delle pipeline 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 tutti gli alias presenti nella tabella che precede la barra verticale.

Differenze principali rispetto alla sintassi standard

La sintassi delle pipeline differisce dalla sintassi standard per i seguenti motivi:

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

Per ulteriori informazioni e per un elenco completo degli operatori pipe, consulta la sintassi delle query pipeline.

Casi d'uso

Di seguito sono riportati alcuni casi d'uso comuni per la sintassi 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 canalizzazione |> è una query valida, che ti consente di visualizzare i risultati intermedi in una query lunga. L'aumento della produttività può 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 offre una struttura familiare che semplifica l'onboarding per gli utenti di Log Analytics e BigQuery.

Funzionalità aggiuntive nella sintassi barra verticale

Con poche eccezioni, la sintassi pipe supporta tutti gli operatori della sintassi standard ha la stessa sintassi. Inoltre, la sintassi barra verticale introduce quanto segue pipe.

Operatore pipe EXTEND

L'operatore di canalizzazione EXTEND, che può essere utilizzato solo immediatamente dopo un simbolo di canalizzazione, consente di accodare le colonne calcolate alla tabella corrente. L'operatore pipe EXTEND è simile all'istruzione SELECT *, new_column, ma ti offre una maggiore flessibilità fare riferimento agli alias di colonna.

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

CREATE 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 non elaborato medio e il punteggio percentuale medio che ogni studente ha ricevuto al test. Nella sintassi standard, le colonne successive in un'istruzione SELECT non ha visibilità sugli 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 mydataset.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 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

La Operatore pipe SET, che può essere utilizzato solo subito dopo il simbolo di una barra verticale, consente di sostituire il valore delle colonne nella tabella corrente. L'operatore pipe SET è simile all'operatore Dichiarazione SELECT * REPLACE (expression AS column). Puoi fare riferimento il 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

La Operatore pipe DROP, che può essere utilizzato solo subito dopo il simbolo di una barra verticale, 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 barra verticale RENAME, che può essere utilizzato solo immediatamente dopo un simbolo di barra verticale, consente di rinominare le colonne della tabella corrente. Operatore pipe RENAME è simile al Dichiarazione 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 barra, utilizza l'operatore barra AGGREGATE, seguito da un numero qualsiasi di funzioni di aggregazione, seguito da una clausola GROUP BY. Non devono ripetere le colonne in una clausola SELECT.

Gli esempi in questa sezione utilizzano la tabella produce:

CREATE 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 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 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() oppure ometti GROUP BY completamente:

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

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

JOIN operatore pipe

L'operatore di canalizzazione 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.

I seguenti esempi fanno riferimento alle tabelle produce e item_data:

CREATE 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 TABLE mydataset.item_data 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.item_data` 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 fare alias utilizzando la classe Operatore pipe AS. Se vuoi, puoi assegnare un alias alla tabella unita. Puoi fare riferimento a entrambi gli alias dopo gli operatori di tubo successivi:

FROM `mydataset.produce`
|> AS produce_table
|> JOIN `mydataset.item_data` 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 la visibilità sul lato sinistro del join, il che significa che non puoi unire la tabella corrente con se stessa. Per esempio, la seguente query ha esito negativo:

-- 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 usare una tabella comune 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 TABLE mydataset.customer_orders 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 sapere, 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.customer_orders
    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, vedrai la colonna total_cost. prima che venga definito. 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 sono ripetute molte volte nelle clausole SELECT e GROUP BY, poi nella clausola ORDER BY.

La seguente query equivalente viene scritta utilizzando sintassi barra verticale:

FROM mydataset.customer_orders
|> 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 un clausola di privacy differenziale in un'istruzione SELECT che segue un operatore pipe. Utilizza invece un clausola di privacy differenziale nella sintassi standard e applicare operatori pipe dopo la query.
• Non puoi utilizzare una finestra denominata nella sintassi della barra verticale.

Passaggi successivi

• Scopri di più sulla sintassi delle query della barra verticale.
• Scopri di più sulla sintassi delle query standard.