Google uses AI technology to translate content into your preferred language. AI translations can contain errors.

Analisar dados usando a sintaxe de pipe

Este tutorial mostra como escrever consultas usando a sintaxe de pipe para analisar dados.

A sintaxe 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. A sintaxe de pipe consiste no símbolo de pipe |>, a nome de operador de pipe, e todos os argumentos. Para saber mais, acesse os recursos a seguir:

Para uma introdução à sintaxe de pipe, consulte Trabalhar com a sintaxe de consulta de pipe.
Para detalhes completos da sintaxe, consulte a sintaxe de consulta de pipe documentação de referência.

Neste tutorial, você vai criar uma consulta complexa na sintaxe de pipe usando a tabela disponível publicamente bigquery-public-data.austin_bikeshare.bikeshare_trips, que contém dados sobre viagens de bicicleta.

Objetivos

Visualizar dados da tabela iniciando uma consulta com uma FROM cláusula.
Adicionar colunas usando o EXTEND operador de pipe.
Agregar dados por dia e semana usando o AGGREGATE operador de pipe.
Agregar dados em uma janela deslizante usando o CROSS JOIN operador de pipe.
Filtrar dados usando o operador de pipe WHERE.
Comparar a estrutura de consulta linear da sintaxe de pipe com a estrutura de consulta aninhada da sintaxe padrão ao realizar agregações de vários níveis.

Antes de começar

Para começar a usar um conjunto de dados públicos do BigQuery, você precisa criar ou selecionar um projeto. O primeiro terabyte de dados processados por mês é sem custo financeiro. Assim, você pode começar a consultar conjuntos de dados públicos sem ativar o faturamento. Se quiser ir além do Nível sem custo financeiro, também é necessário ativar o faturamento.

Faça login na sua Google Cloud conta do. Se você começou a usar o Google Cloud, crie uma conta para avaliar o desempenho dos nossos produtos em situações reais. Clientes novos também recebem US $300 em créditos para executar, testar e implantar cargas de trabalho.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

O BigQuery é ativado automaticamente em novos projetos. Para ativar o BigQuery em um projeto preexistente,
Ative a API BigQuery.
Funções necessárias para ativar APIs
Para ativar as APIs, é necessário ter o papel do IAM de administrador de uso do serviço (roles/serviceusage.serviceUsageAdmin), que contém a permissão serviceusage.services.enable. Saiba como conceder papéis.
Ativar a API

Para mais informações sobre as diferentes maneiras de executar consultas, consulte Executar uma consulta.

Visualizar dados da tabela

Para recuperar todos os dados da tabela bikeshare_trips, execute a seguinte consulta:

Sintaxe de pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`;

Sintaxe padrão

SELECT *
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`;

Na sintaxe de pipe, a consulta pode começar com uma FROM cláusula sem uma SELECT cláusula para retornar resultados da tabela.

O resultado será semelhante ao seguinte:

+----------+-----------------+---------+-----------+-------------------------+-----+
| trip_id  | subscriber_type | bike_id | bike_type | start_time              | ... |
+----------+-----------------+---------+-----------+-------------------------+-----+
| 28875008 | Pay-as-you-ride | 18181   | electric  | 2023-02-12 12:46:32 UTC | ... |
| 28735401 | Explorer        | 214     | classic   | 2023-01-13 12:01:45 UTC | ... |
| 29381980 | Local365        | 21803   | electric  | 2023-04-20 08:43:46 UTC | ... |
| ...      | ...             | ...     | ...       | ...                     | ... |
+----------+-----------------+---------+-----------+-------------------------+-----+

Adicionar colunas

Na tabela bikeshare_trips, a coluna start_time é um carimbo de data/hora, mas talvez você queira adicionar uma coluna que mostre apenas a data da viagem. Para adicionar uma coluna na sintaxe de pipe, use o EXTEND operador de pipe:

Sintaxe de pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date;

Sintaxe padrão

SELECT *, CAST(start_time AS DATE) AS date
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`;

O resultado será semelhante ao seguinte:

+----------+-----------------+---------+-----------+-------------------------+------------+-----+
| trip_id  | subscriber_type | bike_id | bike_type | start_time              | date       | ... |
+----------+-----------------+---------+-----------+-------------------------+------------+-----+
| 28875008 | Pay-as-you-ride | 18181   | electric  | 2023-02-12 12:46:32 UTC | 2023-02-12 | ... |
| 28735401 | Explorer        | 214     | classic   | 2023-01-13 12:01:45 UTC | 2023-01-13 | ... |
| 29381980 | Local365        | 21803   | electric  | 2023-04-20 08:43:46 UTC | 2023-04-20 | ... |
| ...      | ...             | ...     | ...       | ...                     | ...        | ... |
+----------+-----------------+---------+-----------+-------------------------+------------+-----+

Agregar dados diários

É possível agrupar por data para encontrar o número total de viagens feitas e as bicicletas usadas por dia.

Use o AGGREGATE operador de pipe com a COUNT função para encontrar o número total de viagens feitas e bicicletas usadas.
Use a cláusula GROUP BY para agrupar os resultados por data.

Sintaxe de pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> AGGREGATE
     COUNT(*) AS trips,
     COUNT(DISTINCT bike_id) AS distinct_bikes
   GROUP BY date;

Sintaxe padrão

SELECT
  CAST(start_time AS DATE) AS date,
  COUNT(*) AS trips,
  COUNT(DISTINCT bike_id) AS distinct_bikes
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
GROUP BY date;

O resultado será semelhante ao seguinte:

+------------+-------+----------------+
| date       | trips | distinct_bikes |
+------------+-------+----------------+
| 2023-04-20 | 841   | 197            |
| 2023-01-27 | 763   | 148            |
| 2023-06-12 | 562   | 202            |
| ...        | ...   | ...            |
+------------+-------+----------------+

Ordenar resultados

Para classificar os resultados em ordem decrescente pela coluna date, adicione o DESC sufixo à cláusula GROUP BY:

Sintaxe de pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> AGGREGATE
     COUNT(*) AS trips,
     COUNT(DISTINCT bike_id) AS distinct_bikes
   GROUP BY date DESC;

Sintaxe padrão

SELECT
  CAST(start_time AS DATE) AS date,
  COUNT(*) AS trips,
  COUNT(DISTINCT bike_id) AS distinct_bikes
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
GROUP BY date
ORDER BY date DESC;

O resultado será semelhante ao seguinte:

+------------+-------+----------------+
| date       | trips | distinct_bikes |
+------------+-------+----------------+
| 2024-06-30 | 331   | 90             |
| 2024-06-29 | 395   | 123            |
| 2024-06-28 | 437   | 137            |
| ...        | ...   | ...            |
+------------+-------+----------------+

Na sintaxe de pipe, é possível adicionar o sufixo de classificação diretamente à GROUP BY cláusula sem usar o ORDER BY operador de pipe. Adicionar o sufixo à cláusula GROUP BY é um dos vários recursos de ordenação abreviada opcionais com AGGREGATE que a sintaxe de pipe oferece. Na sintaxe padrão, isso não é possível, e você precisa usar a cláusula ORDER BY para classificação.

Agregar dados semanais

Agora que você tem dados sobre o número de bicicletas usadas a cada dia, é possível criar uma consulta para encontrar o número de bicicletas distintas usadas em cada janela de sete dias.

Para atualizar as linhas na tabela para mostrar semanas em vez de dias, use a DATE_TRUNC função na cláusula GROUP BY e defina a granularidade como WEEK:

Sintaxe de pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> AGGREGATE
    COUNT(*) AS trips,
    COUNT(DISTINCT bike_id) AS distinct_bikes,
GROUP BY DATE_TRUNC(date, WEEK) AS date DESC;

Sintaxe padrão

SELECT
  DATE_TRUNC(CAST(start_time AS DATE), WEEK) AS date,
  COUNT(*) AS trips,
  COUNT(DISTINCT bike_id) AS distinct_bikes,
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
GROUP BY date
ORDER BY date DESC;

O resultado será semelhante ao seguinte:

+------------+-------+----------------+
| date       | trips | distinct_bikes |
+------------+-------+----------------+
| 2024-06-30 | 331   | 90             |
| 2024-06-23 | 3206  | 213            |
| 2024-06-16 | 3441  | 212            |
| ...        | ...   | ...            |
+------------+-------+----------------+

Agregar em uma janela deslizante

Os resultados na seção anterior mostram viagens em uma janela fixa entre as datas de início e término, como 2024-06-23 a 2024-06-29. Em vez disso, talvez você queira ver viagens em uma janela deslizante, em um período de sete dias que avança no tempo a cada novo dia. Em outras palavras, para uma determinada data, talvez você queira saber sobre o número de viagens feitas e bicicletas usadas na semana seguinte.

Para aplicar uma janela deslizante aos dados, primeiro copie cada viagem para frente em mais seis dias ativos a partir da data de início. Em seguida, calcule as datas dos dias ativos usando a função DATE_ADD. Por fim, agregue as viagens e os IDs de bicicleta para cada dia ativo.

Para copiar os dados para frente, use a função GENERATE_ARRAY e uma correlação:
Sintaxe de pipe
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips` |> EXTEND CAST(start_time AS DATE) AS date |> CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days;
Sintaxe padrão
SELECT *, CAST(start_time AS DATE) AS date FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips` CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days;
A função GENERATE_ARRAY cria uma matriz com sete elementos, 0 a 6. A operação CROSS JOIN UNNEST cria sete cópias de cada linha, com uma nova coluna diff_days que contém um dos valores de elemento da matriz de 0 a 6 para cada linha. É possível usar os valores diff_days como o ajuste da data original para deslizar a janela para frente em vários dias, até sete dias após a data original.

Para conferir as datas ativas calculadas para viagens, use o operador de pipe EXTEND com a função DATE_ADD para criar uma coluna chamada active_date que contém a data de início mais o valor na coluna diff_days:

Sintaxe de pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days
|> EXTEND DATE_ADD(date, INTERVAL diff_days DAY) AS active_date;

Sintaxe padrão

SELECT *, DATE_ADD(date, INTERVAL diff_days DAY) AS active_date
FROM (
  SELECT *, CAST(start_time AS DATE) AS date
  FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
  CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days)

Por exemplo, uma viagem que começa em 2024-05-20 também é considerada ativa em cada dia até 2024-05-26.

Por fim, agregue IDs de viagens e IDs de bicicletas e agrupe por active_date:

Sintaxe de pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days
|> EXTEND DATE_ADD(date, INTERVAL diff_days DAY) AS active_date
|> AGGREGATE COUNT(DISTINCT bike_id) AS active_7d_bikes,
            COUNT(trip_id) AS active_7d_trips
GROUP BY active_date DESC;

Sintaxe padrão

SELECT
  DATE_ADD(date, INTERVAL diff_days DAY) AS active_date,
  COUNT(DISTINCT bike_id) AS active_7d_bikes,
  COUNT(trip_id) AS active_7d_trips
FROM (
  SELECT *, CAST(start_time AS DATE) AS date
  FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
  CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days)
GROUP BY active_date
ORDER BY active_date DESC;

O resultado será semelhante ao seguinte:

+-------------+-----------------+-----------------+
| active_date | active_7d_bikes | active_7d_trips |
+-------------+-----------------+-----------------+
| 2024-07-06  | 90              | 331             |
| 2024-07-05  | 142             | 726             |
| 2024-07-04  | 186             | 1163            |
| ...         | ...             | ...             |
+-------------+-----------------+-----------------+

Filtrar datas futuras

Na consulta anterior, as datas se estendem até seis dias além da última data nos dados. Para filtrar datas que se estendem além do final dos dados, defina uma data máxima na consulta:

Adicione outro operador de pipe EXTEND que usa uma função de janela com uma cláusula OVER para calcular a data máxima na tabela.
Use o operador de pipe WHERE para filtrar as linhas geradas que estão após a data máxima.

Sintaxe de pipe

FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
|> EXTEND CAST(start_time AS DATE) AS date
|> EXTEND MAX(date) OVER () AS max_date
|> CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days
|> EXTEND DATE_ADD(date, INTERVAL diff_days DAY) AS active_date
|> WHERE active_date <= max_date
|> AGGREGATE COUNT(DISTINCT bike_id) AS active_7d_bikes,
             COUNT(trip_id) AS active_7d_trips
   GROUP BY active_date DESC;

Sintaxe padrão

SELECT
  DATE_ADD(date, INTERVAL diff_days DAY) AS active_date,
  COUNT(DISTINCT bike_id) AS active_7d_bikes,
  COUNT(trip_id) AS active_7d_trips
FROM(
  SELECT *
  FROM (
    SELECT *,
      DATE_ADD(date, INTERVAL diff_days DAY) AS active_date,
      MAX(date) OVER () AS max_date
    FROM(
      SELECT *, CAST(start_time AS DATE) AS date,
      FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
      CROSS JOIN UNNEST(GENERATE_ARRAY(0, 6)) AS diff_days))
  WHERE active_date <= max_date)
GROUP BY active_date
ORDER BY active_date DESC;

O resultado será semelhante ao seguinte:

+-------------+-----------------+-----------------+
| active_date | active_7d_bikes | active_7d_trips |
+-------------+-----------------+-----------------+
| 2024-06-30  | 212             | 3031            |
| 2024-06-29  | 213             | 3206            |
| 2024-06-28  | 219             | 3476            |
| ...         | ...             | ...             |
+-------------+-----------------+-----------------+

A seguir

Para mais informações sobre como a sintaxe de pipe funciona, consulte Trabalhar com a sintaxe de consulta de pipe.
Para mais informações técnicas, consulte a documentação de referência da sintaxe de consulta de pipe.