Analiza datos con la sintaxis de canalización

En este instructivo, se muestra cómo escribir consultas con la sintaxis de canalización para analizar datos.

La sintaxis de canalización es una extensión de GoogleSQL que admite una estructura lineal, diseñada para que tus consultas sean más fáciles de leer, escribir y mantener. La sintaxis de canalización consta del símbolo de canalización |>, un nombre de operador de canalización y cualquier argumento. Para obtener más información, consulta los siguientes recursos:

• Para obtener una introducción a la sintaxis de canalización, consulta Trabaja con la sintaxis de consulta de canalización.
• Para obtener detalles completos sobre la sintaxis, consulta la documentación de referencia de la sintaxis de consulta de canalización.

En este instructivo, compilarás una consulta compleja en la sintaxis de canalización con la tabla disponible de forma pública bigquery-public-data.austin_bikeshare.bikeshare_trips, que contiene datos sobre viajes en bicicleta.

Objetivos

• Visualiza los datos de la tabla iniciando una consulta con una FROM cláusula.
• Agrega columnas con el operador de canalización EXTEND.
• Agrega datos por día y semana con el AGGREGATE operador de canalización.
• Agrega datos en una ventana deslizante con el operador de canalización CROSS JOIN.
• Filtra datos con el operador de canalización WHERE.
• Compara la estructura de consulta lineal de la sintaxis de canalización con la estructura de consulta anidada de la sintaxis estándar cuando realizas agregaciones de varios niveles.

Antes de comenzar

Para comenzar con un conjunto de datos públicos de BigQuery, debes crear o seleccionar un proyecto. El primer terabyte de datos procesados por mes es gratuito para que puedas comenzar a consultar conjuntos de datos públicos sin habilitar la facturación. Si supones que superarás el nivel gratuito, también debes habilitar la facturación.

Accede a tu Google Cloud cuenta de. Si eres nuevo en Google Cloud, crea una cuenta para evaluar el rendimiento de nuestros productos en situaciones reales. Los clientes nuevos también obtienen $300 en créditos gratuitos para ejecutar, probar y, además, implementar cargas de trabajo.

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. BigQuery se habilita automáticamente en proyectos nuevos. Para activar BigQuery en un proyecto existente,

   Habilita la API de BigQuery.

   Roles necesarios para habilitar las APIs

   Para habilitar las APIs, necesitas el rol de IAM de administrador de Service Usage (roles/serviceusage.serviceUsageAdmin), que contiene el permiso serviceusage.services.enable. Obtén más información para otorgar roles.

   Habilitar la API

Para obtener más información sobre las diferentes formas de ejecutar consultas, consulta Ejecuta una consulta.

Visualiza los datos de la tabla

Para recuperar todos los datos de la tabla bikeshare_trips, ejecuta la siguiente consulta:

Sintaxis de canalización

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

Sintaxis estándar

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

En la sintaxis de canalización, la consulta puede comenzar con una FROM cláusula sin una SELECT cláusula para mostrar los resultados de la tabla.

El resultado es similar al siguiente:

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

Agrega columnas

En la tabla bikeshare_trips, la columna start_time es una marca de tiempo, pero es posible que quieras agregar una columna que solo muestre la fecha del viaje. Para agregar una columna en la sintaxis de canalización, usa el EXTEND operador de canalización:

Sintaxis de canalización

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

Sintaxis estándar

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

El resultado es similar al siguiente:

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

Agrega datos diarios

Puedes agrupar por fecha para encontrar la cantidad total de viajes realizados y las bicicletas usadas por día.

• Usa el AGGREGATE operador de canalización con la COUNT función para encontrar la cantidad total de viajes realizados y bicicletas usadas.

• Usa la cláusula GROUP BY para agrupar los resultados por fecha.

Sintaxis de canalización

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;

Sintaxis estándar

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;

El resultado es similar al siguiente:

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

Ordena los resultados

Para ordenar los resultados en orden descendente por la columna date, agrega el DESC sufijo a la cláusula GROUP BY:

Sintaxis de canalización

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;

Sintaxis estándar

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;

El resultado es similar al siguiente:

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

En la sintaxis de canalización, puedes agregar el sufijo de ordenamiento directamente a la GROUP BY cláusula sin usar el ORDER BY operador de canalización. Agregar el sufijo a la cláusula GROUP BY es una de las varias funciones de ordenamiento abreviadas opcionales con AGGREGATE que admite la sintaxis de canalización. En la sintaxis estándar, esto no es posible y debes usar la cláusula ORDER BY para ordenar.

Agrega datos semanales

Ahora que tienes datos sobre la cantidad de bicicletas usadas cada día, puedes compilar tu consulta para encontrar la cantidad de bicicletas distintas usadas en cada ventana de siete días.

Para actualizar las filas de tu tabla para que se muestren semanas en lugar de días, usa la DATE_TRUNC función en la GROUP BY cláusula y establece la granularidad en WEEK:

Sintaxis de canalización

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;

Sintaxis estándar

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;

El resultado es similar al siguiente:

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

Agrega en una ventana deslizante

Los resultados de la sección anterior muestran viajes en una ventana fija entre las fechas de inicio y finalización, como 2024-06-23 y 2024-06-29. En su lugar, es posible que quieras ver los viajes en una ventana deslizante, durante un período de siete días que avanza en el tiempo con cada día nuevo. En otras palabras, para cualquier fecha determinada, es posible que quieras saber la cantidad de viajes realizados y bicicletas usadas durante la semana siguiente.

Para aplicar una ventana deslizante a tus datos, primero copia cada viaje seis días activos adicionales desde su fecha de inicio. Luego, calcula las fechas de los días activos con la función DATE_ADD. Por último, agrega los viajes y los IDs de bicicletas para cada día activo.

1. Para copiar tus datos, usa la función GENERATE_ARRAY y una unión cruzada:

   Sintaxis de canalización

   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;
   

   Sintaxis estándar

   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;
   

   La función GENERATE_ARRAY crea un array con siete elementos, de 0 a 6. La operación CROSS JOIN UNNEST crea siete copias de cada fila, con una nueva columna diff_days que contiene uno de los valores de los elementos del array de 0 a 6 para cada fila. Puedes usar los valores diff_days como el ajuste a la fecha original para deslizar la ventana hacia adelante en esa cantidad de días, hasta siete días después de la fecha original.

2. Para ver las fechas activas calculadas para los viajes, usa el operador de canalización EXTEND con la función DATE_ADD para crear una columna llamada active_date que contenga la fecha de inicio más el valor de la columna diff_days:

   Sintaxis de canalización

   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;
   

   Sintaxis estándar

   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 ejemplo, un viaje que comienza el 2024-05-20 también se considera activo todos los días hasta el 2024-05-26.

3. Por último, agrega los IDs de viajes y los IDs de bicicletas, y agrupa por active_date:

   Sintaxis de canalización

   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;
   

   Sintaxis estándar

   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;
   

   El resultado es similar al siguiente:

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

Filtra fechas futuras

En la consulta anterior, las fechas se extienden hasta seis días después de la última fecha de tus datos. Para filtrar las fechas que se extienden más allá del final de tus datos, establece una fecha máxima en tu consulta:

1. Agrega otro operador de canalización EXTEND que use una función analítica con una cláusula OVER para calcular la fecha máxima en la tabla.
2. Usa el operador de canalización WHERE para filtrar las filas generadas que superan la fecha máxima.

Sintaxis de canalización

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;

Sintaxis estándar

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;

El resultado es similar al siguiente:

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

¿Qué sigue?

• Para obtener más información sobre cómo funciona la sintaxis de canalización, consulta Trabaja con la sintaxis de consulta de canalización.
• Para obtener más información técnica, consulta la documentación de referencia de la sintaxis de consulta de canalización Pipe query syntax.