Se usó la API de Cloud Translation para traducir esta página.

Trabaja con la sintaxis de consulta de tuberías

La sintaxis de consultas con tuberías es una extensión de GoogleSQL que admite una estructura de consultas lineal diseñada para que tus consultas sean más fáciles de leer, escribir y mantener. Puedes usar la sintaxis de barras verticales en cualquier lugar donde escribas GoogleSQL.

La sintaxis de canalización admite las mismas operaciones que la sintaxis de consulta de GoogleSQL existente o la sintaxis estándar, por ejemplo, selección, agregación y agrupación, unión y filtrado, pero las operaciones se pueden aplicar en cualquier orden y cualquier cantidad de veces. La estructura lineal de la sintaxis de tuberías te permite escribir consultas de modo que el orden de la sintaxis de la consulta coincida con el orden de los pasos lógicos que se siguen para crear la tabla de resultados.

Las consultas que usan la sintaxis de tubería se cotizan, ejecutan y optimizan de la misma manera que sus consultas equivalentes con sintaxis estándar. Cuando escribas consultas con sintaxis de tubería, sigue los lineamientos para estimar los costos y optimizar el procesamiento de las consultas.

La sintaxis estándar tiene problemas que pueden dificultar su lectura, escritura y mantenimiento. En la siguiente tabla, se muestra cómo la sintaxis de barra vertical aborda estos problemas:

Sintaxis estándar	Sintaxis de canalización
Las cláusulas deben aparecer en un orden específico.	Los operadores de canalización se pueden aplicar en cualquier orden.
Las consultas más complejas, como las que tienen agregación de varios niveles, suelen requerir CTE o subconsultas anidadas.	Las consultas más complejas suelen expresarse agregando operadores de canalización al final de la consulta.
Durante la agregación, las columnas se repiten en las cláusulas `SELECT`, `GROUP BY` y `ORDER BY`.	Las columnas solo se pueden enumerar una vez por agregación.

Para compilar una consulta compleja paso a paso con la sintaxis de canalización, consulta Analiza datos con la sintaxis de canalización. Para obtener detalles completos sobre la sintaxis, consulta la documentación de referencia de la sintaxis de consultas con tuberías.

Sintaxis básica

En la sintaxis de tubería, las consultas comienzan con una consulta en SQL estándar o una cláusula FROM. Por ejemplo, una cláusula FROM independiente, como FROM MyTable, es una sintaxis de canalización válida. El resultado de la consulta en SQL estándar o la tabla de la cláusula FROM se pueden pasar como entrada a un símbolo de barra vertical, |>, seguido del nombre de un operador de barra vertical y los argumentos de ese operador. El operador de canalización transforma la tabla de alguna manera, y el resultado de esa transformación se puede pasar a otro operador de canalización.

Puedes usar la cantidad que desees de operadores de canalización en tu consulta para realizar acciones como seleccionar, ordenar, filtrar, unir o agregar columnas. Los nombres de los operadores de canalización coinciden con sus equivalentes de sintaxis estándar y, por lo general, tienen el mismo comportamiento. La principal diferencia entre la sintaxis estándar y la sintaxis de barra vertical es la forma en que estructuras tu búsqueda. A medida que la lógica expresada por tu consulta se vuelve más compleja, la consulta se puede seguir expresando como una secuencia lineal de operadores de canalización, sin usar subconsultas anidadas profundamente, lo que facilita su lectura y comprensión.

La sintaxis de canalización tiene las siguientes características clave:

Cada operador de canalización en la sintaxis de canalización consta del símbolo de canalización, |>, un nombre de operador y cualquier argumento:
|> operator_name argument_list
Los operadores de canalización se pueden agregar al final de cualquier búsqueda válida.
Los operadores de canalización se pueden aplicar en cualquier orden y cantidad de veces.
La sintaxis de canalización funciona en cualquier lugar donde se admita la sintaxis estándar: en consultas, vistas, funciones que devuelven tablas y otros contextos.
La sintaxis de canalización se puede combinar con la sintaxis estándar en la misma consulta. Por ejemplo, las subconsultas pueden usar una sintaxis diferente de la consulta principal.
Un operador de canalización puede ver todos los alias que existen en la tabla que precede al canal.
Una búsqueda puede comenzar con una cláusula FROM, y los operadores de canalización se pueden agregar de forma opcional después de la cláusula FROM.

Considera la siguiente tabla:

CREATE OR REPLACE TABLE mydataset.Produce AS (
  SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
  UNION ALL
  SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);

Cada una de las siguientes consultas contiene una sintaxis de canalización válida que muestra cómo puedes compilar una consulta de forma secuencial.

Las búsquedas pueden comenzar con una cláusula FROM y no necesitan contener un símbolo de barra vertical:

-- View the table.
FROM mydataset.Produce;

/*---------+-------+-----------+
 | item    | sales | category  |
 +---------+-------+-----------+
 | apples  | 7     | fruit     |
 | apples  | 2     | fruit     |
 | carrots | 0     | vegetable |
 | bananas | 15    | fruit     |
 +---------+-------+-----------*/

Puedes filtrar con un operador de canalización WHERE:

-- Filter items with no sales.
FROM mydataset.Produce
|> WHERE sales > 0;

/*---------+-------+-----------+
 | item    | sales | category  |
 +---------+-------+-----------+
 | apples  | 7     | fruit     |
 | apples  | 2     | fruit     |
 | bananas | 15    | fruit     |
 +---------+-------+-----------*/

Para realizar la agregación, usa el operador de canalización AGGREGATE, seguido de cualquier cantidad de funciones de agregación y, luego, una cláusula GROUP BY. La cláusula GROUP BY forma parte del operador de canalización AGGREGATE y no está separada por un símbolo de canalización (|>).

-- Compute total sales by item.
FROM mydataset.Produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY item;

/*---------+-------------+-----------+
 | item    | total_sales | num_sales |
 +---------+-------------+-----------+
 | apples  | 9           | 2         |
 | bananas | 15          | 1         |
 +---------+-------------+-----------*/

Ahora supongamos que tienes la siguiente tabla que contiene un ID para cada elemento:

CREATE OR REPLACE TABLE mydataset.ItemData AS (
  SELECT 'apples' AS item, '123' AS id
  UNION ALL
  SELECT 'bananas' AS item, '456' AS id
  UNION ALL
  SELECT 'carrots' AS item, '789' AS id
);

Puedes usar el operador de canalización JOIN para unir los resultados de la consulta anterior con esta tabla y, así, incluir el ID de cada elemento:

FROM mydataset.Produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY item
|> JOIN mydataset.ItemData USING(item);

/*---------+-------------+-----------+-----+
 | item    | total_sales | num_sales | id  |
 +---------+-------------+-----------+-----+
 | apples  | 9           | 2         | 123 |
 | bananas | 15          | 1         | 456 |
 +---------+-------------+-----------+-----*/

Diferencias clave con la sintaxis estándar

La sintaxis de barra vertical difiere de la sintaxis estándar de las siguientes maneras:

Las búsquedas pueden comenzar con una cláusula FROM.
El operador de canalización SELECT no realiza la agregación. En su lugar, debes usar el operador de canalización AGGREGATE.
El filtrado siempre se realiza con el operador de canalización WHERE, que se puede aplicar en cualquier lugar. El operador de canalización WHERE, que reemplaza a HAVING y QUALIFY, puede filtrar los resultados de las funciones de agregación o analíticas.

Para obtener más detalles, consulta la lista completa de operadores de canalización.

Casos de uso

Estos son algunos casos de uso comunes de la sintaxis de barra vertical:

Análisis ad hoc y creación de consultas incrementales: El orden lógico de las operaciones facilita la escritura y depuración de las consultas. El prefijo de cualquier consulta hasta el símbolo de barra vertical |> es una consulta válida, lo que te ayuda a ver los resultados intermedios en una consulta larga. Las ganancias de productividad pueden acelerar el proceso de desarrollo en toda tu organización.
Análisis de registros: Existen otros tipos de sintaxis similares a tuberías que son populares entre los usuarios de análisis de registros. La sintaxis de canalización proporciona una estructura familiar que simplifica la incorporación de esos usuarios a Log Analytics y BigQuery.

Funciones adicionales en la sintaxis de canalización

Con pocas excepciones, la sintaxis de tubería admite todos los operadores que la sintaxis estándar admite con la misma sintaxis. Además, la sintaxis de canalización introduce operadores de canalización adicionales y usa una sintaxis modificada para las agregaciones y las uniones. En las siguientes secciones, se explican estos operadores. Para ver todos los operadores admitidos, consulta la lista completa de operadores de canalización.

Operador de canalización `EXTEND`

El operador de canalización EXTEND te permite agregar columnas calculadas a la tabla actual. El operador de canalización EXTEND es similar a la instrucción SELECT *, new_column, pero te brinda más flexibilidad para hacer referencia a los alias de columna.

Considera la siguiente tabla que contiene dos calificaciones de prueba para cada persona:

CREATE OR REPLACE TABLE mydataset.Scores AS (
  SELECT 'Alex' AS student, 9 AS score1, 10 AS score2, 10 AS points_possible
  UNION ALL
  SELECT 'Dana' AS student, 5 AS score1, 7 AS score2, 10 AS points_possible);

/*---------+--------+--------+-----------------+
 | student | score1 | score2 | points_possible |
 +---------+--------+--------+-----------------+
 | Alex    | 9      | 10     | 10              |
 | Dana    | 5      | 7      | 10              |
 +---------+--------+--------+-----------------*/

Supongamos que deseas calcular la puntuación bruta promedio y el porcentaje promedio de la puntuación que obtuvo cada estudiante en la prueba. En la sintaxis estándar, las columnas posteriores en una declaración SELECT no tienen visibilidad de los alias anteriores. Para evitar una subconsulta, debes repetir la expresión del promedio:

SELECT student,
  (score1 + score2) / 2 AS average_score,
  (score1 + score2) / 2 / points_possible AS average_percent
FROM mydataset.Scores;

El operador de canalización EXTEND puede hacer referencia a alias usados anteriormente, lo que facilita la lectura de la consulta y reduce la probabilidad de errores:

FROM mydataset.Scores
|> EXTEND (score1 + score2) / 2 AS average_score
|> EXTEND average_score / points_possible AS average_percent
|> SELECT student, average_score, average_percent;

/*---------+---------------+-----------------+
 | student | average_score | average_percent |
 +---------+---------------+-----------------+
 | Alex    | 9.5           | .95             |
 | Dana    | 6.0           | 0.6             |
 +---------+---------------+-----------------*/

Operador de canalización `SET`

El operador de canalización SET te permite reemplazar el valor de las columnas en la tabla actual. El operador de canalización SET es similar a la instrucción SELECT * REPLACE (expression AS column). Puedes hacer referencia al valor original calificando el nombre de la columna con un alias de tabla.

FROM (SELECT 3 AS x, 5 AS y)
|> SET x = 2 * x;

/*---+---+
 | x | y |
 +---+---+
 | 6 | 5 |
 +---+---*/

Operador de canalización `DROP`

El operador de canalización DROP te permite quitar columnas de la tabla actual. El operador de canalización DROP es similar a la instrucción SELECT * EXCEPT(column). Después de quitar una columna, aún puedes hacer referencia al valor original si calificas el nombre de la columna con un alias de tabla.

FROM (SELECT 1 AS x, 2 AS y) AS t
|> DROP x;

/*---+
 | y |
 +---+
 | 2 |
 +---*/

Operador de canalización `RENAME`

El operador de canalización RENAME te permite cambiar el nombre de las columnas de la tabla actual. El operador de canalización RENAME es similar a la instrucción SELECT * EXCEPT(old_column), old_column AS new_column.

FROM (SELECT 1 AS x, 2 AS y, 3 AS z) AS t
|> RENAME y AS w;

/*---+---+---+
 | x | w | z |
 +---+---+---+
 | 1 | 2 | 3 |
 +---+---+---*/

Operador de canalización `AGGREGATE`

Para realizar la agregación en la sintaxis de canalización, usa el operador de canalización AGGREGATE, seguido de cualquier cantidad de funciones de agregación y, luego, una cláusula GROUP BY. No es necesario repetir las columnas en una cláusula SELECT.

En los ejemplos de esta sección, se usa la tabla Produce:

CREATE OR REPLACE TABLE mydataset.Produce AS (
  SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
  UNION ALL
  SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);

/*---------+-------+-----------+
 | item    | sales | category  |
 +---------+-------+-----------+
 | apples  | 7     | fruit     |
 | apples  | 2     | fruit     |
 | carrots | 0     | vegetable |
 | bananas | 15    | fruit     |
 +---------+-------+-----------*/

FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP BY item, category;

/*---------+-----------+-------+-------------+
 | item    | category  | total | num_records |
 +---------+-----------+-------+-------------+
 | apples  | fruit     | 9     | 2           |
 | carrots | vegetable | 0     | 1           |
 | bananas | fruit     | 15    | 1           |
 +---------+-----------+-------+-------------*/

Si deseas ordenar los resultados inmediatamente después de la agregación, puedes marcar las columnas en la cláusula GROUP BY que deseas ordenar con ASC o DESC. Las columnas sin marcar no se ordenan.

Si deseas ordenar todas las columnas, puedes reemplazar la cláusula GROUP BY por una cláusula GROUP AND ORDER BY, que ordena todas las columnas en orden ascendente de forma predeterminada. Puedes especificar DESC después de las columnas que deseas ordenar de forma descendente. Por ejemplo, las siguientes tres consultas son equivalentes:

-- Use a separate ORDER BY clause.
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP BY category, item
|> ORDER BY category DESC, item;

-- Explicitly mark how to order columns in the GROUP BY clause.
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP BY category DESC, item ASC;

-- Only mark descending columns in the GROUP AND ORDER BY clause.
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP AND ORDER BY category DESC, item;

La ventaja de usar una cláusula GROUP AND ORDER BY es que no tienes que repetir los nombres de las columnas en dos lugares.

Para realizar la agregación de la tabla completa, usa GROUP BY() o omite la cláusula GROUP BY por completo:

FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records;

/*-------+-------------+
 | total | num_records |
 +-------+-------------+
 | 24    | 4           |
 +-------+-------------*/

Operador de canalización `JOIN`

El operador de canalización JOIN te permite unir la tabla actual con otra tabla y admite las operaciones de unión estándar, incluidas CROSS, INNER, LEFT, RIGHT y FULL.

En los siguientes ejemplos, se hace referencia a las tablas Produce y ItemData:

CREATE OR REPLACE TABLE mydataset.Produce AS (
  SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
  UNION ALL
  SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);

CREATE OR REPLACE TABLE mydataset.ItemData AS (
  SELECT 'apples' AS item, '123' AS id
  UNION ALL
  SELECT 'bananas' AS item, '456' AS id
  UNION ALL
  SELECT 'carrots' AS item, '789' AS id
);

En el siguiente ejemplo, se usa una cláusula USING y se evita la ambigüedad de la columna:

FROM mydataset.Produce
|> JOIN mydataset.ItemData USING(item)
|> WHERE item = 'apples';

/*--------+-------+----------+-----+
 | item   | sales | category | id  |
 +--------+-------+----------+-----+
 | apples | 2     | fruit    | 123 |
 | apples | 7     | fruit    | 123 |
 +--------+-------+----------+-----*/

Para hacer referencia a las columnas de la tabla actual, por ejemplo, para desambiguar columnas en una cláusula ON, debes asignar un alias a la tabla actual con el operador de barra vertical AS. De manera opcional, puedes asignar un alias a la tabla unida. Puedes hacer referencia a ambos alias después de los operadores de canalización posteriores:

FROM mydataset.Produce
|> AS produce_table
|> JOIN mydataset.ItemData AS item_table
   ON produce_table.item = item_table.item
|> WHERE produce_table.item = 'bananas'
|> SELECT item_table.item, sales, id;

/*---------+-------+-----+
 | item    | sales | id  |
 +---------+-------+-----+
 | bananas | 15    | 123 |
 +---------+-------+-----*/

El lado derecho de la unión no tiene visibilidad del lado izquierdo, lo que significa que no puedes unir la tabla actual consigo misma. Por ejemplo, la siguiente consulta falla:

-- This query doesn't work.
FROM mydataset.Produce
|> AS produce_table
|> JOIN produce_table AS produce_table_2 USING(item);

Para realizar una autounión con una tabla modificada, puedes usar una expresión de tabla común (CTE) dentro de una cláusula WITH.

WITH cte_table AS (
  FROM mydataset.Produce
  |> WHERE item = 'carrots'
)
FROM cte_table
|> JOIN cte_table AS cte_table_2 USING(item);

Ejemplo

Considera la siguiente tabla con información sobre los pedidos de los clientes:

CREATE OR REPLACE TABLE mydataset.CustomerOrders AS (
  SELECT 1 AS customer_id, 100 AS order_id, 'WA' AS state, 5 AS cost, 'clothing' AS item_type
  UNION ALL
  SELECT 1 AS customer_id, 101 AS order_id, 'WA' AS state, 20 AS cost, 'clothing' AS item_type
  UNION ALL
  SELECT 1 AS customer_id, 102 AS order_id, 'WA' AS state, 3 AS cost, 'food' AS item_type
  UNION ALL
  SELECT 2 AS customer_id, 103 AS order_id, 'NY' AS state, 16 AS cost, 'clothing' AS item_type
  UNION ALL
  SELECT 2 AS customer_id, 104 AS order_id, 'NY' AS state, 22 AS cost, 'housewares' AS item_type
  UNION ALL
  SELECT 2 AS customer_id, 104 AS order_id, 'WA' AS state, 45 AS cost, 'clothing' AS item_type
  UNION ALL
  SELECT 3 AS customer_id, 105 AS order_id, 'MI' AS state, 29 AS cost, 'clothing' AS item_type);

Supongamos que deseas saber, para cada estado y tipo de artículo, el importe promedio que gastan los clientes recurrentes. Podrías escribir la consulta de la siguiente manera:

SELECT state, item_type, AVG(total_cost) AS average
FROM
  (
    SELECT
      SUM(cost) AS total_cost,
      customer_id,
      state,
      item_type,
      COUNT(*) OVER (PARTITION BY customer_id) AS num_orders
    FROM mydataset.CustomerOrders
    GROUP BY customer_id, state, item_type
    QUALIFY num_orders > 1
  )
GROUP BY state, item_type
ORDER BY state DESC, item_type ASC;

Si lees la consulta de arriba abajo, te encuentras con la columna total_cost antes de que se haya definido. Incluso dentro de la subconsulta, lees los nombres de las columnas antes de ver de qué tabla provienen.

Para comprender esta consulta, se debe leer de adentro hacia afuera. Las columnas state y item_type se repiten varias veces en las cláusulas SELECT y GROUP BY, y luego, en la cláusula ORDER BY.

La siguiente consulta equivalente se escribe con la sintaxis de barra vertical:

FROM mydataset.CustomerOrders
|> AGGREGATE SUM(cost) AS total_cost, GROUP BY customer_id, state, item_type
|> EXTEND COUNT(*) OVER (PARTITION BY customer_id) AS num_orders
|> WHERE num_orders > 1
|> AGGREGATE AVG(total_cost) AS average GROUP BY state DESC, item_type ASC;

/*-------+------------+---------+
 | state | item_type  | average |
 +-------+------------+---------+
 | WA    | clothing   | 35.0    |
 | WA    | food       | 3.0     |
 | NY    | clothing   | 16.0    |
 | NY    | housewares | 22.0    |
 +-------+------------+---------*/

Con la sintaxis de canalización, puedes escribir la búsqueda para seguir los pasos lógicos que podrías pensar para resolver el problema original. Las líneas de sintaxis de la consulta corresponden a los siguientes pasos lógicos:

Comienza con la tabla de pedidos de los clientes.
Descubre cuánto gastó cada cliente en cada tipo de artículo por estado.
Cuenta la cantidad de pedidos de cada cliente.
Restringe los resultados a los clientes recurrentes.
Encuentra el importe promedio que gastan los clientes recurrentes en cada estado y tipo de artículo.

Limitaciones

No puedes incluir una cláusula de privacidad diferencial en una declaración SELECT después de un operador de canalización. En su lugar, usa una cláusula de privacidad diferencial en sintaxis estándar y aplica operadores de canalización después de la consulta.
No puedes usar una ventana con nombre en la sintaxis de canalización.

¿Qué sigue?

Cómo analizar datos con la sintaxis de tuberías
Referencia de la sintaxis de consultas con canalizaciones
Referencia de la sintaxis de consultas estándar
Artículo de la conferencia VLDB 2024 sobre la sintaxis de canalización

Trabaja con la sintaxis de consulta de tuberías Organiza tus páginas con colecciones Guarda y categoriza el contenido según tus preferencias.