Trabajar con la sintaxis de consulta de tubería

La sintaxis de consulta de tuberías 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. Puedes usar la sintaxis de barra vertical en cualquier lugar en el que escribas GoogleSQL.

La sintaxis de tuberías admite las mismas operaciones que la sintaxis de consulta de GoogleSQL o la sintaxis estándar, como la selección, la agregación y la agrupación, la unión y el filtrado, pero las operaciones se pueden aplicar en cualquier orden y cualquier número de veces. La estructura lineal de la sintaxis de tubería te permite escribir consultas de forma 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 barra vertical se cobran, ejecutan y optimizan de la misma forma que las consultas con sintaxis estándar equivalentes. Cuando escribas consultas con sintaxis de tubería, sigue las directrices para estimar los costes y optimizar el cálculo de las consultas.

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

Sintaxis estándar Sintaxis de tubería
Las cláusulas deben aparecer en un orden concreto. Los operadores de tubería se pueden aplicar en cualquier orden.
Las consultas más complejas, como las que tienen agregaciones de varios niveles, suelen requerir CTEs o subconsultas anidadas. Las consultas más complejas suelen expresarse añadiendo operadores de barra vertical 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 crear una consulta compleja paso a paso con la sintaxis de tubería, consulta Analizar datos con la sintaxis de tubería. Para obtener información detallada sobre la sintaxis, consulta la documentación de referencia de la sintaxis de consulta de tuberías.

Sintaxis básica

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

Puedes usar cualquier número de operadores de barra vertical en tu consulta para hacer cosas como seleccionar, ordenar, filtrar, unir o agregar columnas. Los nombres de los operadores de tubería 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 se estructura la consulta. Aunque la lógica expresada por tu consulta se vuelva más compleja, la consulta se puede seguir expresando como una secuencia lineal de operadores de tubería sin usar subconsultas anidadas, lo que facilita su lectura y comprensión.

La sintaxis de barra vertical tiene las siguientes características clave:

  • Cada operador de canalización de la sintaxis de canalización consta del símbolo de canalización, |>, un nombre de operador y los argumentos:
    |> operator_name argument_list
  • Los operadores de barra vertical se pueden añadir al final de cualquier consulta válida.
  • Los operadores de tubería se pueden aplicar en cualquier orden y cualquier número de veces.
  • La sintaxis de la barra vertical funciona en cualquier lugar donde se admita la sintaxis estándar: en consultas, vistas, funciones con valores de tabla y otros contextos.
  • La sintaxis de barra vertical se puede combinar con la sintaxis estándar en la misma consulta. Por ejemplo, las subconsultas pueden usar una sintaxis diferente a la de la consulta principal.
  • Un operador de barra vertical puede ver todos los alias que hay en la tabla que precede a la barra vertical.
  • Una consulta puede empezar con una cláusula FROM y, opcionalmente, se pueden añadir operadores de canalización después de la cláusula FROM.

Consulta 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 barra vertical válida que muestra cómo puedes crear una consulta de forma secuencial.

Las consultas pueden empezar por una cláusula FROM y no tienen por qué incluir el 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 el WHERE operador de barra vertical:

-- 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 una agregación, usa el operador de canalización AGGREGATE, seguido de cualquier número de funciones de agregación y, a continuación, una cláusula GROUP BY. La cláusula GROUP BY forma parte del operador de canalización AGGREGATE y no está separada por el 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         |
 +---------+-------------+-----------*/

Supongamos que tiene 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 JOIN operador de canalización para combinar los resultados de la consulta anterior con esta tabla e 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 respecto a la sintaxis estándar

La sintaxis de la barra vertical se diferencia de la sintaxis estándar en los siguientes aspectos:

Para obtener más información, consulta la lista completa de operadores de tubería.

Casos prácticos

Estos son algunos de los usos más habituales 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 la depuración de 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 de una consulta larga. El aumento de la productividad puede acelerar el proceso de desarrollo en toda tu organización.
  • Analíticas de registros: hay otros tipos de sintaxis similares a tuberías que son populares entre los usuarios de analíticas de registros. La sintaxis de tuberías proporciona una estructura familiar que simplifica la incorporación de esos usuarios a Analíticas de registros y BigQuery.

Funciones adicionales en la sintaxis de tubería

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

Operador de barra vertical EXTEND

El operador de canalización EXTEND te permite añadir columnas calculadas a la tabla actual. El operador de canalización EXTEND es similar a la instrucción SELECT *, new_column, pero te ofrece más flexibilidad a la hora de hacer referencia a alias de columnas.

Consulta la siguiente tabla, que contiene dos puntuaciones de prueba de 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 quieres calcular la puntuación bruta media y el porcentaje medio que ha obtenido cada alumno en el examen. En la sintaxis estándar, las columnas posteriores de una instrucción SELECT no tienen visibilidad de los alias anteriores. Para evitar una subconsulta, debes repetir la expresión de la media:

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 hace que la consulta sea más fácil de leer y menos propensa a 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 barra vertical SET

El operador de canalización SET te permite sustituir el valor de las columnas de la tabla actual. El operador de SET tubería 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 barra vertical DROP

El operador de canalización DROP te permite quitar columnas de la tabla actual. El operador de DROP tubería es similar a la instrucción SELECT * EXCEPT(column). Aunque se haya eliminado una columna, puedes seguir haciendo 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 barra vertical RENAME

El operador de canalización RENAME te permite cambiar el nombre de las columnas de la tabla actual. El operador de RENAME tubería 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 barra vertical AGGREGATE

Para realizar agregaciones con la sintaxis de tubería, usa el operador de tubería AGGREGATE, seguido de cualquier número de funciones de agregación y, a continuación, una cláusula GROUP BY. No es necesario repetir 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 quieres ordenar los resultados inmediatamente después de la agregación, puedes marcar las columnas de la cláusula GROUP BY que quieras ordenar con ASC o DESC. Las columnas sin marcar no se ordenan.

Si quiere ordenar todas las columnas, puede sustituir la cláusula GROUP BY por una cláusula GROUP AND ORDER BY, que ordena todas las columnas en orden ascendente de forma predeterminada. Puede especificar DESC después de las columnas por las que quiera ordenar los datos en orden descendente. Por ejemplo, las tres consultas siguientes 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 sitios.

Para realizar una agregación de 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 barra vertical JOIN

El operador de canalización JOIN te permite combinar 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 las columnas:

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 mediante el operador de AS barra vertical. También puedes asignar un alias a la tabla combinada. Puedes hacer referencia a ambos alias con los operadores de tubería 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 combinación no tiene visibilidad del lado izquierdo, lo que significa que no puedes combinar 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 autocombinació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

Consulta 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 quieres saber, para cada estado y tipo de artículo, la cantidad media gastada por 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 encontrarás con la columna total_cost antes de que se haya definido. Incluso en la subconsulta, se leen los nombres de las columnas antes de ver de qué tabla proceden.

Para entender esta consulta, debe leerse de dentro hacia fuera. Las columnas state y item_type se repiten varias veces en las cláusulas SELECT y GROUP BY, y de nuevo 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 barra vertical, puedes escribir la consulta para seguir los pasos lógicos que podrías seguir para resolver el problema original. Las líneas de sintaxis de la consulta corresponden a los siguientes pasos lógicos:

  • Empieza con la tabla de pedidos de clientes.
  • Descubre cuánto ha gastado cada cliente en cada tipo de artículo por estado.
  • Cuenta el número de pedidos de cada cliente.
  • Restringe los resultados a los clientes recurrentes.
  • Calcula el importe medio 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 instrucción SELECT después de un operador de barra vertical. En su lugar, usa una cláusula de privacidad diferencial con sintaxis estándar y aplica operadores de barra vertical después de la consulta.

Siguientes pasos