Trabalhar com a sintaxe de consulta de pipe

Para inscrever um projeto na prévia da sintaxe de pipe, preencha o formulário de inscrição da sintaxe 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 o GoogleSQL.

A sintaxe de pipe oferece suporte às mesmas operações da sintaxe de consulta do GoogleSQL ou da sintaxe padrão, como seleção, agregação e agrupamento, mesclagem 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 que você escreva consultas para que a ordem da sintaxe 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 de sintaxe padrão equivalentes. 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 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 consultas com agregação de vários níveis, geralmente exigem CTEs ou subconsultas aninhadas. As 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 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 válida de pipe. 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 barra vertical transforma a tabela de alguma forma, e o resultado dessa transformação pode ser transmitido a outro operador de barra vertical.

Você pode 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 respectivas 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, 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 consiste no símbolo de barra, |>, um nome de operador e quaisquer 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 você quiser.
  • 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 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 acessar todos os aliases que existem na tabela anterior ao pipe.
  • Uma consulta pode começar com uma cláusula FROM, e os operadores de canal podem ser adicionados após a cláusula FROM.

Considere a tabela a seguir:

CREATE OR REPLACE TABLE 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 pipe:

-- View the table.
FROM 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 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 de qualquer número de funções de agregação e, em seguida, de 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 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 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 esta tabela para incluir o ID de cada item:

FROM Produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY item
|> JOIN ItemData USING(item);

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

Principais diferenças em relação à 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 canal.

Casos de uso

Os casos de uso comuns para a 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 pipe |> é uma consulta válida, 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 pipe que são populares entre os usuários de análise de registros. A sintaxe de pipe fornece uma estrutura familiar que simplifica a integração para esses usuários e .

Outros recursos na sintaxe de pipe

Com poucas exceções, a sintaxe de pipe aceita todos os operadores que a sintaxe padrão aceita com a mesma sintaxe. Além disso, a sintaxe de pipe apresenta os operadores de pipe a seguir.

Operador de pipe EXTEND

O operador de barra vertical EXTEND permite anexar colunas computadas à tabela atual. O operador de pipe EXTEND é semelhante à instrução SELECT *, new_column, mas oferece mais flexibilidade na referência de pseudônimos de coluna.

Considere a tabela a seguir, que contém duas notas de cada pessoa:

CREATE OR REPLACE TABLE 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 média bruta e a pontuação média percentual que cada aluno recebeu no teste. Na sintaxe padrão, as colunas posteriores em uma instrução SELECT não têm visibilidade para os aliases anteriores. Para evitar uma subconsulta, repita a expressão para a média:

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

O operador de pipe EXTEND pode se referir a aliases usados anteriormente, facilitando a leitura da consulta e reduzindo a probabilidade de erros:

FROM 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 das colunas na tabela atual. O operador de pipe SET é semelhante à instrução SELECT * REPLACE (expression AS column). Para fazer referência ao valor original, qualifique 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 operador de pipe AGGREGATE, seguido de qualquer número de funções de agregação, seguido de 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 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 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 cláusula GROUP AND ORDER BY, que ordena cada coluna 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 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 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 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 a cláusula GROUP BY inteiramente:

FROM 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 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 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 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 Produce
|> JOIN `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 em uma cláusula ON, é necessário criar um alias para a tabela atual usando o operador de pipe AS. Você pode usar um alias para a tabela mesclada. É possível fazer referência aos dois aliases seguindo os operadores de pipe subsequentes:

FROM Produce
|> AS produce_table
|> JOIN `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 você não pode mesclar a tabela atual com ela mesma. Por exemplo, a consulta a seguir falha:

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

Para realizar uma autojunção com uma tabela modificada, use uma expressão de tabela comum (CTE) em uma cláusula WITH.

WITH cte_table AS (
  FROM 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 os pedidos do cliente:

CREATE OR REPLACE TABLE 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 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 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 que ela seja definida. Mesmo dentro da subconsulta, você lê os nomes das colunas antes de saber 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 depois na cláusula ORDER BY.

A consulta equivalente a seguir é gravada usando a sintaxe de pipe:

FROM 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 você pensa para resolver o problema original. As linhas de sintaxe na consulta correspondem às seguintes etapas lógicas:

  • Comece com a tabela de pedidos do cliente.
  • Descubra quanto cada cliente gastou em cada tipo de item por estado.
  • Conte o número de pedidos de cada cliente.
  • Restringir os resultados a clientes recorrentes.
  • Encontre o valor médio gasto pelos clientes recorrentes em cada 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 de pipe após a consulta.
  • Não é possível usar uma janela nomeada na sintaxe de pipe.