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ê escreve o GoogleSQL.

A sintaxe de pipe oferece suporte às mesmas operações que a sintaxe de consulta do GoogleSQL atual, ou 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 para que a ordem da sintaxe de 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 a sintaxe de pipe, siga as diretrizes para estimar custos e otimizar a computação de consultas.

A sintaxe padrão tem 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 consultas com agregação de 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 podem ser listadas apenas 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 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 pipe válida. O resultado da consulta SQL padrão ou da tabela da FROM cláusula pode ser transmitido como entrada para um símbolo de pipe, |>, seguido por um pipe nome de operador e todos os 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 fazer coisas como selecionar, ordenar, filtrar, mesclar 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 sintaxe de pipe é a maneira como você estrutura a consulta. À medida que a lógica expressa pela consulta se torna mais complexa, a consulta 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 pipe na sintaxe de pipe consiste no símbolo de pipe, |>, 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 em que a sintaxe padrão seja compatível: em consultas, visualizações, funções com valor de tabela e outros contextos.
  • A sintaxe de pipe pode ser misturada com a sintaxe padrão na mesma consulta. Por exemplo, as subconsultas podem usar uma sintaxe diferente da consulta mãe.
  • Um operador de pipe pode ver todos os aliases que existem na tabela anterior ao pipe.
  • Uma consulta pode começar com uma cláusula FROM, e os operadores de pipe podem ser adicionados opcionalmente após a cláusula FROM.

Pense na tabela a seguir:

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 FROM cláusula e não precisam conter um símbolo de pipe:

-- 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 WHEREoperador de pipe:

-- 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 AGGREGATE pipe operator, seguido por qualquer número de aggregate functions e uma cláusula GROUP BY. A cláusula GROUP BY faz parte do AGGREGATE operador de pipe 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 tabela a seguir, 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
);

É possível usar o operador de pipe JOIN `JOIN` para mesclar os resultados da consulta anterior com essa 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 pipe difere da sintaxe padrão das seguintes maneiras:

  • As consultas podem começar com uma FROM cláusula.
  • O operador de pipe SELECT não realiza a agregação. É necessário usar o AGGREGATE operador de pipe em vez disso.
  • A filtragem é sempre feita com o WHERE pipe operator, que pode ser aplicado em qualquer lugar. O operador de pipe WHERE, que substitui HAVING e QUALIFY, pode filtrar os resultados de funções de agregação ou de janela.

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

Casos de uso

Os casos de uso comuns da sintaxe de pipe incluem o seguinte:

  • Análise ad hoc e criação de consultas incrementais: a ordem lógica das operações facilita a gravação e a depuração de consultas. O prefixo de qualquer consulta até um símbolo de pipe |> é 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 semelhantes 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 ao Observability Analytics e ao BigQuery.

Outros recursos na sintaxe de pipe

Com poucas exceções, a sintaxe de pipe oferece suporte a todos os operadores que a sintaxe padrão faz com a mesma sintaxe. Além disso, a sintaxe de pipe apresenta 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 com suporte, consulte a lista completa de operadores de pipe.

Operador de 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 na referência de aliases de coluna.

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

Suponha que você queira calcular a pontuação bruta média e a pontuação percentual média que cada aluno 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, é necessário repetir 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 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 de pipe SET

O operador de pipe SET permite substituir o valor de 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 de 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 de 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 de pipe AGGREGATE

Para realizar a agregação na sintaxe de pipe, use o AGGREGATE pipe operador, 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 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ê estiver pronto para 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 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 não é necessário repetir nomes de colunas em dois lugares.

Para realizar a agregação de tabelas completas, use GROUP BY() ou omita a GROUP BY cláusula completamente:

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

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

Operador de pipe JOIN

O operador de pipe JOIN permite mesclar a tabela atual com outra tabela e oferece suporte às 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 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 referenciar colunas na tabela atual, como para desambiguar colunas em uma ON cláusula, é necessário criar um alias da tabela atual usando o AS pipe operador. Opcionalmente, é possível criar um alias da tabela mesclada. É possível referenciar os dois aliases seguindo 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 mesclagem não tem visibilidade para o lado esquerdo da mesclagem, o que significa que não é possível mesclar a tabela atual com 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 realizar uma mesclagem automática com uma tabela modificada, é possível usar 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

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

Suponha que você queira saber, para cada estado e tipo de item, o valor médio gasto por clientes recorrentes. É possível 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, 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, novamente, 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 pensar 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.
  • 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 para cada estado e tipo de item.

Limitações

  • Não é possível incluir uma cláusula de privacidade diferencial em uma SELECT instrução após 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