Trabalhar com a sintaxe de consulta de pipe

A sintaxe de consulta de pipe é uma extensão do GoogleSQL que oferece 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ê escreva o 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, mesclagem e filtragem, mas 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 de modo que a ordem da sintaxe de consulta corresponda à ordem das etapas lógicas usadas 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 de sintaxe padrão equivalentes. Ao escrever consultas com a sintaxe de pipe, siga as diretrizes para estimar custos e otimizar o cálculo de consultas.

A sintaxe padrão sofre com problemas que podem dificultar a leitura, a gravação 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 aquelas com agregação em vários níveis, 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 passo a passo na sintaxe de pipe, consulte Analisar dados usando a sintaxe de pipe. Para detalhes completos sobre a 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 pipe válida. O resultado da consulta SQL padrão ou da tabela da cláusula FROM pode ser transmitido como entrada para um símbolo de pipe, |>, seguido por um nome de operador de pipe e quaisquer argumentos para esse operador. O operador de pipe transforma a tabela de alguma forma, e o resultado dessa transformação pode ser transmitido para outro operador de pipe.

É possível usar qualquer número de operadores de pipe na consulta para realizar ações como selecionar, ordenar, filtrar, mesclar ou agregar colunas. Os nomes dos operadores de pipe correspondem aos equivalentes de sintaxe padrão e geralmente têm o mesmo comportamento. A principal diferença entre a sintaxe padrão e a sintaxe de pipe é a maneira 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, facilitando a leitura e a compreensão.

A sintaxe de pipe tem as seguintes características principais:

  • Cada operador de barra na sintaxe de barra consiste no símbolo de barra, |>, um nome de operador e todos os 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 que aceite a sintaxe padrão: em consultas, visualizações, funções com valor de tabela e outros contextos.
  • A sintaxe de pipe pode ser combinada com a sintaxe padrão na mesma consulta. Por exemplo, as subconsultas podem usar uma sintaxe diferente da consulta pai.
  • Um operador de pipe pode ver todos os aliases que existem na tabela que precede o pipe.
  • Uma consulta pode começar com uma cláusula FROM, e os operadores de pipe podem ser adicionados 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);

As consultas a seguir contêm uma sintaxe de pipe válida que mostra como 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 por uma cláusula GROUP BY. A cláusula GROUP BY faz parte do operador de pipe AGGREGATE e não é separada por um símbolo de 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         |
 +---------+-------------+-----------*/

Agora, suponha que você tenha a seguinte tabela que contém 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 mesclar os resultados da consulta anterior com essa 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 é diferente da sintaxe padrão das seguintes maneiras:

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

Casos de uso

Os casos de uso comuns da sintaxe de pipe incluem:

  • Análise ad hoc e criação de consultas incrementais: a ordem lógica das operações facilita a criação 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 dados de registros: existem outros tipos de sintaxe semelhantes a pipe que são populares entre os usuários de análise de dados de registros. A sintaxe de pipe oferece uma estrutura familiar que simplifica a integração desses usuários à Análise de Observabilidade e ao BigQuery.

Outros recursos na sintaxe de pipe

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

Operador pipe EXTEND

O operador de pipe EXTEND permite anexar colunas calculadas à tabela atual. O operador de pipe EXTEND é semelhante à instrução SELECT *, new_column, mas oferece mais flexibilidade para fazer referência a aliases de coluna.

Considere a tabela a seguir, que contém duas pontuações 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              |
 +---------+--------+--------+-----------------*/

Imagine que você queira calcular a pontuação bruta média e a pontuação percentual 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, é preciso 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 pipe EXTEND pode referenciar 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 por uma cláusula GROUP BY. Não é necessário repetir colunas em uma cláusula SELECT.

Os exemplos desta 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 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 cláusula GROUP AND ORDER BY, que ordena todas as colunas em ordem crescente por padrão. É possível especificar DESC após as 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 completa da tabela, use GROUP BY() ou omita totalmente a cláusula GROUP BY:

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 mesclar a tabela atual com outra tabela e é compatível com as operações de mesclagem padrão, incluindo CROSS, INNER, LEFT, RIGHT e FULL.

Os exemplos a seguir 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 a seguir 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 referenciar 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. Se quiser, é possível criar um alias para a tabela mesclada. É possível fazer referência aos dois aliases após os 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 si 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 realizar uma mesclagem automática com uma tabela modificada, use uma expressão de tabela comum (CTE, na sigla em inglês) 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 por 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 na subconsulta, você lê os nomes das colunas antes de ver de qual tabela elas vêm.

Para entender essa consulta, ela precisa ser lida de dentro para fora. As colunas state e item_type são repetidas várias vezes nas cláusulas SELECT e GROUP BY e, em seguida, na cláusula ORDER BY.

A consulta equivalente a seguir é escrita usando a sintaxe de 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    |
 +-------+------------+---------*/

Com a sintaxe de pipe, é possível escrever a consulta para seguir as etapas lógicas que você pode considerar para resolver o problema original. As linhas de sintaxe na consulta correspondem às seguintes etapas lógicas:

  • Comece com a tabela de pedidos de clientes.
  • Descubra quanto cada cliente gastou em cada tipo de item por estado.
  • Contar o número de pedidos de cada cliente.
  • Restringir 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 seguindo um operador de pipe. Em vez disso, use uma cláusula de privacidade diferencial na sintaxe padrão e aplique operadores de pipe após a consulta.

A seguir