Analisar dados usando a sintaxe pipe

Neste tutorial, mostramos 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 barra vertical consiste no símbolo |>, um nome de operador de barra vertical e 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 conferir todos os detalhes da sintaxe, consulte a documentação de referência da sintaxe de consulta de pipe.

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

Objetivos

• Para ver os dados da tabela, inicie uma consulta com uma cláusula FROM.
• Adicione colunas usando o operador de pipe EXTEND.
• Agregue dados por dia e semana usando o operador de pipe AGGREGATE.
• Agregue dados em uma janela deslizante usando o operador de pipe CROSS JOIN.
• Filtre os dados usando o operador de pipe WHERE.
• Compare 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 conta do Google Cloud . Se você começou a usar o Google Cloud, crie uma conta para avaliar o desempenho de 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.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

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.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

1. 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.

Ver 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 cláusula FROM sem uma cláusula SELECT 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 operador de pipe EXTEND:

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 operador de pipe AGGREGATE com a função COUNT 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 sufixo DESC à 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 à cláusula GROUP BY sem usar o operador de pipe ORDER BY. Adicionar o sufixo à cláusula GROUP BY é um dos vários recursos opcionais de ordenação abreviada com AGGREGATE que a sintaxe de pipe oferece suporte. 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 período de sete dias.

Para atualizar as linhas da tabela e mostrar semanas em vez de dias, use a função DATE_TRUNC 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 datas de início e término, como 2024-06-23 a 2024-06-29. Em vez disso, talvez você queira ver trajetos 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 o número de viagens feitas e bicicletas usadas na semana seguinte.

Para aplicar uma janela deslizante aos seus dados, primeiro copie cada viagem para frente seis dias ativos adicionais 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 das bicicletas de cada dia ativo.

1. Para copiar seus 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, de 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 avançar a janela em até sete dias após a data original.

2. Para conferir as datas ativas calculadas das 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.

3. Por fim, agregue os IDs de viagens e 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 após a última data nos seus dados. Para filtrar datas que vão além do fim dos seus dados, defina uma data máxima na consulta:

1. 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.
2. 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 sobre a sintaxe de consulta de pipe.