Sintaxis de tubería

La sintaxis de barra vertical 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.

Para inscribir un proyecto en la vista previa de la sintaxis de barra, completa el formulario de inscripción de la sintaxis de barra de BigQuery.

Descripción general

Puedes usar la sintaxis de barra en cualquier lugar donde escribas GoogleSQL. La sintaxis de barra admite las mismas operaciones que la sintaxis existente de GoogleSQL 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 barra 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 realizan para compilar la tabla de resultados.

Las consultas que usan la sintaxis de barra se valoran, ejecutan y optimizan de la misma manera que sus consultas equivalentes de sintaxis estándar. Cuando escribas consultas con la sintaxis de virgulillas, 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 tuberías aborda estos problemas:

Sintaxis estándar Sintaxis de tubería
Las cláusulas deben aparecer en un orden determinado. Los operadores de barra 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. Por lo general, las consultas más complejas se expresan 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 incluir una vez por agregación.

Sintaxis básica

En la sintaxis de tubería, las consultas comienzan con una consulta estándar de SQL o una cláusula FROM. Por ejemplo, una cláusula FROM independiente, como FROM mydataset.mytable, es una sintaxis de barra válida. Luego, el resultado de la consulta en SQL estándar o la tabla de la cláusula FROM se puede pasar como entrada a un símbolo de barra, |>, seguido de un nombre de operador de barra y cualquier argumento para 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 cualquier cantidad de operadores de barra en tu consulta para realizar acciones como seleccionar, ordenar, filtrar, unir o agregar columnas. Los nombres de los operadores de barra coinciden con sus contrapartes de sintaxis estándar y, por lo general, tienen el mismo comportamiento. La diferencia principal entre la sintaxis estándar y la sintaxis de barra vertical es la forma en que estructuras tu consulta. A medida que la lógica que expresa tu consulta se vuelve más compleja, esta aún se puede expresar como una secuencia lineal de operadores de barra, sin usar subconsultas anidadas profundamente, lo que facilita su lectura y razonamiento.

Considera la siguiente tabla:

CREATE 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 un formato de barra vertical válido que muestra cómo puedes compilar una consulta de forma secuencial.

Las consultas pueden comenzar con una cláusula FROM y no es necesario que contengan un símbolo de barra:

-- 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 barra 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 barra 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 barra AGGREGATE y no está separada por una barra (|>). 

-- 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 TABLE mydataset.item_data 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 barra 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.item_data USING(item);

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

La sintaxis de tuberías tiene las siguientes características clave:

  • Los operadores de tuberías se pueden aplicar en cualquier orden y cualquier cantidad de veces.
  • La sintaxis de barra funciona en cualquier lugar donde se admita la sintaxis estándar: consultas, vistas, funciones de valor 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 de la consulta principal.
  • Un operador de barra puede ver todos los alias que existen en la tabla que precede a la barra.

Diferencias clave con la sintaxis estándar

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

  • Las consultas 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.
  • Los filtros siempre se realizan con el operador de barra WHERE, que se puede aplicar en cualquier lugar. El operador de barra WHERE, que reemplaza a HAVING y QUALIFY, puede filtrar los resultados de las funciones de agregación o analíticas.

Para obtener más información y una lista completa de operadores de barra, consulta Sintaxis de consulta de barra.

Casos de uso

Estos son algunos casos de uso comunes para la sintaxis de tuberías:

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

Funciones adicionales en la sintaxis de tubería

Con pocas excepciones, la sintaxis de barra admite todos los operadores que la sintaxis estándar hace con la misma sintaxis. Además, la sintaxis de tubería introduce los siguientes operadores de tubería.

Operador de canalización EXTEND

El operador de barra EXTEND, que solo se puede usar inmediatamente después de un símbolo de barra, te permite agregar columnas calculadas a la tabla actual. El operador de barra EXTEND es similar a la sentencia 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 pruebas para cada persona:

CREATE 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 promedio sin procesar y la puntuación promedio porcentual que cada estudiante recibió en la prueba. En la sintaxis estándar, las columnas posteriores de una sentencia 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 barra 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 canalización SET

El operador de barra vertical SET, que solo se puede usar inmediatamente después de un símbolo de barra vertical, te permite reemplazar el valor de las columnas en la tabla actual. El operador de barra SET es similar a la sentencia SELECT * REPLACE (expression AS column). Para hacer referencia al valor original, califica 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 barra vertical DROP, que solo se puede usar inmediatamente después de un símbolo de barra vertical, te permite quitar columnas de la tabla actual. El operador de tubería DROP es similar a la sentencia SELECT * EXCEPT(column). Después de que se descarta una columna, 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 barra vertical RENAME, que solo se puede usar inmediatamente después de un símbolo de barra vertical, te permite cambiar el nombre de las columnas de la tabla actual. El operador de tubería RENAME es similar a la sentencia 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 tubería, 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 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 tienes todo listo para ordenar los resultados inmediatamente después de la agregación, puedes marcar las columnas de 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 de forma 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 completa de la tabla, usa GROUP BY() o omite por completo la cláusula GROUP BY:

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 barra JOIN te permite unir la tabla actual con otra y admite las operaciones de unión estándar, como CROSS, INNER, LEFT, RIGHT y FULL.

En los siguientes ejemplos, se hace referencia a las tablas produce y item_data:

CREATE 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 TABLE mydataset.item_data 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.item_data` 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, como para desambiguar las columnas en una cláusula ON, debes asignar un alias a la tabla actual con el operador de barra AS. De manera opcional, puedes asignar un alias a la tabla combinada. Puedes hacer referencia a ambos alias después de los operadores de barra siguientes:

FROM `mydataset.produce`
|> AS produce_table
|> JOIN `mydataset.item_data` 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 de la unión, lo que significa que no puedes unir la tabla actual con ella misma. Por ejemplo, la siguiente consulta fallará:

-- 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 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 TABLE mydataset.customer_orders 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 gastaron los clientes recurrentes. Puedes 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.customer_orders
    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 defina. 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 nuevamente en la cláusula ORDER BY.

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

FROM mydataset.customer_orders
|> 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 pensar para resolver el problema original. Las líneas de sintaxis en 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.
  • Limita los resultados a los clientes recurrentes.
  • Encuentra el importe promedio que invierten los clientes recurrentes en cada estado y tipo de artículo.

Limitaciones

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

¿Qué sigue?