Esta página foi traduzida pela API Cloud Translation.

Analise dados através da sintaxe de barra vertical

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

A sintaxe de barra vertical é uma extensão do GoogleSQL que suporta uma estrutura de consulta linear concebida para facilitar a leitura, a escrita e a manutenção das suas consultas. A sintaxe de barra vertical consiste no símbolo de barra vertical |>, num nome de operador de barra vertical e em quaisquer argumentos. Para obter mais informações, consulte os seguintes recursos:

Para uma introdução à sintaxe de barra vertical, consulte o artigo Trabalhe com a sintaxe de consulta de barra vertical.
Para ver os detalhes completos da sintaxe, consulte a documentação de referência da sintaxe de consulta de pipes.

Neste tutorial, vai criar uma consulta complexa na sintaxe de barra vertical usando a tabela bigquery-public-data.austin_bikeshare.bikeshare_tripsdisponível publicamente, que contém dados sobre viagens de bicicleta.

Objetivos

Veja os dados da tabela iniciando uma consulta com uma cláusula FROM.
Adicione colunas usando o EXTEND operador de barra vertical.
Agregue dados por dia e semana usando o operador de barra vertical AGGREGATE.
Agregue dados numa janela deslizante usando o operador de barra vertical CROSS JOIN.
Filtre os dados através do operador de barra vertical WHERE.
Compare a estrutura de consulta linear da sintaxe de barra vertical com a estrutura de consulta aninhada da sintaxe padrão quando realizar agregações de vários níveis.

Antes de começar

Para começar a usar um conjunto de dados público do BigQuery, tem de criar ou selecionar um projeto. O primeiro terabyte de dados processados por mês é gratuito, pelo que pode começar a consultar conjuntos de dados públicos sem ativar a faturação. Se pretender ir além do nível gratuito, também tem de ativar a faturação.

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

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 (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 (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 num projeto pré-existente:
Enable the BigQuery API.
Roles required to enable APIs
To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains the serviceusage.services.enable permission. Learn how to grant roles.
Enable the API

Para mais informações sobre as diferentes formas de executar consultas, consulte o artigo Execute uma consulta.

Ver dados da tabela

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

Sintaxe de barra vertical

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

Sintaxe padrão

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

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

O resultado é 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 | ... |
| ...      | ...             | ...     | ...       | ...                     | ... |
+----------+-----------------+---------+-----------+-------------------------+-----+

Adicione colunas

Na tabela bikeshare_trips, a coluna start_time é uma indicação de tempo, mas pode querer adicionar uma coluna que mostre apenas a data da viagem. Para adicionar uma coluna na sintaxe de barra vertical, use o operador de barra vertical EXTEND:

Sintaxe de barra vertical

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 é 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 | ... |
| ...      | ...             | ...     | ...       | ...                     | ...        | ... |
+----------+-----------------+---------+-----------+-------------------------+------------+-----+

Agregue dados diários

Pode agrupar por data para encontrar o número total de viagens feitas e as bicicletas usadas por dia.

Use o AGGREGATE operador de barra vertical 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 barra vertical

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 é 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 ordenar os resultados por ordem descendente pela coluna date, adicione o sufixo DESC à cláusula GROUP BY:

Sintaxe de barra vertical

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 é 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 barra vertical, pode adicionar o sufixo de ordenação diretamente à cláusula GROUP BY sem usar o operador de barra vertical ORDER BY. Adicionar o sufixo à cláusula GROUP BY é uma das várias funcionalidades de ordenação abreviadas opcionais com AGGREGATE que a sintaxe de barra vertical suporta. Na sintaxe padrão, isto não é possível e tem de usar a cláusula ORDER BY para a ordenação.

Agregue dados semanais

Agora que tem dados sobre o número de bicicletas usadas todos os dias, pode criar a sua consulta para encontrar o número de bicicletas distintas usadas em cada período de sete dias.

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

Sintaxe de barra vertical

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 é semelhante ao seguinte:

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

Agregue dados numa janela deslizante

Os resultados na secção anterior mostram viagens num intervalo fixo entre as datas de início e de fim, como de 2024-06-23 a 2024-06-29. Em alternativa, pode querer ver viagens numa janela deslizante, durante um período de sete dias que avança no tempo a cada novo dia. Por outras palavras, para uma determinada data, pode querer 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 seis dias ativos adicionais a partir da respetiva data de início. Em seguida, calcule as datas dos dias ativos através da função DATE_ADD. Por fim, agregue as viagens e os IDs das bicicletas para cada dia ativo.

Para copiar os dados para a frente, use a função GENERATE_ARRAY e uma junção cruzada:
Sintaxe de barra vertical
```
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 dos elementos da matriz de 0 a 6 para cada linha. Pode usar os valores diff_days como o ajuste à data original para avançar o período em esse número de dias, até sete dias após a data original.

Para ver as datas ativas calculadas para viagens, use o EXTENDoperador de barra vertical com a função DATE_ADD para criar uma coluna denominada active_date que contém a data de início mais o valor na coluna diff_days:

Sintaxe de barra vertical

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 a 2024-05-20 também é considerada ativa em todos os dias até 2024-05-26.

Por último, agregue os IDs das viagens e os IDs das bicicletas e agrupe por active_date:

Sintaxe de barra vertical

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 é 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            |
| ...         | ...             | ...             |
+-------------+-----------------+-----------------+

Filtre datas futuras

Na consulta anterior, as datas estendem-se até seis dias após a última data nos seus dados. Para filtrar datas que se estendem para além do fim dos dados, defina uma data máxima na consulta:

Adicione outro operador de EXTENDbarra vertical que use uma função de janela com uma cláusula OVER para calcular a data máxima na tabela.
Use o operador de WHERE barra vertical para filtrar as linhas geradas que ultrapassam a data máxima.

Sintaxe de barra vertical

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 é 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            |
| ...         | ...             | ...             |
+-------------+-----------------+-----------------+

O que se segue?

Para mais informações sobre o funcionamento da sintaxe de barra vertical, consulte o artigo Trabalhe com a sintaxe de consulta de barra vertical.
Para mais informações técnicas, consulte a documentação de referência da sintaxe de consulta com pipes.