Trabalhar com a sintaxe de consulta de pipe

A sintaxe de consulta de pipe é uma extensão do GoogleSQL que oferece suporte a uma estrutura de consulta linear projetada para facilitar a leitura, a gravação e a manutenção das consultas. É possível usar a sintaxe de pipe em qualquer lugar em que você escreve GoogleSQL.

A sintaxe de pipe é compatível com as mesmas operações da sintaxe de consulta do GoogleSQL ou da sintaxe padrão, como seleção, agregação e agrupamento, junção e filtragem. No entanto, as operações podem ser aplicadas em qualquer ordem e quantas vezes forem necessárias. A estrutura linear da sintaxe de pipe permite escrever consultas para que a ordem da sintaxe da consulta corresponda à ordem das etapas lógicas realizadas para criar a tabela de resultados.

As consultas que usam a sintaxe de pipe são precificadas, executadas e otimizadas da mesma forma que as consultas equivalentes de sintaxe padrão. Ao escrever consultas com sintaxe de pipe, siga as diretrizes para estimar custos e otimizar o cálculo de consultas.

A sintaxe padrão sofre de problemas que podem dificultar a leitura, a escrita e a manutenção. A tabela a seguir mostra como a sintaxe de pipe resolve esses problemas:

Sintaxe padrão Sintaxe de pipe
As cláusulas precisam aparecer em uma ordem específica. Os operadores de pipe podem ser aplicados em qualquer ordem.
Consultas mais complexas, como as que têm agregação multinível, geralmente exigem CTEs ou subconsultas aninhadas. Consultas mais complexas geralmente são expressas adicionando operadores de pipe 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 listadas uma vez por agregação.

Para criar uma consulta complexa usando a sintaxe pipe, consulte Analisar dados usando a sintaxe pipe. Para conferir todos os detalhes da sintaxe, consulte a documentação de referência da sintaxe de consulta de pipe.

Sintaxe básica

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

Você pode usar quantos operadores de pipe quiser na consulta para fazer coisas como selecionar, ordenar, filtrar, unir ou agregar colunas. Os nomes dos operadores de pipe correspondem às contrapartes de sintaxe padrão e geralmente têm o mesmo comportamento. A principal diferença entre a sintaxe padrão e a de pipe é a forma como você estrutura a consulta. À medida que a lógica expressa pela consulta se torna mais complexa, ela ainda pode ser expressa como uma sequência linear de operadores de pipe, sem usar subconsultas profundamente aninhadas, o que facilita a leitura e a compreensão.

A sintaxe de pipe 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 argumentos:
    |> operator_name argument_list
  • Os operadores de pipe podem ser adicionados ao final de qualquer consulta válida.
  • Os operadores de pipe podem ser aplicados em qualquer ordem e quantas vezes forem necessárias.
  • A sintaxe de pipe funciona em qualquer lugar em que a sintaxe padrão é aceita: em consultas, visualizações, funções com valor de tabela e outros contextos.
  • A sintaxe de barra vertical pode ser combinada com a sintaxe padrão na mesma consulta. Por exemplo, as subconsultas podem usar uma sintaxe diferente da consulta principal.
  • Um operador de pipe pode ver todos os aliases que existem na tabela antes do pipe.
  • Uma consulta pode começar com uma cláusula FROM, e operadores de pipe podem ser adicionados depois dela.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 a seguir contém uma sintaxe de pipe válida que mostra como você pode criar uma consulta sequencialmente.

As consultas podem começar com uma cláusula FROM e não precisam 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     |
 +---------+-------+-----------*/

É possível filtrar com um operador de 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     |
 +---------+-------+-----------*/

Para realizar a agregação, use o operador de pipe AGGREGATE, seguido por qualquer número de funções de agregação e uma cláusula GROUP BY. A cláusula GROUP BY faz parte do operador de barra vertical AGGREGATE e não é 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         |
 +---------+-------------+-----------*/

Agora suponha que você tenha a seguinte tabela com um ID para cada item:

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

Use o operador de pipe JOIN para juntar os resultados da consulta anterior com esta tabela e 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 pipe difere da sintaxe padrão das seguintes maneiras:

Para mais detalhes, consulte a lista completa de operadores de pipe.

Casos de uso

Confira alguns casos de uso comuns da sintaxe de pipe:

  • 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 visualizar resultados intermediários em uma consulta longa. Os ganhos de produtividade podem acelerar o processo de desenvolvimento em toda a organização.
  • Análise de registros: existem outros tipos de sintaxe semelhante a pipes que são populares entre os usuários de análise de registros. A sintaxe de pipe oferece uma estrutura familiar que simplifica a integração desses usuários à Análise de dados de registros e ao BigQuery.

Outros recursos na sintaxe de pipe

Com poucas exceções, a sintaxe de pipe é compatível com todos os operadores da sintaxe padrão, usando a mesma sintaxe. Além disso, a sintaxe de pipe apresenta outros operadores de pipe e usa uma sintaxe modificada para agregações e junções. As seções a seguir explicam esses operadores. Para todos os operadores compatíveis, consulte a lista completa de operadores de pipe.

Operador pipe EXTEND

O operador de barra vertical EXTEND permite anexar colunas calculadas à tabela atual. O operador de pipe EXTEND é semelhante à instrução SELECT *, new_column, mas oferece mais flexibilidade ao referenciar aliases de coluna.

Considere a tabela a seguir, que contém duas notas de teste 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              |
 +---------+--------+--------+-----------------*/

Suponha que você queira calcular a pontuação bruta média e a porcentagem média que cada estudante recebeu no teste. Na sintaxe padrão, as colunas posteriores em uma instrução SELECT não têm visibilidade para aliases anteriores. Para evitar uma subconsulta, repita a expressão da média:

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

O operador de pipe EXTEND pode fazer referência a aliases usados anteriormente, facilitando a leitura da consulta e reduzindo a probabilidade de 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             |
 +---------+---------------+-----------------*/

Operador pipe SET

O operador de pipe SET permite substituir o valor das colunas na tabela atual. O operador de pipe SET é semelhante à instrução SELECT * REPLACE (expression AS column). É possível referenciar o valor original qualificando o nome da coluna com um alias de tabela.

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

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

Operador pipe DROP

O operador de pipe DROP permite remover colunas da tabela atual. O operador de pipe DROP é semelhante à instrução SELECT * EXCEPT(column). Depois que uma coluna é descartada, ainda é possível referenciar o 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 |
 +---*/

Operador pipe RENAME

O operador de pipe RENAME permite renomear colunas da tabela atual. O operador de pipe RENAME é semelhante à instruçã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 |
 +---+---+---*/

Operador pipe AGGREGATE

Para realizar a agregação na sintaxe de pipe, use o operador de pipe AGGREGATE, seguido por qualquer número de funções de agregação e uma cláusula GROUP BY. Não é necessário repetir colunas em uma cláusula SELECT.

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

Se você quiser ordenar todas as colunas, substitua a cláusula GROUP BY por uma GROUP AND ORDER BY, que ordena todas as colunas em ordem crescente por padrão. É possível especificar DESC depois das colunas que você quer ordenar em ordem decrescente. Por exemplo, as três consultas a seguir 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 você não precisa repetir os nomes das colunas em dois lugares.

Para realizar a agregação de tabela completa, 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           |
 +-------+-------------*/

Operador pipe JOIN

O operador de pipe JOIN permite unir a tabela atual a outra e oferece suporte às operações de junção padrão, incluindo CROSS, INNER, LEFT, RIGHT e FULL.

Os exemplos a seguir referenciam as 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 a seguir usa uma cláusula USING e evita ambiguidade de coluna:

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 eliminar a ambiguidade de colunas em uma cláusula ON, é necessário criar um alias para a tabela atual usando o operador de pipe AS. Você pode criar um alias para a tabela associada. É possível fazer referência aos dois aliases usando operadores de pipe 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, o que significa que não é possível unir a tabela atual a ela mesma. Por exemplo, a consulta a seguir falha:

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

Para fazer uma autojunção com uma tabela modificada, use uma expressão de tabela comum (CTE) dentro de uma 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 tabela a seguir com informações sobre pedidos de 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);

Imagine que você queira saber, para cada estado e tipo de item, o valor médio gasto pelos clientes recorrentes. Você pode escrever a consulta da seguinte maneira:

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 você ler a consulta de cima para baixo, vai encontrar a coluna total_cost antes de ela ser definida. Mesmo dentro da subconsulta, você lê os nomes das colunas antes de saber de qual tabela elas vêm.

Para entender essa consulta, é preciso lê-la 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 consulta equivalente a seguir é escrita usando 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 pipe, você pode escrever a consulta para seguir as etapas lógicas que talvez pense para resolver o problema original. As linhas de sintaxe na consulta correspondem às seguintes etapas lógicas:

  • Comece com a tabela de pedidos dos clientes.
  • Descubra quanto cada cliente gastou em cada tipo de item por estado.
  • Conte o número de pedidos de cada cliente.
  • Restrinja os resultados a clientes recorrentes.
  • Encontre o valor médio que os clientes recorrentes gastam por estado e tipo de item.

Limitações

  • Não é possível incluir uma cláusula de privacidade diferencial em uma instrução SELECT após um operador de pipe. Em vez disso, use uma cláusula de privacidade diferencial na sintaxe padrão e aplique operadores pipe após a consulta.
  • Não é possível usar uma janela nomeada na sintaxe de pipe.

A seguir