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 de consulta 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 |>, el nombre de un operador de canalización y los argumentos. 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 consultas de canalización.
• Para obtener detalles completos sobre la sintaxis, consulta la documentación de referencia de la sintaxis de consultas con canalización.

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

Objetivos

• Visualiza los datos de la tabla iniciando una consulta con una cláusula FROM.
• Agrega columnas con el operador de canalización EXTEND.
• Agrega los datos por día y semana con el operador de canalización AGGREGATE.
• Agrega datos en una ventana deslizante con el operador de canalización CROSS JOIN.
• Filtrar 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 realices 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.

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

   Go to project selector

2. Verify that billing is enabled for your Trusted Cloud project.

3. BigQuery se habilita automáticamente en proyectos nuevos. Para activar BigQuery en un proyecto preexistente,

   Enable the BigQuery API.

   Enable the API

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

Cómo ver 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 cláusula FROM sin una cláusula SELECT para devolver 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 | ... |
| ...      | ...             | ...     | ...       | ...                     | ... |
+----------+-----------------+---------+-----------+-------------------------+-----+

Agregar columnas

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

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 los datos diarios

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

• Usa el operador de canalización AGGREGATE con la función COUNT 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            |
| ...        | ...   | ...            |
+------------+-------+----------------+

Ordenar los resultados

Para ordenar los resultados de forma descendente por la columna date, agrega el sufijo DESC 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 cláusula GROUP BY sin usar el operador de canalización ORDER BY. Agregar el sufijo a la cláusula GROUP BY es una de las varias funciones de ordenamiento abreviado con AGGREGATE opcionales 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 que se usan cada día, puedes ampliar tu consulta para encontrar la cantidad de bicicletas distintas que se usaron en cada período de siete días.

Para actualizar las filas de tu tabla de modo que muestren semanas en lugar de días, usa la función DATE_TRUNC en la cláusula GROUP BY 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 datos 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 del 2024-06-23 al 2024-06-29. En cambio, es posible que desees 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 desees 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 a partir de 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 las bicicletas para cada día activo.

1. Para copiar tus datos hacia adelante, 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, del 0 al 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 de diff_days como ajuste de la fecha original para adelantar el período en esa cantidad de días, hasta siete días después de la fecha original.

2. Para ver las fechas activas calculadas de 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 de bicicletas, y agrupa los resultados 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            |
   | ...         | ...             | ...             |
   +-------------+-----------------+-----------------+
   

Cómo filtrar 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 consultas de canalización.
• Para obtener más información técnica, consulta la documentación de referencia de la sintaxis de consultas con tuberías.