Trabalhe com a sintaxe de consulta de barra vertical

A sintaxe de consulta de pipes é uma extensão do GoogleSQL que suporta uma estrutura de consulta linear concebida para tornar as suas consultas mais fáceis de ler, escrever e manter. Pode usar a sintaxe de barra vertical em qualquer lugar onde escreva GoogleSQL.

A sintaxe de barra vertical suporta as mesmas operações que a sintaxe de consulta GoogleSQL existente ou a sintaxe padrão, por exemplo, seleção, agregação e agrupamento, junção e filtragem, mas as operações podem ser aplicadas em qualquer ordem e qualquer número de vezes. A estrutura linear da sintaxe de barra vertical permite-lhe escrever consultas de modo que a ordem da sintaxe de consulta corresponda à ordem dos passos lógicos realizados para criar a tabela de resultados.

As consultas que usam a sintaxe de barra vertical são avaliadas, executadas e otimizadas da mesma forma que as consultas de sintaxe padrão equivalentes. Quando escreve consultas com a sintaxe de barra vertical, siga as diretrizes para estimar custos e otimizar a computação de consultas.

A sintaxe padrão sofre de problemas que podem dificultar a leitura, a escrita e a manutenção. A tabela seguinte mostra como a sintaxe de barra vertical resolve estes problemas:

Sintaxe padrão Sintaxe de barra vertical
As cláusulas têm de aparecer numa ordem específica. Os operadores de barra vertical podem ser aplicados em qualquer ordem.
As consultas mais complexas, como consultas com agregação de vários níveis, requerem normalmente CTEs ou subconsultas aninhadas. As consultas mais complexas são normalmente expressas através da adição de operadores de barra vertical ao final da consulta.
Durante a agregação, as colunas são repetidas nas cláusulas SELECT, GROUP BY e ORDER BY. As colunas só podem ser apresentadas uma vez por agregação.

Para criar uma consulta complexa passo a passo na sintaxe de barra vertical, consulte o artigo Analise dados com a sintaxe de barra vertical. Para ver detalhes completos da sintaxe, consulte a documentação de referência da sintaxe de consulta com pipes.

Sintaxe básica

Na sintaxe de pipes, as consultas começam com uma consulta SQL padrão ou uma cláusula FROM. Por exemplo, uma cláusula FROM autónoma, como FROM MyTable, é uma sintaxe de barra vertical válida. O resultado da consulta SQL padrão ou da tabela da cláusula FROM pode, em seguida, ser transmitido como entrada a um símbolo de barra vertical, |>, seguido de um nome do operador de barra vertical e quaisquer argumentos para esse operador. O operador de barra vertical transforma a tabela de alguma forma e o resultado dessa transformação pode ser transmitido a outro operador de barra vertical.

Pode usar qualquer número de operadores de barra vertical na sua consulta para realizar ações como selecionar, ordenar, filtrar, juntar ou agregar colunas. Os nomes dos operadores de barra vertical correspondem às respetivas contrapartes de sintaxe padrão e, geralmente, têm o mesmo comportamento. A principal diferença entre a sintaxe padrão e a sintaxe de barra vertical é a forma como estrutura a sua consulta. À medida que a lógica expressa pela sua consulta se torna mais complexa, a consulta pode continuar a ser expressa como uma sequência linear de operadores de barra vertical, sem usar subconsultas profundamente aninhadas, o que facilita a leitura e a compreensão.

A sintaxe de barra vertical tem as seguintes características principais:

  • Cada operador de barra vertical na sintaxe de barra vertical consiste no símbolo de barra vertical, |>, um nome de operador e quaisquer argumentos:
    |> operator_name argument_list
  • Os operadores de barra vertical podem ser adicionados ao final de qualquer consulta válida.
  • Os operadores de barra vertical podem ser aplicados em qualquer ordem e qualquer número de vezes.
  • A sintaxe de barra vertical funciona em qualquer lugar onde a sintaxe padrão seja suportada: em consultas, vistas, funções de valor de tabela e outros contextos.
  • A sintaxe de barra vertical pode ser misturada com a sintaxe padrão na mesma consulta. Por exemplo, as subconsultas podem usar uma sintaxe diferente da consulta principal.
  • Um operador de barra vertical pode ver todos os alias existentes na tabela antes da barra vertical.
  • Uma consulta pode começar com uma cláusula FROM, e os operadores de barra vertical podem ser adicionados opcionalmente após a cláusula FROM.

Considere a seguinte tabela:

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);

Cada uma das consultas seguintes contém uma sintaxe de barra vertical válida que mostra como pode criar uma consulta sequencialmente.

As consultas podem começar com uma cláusula FROM e não precisam de conter um símbolo de barra vertical:

-- View the table.
FROM mydataset.Produce;

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

Pode filtrar com um WHERE operador de barra vertical:

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

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

Para fazer a agregação, use o operador de barra vertical AGGREGATE, seguido de qualquer número de funções agregadas e, de seguida, uma cláusula GROUP BY. A cláusula GROUP BY faz parte do operador de AGGREGATE barra vertical e não está separada por um símbolo de barra vertical (|>).

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

Suponhamos que tem a seguinte tabela que contém um ID para cada artigo:

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
);

Pode usar o JOIN operador de barra vertical para juntar os resultados da consulta anterior com esta tabela para incluir o ID de cada item:

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

Principais diferenças da sintaxe padrão

A sintaxe de barra vertical difere da sintaxe padrão das seguintes formas:

Para mais detalhes, consulte a lista completa de operadores de barra vertical.

Exemplos de utilização

Os exemplos de utilização comuns da sintaxe de barra vertical incluem o seguinte:

  • Análise ad hoc e criação incremental de consultas: A ordem lógica das operações facilita a escrita e a depuração de consultas. O prefixo de qualquer consulta até um símbolo de barra vertical |> é uma consulta válida, o que ajuda a ver os resultados intermédios numa consulta longa. Os ganhos de produtividade podem acelerar o processo de desenvolvimento em toda a sua organização.
  • Análise de registos: existem outros tipos de sintaxe semelhantes a pipes que são populares entre os utilizadores da análise de registos. A sintaxe de barra vertical oferece uma estrutura familiar que simplifica a integração desses utilizadores no Log Analytics e no BigQuery.

Funcionalidades adicionais na sintaxe de barra vertical

Com algumas exceções, a sintaxe de barra vertical suporta todos os operadores que a sintaxe padrão suporta com a mesma sintaxe. Além disso, a sintaxe de barra vertical introduz operadores de barra vertical adicionais e usa uma sintaxe modificada para agregações e junções. As secções seguintes explicam alguns destes operadores. Para todos os operadores suportados, consulte a lista completa de operadores de barra vertical.

EXTEND operador de barra vertical

O EXTEND operador de barra vertical permite-lhe anexar colunas calculadas à tabela atual. O operador de barra vertical EXTEND é semelhante à declaração SELECT *, new_column, mas oferece-lhe mais flexibilidade na referência a alias de colunas.

Considere a seguinte tabela que contém duas pontuações de testes para cada pessoa:

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

Suponhamos que quer calcular a pontuação bruta média e a percentagem média de pontuação que cada aluno recebeu no teste. Na sintaxe padrão, as colunas posteriores numa declaração SELECT não têm visibilidade para aliases anteriores. Para evitar uma subconsulta, tem de repetir a expressão para a média:

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

O operador de EXTENDbarra vertical pode fazer referência a aliases usados anteriormente, o que torna a consulta mais fácil de ler e menos propensa a erros:

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

SET operador de barra vertical

O SET operador de barra vertical permite-lhe substituir o valor das colunas na tabela atual. O operador de barra vertical SET é semelhante à declaração SELECT * REPLACE (expression AS column). Pode fazer referência ao valor original qualificando o nome da coluna com um alias da tabela.

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

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

DROP operador de barra vertical

O operador de barra vertical DROP permite remover colunas da tabela atual. O operador de barra vertical DROP é semelhante à declaração SELECT * EXCEPT(column). Depois de eliminar uma coluna, ainda pode fazer referência ao valor original qualificando o nome da coluna com um alias de tabela.

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

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

RENAME operador de barra vertical

O RENAME operador de barra vertical permite-lhe mudar o nome das colunas da tabela atual. O operador de barra vertical RENAME é semelhante à declaração 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 operador de barra vertical

Para fazer a agregação na sintaxe de barra vertical, use o operador de barra vertical AGGREGATE, seguido de qualquer número de funções agregadas, seguido de uma cláusula GROUP BY. Não precisa de repetir colunas numa cláusula SELECT.

Os exemplos nesta secção usam a tabela 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 quiser ordenar os resultados imediatamente após a agregação, pode marcar as colunas na cláusula GROUP BY que quer ordenar com ASC ou DESC. As colunas não marcadas não são ordenadas.

Se quiser ordenar todas as colunas, pode substituir a cláusula GROUP BY por uma cláusula GROUP AND ORDER BY, que ordena todas as colunas por ordem ascendente por predefinição. Pode especificar DESC as colunas que quer ordenar por ordem descendente. Por exemplo, as seguintes três consultas são equivalentes:

-- 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;

A vantagem de usar uma cláusula GROUP AND ORDER BY é que não tem de repetir os nomes das colunas em dois locais.

Para fazer a agregação completa de tabelas, use GROUP BY() ou omita a cláusula GROUP BY por completo:

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

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

JOIN operador de barra vertical

O JOIN operador de barra vertical permite-lhe juntar a tabela atual a outra tabela e suporta as operações de junção padrão, incluindo CROSS, INNER, LEFT, RIGHT e FULL.

Os exemplos seguintes fazem referência às tabelas 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
);

O exemplo seguinte usa uma cláusula USING e evita a ambiguidade de colunas:

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

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

Para fazer referência a colunas na tabela atual, como para desambiguar colunas numa cláusula ON, tem de atribuir um alias à tabela atual usando o operador AS pipe. Opcionalmente, pode atribuir um alias à tabela unida. Pode fazer referência a ambos os alias após os operadores de barra vertical subsequentes:

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

O lado direito da junção não tem visibilidade do lado esquerdo da junção, o que significa que não pode juntar a tabela atual a si própria. Por exemplo, a seguinte consulta falha:

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

Para fazer uma autocombinação com uma tabela modificada, pode usar uma expressão de tabela comum (CTE) numa cláusula WITH.

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

Exemplo

Considere a seguinte tabela com informações sobre as encomendas dos clientes:

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);

Suponhamos que quer saber, para cada estado e tipo de artigo, o valor médio gasto pelos clientes recorrentes. Pode escrever a consulta da seguinte forma:

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 ler a consulta de cima para baixo, encontra a coluna total_cost antes de ter sido definida. Mesmo na subconsulta, lê os nomes das colunas antes de ver a tabela de origem.

Para compreender esta consulta, tem de a ler de dentro para fora. As colunas state e item_type são repetidas várias vezes nas cláusulas SELECT e GROUP BY e, novamente, na cláusula ORDER BY.

A seguinte consulta equivalente é escrita com a sintaxe de barra vertical:

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

Com a sintaxe de barra vertical, pode escrever a consulta para seguir os passos lógicos que pode pensar para resolver o problema original. As linhas de sintaxe na consulta correspondem aos seguintes passos lógicos:

  • Comece pela tabela de encomendas de clientes.
  • Saiba quanto cada cliente gastou em cada tipo de artigo por estado.
  • Contar o número de encomendas de cada cliente.
  • Restringir os resultados a clientes recorrentes.
  • Encontre o valor médio que os clientes recorrentes gastam por cada estado e tipo de artigo.

Limitações

  • Não pode incluir uma cláusula de privacidade diferencial numa SELECTdeclaração após um operador de barra vertical. Em alternativa, use uma cláusula de privacidade diferencial na sintaxe padrão e aplique operadores de barra vertical após a consulta.

O que se segue?