Las instrucciones de consulta analizan una o más tablas o expresiones y muestran las filas de resultados calculados. En este tema se describe la sintaxis para consultas de SQL en BigQuery.
Sintaxis de SQL
query_statement: query_expr query_expr: [ WITH [ RECURSIVE ] { non_recursive_cte | recursive_cte }[, ...] ] { select | ( query_expr ) | set_operation } [ ORDER BY expression [{ ASC | DESC }] [, ...] ] [ LIMIT count [ OFFSET skip_rows ] ] select: SELECT [ { ALL | DISTINCT } ] [ AS { STRUCT | VALUE } ] select_list [ FROM from_clause[, ...] ] [ WHERE bool_expression ] [ GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) } ] [ HAVING bool_expression ] [ QUALIFY bool_expression ] [ WINDOW window_clause ]
Reglas de notación
- Los corchetes “[ ]” indican cláusulas opcionales.
- Los paréntesis “( )” indican paréntesis literales.
- La barra vertical “|” indica un OR lógico.
- Las llaves “{ }” encierran un conjunto de opciones.
- Una coma seguida de puntos suspensivos entre corchetes “[, … ]” indica que el elemento anterior se puede repetir en una lista separada por comas.
Tablas de muestra
Las siguientes tablas se usan para ilustrar el comportamiento de distintas cláusulas de consulta en esta referencia.
Tabla Roster
En la tabla Roster
, se incluye una lista de nombres de jugadores (LastName
) y el ID único asignado a su escuela (SchoolID
). El resultado será el siguiente:
+-----------------------+
| LastName | SchoolID |
+-----------------------+
| Adams | 50 |
| Buchanan | 52 |
| Coolidge | 52 |
| Davis | 51 |
| Eisenhower | 77 |
+-----------------------+
Puedes usar esta cláusula WITH
con el fin de emular un nombre de tabla temporal para los ejemplos de esta referencia:
WITH Roster AS
(SELECT 'Adams' as LastName, 50 as SchoolID UNION ALL
SELECT 'Buchanan', 52 UNION ALL
SELECT 'Coolidge', 52 UNION ALL
SELECT 'Davis', 51 UNION ALL
SELECT 'Eisenhower', 77)
SELECT * FROM Roster
Tabla PlayerStats
En la tabla PlayerStats
, se incluye una lista de nombres de jugadores (LastName
), el ID único asignado al oponente contra el que jugaron en un partido determinado (OpponentID
) y la cantidad de puntos marcados por cada atleta en ese partido (PointsScored
).
+----------------------------------------+
| LastName | OpponentID | PointsScored |
+----------------------------------------+
| Adams | 51 | 3 |
| Buchanan | 77 | 0 |
| Coolidge | 77 | 1 |
| Adams | 52 | 4 |
| Buchanan | 50 | 13 |
+----------------------------------------+
Puedes usar esta cláusula WITH
con el fin de emular un nombre de tabla temporal para los ejemplos de esta referencia:
WITH PlayerStats AS
(SELECT 'Adams' as LastName, 51 as OpponentID, 3 as PointsScored UNION ALL
SELECT 'Buchanan', 77, 0 UNION ALL
SELECT 'Coolidge', 77, 1 UNION ALL
SELECT 'Adams', 52, 4 UNION ALL
SELECT 'Buchanan', 50, 13)
SELECT * FROM PlayerStats
Tabla TeamMascot
En la tabla TeamMascot
, se incluye una lista de los ID escolares únicos (SchoolID
) y la mascota de esa escuela (Mascot
).
+---------------------+
| SchoolID | Mascot |
+---------------------+
| 50 | Jaguars |
| 51 | Knights |
| 52 | Lakers |
| 53 | Mustangs |
+---------------------+
Puedes usar esta cláusula WITH
con el fin de emular un nombre de tabla temporal para los ejemplos de esta referencia:
WITH TeamMascot AS
(SELECT 50 as SchoolID, 'Jaguars' as Mascot UNION ALL
SELECT 51, 'Knights' UNION ALL
SELECT 52, 'Lakers' UNION ALL
SELECT 53, 'Mustangs')
SELECT * FROM TeamMascot
Declaración SELECT
SELECT [ { ALL | DISTINCT } ] [ AS { STRUCT | VALUE } ] select_list select_list: { select_all | select_expression } [, ...] select_all: [ expression. ]* [ EXCEPT ( column_name [, ...] ) ] [ REPLACE ( expression [ AS ] column_name [, ...] ) ] select_expression: expression [ [ AS ] alias ]
La lista SELECT
define las columnas que mostrará la consulta. Las expresiones de la lista SELECT
se pueden referir a columnas en cualquiera de los from_item
en su cláusula FROM
correspondiente.
Cada elemento de la lista SELECT
es uno de los siguientes:
*
expression
expression.*
SELECT *
SELECT *
, conocida como seleccionar Destacar, genera una columna de salida para cada columna que es visible después de ejecutar la consulta completa.
SELECT * FROM (SELECT "apple" AS fruit, "carrot" AS vegetable);
+-------+-----------+
| fruit | vegetable |
+-------+-----------+
| apple | carrot |
+-------+-----------+
expression
de SELECT
Los elementos en una lista SELECT
pueden ser expresiones. Estas expresiones se evalúan en un solo valor y producen una columna de salida, con un alias
explícito opcional.
Si la expresión no tiene un alias explícito, de ser posible, recibe un alias implícito según las reglas de los alias implícitos. De lo contrario, la columna será anónima y no se podrá hacer referencia a ella por su nombre en ninguna otra parte de la consulta.
expression.*
de SELECT
Un elemento de una lista SELECT
también puede adoptar la forma de expression.*
. Esto produce una columna de salida para cada columna o campo de nivel superior expression
.
La expresión debe ser un alias de tabla o evaluar un solo valor de un tipo de datos con campos, como un STRUCT.
La siguiente consulta genera una columna de salida para cada columna en la tabla groceries
, con un alias g
.
WITH groceries AS
(SELECT "milk" AS dairy,
"eggs" AS protein,
"bread" AS grain)
SELECT g.*
FROM groceries AS g;
+-------+---------+-------+
| dairy | protein | grain |
+-------+---------+-------+
| milk | eggs | bread |
+-------+---------+-------+
Más ejemplos:
WITH locations AS
(SELECT STRUCT("Seattle" AS city, "Washington" AS state) AS location
UNION ALL
SELECT STRUCT("Phoenix" AS city, "Arizona" AS state) AS location)
SELECT l.location.*
FROM locations l;
+---------+------------+
| city | state |
+---------+------------+
| Seattle | Washington |
| Phoenix | Arizona |
+---------+------------+
WITH locations AS
(SELECT ARRAY<STRUCT<city STRING, state STRING>>[("Seattle", "Washington"),
("Phoenix", "Arizona")] AS location)
SELECT l.LOCATION[offset(0)].*
FROM locations l;
+---------+------------+
| city | state |
+---------+------------+
| Seattle | Washington |
+---------+------------+
SELECT * EXCEPT
Una declaración SELECT * EXCEPT
especifica los nombres de una o más columnas para que se excluyan del resultado. Todos los nombres de columna que coincidan se omiten de la salida.
WITH orders AS
(SELECT 5 as order_id,
"sprocket" as item_name,
200 as quantity)
SELECT * EXCEPT (order_id)
FROM orders;
+-----------+----------+
| item_name | quantity |
+-----------+----------+
| sprocket | 200 |
+-----------+----------+
SELECT * REPLACE
Una declaración SELECT * REPLACE
especifica una o más cláusulas expression AS identifier
. Cada identificador debe coincidir con el nombre de una columna de la declaración SELECT *
. En la lista de las columnas de salida, la columna que coincide con el identificador en una cláusula REPLACE
se reemplaza por la expresión en esa cláusula REPLACE
.
Una declaración SELECT * REPLACE
no cambia los nombres ni el orden de las columnas.
Sin embargo, puede cambiar el valor y el tipo de valor.
WITH orders AS
(SELECT 5 as order_id,
"sprocket" as item_name,
200 as quantity)
SELECT * REPLACE ("widget" AS item_name)
FROM orders;
+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5 | widget | 200 |
+----------+-----------+----------+
WITH orders AS
(SELECT 5 as order_id,
"sprocket" as item_name,
200 as quantity)
SELECT * REPLACE (quantity/2 AS quantity)
FROM orders;
+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5 | sprocket | 100 |
+----------+-----------+----------+
SELECT DISTINCT
Una declaración SELECT DISTINCT
descarta las filas duplicadas y muestra solo las filas restantes. SELECT DISTINCT
no puede mostrar las columnas de los siguientes tipos:
STRUCT
ARRAY
SELECT ALL
Una declaración SELECT ALL
muestra todas las filas, incluidas las filas duplicadas.
SELECT ALL
es el comportamiento predeterminado de SELECT
.
SELECT AS STRUCT
SELECT AS STRUCT expr [[AS] struct_field_name1] [,...]
Esto produce una tabla de valores con un tipo de fila STRUCT, en el SELECT
que los nombres y tipos de campo STRUCT coinciden con los nombres y tipos de columnas generados en la lista .
Ejemplo:
SELECT ARRAY(SELECT AS STRUCT 1 a, 2 b)
SELECT AS STRUCT
se puede usar en una subconsulta escalar o de arreglo para producir un solo tipo de STRUCT que agrupe varios valores. Las subconsultas escalares y de array (consulta las Subconsultas), por lo general, no pueden mostrar varias columnas, pero pueden mostrar una sola columna con el tipo STRUCT.
SELECT AS VALUE
SELECT AS VALUE
genera una tabla de valores de cualquier lista SELECT
que produce exactamente una columna. En lugar de producir una tabla de salida de una columna, posiblemente con un nombre, la salida será una tabla de valores en la que el tipo de fila es solo el tipo de valor que se generó en la única columna SELECT
. Cualquier alias que tuviera la columna se descartará en la tabla de valores.
Ejemplo:
SELECT AS VALUE STRUCT(1 AS a, 2 AS b) xyz
La consulta anterior produce una tabla con el tipo de fila STRUCT<a int64, b int64>
.
Cláusula FROM
FROM from_clause[, ...] from_clause: from_item [ { pivot_operator | unpivot_operator } ] [ tablesample_operator ] from_item: { table_name [ as_alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ] | { join_operation | ( join_operation ) } | ( query_expr ) [ as_alias ] | field_path | unnest_operator | cte_name [ as_alias ] } as_alias: [ AS ] alias
La cláusula FROM
indica la tabla o tablas de las cuales se pueden recuperar filas y especifica cómo unir esas filas a fin de producir una sola secuencia de filas para procesar en el resto de la consulta.
pivot_operator
Consulta Operador PIVOT.
unpivot_operator
Consulta Operador UNPIVOT.
tablesample_operator
Consulta Operador TABLESAMPLE.
table_name
El nombre (calificado de forma opcional) de una tabla existente.
SELECT * FROM Roster; SELECT * FROM dataset.Roster; SELECT * FROM project.dataset.Roster;
FOR SYSTEM_TIME AS OF
FOR SYSTEM_TIME AS OF
hace referencia a las versiones históricas de la definición de la tabla y las filas que eran actuales a las timestamp_expression
.
Limitaciones:
La tabla de origen en la cláusula FROM
que contiene FOR SYSTEM_TIME AS OF
no debe ser ninguna de las siguientes opciones:
- Un análisis de
ARRAY
, incluido un arreglo plano o el resultado del operadorUNNEST
- Una expresión de tabla común definida por una cláusula
WITH
timestamp_expression
debe ser una expresión constante. No puede contener lo que se detalla a continuación:
- Subconsultas
- Referencias correlacionadas (referencias a las columnas de una tabla que aparecen en un nivel superior de la declaración de consulta, como en la lista
SELECT
) - Funciones definidas por el usuario (UDF)
El valor de timestamp_expression
no puede estar dentro de los siguientes rangos:
- Después de la marca de tiempo actual (en el futuro)
- Más de siete (7) días antes de la marca de tiempo actual.
Una declaración de consulta única no puede hacer referencia a una sola tabla en más de un punto en el tiempo, incluida la hora actual. Es decir, una consulta puede hacer referencia a una tabla varias veces en la misma marca de tiempo, pero no a la versión actual y una versión histórica o a dos versiones históricas diferentes.
Ejemplos:
La siguiente consulta muestra una versión histórica de la tabla de una hora antes.
SELECT *
FROM t
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);
La siguiente consulta muestra una versión histórica de la tabla en un momento determinado absoluto.
SELECT *
FROM t
FOR SYSTEM_TIME AS OF '2017-01-01 10:00:00-07:00';
La siguiente consulta muestra un error, ya que la timestamp_expression
contiene una referencia correlacionada con una columna en la consulta que la contiene.
SELECT *
FROM t1
WHERE t1.a IN (SELECT t2.a
FROM t2 FOR SYSTEM_TIME AS OF t1.timestamp_column);
En las siguientes operaciones, se muestra cómo acceder a una versión histórica de la tabla antes de reemplazarla.
DECLARE before_replace_timestamp TIMESTAMP;
-- Create table books.
CREATE TABLE books AS
SELECT 'Hamlet' title, 'William Shakespeare' author;
-- Get current timestamp before table replacement.
SET before_replace_timestamp = CURRENT_TIMESTAMP();
-- Replace table with different schema(title and release_date).
CREATE OR REPLACE TABLE books AS
SELECT 'Hamlet' title, DATE '1603-01-01' release_date;
-- This query returns Hamlet, William Shakespeare as result.
SELECT * FROM books FOR SYSTEM_TIME AS OF before_replace_timestamp;
En las siguientes operaciones, se muestra cómo acceder a una versión histórica de la tabla antes de un trabajo DML.
DECLARE JOB_START_TIMESTAMP TIMESTAMP;
-- Create table books.
CREATE OR REPLACE TABLE books AS
SELECT 'Hamlet' title, 'William Shakespeare' author;
-- Insert two rows into the books.
INSERT books (title, author)
VALUES('The Great Gatsby', 'F. Scott Fizgerald'),
('War and Peace', 'Leo Tolstoy');
SELECT * FROM books;
SET JOB_START_TIMESTAMP = (
SELECT start_time
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE job_type="QUERY"
AND statement_type="INSERT"
ORDER BY start_time DESC
LIMIT 1
);
-- This query only returns Hamlet, William Shakespeare as result.
SELECT * FROM books FOR SYSTEM_TIME AS OF JOB_START_TIMESTAMP;
La siguiente consulta muestra un error debido a que el DML opera en la versión actual de la tabla y en una versión histórica de la tabla de hace un día.
INSERT INTO t1
SELECT * FROM t1
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY);
join_operation
Consulta Operación JOIN.
query_expr
( query_expr ) [ [ AS ] alias ]
es una subconsulta de tabla.
field_path
En la cláusula FROM
, field_path
es cualquier ruta que se resuelva en un campo dentro de un tipo de datos. field_path
puede analizar una estructura de datos anidada, de manera arbitraria, en más profundidad.
Estos son algunos ejemplos de valores válidos de field_path
:
SELECT * FROM T1 t1, t1.array_column;
SELECT * FROM T1 t1, t1.struct_column.array_field;
SELECT (SELECT ARRAY_AGG(c) FROM t1.array_column c) FROM T1 t1;
SELECT a.struct_field1 FROM T1 t1, t1.array_of_structs a;
SELECT (SELECT STRING_AGG(a.struct_field1) FROM t1.array_of_structs a) FROM T1 t1;
Las rutas de campo en la cláusula FROM
deben finalizar en un campo de arreglo. Además, las rutas de campo no pueden contener arreglos antes del final de la ruta de acceso. Por ejemplo, la ruta array_column.some_array.some_array_field
no es válida, ya que contiene un arreglo antes del final de la ruta de acceso.
unnest_operator
Consulta Operador UNNEST.
cte_name
Las expresiones de tabla comunes (CTE) en una cláusula WITH
actúan como tablas temporales a las que puedes hacer referencia en cualquier parte de la cláusula FROM
.
En el siguiente ejemplo, subQ1
y subQ2
son CTE.
Ejemplo:
WITH
subQ1 AS (SELECT * FROM Roster WHERE SchoolID = 52),
subQ2 AS (SELECT SchoolID FROM subQ1)
SELECT DISTINCT * FROM subQ2;
La cláusula WITH
oculta las tablas permanentes con el mismo nombre mientras dura la consulta, a menos que califiques el nombre de la tabla, por ejemplo:
dataset.Roster
o project.dataset.Roster
.
Operador UNNEST
unnest_operator: { UNNEST( array_expression ) | UNNEST( array_path ) | array_path } [ as_alias ] [ WITH OFFSET [ as_alias ] ] as_alias: [AS] alias
El operador UNNEST
toma un ARRAY
y muestra una tabla con una fila para cada elemento en el ARRAY
. También puedes usar UNNEST
fuera de la cláusula FROM
con el operador IN
.
Para los ARRAY
de entrada de la mayoría de los tipos de elementos, la salida de UNNEST
, por lo general, tiene una columna. Esta columna única tiene un alias
opcional, que puedes usar para referirte a la columna en otras partes de la consulta. Los ARRAYS
con estos tipos de elementos muestran varias columnas:
- STRUCT
UNNEST
destruye el orden de los elementos en el ARRAY
de entrada. Usa la cláusula WITH OFFSET
opcional para mostrar una segunda columna con los índices de los elementos del arreglo.
Para ver varias formas de usar UNNEST
, incluida la construcción, la compactación y el filtrado, consulta Trabaja con arreglos.
UNNEST y STRUCTS
Para un ARRAY
de entrada de STRUCT
, UNNEST
muestra una fila de cada STRUCT
, con una columna aparte de cada campo en STRUCT
. El alias de cada columna es el nombre del campo STRUCT
correspondiente.
Ejemplo:
SELECT *
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')]);
+---+-----+
| x | y |
+---+-----+
| 3 | bar |
| 1 | foo |
+---+-----+
Como el operador UNNEST
muestra una tabla de valores, puedes ponerle un alias a UNNEST
para definir una variable de rango a la que puedas hacer referencia en otra parte de la consulta. Si haces referencia a la variable de rango en la lista SELECT
, la consulta muestra una STRUCT
que contiene todos los campos de la STRUCT
original en la tabla de entrada.
Ejemplo:
SELECT *, struct_value
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')])
AS struct_value;
+---+-----+--------------+
| x | y | struct_value |
+---+-----+--------------+
| 3 | bar | {3, bar} |
| 1 | foo | {1, foo} |
+---+-----+--------------+
UNNEST explícito e implícito
El desanidado de ARRAY
puede ser explícito o implícito.
En el desanidado explícito, array_expression
debe mostrar un valor ARRAY
, pero no es necesario que se resuelva en un ARRAY
y, además, se requiere la palabra clave UNNEST
.
Ejemplo:
SELECT * FROM UNNEST ([1, 2, 3]);
En el desanidado implícito, array_path
debe resolverse en un ARRAY
y la palabra clave UNNEST
es opcional.
Ejemplo:
SELECT x
FROM mytable AS t,
t.struct_typed_column.array_typed_field1 AS x;
En este caso, array_path
puede analizar de manera arbitraria y en detalle una estructura de datos, pero el último campo debe ser de tipo ARRAY
. Ningún campo anterior en la expresión puede ser de tipo ARRAY
, ya que no es posible extraer un campo con nombre de un ARRAY
.
UNNEST y NULLS
UNNEST
trata NULL
s de la siguiente manera:
NULL
y los arrays vacíos no producen filas.- Un array que contiene
NULL
s produce filas que contienen valoresNULL
.
UNNEST Y WITH OFFSET
La cláusula opcional WITH OFFSET
muestra una columna aparte que contiene el valor de offset, en el que el conteo comienza en cero para cada fila que genera la operación UNNEST
. Esta columna tiene un alias opcional. Si no se usa el alias opcional, el nombre predeterminado de la columna es offset
.
Ejemplo:
SELECT * FROM UNNEST ([10,20,30]) as numbers WITH OFFSET;
+---------+--------+
| numbers | offset |
+---------+--------+
| 10 | 0 |
| 20 | 1 |
| 30 | 2 |
+---------+--------+
Operador PIVOT
FROM from_item[, ...] pivot_operator pivot_operator: PIVOT( aggregate_function_call [as_alias][, ...] FOR input_column IN ( pivot_column [as_alias][, ...] ) ) [AS alias] as_alias: [AS] alias
El operador PIVOT
rota las filas en columnas mediante la agregación.
PIVOT
es parte de la cláusula FROM
.
PIVOT
se puede usar para modificar cualquier expresión de tabla.- No se permite combinar
PIVOT
conFOR SYSTEM_TIME AS OF
, aunque los usuarios pueden usarPIVOT
en una entrada de subconsulta que usaFOR SYSTEM_TIME AS OF
. - Una cláusula
WITH OFFSET
que precede de inmediato al operadorPIVOT
no está permitida.
Ejemplo conceptual:
-- Before PIVOT is used to rotate sales and quarter into Q1, Q2, Q3, Q4 columns:
+---------+-------+---------+------+
| product | sales | quarter | year |
+---------+-------+---------+------|
| Kale | 51 | Q1 | 2020 |
| Kale | 23 | Q2 | 2020 |
| Kale | 45 | Q3 | 2020 |
| Kale | 3 | Q4 | 2020 |
| Kale | 70 | Q1 | 2021 |
| Kale | 85 | Q2 | 2021 |
| Apple | 77 | Q1 | 2020 |
| Apple | 0 | Q2 | 2020 |
| Apple | 1 | Q1 | 2021 |
+---------+-------+---------+------+
-- After PIVOT is used to rotate sales and quarter into Q1, Q2, Q3, Q4 columns:
+---------+------+----+------+------+------+
| product | year | Q1 | Q2 | Q3 | Q4 |
+---------+------+----+------+------+------+
| Apple | 2020 | 77 | 0 | NULL | NULL |
| Apple | 2021 | 1 | NULL | NULL | NULL |
| Kale | 2020 | 51 | 23 | 45 | 3 |
| Kale | 2021 | 70 | 85 | NULL | NULL |
+---------+------+----+------+------+------+
Definiciones
Definiciones de nivel superior:
from_item
: La tabla o subconsulta en la que se realiza una operación pivot. Elfrom_item
debe seguir estas reglas.pivot_operator
: La operación pivot que se realiza en unfrom_item
.alias
: Un alias para usar en un elemento de la consulta.
Definiciones de pivot_operator
:
aggregate_function_call
: Una llamada a función agregada que agrega todas las filas de entrada de modo queinput_column
coincida con un valor particular enpivot_column
. Cada agregación correspondiente a un valorpivot_column
diferente produce una columna diferente en el resultado. Sigue estas reglas cuando crees una llamada a función agregada.input_column
: Toma una columna y recupera los valores de fila de esa columna, sigue estas reglas.pivot_column
: Una columna dinámica que se creará para cada llamada a función agregada. Si no se proporciona un alias, se crea uno predeterminado. Un tipo de valor de columna dinámica debe coincidir con el tipo de valor eninput_column
para que los valores se puedan comparar. Es posible tener un valor enpivot_column
que no coincida con un valor eninput_column
. Debe ser una constante y seguir estas reglas.
Reglas
Reglas para un from_item
que se pasan a PIVOT
:
- El
from_item
puede consistir en cualquier resultado de la tabla o subconsulta. - El
from_item
no puede producir una tabla de valores. - El
from_item
no puede ser una subconsulta que useSELECT AS STRUCT
.
Reglas para aggregate_function_call
:
- Debe ser una función agregada. Por ejemplo,
SUM
. - Puedes hacer referencia a columnas en una tabla que se pasa a
PIVOT
, así como a columnas correlacionadas, pero no puede acceder a columnas definidas por la cláusulaPIVOT
en sí. - Se puede acceder a una tabla que se pasa a
PIVOT
a través de su alias si se proporciona una. - Solo puedes usar una función agregada que tome un argumento.
- A excepción de
COUNT
, solo puedes usar funciones agregadas que ignoran las entradasNULL
. - Si usas
COUNT
, puedes usar*
como argumento.
- Puede acceder a las columnas de la tabla de entrada, así como a las columnas correlacionadas, no a las columnas definidas por la cláusula
PIVOT
. - Se evalúa con cada fila en la tabla de entrada. Están prohibidas las llamadas a funciones agregadas y analíticas.
- El término que no es determinismo es aceptable.
- El tipo debe ser agrupable.
- Se puede acceder a la tabla de entrada a través de su alias si se proporciona una.
- Una
pivot_column
debe ser una constante. - No se admiten constantes con nombre, como variables.
- No se admiten los parámetros de búsqueda.
- Si se requiere un nombre para una constante con nombre o parámetros de búsqueda, especifícalo de forma explícita con un alias.
- Existen casos excepcionales en los que diferentes
pivot_column
pueden terminar con los mismos nombres de columnas predeterminados. Por ejemplo, una columna de entrada puede contener un valor deNULL
y el literal de la string"NULL"
. Cuando esto sucede, se crean varias columnas dinámicas con el mismo nombre. A fin de evitar esta situación, usa alias para nombres de columnas dinámicas. - Si un objeto
pivot_column
no especifica un alias, se creará un nombre de columna de la siguiente manera:
Desde | Hasta | Ejemplo |
---|---|---|
NULL | NULL |
Entrada: NULL Resultado: “NULL” |
INT64 NUMERIC BIGNUMERIC |
El número en formato de string con las siguientes reglas:
|
Entrada: 1 Resultado: _1 Entrada: -1 Resultado: minus_1 Entrada: 1.0 Resultado: _1_point_0 |
BOOL | TRUE o FALSE . |
Entrada: TRUE Resultado: TRUE Entrada: FALSE Resultado: FALSE |
STRING | El valor de string. |
Entrada: “PlayerName” Resultado: PlayerName |
DATE | Es la fecha en formato _YYYY_MM_DD . |
Entrada: DATE “2013-11-25” Resultado: _2013_11_25 |
ENUM | El nombre de la constante de enumeración. |
Entrada: COLOR.RED Resultado: RED |
STRUCT |
Una string formada cuando se calcula el nombre de pivot_column para cada campo y se unen los resultados junto con un guion bajo. Se aplican las siguientes reglas:
Debido a la coerción de tipo implícita de los valores de lista |
Entrada: STRUCT(“one”, “two”) Resultado: one_two Entrada: STRUCT(“one” AS a, “two” AS b) Resultado: one_a_two_b |
Todos los demás tipos de datos | No compatible. Debes proporcionar un alias. |
Ejemplos
En los siguientes ejemplos, se hace referencia a una tabla llamada Produce
que se ve de la siguiente manera:
WITH Produce AS (
SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter, 2020 as year UNION ALL
SELECT 'Kale', 23, 'Q2', 2020 UNION ALL
SELECT 'Kale', 45, 'Q3', 2020 UNION ALL
SELECT 'Kale', 3, 'Q4', 2020 UNION ALL
SELECT 'Kale', 70, 'Q1', 2021 UNION ALL
SELECT 'Kale', 85, 'Q2', 2021 UNION ALL
SELECT 'Apple', 77, 'Q1', 2020 UNION ALL
SELECT 'Apple', 0, 'Q2', 2020 UNION ALL
SELECT 'Apple', 1, 'Q1', 2021)
SELECT * FROM Produce
+---------+-------+---------+------+
| product | sales | quarter | year |
+---------+-------+---------+------|
| Kale | 51 | Q1 | 2020 |
| Kale | 23 | Q2 | 2020 |
| Kale | 45 | Q3 | 2020 |
| Kale | 3 | Q4 | 2020 |
| Kale | 70 | Q1 | 2021 |
| Kale | 85 | Q2 | 2021 |
| Apple | 77 | Q1 | 2020 |
| Apple | 0 | Q2 | 2020 |
| Apple | 1 | Q1 | 2021 |
+---------+-------+---------+------+
Con el operador PIVOT
, las filas de la columna quarter
se rotan en estas columnas nuevas: Q1
, Q2
, Q3
, Q4
. La función agregada SUM
se agrupa de forma implícita por todas las columnas no agregadas que no sean pivot_column
: product
y year
.
SELECT * FROM
Produce
PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))
+---------+------+----+------+------+------+
| product | year | Q1 | Q2 | Q3 | Q4 |
+---------+------+----+------+------+------+
| Apple | 2020 | 77 | 0 | NULL | NULL |
| Apple | 2021 | 1 | NULL | NULL | NULL |
| Kale | 2020 | 51 | 23 | 45 | 3 |
| Kale | 2021 | 70 | 85 | NULL | NULL |
+---------+------+----+------+------+------+
Si no incluyes year
, SUM
se agrupa solo por product
.
SELECT * FROM
(SELECT product, sales, quarter FROM Produce)
PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))
+---------+-----+-----+------+------+
| product | Q1 | Q2 | Q3 | Q4 |
+---------+-----+-----+------+------+
| Apple | 78 | 0 | NULL | NULL |
| Kale | 121 | 108 | 45 | 3 |
+---------+-----+-----+------+------+
Puedes seleccionar un subconjunto de valores en la pivot_column
:
SELECT * FROM
(SELECT product, sales, quarter FROM Produce)
PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3')
+---------+-----+-----+------+
| product | Q1 | Q2 | Q3 |
+---------+-----+-----+------+
| Apple | 78 | 0 | NULL |
| Kale | 121 | 108 | 45 |
+---------+-----+-----+------+
SELECT * FROM
(SELECT sales, quarter FROM Produce)
PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3')
+-----+-----+----+
| Q1 | Q2 | Q3 |
+-----+-----+----+
| 199 | 108 | 45 |
+-----+-----+----+
Puedes incluir varias funciones de agregación en PIVOT
. En este caso, debes especificar un alias para cada agregación. Estos alias se usan para construir los nombres de las columnas en la tabla resultante.
SELECT * FROM
(SELECT product, sales, quarter FROM Produce)
PIVOT(SUM(sales) total_sales, COUNT(*) num_records FOR quarter IN ('Q1', 'Q2'))
+--------+----------------+----------------+----------------+----------------+
|product | total_sales_Q1 | num_records_Q1 | total_sales_Q2 | num_records_Q2 |
+--------+----------------+----------------+----------------+----------------+
| Kale | 121 | 2 | 108 | 2 |
| Apple | 78 | 2 | 0 | 1 |
+--------+----------------+----------------+----------------+----------------+
Operador UNPIVOT
FROM from_item[, ...] unpivot_operator unpivot_operator: UNPIVOT [ { INCLUDE NULLS | EXCLUDE NULLS } ] ( { single_column_unpivot | multi_column_unpivot } ) [unpivot_alias] single_column_unpivot: values_column FOR name_column IN (columns_to_unpivot) multi_column_unpivot: values_column_set FOR name_column IN (column_sets_to_unpivot) values_column_set: (values_column[, ...]) columns_to_unpivot: unpivot_column [row_value_alias][, ...] column_sets_to_unpivot: (unpivot_column [row_value_alias][, ...]) unpivot_alias and row_value_alias: [AS] alias
El operador UNPIVOT
rota las columnas en filas. UNPIVOT
es parte de la cláusula FROM
.
UNPIVOT
se puede usar para modificar cualquier expresión de tabla.- No se permite combinar
UNPIVOT
conFOR SYSTEM_TIME AS OF
, aunque los usuarios pueden usarUNPIVOT
en una entrada de subconsulta que en sí usaFOR SYSTEM_TIME AS OF
. - Una cláusula
WITH OFFSET
que precede de inmediato al operadorUNPIVOT
no está permitida. - Las agregaciones de
PIVOT
no se pueden revertir conUNPIVOT
.
Ejemplo conceptual:
-- Before UNPIVOT is used to rotate Q1, Q2, Q3, Q4 into sales and quarter columns:
+---------+----+----+----+----+
| product | Q1 | Q2 | Q3 | Q4 |
+---------+----+----+----+----+
| Kale | 51 | 23 | 45 | 3 |
| Apple | 77 | 0 | 25 | 2 |
+---------+----+----+----+----+
-- After UNPIVOT is used to rotate Q1, Q2, Q3, Q4 into sales and quarter columns:
+---------+-------+---------+
| product | sales | quarter |
+---------+-------+---------+
| Kale | 51 | Q1 |
| Kale | 23 | Q2 |
| Kale | 45 | Q3 |
| Kale | 3 | Q4 |
| Apple | 77 | Q1 |
| Apple | 0 | Q2 |
| Apple | 25 | Q3 |
| Apple | 2 | Q4 |
+---------+-------+---------+
Definiciones
Definiciones de nivel superior:
from_item
: La tabla o subconsulta en la que se realiza una operación pivot. Elfrom_item
debe seguir estas reglas.unpivot_operator
: La operación pivot que se realiza en unfrom_item
.
Definiciones de unpivot_operator
:
INCLUDE NULLS
: Agrega filas con valoresNULL
al resultado.EXCLUDE NULLS
: No agrega filas con valoresNULL
al resultado. De forma predeterminada,UNPIVOT
excluye las filas con valoresNULL
.single_column_unpivot
: Rota columnas en unvalues_column
y en unname_column
.multi_column_unpivot
: Rota columnas en variosvalues_column
yname_column
.unpivot_alias
: Es un alias para los resultados de la operaciónUNPIVOT
. Se puede hacer referencia a este alias en otra parte de la consulta.
Definiciones de single_column_unpivot
:
values_column
: Es una columna para contener los valores de fila decolumns_to_unpivot
. Sigue estas reglas cuando crees una columna de valores.name_column
: Es una columna que contiene los nombres de columna decolumns_to_unpivot
. Sigue estas reglas cuando crees una columna de nombres.columns_to_unpivot
: Las columnas defrom_item
a fin de propagarvalues_column
yname_column
. Sigue estas reglas cuando crees una anulación de dinamización de columnas.row_value_alias
: Es un alias opcional de una columna que se muestra para la columna enname_column
. Si no se especifica, se usa el valor de string del nombre de la columna. Sigue estas reglas cuando crees un alias de valor de fila.
Definiciones de multi_column_unpivot
:
values_column_set
: Es un conjunto de columnas que contiene los valores de fila decolumns_to_unpivot
. Sigue estas reglas cuando crees una columna de valores.name_column
: Es un conjunto de columnas para contener los nombres de columna decolumns_to_unpivot
Sigue estas reglas cuando crees una columna de nombres.column_sets_to_unpivot
: Las columnas defrom_item
para anular el dinamización. Sigue estas reglas cuando crees una anulación de dinamización de columnas.row_value_alias
: Es un alias opcional para un conjunto de columnas que se muestra en el conjunto de columnas dename_column
. Si no se especifica, se usa un valor de string para el conjunto de columnas, y cada columna de la string está separada por un guion bajo (_
). Por ejemplo,(col1, col2)
generacol1_col2
. Sigue estas reglas cuando crees un alias de valor de fila.
Reglas
Reglas para un from_item
que se pasan a UNPIVOT
:
- El
from_item
puede consistir en cualquier resultado de la tabla o subconsulta. - El
from_item
no puede producir una tabla de valores. - No se puede hacer referencia a las columnas duplicadas en una
from_item
en la cláusulaUNPIVOT
.
- No se permiten expresiones.
- No se permiten nombres calificados. Por ejemplo, no se permite
mytable.mycolumn
. - Si el resultado de
UNPIVOT
tiene nombres de columna duplicados, haz lo siguiente:- Se admite
SELECT *
. SELECT values_column
genera ambigüedad.
- Se admite
- No puede ser un nombre que se usa para una
name_column
o unaunpivot_column
. - Puede tener el mismo nombre que una columna de
from_item
.
- No puede ser un nombre que se usa para una
values_column
o unaunpivot_column
. - Puede tener el mismo nombre que una columna de
from_item
.
- Debe ser el nombre de una columna de
from_item
. - No puede hacer referencia a nombres de columnas de
from_item
duplicados. - Todas las columnas de un conjunto de columnas deben tener tipos de datos equivalentes.
- Los tipos de datos no se pueden coercionar a un supertipo común.
- Si los tipos de datos son coincidencias exactas (por ejemplo, un struct con nombres de campo diferentes), el tipo de datos de la primera entrada es el tipo de datos del resultado.
- No puedes tener el mismo nombre en el mismo conjunto de columnas. Por ejemplo,
(emp1, emp1)
genera un error. - Puede tener el mismo nombre en diferentes conjuntos de columnas. Por ejemplo,
(emp1, emp2), (emp1, emp3)
es válido.
- Puede ser un literal
STRING
oINT64
. - El tipo de datos para todas las cláusulas
row_value_alias
debe ser el mismo. - Si el valor es un
INT64
, se debe especificar elrow_value_alias
para cadaunpivot_column
.
Ejemplos
En los siguientes ejemplos, se hace referencia a una tabla llamada Produce
que se ve de la siguiente manera:
WITH Produce AS (
SELECT 'Kale' as product, 51 as Q1, 23 as Q2, 45 as Q3, 3 as Q4 UNION ALL
SELECT 'Apple', 77, 0, 25, 2)
SELECT * FROM Produce
+---------+----+----+----+----+
| product | Q1 | Q2 | Q3 | Q4 |
+---------+----+----+----+----+
| Kale | 51 | 23 | 45 | 3 |
| Apple | 77 | 0 | 25 | 2 |
+---------+----+----+----+----+
Con el operador UNPIVOT
, se rotan las columnas Q1
, Q2
, Q3
y Q4
. Los valores de estas columnas ahora propagan una columna nueva llamada Sales
y los nombres de estas columnas propagan una columna nueva llamada Quarter
.
Esta es una operación de anulación de dinamización de una sola columna.
SELECT * FROM Produce
UNPIVOT(sales FOR quarter IN (Q1, Q2, Q3, Q4))
+---------+-------+---------+
| product | sales | quarter |
+---------+-------+---------+
| Kale | 51 | Q1 |
| Kale | 23 | Q2 |
| Kale | 45 | Q3 |
| Kale | 3 | Q4 |
| Apple | 77 | Q1 |
| Apple | 0 | Q2 |
| Apple | 25 | Q3 |
| Apple | 2 | Q4 |
+---------+-------+---------+
En este ejemplo, se UNPIVOT
cuatro trimestres a dos semestres.
Esta es una operación de anulación de dinamización de varias columnas.
SELECT * FROM Produce
UNPIVOT(
(first_half_sales, second_half_sales)
FOR semesters
IN ((Q1, Q2) AS 'semester_1', (Q3, Q4) AS 'semester_2'))
+---------+------------------+-------------------+------------+
| product | first_half_sales | second_half_sales | semesters |
+---------+------------------+-------------------+------------+
| Kale | 51 | 23 | semester_1 |
| Kale | 45 | 3 | semester_2 |
| Apple | 77 | 0 | semester_1 |
| Apple | 25 | 2 | semester_2 |
+---------+------------------+-------------------+------------+
Operador TABLESAMPLE
TABLESAMPLE SYSTEM ( percent PERCENT )
Descripción
Puedes usar el operador TABLESAMPLE
para seleccionar una muestra aleatoria de un conjunto de datos.
Este operador es útil cuando trabajas con tablas que tienen grandes cantidades de datos y no necesitas respuestas precisas.
En el ejemplo, se muestra una variedad de registros y se evitan los costos asociados con el análisis y el procesamiento de una tabla completa. Cada ejecución de la consulta podría mostrar resultados diferentes, ya que cada ejecución procesa una muestra calculada de forma independiente. BigQuery no almacena en caché los resultados de las consultas que incluyen una cláusula TABLESAMPLE
.
Reemplaza percent
por el porcentaje del conjunto de datos que deseas incluir en los resultados. El valor debe ser de entre 0
y 100
. El valor puede ser un valor literal o un parámetro de consulta. No puede ser una variable.
Para obtener más información, consulta Muestreo de tablas.
Ejemplo
La siguiente consulta selecciona alrededor de un 10% de los datos de una tabla:
SELECT * FROM dataset.my_table TABLESAMPLE SYSTEM (10 PERCENT)
Operación JOIN
join_operation: { cross_join_operation | condition_join_operation } cross_join_operation: from_item cross_join_operator from_item condition_join_operation: from_item condition_join_operator from_item join_condition cross_join_operator: { CROSS JOIN | , } condition_join_operator: { [INNER] JOIN | FULL [OUTER] JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER] JOIN } join_condition: { on_clause | using_clause } on_clause: ON bool_expression using_clause: USING ( join_column [, ...] )
La cláusula JOIN
combina dos from_item
para que la cláusula SELECT
pueda consultarlos como una sola fuente. La cláusula join_type
y ON
o USING
(una “condición JOIN”) especifica cómo combinar y descartar las filas de los dos from_item
para formar una sola fuente.
[INNER] JOIN
Una INNER JOIN
, o tan solo JOIN
, calcula de manera efectiva el producto cartesiano de los dos from_item
y descarta todas las filas que no cumplen con la condición de unión. “De manera efectiva” significa que es posible implementar INNER JOIN
sin calcular el producto cartesiano.
FROM A INNER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------+
| 1 | a | | 2 | k | | 2 | b | 2 | k |
| 2 | b | | 3 | m | | 3 | c | 3 | m |
| 3 | c | | 3 | n | | 3 | c | 3 | n |
| 3 | d | | 4 | p | | 3 | d | 3 | m |
+-------+ +-------+ | 3 | d | 3 | n |
+---------------+
FROM A INNER JOIN B USING (x)
Table A Table B Result
+-------+ +-------+ +-----------+
| x | y | * | x | z | = | x | y | z |
+-------+ +-------+ +-----------+
| 1 | a | | 2 | k | | 2 | b | k |
| 2 | b | | 3 | m | | 3 | c | m |
| 3 | c | | 3 | n | | 3 | c | n |
| 3 | d | | 4 | p | | 3 | d | m |
+-------+ +-------+ | 3 | d | n |
+-----------+
Ejemplo
Mediante esta consulta, se realiza una INNER JOIN
en las tablas Roster
y TeamMascot
.
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;
+---------------------------+
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Buchanan | Lakers |
| Coolidge | Lakers |
| Davis | Knights |
+---------------------------+
CROSS JOIN
CROSS JOIN
muestra el producto cartesiano de los dos from_item
. En otras palabras, combina cada fila del primer from_item
con cada fila del segundo from_item
.
Si las filas de los dos from_item
son independientes, el resultado tiene M x N filas, si es que hay M filas en un from_item
y N en el otro. Ten en cuenta que esto se mantiene incluso si cualquiera de los dos from_item
tiene cero filas.
En una cláusula FROM
, se puede escribir una CROSS JOIN
como la siguiente:
FROM A CROSS JOIN B
Table A Table B Result
+-------+ +-------+ +---------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------+
| 1 | a | | 2 | c | | 1 | a | 2 | c |
| 2 | b | | 3 | d | | 1 | a | 3 | d |
+-------+ +-------+ | 2 | b | 2 | c |
| 2 | b | 3 | d |
+---------------+
Puedes usar una unión cruzada correlacionada para convertir o compactar un ARRAY
en un conjunto de filas. Para obtener más información, consulta Convierte elementos de un arreglo en filas de una tabla.
Ejemplos
Mediante esta consulta, se realiza una CROSS JOIN
en las tablas Roster
y TeamMascot
.
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot;
+---------------------------+
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Adams | Knights |
| Adams | Lakers |
| Adams | Mustangs |
| Buchanan | Jaguars |
| Buchanan | Knights |
| Buchanan | Lakers |
| Buchanan | Mustangs |
| ... |
+---------------------------+
Unión cruzada con coma (,)
CROSS JOIN
se pueden escribir de forma implícita con una coma. Esto se conoce como una unión cruzada con comas.
Una unión cruzada con coma se ve de la siguiente manera en una cláusula FROM
:
FROM A, B
Table A Table B Result
+-------+ +-------+ +---------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------+
| 1 | a | | 2 | c | | 1 | a | 2 | c |
| 2 | b | | 3 | d | | 1 | a | 3 | d |
+-------+ +-------+ | 2 | b | 2 | c |
| 2 | b | 3 | d |
+---------------+
No puedes escribir CROSS JOIN con comas entre paréntesis. Para obtener más información, consulta Operaciones de unión en una secuencia.
FROM (A, B) // INVALID
Puedes usar una unión cruzada con coma correlacionada para convertir o compactar un ARRAY
en un conjunto de filas. Para obtener más información, consulta Convierte elementos de un arreglo en filas de una tabla.
Ejemplos
Esta consulta realiza una unión cruzada con coma en las tablas Roster
y TeamMascot
.
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster, TeamMascot;
+---------------------------+
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Adams | Knights |
| Adams | Lakers |
| Adams | Mustangs |
| Buchanan | Jaguars |
| Buchanan | Knights |
| Buchanan | Lakers |
| Buchanan | Mustangs |
| ... |
+---------------------------+
FULL [OUTER] JOIN
Una FULL OUTER JOIN
(o simplemente FULL JOIN
) muestra todos los campos de todas las filas de ambos from_item
que cumplen con la condición JOIN.
FULL
indica que todas las filas de ambos from_item
se muestran, incluso si no cumplen con la condición JOIN.
OUTER
indica que si una fila determinada de un from_item
no se une a ninguna fila del otro from_item
, la fila mostrará NULL para todas las columnas del otro from_item
.
FROM A FULL OUTER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------------------+
| 1 | a | | 2 | k | | 1 | a | NULL | NULL |
| 2 | b | | 3 | m | | 2 | b | 2 | k |
| 3 | c | | 3 | n | | 3 | c | 3 | m |
| 3 | d | | 4 | p | | 3 | c | 3 | n |
+-------+ +-------+ | 3 | d | 3 | m |
| 3 | d | 3 | n |
| NULL | NULL | 4 | p |
+---------------------------+
FROM A FULL OUTER JOIN B USING (x)
Table A Table B Result
+-------+ +-------+ +--------------------+
| x | y | * | x | z | = | x | y | z |
+-------+ +-------+ +--------------------+
| 1 | a | | 2 | k | | 1 | a | NULL |
| 2 | b | | 3 | m | | 2 | b | k |
| 3 | c | | 3 | n | | 3 | c | m |
| 3 | d | | 4 | p | | 3 | c | n |
+-------+ +-------+ | 3 | d | m |
| 3 | d | n |
| 4 | NULL | p |
+--------------------+
Ejemplo
Mediante esta consulta, se realiza una FULL JOIN
en las tablas Roster
y TeamMascot
.
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster FULL JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;
+---------------------------+
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Buchanan | Lakers |
| Coolidge | Lakers |
| Davis | Knights |
| Eisenhower | NULL |
| NULL | Mustangs |
+---------------------------+
LEFT [OUTER] JOIN
El resultado de una LEFT OUTER JOIN
(o tan solo LEFT JOIN
) para dos from_item
siempre conserva todas las filas del from_item
de la izquierda en la cláusula JOIN
, incluso si ninguna fila en el from_item
de la derecha satisface el predicado de JOIN.
LEFT
indica que se muestran todas las filas from_item
de la izquierda. Si una fila determinada del from_item
de la izquierda no se une a ninguna fila del from_item
de la derecha, se mostrarán en la fila todas las columnas del from_item
de la derecha como NULL. Las filas del from_item
de la derecha que no se unen a ninguna fila del from_item
de la izquierda se descartan.
FROM A LEFT OUTER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------------------+
| 1 | a | | 2 | k | | 1 | a | NULL | NULL |
| 2 | b | | 3 | m | | 2 | b | 2 | k |
| 3 | c | | 3 | n | | 3 | c | 3 | m |
| 3 | d | | 4 | p | | 3 | c | 3 | n |
+-------+ +-------+ | 3 | d | 3 | m |
| 3 | d | 3 | n |
+---------------------------+
FROM A LEFT OUTER JOIN B USING (x)
Table A Table B Result
+-------+ +-------+ +--------------------+
| x | y | * | x | z | = | x | y | z |
+-------+ +-------+ +--------------------+
| 1 | a | | 2 | k | | 1 | a | NULL |
| 2 | b | | 3 | m | | 2 | b | k |
| 3 | c | | 3 | n | | 3 | c | m |
| 3 | d | | 4 | p | | 3 | c | n |
+-------+ +-------+ | 3 | d | m |
| 3 | d | n |
+--------------------+
Ejemplo
Mediante esta consulta, se realiza una LEFT JOIN
en las tablas Roster
y TeamMascot
.
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster LEFT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;
+---------------------------+
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Buchanan | Lakers |
| Coolidge | Lakers |
| Davis | Knights |
| Eisenhower | NULL |
+---------------------------+
RIGHT [OUTER] JOIN
El resultado de una RIGHT OUTER JOIN
(o tan solo RIGHT JOIN
) es similar y simétrico al de una LEFT OUTER JOIN
.
FROM A RIGHT OUTER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------------------+
| 1 | a | | 2 | k | | 2 | b | 2 | k |
| 2 | b | | 3 | m | | 3 | c | 3 | m |
| 3 | c | | 3 | n | | 3 | c | 3 | n |
| 3 | d | | 4 | p | | 3 | d | 3 | m |
+-------+ +-------+ | 3 | d | 3 | n |
| NULL | NULL | 4 | p |
+---------------------------+
FROM A RIGHT OUTER JOIN B USING (x)
Table A Table B Result
+-------+ +-------+ +--------------------+
| x | y | * | x | z | = | x | y | z |
+-------+ +-------+ +--------------------+
| 1 | a | | 2 | k | | 2 | b | k |
| 2 | b | | 3 | m | | 3 | c | m |
| 3 | c | | 3 | n | | 3 | c | n |
| 3 | d | | 4 | p | | 3 | d | m |
+-------+ +-------+ | 3 | d | n |
| 4 | NULL | p |
+--------------------+
Ejemplo
Mediante esta consulta, se realiza una RIGHT JOIN
en las tablas Roster
y TeamMascot
.
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster RIGHT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;
+---------------------------+
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Buchanan | Lakers |
| Coolidge | Lakers |
| Davis | Knights |
| NULL | Mustangs |
+---------------------------+
Cláusula ON
Una fila combinada (el resultado de la unión de dos filas) cumple con la condición de unión ON
si la condición de unión muestra TRUE
.
FROM A JOIN B ON A.x = B.x
Table A Table B Result (A.x, B.x)
+---+ +---+ +-------+
| x | * | x | = | x | x |
+---+ +---+ +-------+
| 1 | | 2 | | 2 | 2 |
| 2 | | 3 | | 3 | 3 |
| 3 | | 4 | +-------+
+---+ +---+
Ejemplo
Mediante esta consulta, se realiza una INNER JOIN
en las tablas Roster
y TeamMascot
.
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;
+---------------------------+
| LastName | Mascot |
+---------------------------+
| Adams | Jaguars |
| Buchanan | Lakers |
| Coolidge | Lakers |
| Davis | Knights |
+---------------------------+
Cláusula USING
La cláusula USING
requiere una lista de columnas de una o más columnas que aparecen en ambas tablas de entrada. Realiza una comparación de igualdad en esa columna, y las filas cumplen la condición de unión si la comparación de igualdad muestra TRUE
.
FROM A JOIN B USING (x)
Table A Table B Result
+---+ +---+ +---+
| x | * | x | = | x |
+---+ +---+ +---+
| 1 | | 2 | | 2 |
| 2 | | 3 | | 3 |
| 3 | | 4 | +---+
+---+ +---+
Ejemplo
Mediante esta consulta, se realiza una INNER JOIN
en las tablas Roster
y TeamMascot
.
En esta declaración, se muestran las filas de Roster
y TeamMascot
en las que Roster.SchoolID
es lo mismo que TeamMascot.SchoolID
. En los resultados, se incluye una sola columna SchoolID
.
SELECT * FROM Roster INNER JOIN TeamMascot USING (SchoolID);
+----------------------------------------+
| SchoolID | LastName | Mascot |
+----------------------------------------+
| 50 | Adams | Jaguars |
| 52 | Buchanan | Lakers |
| 52 | Coolidge | Lakers |
| 51 | Davis | Knights |
+----------------------------------------+
Equivalencia entre USING y ON
Las palabras clave USING
y ON
no son equivalentes, pero son similares.
Mediante ON
, se muestran varias columnas, y mediante USING
, se muestra una.
FROM A JOIN B ON A.x = B.x
FROM A JOIN B USING (x)
Table A Table B Result ON Result USING
+---+ +---+ +-------+ +---+
| x | * | x | = | x | x | | x |
+---+ +---+ +-------+ +---+
| 1 | | 2 | | 2 | 2 | | 2 |
| 2 | | 3 | | 3 | 3 | | 3 |
| 3 | | 4 | +-------+ +---+
+---+ +---+
Si bien ON
y USING
no son equivalentes, pueden mostrar los mismos resultados si especificas las columnas que deseas mostrar.
SELECT x FROM A JOIN B USING (x);
SELECT A.x FROM A JOIN B ON A.x = B.x;
Table A Table B Result
+---+ +---+ +---+
| x | * | x | = | x |
+---+ +---+ +---+
| 1 | | 2 | | 2 |
| 2 | | 3 | | 3 |
| 3 | | 4 | +---+
+---+ +---+
Operaciones de unión en una secuencia
La cláusula FROM
puede contener varias cláusulas JOIN
en una secuencia. Las JOIN
están vinculadas de izquierda a derecha. Por ejemplo:
FROM A JOIN B USING (x) JOIN C USING (x)
-- A JOIN B USING (x) = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2 = return value
También puedes insertar paréntesis para agrupar las JOIN
:
FROM ( (A JOIN B USING (x)) JOIN C USING (x) )
-- A JOIN B USING (x) = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2 = return value
Mediante los paréntesis, puedes agrupar las JOIN
para que se vinculen en un orden diferente:
FROM ( A JOIN (B JOIN C USING (x)) USING (x) )
-- B JOIN C USING (x) = result_1
-- A JOIN result_1 = result_2
-- result_2 = return value
Una cláusula FROM
puede tener varias uniones. Siempre que no haya uniones cruzadas con coma en la cláusula FROM
, las uniones no requieren paréntesis, aunque los paréntesis pueden ayudar a facilitar la lectura:
FROM A JOIN B JOIN C JOIN D USING (w) ON B.x = C.y ON A.z = B.x
Si la cláusula contiene uniones cruzadas con coma, debes usar paréntesis:
FROM A, B JOIN C JOIN D ON C.x = D.y ON B.z = C.x // INVALID
FROM A, B JOIN (C JOIN D ON C.x = D.y) ON B.z = C.x // VALID
Cuando hay uniones cruzadas con comas en una consulta que tiene una secuencia de JOIN, se agrupan de izquierda a derecha como los otros tipos de JOIN
:
FROM A JOIN B USING (x) JOIN C USING (x), D
-- A JOIN B USING (x) = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2 CROSS JOIN D = return value
No puede haber una RIGHT JOIN
o una FULL JOIN
después de una unión cruzada con coma, a menos que tenga paréntesis:
FROM A, B RIGHT JOIN C ON TRUE // INVALID
FROM A, B FULL JOIN C ON TRUE // INVALID
FROM A, B JOIN C ON TRUE // VALID
FROM A, (B RIGHT JOIN C ON TRUE) // VALID
FROM A, (B FULL JOIN C ON TRUE) // VALID
Operación de unión correlacionada
Una operación de unión está correlacionada cuando la from_item
derecha contiene una referencia al menos una variable de rango o un nombre de columna ingresado por el from_item
izquierda.
En una operación de unión correlacionada, las filas del from_item
derecho se determinan por una fila del from_item
izquierdo. En consecuencia, las uniones de RIGHT OUTER
y FULL OUTER
no pueden correlacionarse porque las filas de from_item
derecha no se pueden determinar en el caso cuando no hay una fila del from_item
de la izquierda.
Todas las operaciones de unión correlacionadas deben hacer referencia a un arreglo en el from_item
derecho.
Este es un ejemplo conceptual de una operación de unión correlacionada que incluye una subconsulta correlacionada:
FROM A JOIN UNNEST(ARRAY(SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)) AS C
from_item
izquierdo:A
from_item
derecho:UNNEST(...) AS C
- Una subconsulta correlacionada:
(SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)
Este es otro ejemplo conceptual de una operación de unión correlacionada.
array_of_IDs
es parte del from_item
de la izquierda, pero se hace referencia a él en el from_item
de la derecha.
FROM A JOIN UNNEST(A.array_of_IDs) AS C
El operador UNNEST
puede ser explícito o implícito.
Ambos se permiten:
FROM A JOIN UNNEST(A.array_of_IDs) AS IDs
FROM A JOIN A.array_of_IDs AS IDs
En una operación de unión correlacionada, el from_item
derecho se vuelve a evaluar en función de cada fila diferente del from_item
izquierdo. En el siguiente ejemplo conceptual, la operación de unión correlacionada primero evalúa A
y B
, y, luego, A
y C
:
FROM
A
JOIN
UNNEST(ARRAY(SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)) AS C
ON A.Name = C.Name
Advertencias
- En una correlación
LEFT JOIN
, cuando la tabla de entrada del lado derecho está vacía para alguna fila del lado izquierdo, es como si ninguna fila del lado derecho satisface la condición de unión en unaLEFT JOIN
regular. Cuando no hay filas unidas, se genera una fila con valoresNULL
para todas las columnas del lado derecho a fin de unirse con la fila desde el lado izquierdo. - En una
CROSS JOIN
correlacionada, cuando la tabla de entrada del lado derecho está vacía para alguna fila del lado izquierdo, es como si ninguna fila del lado derecho cumpliera la condición de unión en unaINNER JOIN
correlacionada normal. Esto significa que la fila se descarta de los resultados.
Ejemplos
Este es un ejemplo de una unión correlacionada mediante las tablas Roster y PlayerStats:
SELECT *
FROM
Roster
JOIN
UNNEST(
ARRAY(
SELECT AS STRUCT *
FROM PlayerStats
WHERE PlayerStats.OpponentID = Roster.SchoolID
)) AS PlayerMatches
ON PlayerMatches.LastName = 'Buchanan'
+------------+----------+----------+------------+--------------+
| LastName | SchoolID | LastName | OpponentID | PointsScored |
+------------+----------+----------+------------+--------------+
| Adams | 50 | Buchanan | 50 | 13 |
| Eisenhower | 77 | Buchanan | 77 | 0 |
+------------+----------+----------+------------+--------------+
Un patrón común para un LEFT JOIN
correlacionado es tener una operación UNNEST
en el lado derecho que haga referencia a un array de alguna columna ingresada por la entrada en el lado izquierdo. En las filas en las que ese array está vacío o es NULL
, la operación UNNEST
no produce filas en la entrada derecha. En ese caso, se crea una fila con una entrada NULL
en cada columna de la entrada derecha para unirse con la fila de la entrada izquierda. Por ejemplo:
SELECT A.name, item, ARRAY_LENGTH(A.items) item_count_for_name
FROM
UNNEST(
[
STRUCT(
'first' AS name,
[1, 2, 3, 4] AS items),
STRUCT(
'second' AS name,
[] AS items)]) AS A
LEFT JOIN
A.items AS item;
+--------+------+---------------------+
| name | item | item_count_for_name |
+--------+------+---------------------+
| first | 1 | 4 |
| first | 2 | 4 |
| first | 3 | 4 |
| first | 4 | 4 |
| second | NULL | 0 |
+--------+------+---------------------+
En el caso de una CROSS JOIN
correlacionada, cuando la entrada del lado derecho está vacía durante una fila del lado izquierdo, la fila final se descarta de los resultados. Por ejemplo:
SELECT A.name, item
FROM
UNNEST(
[
STRUCT(
'first' AS name,
[1, 2, 3, 4] AS items),
STRUCT(
'second' AS name,
[] AS items)]) AS A
CROSS JOIN
A.items AS item;
+-------+------+
| name | item |
+-------+------+
| first | 1 |
| first | 2 |
| first | 3 |
| first | 4 |
+-------+------+
Cláusula WHERE
WHERE bool_expression
La cláusula WHERE
filtra los resultados de la cláusula FROM
.
Solo se incluyen las filas cuya bool_expression
se evalúa como TRUE
. Se descartan las filas cuya bool_expression
se evalúa como NULL
o FALSE
.
La evaluación de una consulta con una cláusula WHERE
, por lo general, se completa en este orden:
FROM
WHERE
GROUP BY
y agregaciónHAVING
WINDOW
QUALIFY
DISTINCT
ORDER BY
LIMIT
La cláusula WHERE
solo puede hacer referencia a columnas disponibles a través de la cláusula FROM
; no puede hacer referencia a los alias de la lista SELECT
.
Ejemplos
Esta consulta muestra todas las filas de la tabla Roster
en las que la columna SchoolID
tiene el valor 52
:
SELECT * FROM Roster
WHERE SchoolID = 52;
La bool_expression
puede contener varias subcondiciones.
SELECT * FROM Roster
WHERE STARTS_WITH(LastName, "Mc") OR STARTS_WITH(LastName, "Mac");
Las expresiones en una INNER JOIN
tienen una expresión equivalente en la cláusula WHERE
. Por ejemplo, una consulta que usa INNER
JOIN
y ON
tiene una expresión equivalente mediante CROSS JOIN
y WHERE
. Por ejemplo, las dos consultas siguientes son equivalentes:
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster INNER JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot
WHERE Roster.SchoolID = TeamMascot.SchoolID;
Cláusula GROUP BY
GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) }
La cláusula GROUP BY
agrupa las filas en una tabla con valores no distintos para la expression
en la cláusula GROUP BY
. Para varias filas en la tabla de origen con valores no distintos de la expression
, la cláusula GROUP BY
produce una única fila combinada. Por lo general, GROUP BY
se usa cuando las funciones de agregación están presentes en la lista SELECT
o para eliminar la redundancia en la salida. El tipo de datos de expression
debe ser agrupable.
Ejemplo:
SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY LastName;
La cláusula GROUP BY
puede hacer referencia a nombres de expresiones en la lista SELECT
. La cláusula GROUP BY
también permite referencias ordinales a expresiones en la lista SELECT
mediante valores de números enteros. 1
se refiere a la primera expresión en la lista SELECT
, 2
a la segunda, y así sucesivamente. La lista de expresiones puede combinar ordinales y nombres de expresiones.
Ejemplo:
SELECT SUM(PointsScored), LastName, FirstName
FROM PlayerStats
GROUP BY LastName, FirstName;
La consulta anterior es equivalente a lo siguiente:
SELECT SUM(PointsScored), LastName, FirstName
FROM PlayerStats
GROUP BY 2, FirstName;
Las cláusulas GROUP BY
pueden referirse también a los alias. Si una consulta contiene alias en la cláusula SELECT
, esos alias anulan los nombres en la cláusula FROM
correspondiente.
Ejemplo:
SELECT SUM(PointsScored), LastName as last_name
FROM PlayerStats
GROUP BY last_name;
GROUP BY ROLLUP
muestra los resultados de GROUP BY
para los prefijos de las expresiones en la lista ROLLUP
, cada uno de los cuales se conoce como un conjunto de agrupación. Para la lista ROLLUP
(a, b, c)
, los conjuntos de agrupación son (a, b, c)
, (a, b)
, (a)
y ()
. Cuando evalúas los resultados de GROUP BY
para un conjunto de agrupación en particular, GROUP BY ROLLUP
trata las expresiones que no están en el conjunto de agrupación como si tuvieran un valor NULL
. Una declaración SELECT
como la que se muestra a continuación:
SELECT a, b, SUM(c) FROM Input GROUP BY ROLLUP(a, b);
usa la lista acumulativa (a, b)
. El resultado incluirá los resultados de GROUP BY
para los conjuntos de agrupación (a, b)
, (a)
y ()
, que incluye todas las filas. Esto muestra las mismas filas que se muestran a continuación:
SELECT NULL, NULL, SUM(c) FROM Input UNION ALL
SELECT a, NULL, SUM(c) FROM Input GROUP BY a UNION ALL
SELECT a, b, SUM(c) FROM Input GROUP BY a, b;
Esto permite el cálculo de agregados para los conjuntos de agrupación definidos por las expresiones en la lista ROLLUP
y los prefijos de esa lista.
Ejemplo:
WITH Sales AS (
SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
SELECT 123, 1, 8.99 UNION ALL
SELECT 456, 1, 4.56 UNION ALL
SELECT 123, 2, 9.99 UNION ALL
SELECT 789, 3, 1.00 UNION ALL
SELECT 456, 3, 4.25 UNION ALL
SELECT 789, 3, 0.99
)
SELECT
day,
SUM(price) AS total
FROM Sales
GROUP BY ROLLUP(day);
La consulta anterior da como resultado una fila para cada día además del total acumulado en todos los días, como lo indica el día NULL
:
+------+-------+
| day | total |
+------+-------+
| NULL | 39.77 |
| 1 | 23.54 |
| 2 | 9.99 |
| 3 | 6.24 |
+------+-------+
Ejemplo:
WITH Sales AS (
SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
SELECT 123, 1, 8.99 UNION ALL
SELECT 456, 1, 4.56 UNION ALL
SELECT 123, 2, 9.99 UNION ALL
SELECT 789, 3, 1.00 UNION ALL
SELECT 456, 3, 4.25 UNION ALL
SELECT 789, 3, 0.99
)
SELECT
sku,
day,
SUM(price) AS total
FROM Sales
GROUP BY ROLLUP(sku, day)
ORDER BY sku, day;
La consulta anterior muestra las filas agrupadas mediante los siguientes conjuntos de agrupación:
- sku y día
- sku (día es
NULL
) - El conjunto de agrupación vacío (día y sku son
NULL
)
Las sumas de estos conjuntos de agrupación corresponden al total de cada combinación de sku y día, el total de cada sku en todos los días y el total general:
+------+------+-------+
| sku | day | total |
+------+------+-------+
| NULL | NULL | 39.77 |
| 123 | NULL | 28.97 |
| 123 | 1 | 18.98 |
| 123 | 2 | 9.99 |
| 456 | NULL | 8.81 |
| 456 | 1 | 4.56 |
| 456 | 3 | 4.25 |
| 789 | 3 | 1.99 |
| 789 | NULL | 1.99 |
+------+------+-------+
Cláusula HAVING
HAVING bool_expression
La cláusula HAVING
filtra los resultados producidos por GROUP BY
o la agregación. GROUP BY
o la agregación deben estar presentes en la consulta. Si la agregación está presente, la cláusula HAVING
se evalúa una vez en cada fila agregada en el conjunto de resultados.
Solo se incluyen las filas cuya bool_expression
se evalúa como TRUE
. Se descartan las filas cuya bool_expression
se evalúa como NULL
o FALSE
.
La evaluación de una consulta con una cláusula HAVING
, por lo general, se completa en este orden:
FROM
WHERE
GROUP BY
y agregaciónHAVING
WINDOW
QUALIFY
DISTINCT
ORDER BY
LIMIT
La cláusula HAVING
puede hacer referencia a columnas disponibles a través de la cláusula FROM
, así como los alias de la lista SELECT
. Las expresiones a las que se hace referencia en la cláusula HAVING
deben aparecer en la cláusula GROUP BY
o deben ser el resultado de una función de agregación:
SELECT LastName
FROM Roster
GROUP BY LastName
HAVING SUM(PointsScored) > 15;
Si una consulta contiene alias en la cláusula SELECT
, esos alias anulan los nombres en una cláusula FROM
.
SELECT LastName, SUM(PointsScored) AS ps
FROM Roster
GROUP BY LastName
HAVING ps > 0;
Agregación obligatoria
La agregación no tiene que estar presente en la cláusula HAVING
, pero debe estar en al menos una de las siguientes formas:
Función de agregación en la lista SELECT
.
SELECT LastName, SUM(PointsScored) AS total
FROM PlayerStats
GROUP BY LastName
HAVING total > 15;
Función de agregación en la cláusula “HAVING”.
SELECT LastName
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;
Agregación en la lista SELECT
y en la cláusula HAVING
.
Cuando las funciones de agregación están presentes en la lista SELECT
y en la cláusula HAVING
, no es necesario que estas funciones y las columnas a las que hacen referencia sean las mismas. En el siguiente ejemplo, las dos funciones de agregación, COUNT()
y SUM()
, son diferentes y también usan columnas diferentes.
SELECT LastName, COUNT(*)
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;
Cláusula ORDER BY
ORDER BY expression [{ ASC | DESC }] [{ NULLS FIRST | NULLS LAST }] [, ...]
La cláusula ORDER BY
especifica una columna o expresión como criterio de clasificación para el conjunto de resultados. Si una cláusula ORDER BY no está presente, el orden de los resultados de una consulta no se define. Se permiten los alias de columna de una cláusula FROM
o una lista SELECT
. Si una consulta contiene alias en la cláusula SELECT
, esos alias anulan los nombres en la cláusula FROM
correspondiente. El tipo de datos de expression
debe ser ordenable.
Cláusulas opcionales
NULLS FIRST | NULLS LAST
:NULLS FIRST
: Ordena los valores nulos antes de los valores que no son nulos.NULLS LAST
: Ordena los valores nulos después de los valores que no son nulos.
ASC | DESC
: Ordenan los resultados de los valores deexpression
en orden ascendente o descendente.ASC
es el valor predeterminado. Si no se especifica un ordenamiento de valores nulos medianteNULLS FIRST
oNULLS LAST
, sucede lo siguiente:NULLS FIRST
se aplica de forma predeterminada si el orden de clasificación es ascendente.NULLS LAST
se aplica de forma predeterminada si el orden de clasificación es descendente.
Ejemplos
Usa el orden de clasificación predeterminado (ascendente).
SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
SELECT 9, true UNION ALL
SELECT NULL, false)
ORDER BY x;
+------+-------+
| x | y |
+------+-------+
| NULL | false |
| 1 | true |
| 9 | true |
+------+-------+
Usa el orden de clasificación predeterminado (ascendente), pero muestra los valores nulos al final.
SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
SELECT 9, true UNION ALL
SELECT NULL, false)
ORDER BY x NULLS LAST;
+------+-------+
| x | y |
+------+-------+
| 1 | true |
| 9 | true |
| NULL | false |
+------+-------+
Usa el orden de clasificación descendente.
SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
SELECT 9, true UNION ALL
SELECT NULL, false)
ORDER BY x DESC;
+------+-------+
| x | y |
+------+-------+
| 9 | true |
| 1 | true |
| NULL | false |
+------+-------+
Usa el orden de clasificación descendente, pero muestra los valores nulos al principio.
SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
SELECT 9, true UNION ALL
SELECT NULL, false)
ORDER BY x DESC NULLS FIRST;
+------+-------+
| x | y |
+------+-------+
| NULL | false |
| 9 | true |
| 1 | true |
+------+-------+
Es posible ordenar por varias columnas. En el siguiente ejemplo, el conjunto de resultados se ordena primero por SchoolID
y, luego, por LastName
:
SELECT LastName, PointsScored, OpponentID
FROM PlayerStats
ORDER BY SchoolID, LastName;
Cuando se usa junto con los operadores de conjuntos, la cláusula ORDER BY
se aplica al conjunto de resultados de toda la consulta, no solo a la declaración SELECT
más cercana. Por esta razón, puede ser útil (aunque no es obligatorio) usar paréntesis para mostrar el alcance de ORDER
BY
.
Esta consulta sin paréntesis:
SELECT * FROM Roster
UNION ALL
SELECT * FROM TeamMascot
ORDER BY SchoolID;
es equivalente a esta consulta con paréntesis:
( SELECT * FROM Roster
UNION ALL
SELECT * FROM TeamMascot )
ORDER BY SchoolID;
pero no a esta consulta, en la que la cláusula ORDER BY
se aplica solo a la segunda declaración SELECT
:
SELECT * FROM Roster
UNION ALL
( SELECT * FROM TeamMascot
ORDER BY SchoolID );
También puedes usar literales de números enteros como referencias de columna en las cláusulas ORDER BY
. Un literal de número entero se convierte en un ordinal (por ejemplo, el conteo comienza en 1) en la lista SELECT
.
Por ejemplo, las dos consultas siguientes son equivalentes:
SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY LastName
ORDER BY LastName;
SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY 2
ORDER BY 2;
Cláusula QUALIFY
QUALIFY bool_expression
La cláusula QUALIFY
filtra los resultados de las funciones analíticas.
Se requiere una función analítica en la cláusula QUALIFY
o en la lista SELECT
.
Solo se incluyen las filas cuya bool_expression
se evalúa como TRUE
. Se descartan las filas cuya bool_expression
se evalúa como NULL
o FALSE
.
La evaluación de una consulta con una cláusula QUALIFY
, por lo general, se completa en este orden:
FROM
WHERE
GROUP BY
y agregaciónHAVING
WINDOW
QUALIFY
DISTINCT
ORDER BY
LIMIT
Ejemplos
En la siguiente consulta, se muestran las verduras más populares de la tabla Produce
y su clasificación.
SELECT
item,
RANK() OVER (PARTITION BY category ORDER BY purchases DESC) as rank
FROM Produce
WHERE Produce.category = 'vegetable'
QUALIFY rank <= 3
+---------+------+
| item | rank |
+---------+------+
| kale | 1 |
| lettuce | 2 |
| cabbage | 3 |
+---------+------+
No es necesario que incluyas una función analítica en la lista SELECT
para usar QUALIFY
. En la siguiente consulta, se muestran los vegetales más populares de la tabla Produce
.
SELECT item
FROM Produce
WHERE Produce.category = 'vegetable'
QUALIFY RANK() OVER (PARTITION BY category ORDER BY purchases DESC) <= 3
+---------+
| item |
+---------+
| kale |
| lettuce |
| cabbage |
+---------+
Cláusula WINDOW
WINDOW named_window_expression [, ...] named_window_expression: named_window AS { named_window | ( [ window_specification ] ) }
Mediante una cláusula WINDOW
, se define una lista de ventanas con nombre.
Una ventana con nombre representa un grupo de filas de una tabla en el que se debe usar una función analítica. Una ventana con nombre se puede definir con una especificación de ventana o una referencia a otra ventana con nombre. Si se hace referencia a otra ventana con nombre, la definición de la ventana a la que se hace referencia debe preceder a la ventana que realiza la referencia.
Ejemplos
En estos ejemplos, se hace referencia a una tabla llamada Produce
.
En todos se muestra el mismo resultado. Ten en cuenta las diferentes formas en las que puedes combinar ventanas con nombre y usarlas en la cláusula OVER
de una función analítica.
SELECT item, purchases, category, LAST_VALUE(item)
OVER (item_window) AS most_popular
FROM Produce
WINDOW item_window AS (
PARTITION BY category
ORDER BY purchases
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
SELECT item, purchases, category, LAST_VALUE(item)
OVER (d) AS most_popular
FROM Produce
WINDOW
a AS (PARTITION BY category),
b AS (a ORDER BY purchases),
c AS (b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING),
d AS (c)
SELECT item, purchases, category, LAST_VALUE(item)
OVER (c ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
FROM Produce
WINDOW
a AS (PARTITION BY category),
b AS (a ORDER BY purchases),
c AS b
Configurar operadores
set_operation: query_expr set_operator query_expr set_operator: UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT DISTINCT
Los operadores de conjuntos combinan los resultados de dos o más consultas de entrada en un único conjunto de resultados. Debes especificar ALL
o DISTINCT
; si especificas ALL
, se conservan todas las filas. Si especificas DISTINCT
, se descartan las filas duplicadas.
Si una determinada fila R aparece exactamente m veces en la primera consulta de entrada y n veces en la segunda consulta de entrada (m> = 0, n> = 0):
- En
UNION ALL
, R aparece exactamente m + n veces en el resultado. - En
UNION DISTINCT
,DISTINCT
se calcula después de que se calculaUNION
, por lo que R aparece exactamente una vez. - En
INTERSECT DISTINCT
,DISTINCT
se calcula después de que se calcula el resultado anterior. - En
EXCEPT DISTINCT
, la fila R aparece una vez en el resultado si m > 0 y n = 0. - Si hay más de dos consultas de entrada, las operaciones anteriores se generalizan y la salida es la misma que si las entradas se combinaran de forma incremental de izquierda a derecha.
Se aplican las siguientes reglas:
- Para las operaciones de conjuntos distintas de
UNION ALL
, todos los tipos de columna deben admitir la comparación de igualdad. - Las consultas de entrada a cada lado del operador deben mostrar la misma cantidad de columnas.
- Los operadores sincronizan las columnas que se muestran por cada consulta de entrada de acuerdo con las posiciones de las columnas en sus respectivas listas
SELECT
. Es decir, la primera columna en la primera consulta de entrada se sincroniza con la primera columna en la segunda consulta de entrada. - El conjunto de resultados siempre usa los nombres de columna de la primera consulta de entrada.
- El conjunto de resultados siempre usa los supertipos de tipos de entrada en las columnas correspondientes, por lo que las columnas sincronizadas también deben tener el mismo tipo de datos o un supertipo en común.
- Debes usar paréntesis si deseas separar diferentes operaciones de conjuntos. Para este fin, las operaciones de conjuntos como
UNION ALL
yUNION DISTINCT
son diferentes. Si la declaración solo repite la misma operación de conjunto, no se necesitan los paréntesis.
Ejemplos:
query1 UNION ALL (query2 UNION DISTINCT query3)
query1 UNION ALL query2 UNION ALL query3
No válido:
query1 UNION ALL query2 UNION DISTINCT query3
query1 UNION ALL query2 INTERSECT ALL query3; // INVALID.
UNION
El operador UNION
combina los conjuntos de resultados de dos o más consultas de entrada mediante la sincronización de las columnas del conjunto de resultados de cada consulta, concatenándolas de forma vertical.
INTERSECT
El operador INTERSECT
muestra las filas que se encuentran en los conjuntos de resultados de las consultas de entrada izquierda y derecha. A diferencia de como ocurre en EXCEPT
, la posición de las consultas de entrada (a la izquierda en lugar de a la derecha del operador INTERSECT
) no importa.
EXCEPT
El operador EXCEPT
muestra filas de la consulta de entrada izquierda que no están presentes en la consulta de entrada derecha.
Ejemplo:
SELECT * FROM UNNEST(ARRAY<int64>[1, 2, 3]) AS number
EXCEPT DISTINCT SELECT 1;
+--------+
| number |
+--------+
| 2 |
| 3 |
+--------+
Cláusulas LIMIT y OFFSET
LIMIT count [ OFFSET skip_rows ]
LIMIT
especifica un count
no negativo de tipo INT64 y no se mostrarán más que las filas count
. LIMIT
0
muestra 0 filas.
Si hay una operación de conjunto, se aplica LIMIT
después de que se evalúa esa operación.
OFFSET
especifica un número no negativo de filas que se omitirán antes de aplicar LIMIT
. skip_rows
es del tipo INT64.
Estas cláusulas aceptan solo valores literales o de parámetros. Las filas que muestran LIMIT
y OFFSET
no se especifican, a menos que estos operadores se usen después de ORDER BY
.
Ejemplos:
SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 2
+---------+
| letter |
+---------+
| a |
| b |
+---------+
SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 3 OFFSET 1
+---------+
| letter |
+---------+
| b |
| c |
| d |
+---------+
Cláusula WITH
WITH [ RECURSIVE ] { non_recursive_cte | recursive_cte }[, ...]
Una cláusula WITH
contiene una o más expresiones de tabla comunes (CTE).
Una CTE actúa como una tabla temporal a la que puedes hacer referencia dentro de una sola expresión de consulta. Cada CTE vincula los resultados de una subconsulta a un nombre de tabla, que se puede usar en otro lugar en la misma expresión de consulta, pero se aplican reglas.
Las CTE pueden ser no recurrentes o recurrentes y puedes incluir ambas en la cláusula WITH
. Una CTE recurrente hace referencia a sí misma, mientras que una CTE no recurrente no lo hace. Si se incluye una CTE recurrente en la cláusula WITH
, también se debe incluir la palabra clave RECURSIVE
.
Puedes incluir la palabra clave RECURSIVE
en una cláusula WITH
, incluso si no hay CTE recurrentes. Puedes obtener más información sobre la palabra clave RECURSIVE
aquí.
BigQuery solo materializa los resultados de las CTE recurrentes, pero no materializa los resultados de las CTE no recurrentes dentro de la cláusula WITH
. Si se hace referencia a un CTE no recurrente en varios lugares de una consulta, el CTE se ejecuta una vez en cada referencia. La cláusula WITH
con CTE no recurrentes es útil sobre todo para facilitar la lectura.
Palabra clave RECURRENTE
Una cláusula WITH
puede incluir de manera opcional la palabra clave RECURSIVE
, que realiza dos acciones:
- Habilita la recurrencia en la cláusula
WITH
. Si esta palabra clave no está presente, solo puedes incluir expresiones de tablas comunes (CTE) no recurrentes. Si esta palabra clave está presente, puedes usar CTE recursivas y no recurrentes. - Cambia la visibilidad de las CTE en la cláusula
WITH
. Si esta palabra clave no está presente, una CTE solo es visible para las CTE definidas después de ella en la cláusulaWITH
. Si esta palabra clave está presente, un CTE es visible para todas las CTE en la cláusulaWITH
en la que se definió.
CTE no recurrentes
non_recursive_cte: cte_name AS ( query_expr )
Una expresión de tabla común (CTE) no recurrente contiene una subconsulta no recurrente y un nombre asociado con la CTE.
- Una CTE no recurrente no puede hacer referencia a sí mismo.
- La expresión de consulta que contiene la cláusula
WITH
puede hacer referencia a una CTE no recurrente, pero se aplican las reglas.
Examples
En este ejemplo, una cláusula WITH
define dos CTE no recurrentes a las que se hace referencia en la operación de conjunto relacionada, en la que cada CTE hace referencia a una CTE por las expresiones de consulta de entrada de la operación set:
WITH subQ1 AS (SELECT SchoolID FROM Roster),
subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2
Puedes dividir las consultas más complejas en una cláusula WITH
y en una sentencia WITH
SELECT
en lugar de escribir subconsultas de tablas anidadas.
Por ejemplo:
WITH q1 AS (my_query)
SELECT *
FROM
(WITH q2 AS (SELECT * FROM q1) SELECT * FROM q2)
WITH q1 AS (my_query)
SELECT *
FROM
(WITH q2 AS (SELECT * FROM q1), # q1 resolves to my_query
q3 AS (SELECT * FROM q1), # q1 resolves to my_query
q1 AS (SELECT * FROM q1), # q1 (in the query) resolves to my_query
q4 AS (SELECT * FROM q1) # q1 resolves to the WITH subquery on the previous line.
SELECT * FROM q1) # q1 resolves to the third inner WITH subquery.
CTE recurrentes
recursive_cte: cte_name AS ( recursive_union_operation ) recursive_union_operation: base_term union_operator recursive_term base_term: query_expr recursive_term: query_expr union_operator: UNION ALL
Una expresión de tabla común (CTE) recurrente contiene una subconsulta recurrente y un nombre asociado con la CTE.
- Una referencia de CTE recurrente.
- Se puede hacer referencia a una CTE recurrente en la expresión de la consulta que contiene la cláusula
WITH
, pero se aplican reglas. - Cuando se define una CTE recurrente en una cláusula
WITH
, la palabra claveRECURSIVE
debe estar presente.
Una CTE recurrente se define mediante una operación de unión recurrente. La operación de unión recurrente define cómo se procesa la entrada de manera recurrente para producir el resultado final de CTE. La operación de unión recurrente está compuesta por las siguientes partes:
base_term
: Ejecuta la primera iteración de la operación de unión recurrente. Este término debe seguir las reglas de términos base.union_operator
: El operadorUNION
muestra las filas que provienen de la unión del término base y el término recurrente. ConUNION ALL
, cada fila producida en la iteraciónN
se convierte en parte del resultado y la entrada de CTE final para la iteraciónN+1
. La iteración se detiene cuando una iteración no produce filas para pasar a la siguiente iteración.recursive_term
: ejecuta las iteraciones restantes. Debe incluir una autorreferencia (referencia recurrente) a la CTE recurrente. Solo este término puede incluir una autorreferencia. Este término debe seguir las reglas de términos recurrentes.
Una CTE recurrente se ve de la siguiente manera:
WITH RECURSIVE
T1 AS ( (SELECT 1 AS n) UNION ALL (SELECT n + 1 AS n FROM T1 WHERE n < 3) )
SELECT n FROM T1
+---+
| n |
+---+
| 2 |
| 1 |
| 3 |
+---+
La primera iteración de una operación de unión recurrente ejecuta el término base. Luego, cada iteración posterior ejecuta el término recurrente y produce filas nuevas que se unen con la iteración anterior. La operación de unión recurrente finaliza cuando una iteración de término recurrente no produce filas nuevas.
Si la recurrencia no finaliza, la consulta falla después de alcanzar 100 iteraciones, que se pueden personalizar a nivel del proyecto.
Ejemplos de CTE recurrentes permitidas
Este es una CTE recurrente simple:
WITH RECURSIVE
T1 AS (
(SELECT 1 AS n) UNION ALL
(SELECT n + 2 FROM T1 WHERE n < 4))
SELECT * FROM T1 ORDER BY n
+---+
| n |
+---+
| 1 |
| 3 |
| 5 |
+---+
Se pueden realizar varias subconsultas en la misma CTE recurrente, siempre que cada recurrencia tenga una duración de ciclo de 1. También está bien que las entradas recurrentes dependan de las entradas no recurrentes y viceversa:
WITH RECURSIVE
T0 AS (SELECT 1 AS n),
T1 AS ((SELECT * FROM T0) UNION ALL (SELECT n + 1 FROM T1 WHERE n < 4)),
T2 AS ((SELECT 1 AS n) UNION ALL (SELECT n + 1 FROM T2 WHERE n < 4)),
T3 AS (SELECT * FROM T1 INNER JOIN T2 USING (n))
SELECT * FROM T3 ORDER BY n
+---+
| n |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
Las funciones agregadas pueden invocarse en subconsultas, siempre que no se agreguen en la tabla que se define:
WITH RECURSIVE
T0 AS (SELECT * FROM UNNEST ([60, 20, 30])),
T1 AS ((SELECT 1 AS n) UNION ALL (SELECT n + (SELECT COUNT(*) FROM T0) FROM T1 WHERE n < 4))
SELECT * FROM T1 ORDER BY n
+---+
| n |
+---+
| 1 |
| 4 |
+---+
INNER JOIN
se puede usar dentro de las subconsultas:
WITH RECURSIVE
T0 AS (SELECT 1 AS n),
T1 AS ((SELECT 1 AS n) UNION ALL (SELECT n + 1 FROM T1 INNER JOIN T0 USING (n)))
SELECT * FROM T1 ORDER BY n
+---+
| n |
+---+
| 1 |
| 2 |
+---+
CROSS JOIN
se puede usar dentro de las subconsultas:
WITH RECURSIVE
T0 AS (SELECT 2 AS p),
T1 AS ((SELECT 1 AS n) UNION ALL (SELECT T1.n + T0.p FROM T1 CROSS JOIN T0 WHERE T1.n < 4))
SELECT * FROM T1 CROSS JOIN T0 ORDER BY n
+---+---+
| n | p |
+---+---+
| 1 | 2 |
| 3 | 2 |
| 5 | 2 |
+---+---+
Ejemplos de CTE recurrentes no permitidas
La siguiente CTE recurrente no está permitida porque la autoreferencia no incluye un operador de conjunto, un término base y un término recurrente.
WITH RECURSIVE
T1 AS (SELECT * FROM T1)
SELECT * FROM T1
-- Error
La siguiente CTE recurrente no está permitida porque la autoreferencia a T1
está en el término base. La referencia a sí misma solo se permite en el término recurrente.
WITH RECURSIVE
T1 AS ((SELECT * FROM T1) UNION ALL (SELECT 1))
SELECT * FROM T1
-- Error
La siguiente CTE recurrente no está permitida porque hay varias referencias a sí mismas en el término recurrente cuando solo debe haber una.
WITH RECURSIVE
T1 AS ((SELECT 1 AS n) UNION ALL ((SELECT * FROM T1) UNION ALL (SELECT * FROM T1)))
SELECT * FROM T1
-- Error
La siguiente CTE recurrente no está permitida porque la autoreferencia está dentro de una subconsulta de expresión.
WITH RECURSIVE
T1 AS ((SELECT 1 AS n) UNION ALL (SELECT (SELECT n FROM T1)))
SELECT * FROM T1
-- Error
La siguiente CTE recurrente no está permitida porque hay una autoreferencia como entrada para una unión externa.
WITH RECURSIVE
T0 AS (SELECT 1 AS n),
T1 AS ((SELECT 1 AS n) UNION ALL (SELECT * FROM T1 FULL OUTER JOIN T0 USING (n)))
SELECT * FROM T1;
-- Error
La siguiente CTE recurrente no está permitida porque no puedes usar la agregación con una autoreferencia.
WITH RECURSIVE
T1 AS (
(SELECT 1 AS n) UNION ALL
(SELECT COUNT(*) FROM T1))
SELECT * FROM T1;
-- Error
La siguiente CTE recurrente no está permitida porque no puedes usar la cláusula OVER
de la función analítica con una autoreferencia.
WITH RECURSIVE
T1 AS (
(SELECT 1.0 AS n) UNION ALL
SELECT 1 + AVG(n) OVER(ROWS between 2 PRECEDING and 0 FOLLOWING) FROM T1 WHERE n < 10)
SELECT n FROM T1;
-- Error
La siguiente CTE recurrente no está permitida porque no puedes usar una cláusula LIMIT
con una autoreferencia.
WITH RECURSIVE
T1 AS ((SELECT 1 AS n) UNION ALL (SELECT n FROM T1 LIMIT 3))
SELECT * FROM T1;
-- Error
Las siguientes CTE recurrentes no están permitidas porque no puedes usar una cláusula ORDER BY
con una autoreferencia.
WITH RECURSIVE
T1 AS ((SELECT 1 AS n) UNION ALL (SELECT n + 1 FROM T1 ORDER BY n))
SELECT * FROM T1;
-- Error
La siguiente CTE recurrente no está permitida porque no se puede hacer referencia a la tabla T1
desde una cláusula WITH
interna de forma recurrentes.
WITH RECURSIVE
T1 AS ((SELECT 1 AS n) UNION ALL (WITH t AS (SELECT n FROM T1) SELECT * FROM t))
SELECT * FROM T1
-- Error
Reglas y restricciones del CTE
Se puede hacer referencia a las expresiones de tabla comunes (CTE) dentro de la expresión de consulta que contiene la cláusula WITH
.
Reglas Generales
Estas son algunas reglas y limitaciones generales que debes tener en cuenta cuando trabajes con CTE:
- Cada CTE en la misma cláusula
WITH
debe tener un nombre único. - Debes incluir la palabra clave
RECURSIVE
word si la cláusulaWITH
contiene una CTE recurrente. - La palabra clave
RECURSIVE
en la cláusulaWITH
cambia la visibilidad de las CTE a otras CTE en la misma cláusulaWITH
. Obtenga más información en este vínculo. WITH
no está permitido dentro deWITH RECURSIVE
.- Se permite
WITH RECURSIVE
en la declaraciónSELECT
. WITH RECURSIVE
solo se permite en el nivel superior de la consulta.WITH RECURSIVE
no está permitido en vistas y funciones.- Una CTE local anula una CTE externo o una tabla con el mismo nombre.
- Una CTE en una subconsulta no puede hacer referencia a columnas correlacionadas de la consulta externa.
Reglas de término base
Las siguientes reglas se aplican al término base en una CTE recurrente:
- El término base debe ser no recurrente.
- El término base determina los nombres y tipos de todas las columnas de la tabla.
- El término base no puede tener columnas de resultado STRUCT.
Reglas de términos recurrentes
Las siguientes reglas se aplican al término recurrente en una CTE recurrente:
- El término recurrente debe incluir exactamente una referencia a la tabla definida de forma recurrente en el término base.
- El término recurrente debe contener la misma cantidad de columnas que el término base, y el tipo de cada columna debe ser coercible de forma implícita al tipo de columna correspondiente en el término base.
- Una referencia de tabla recurrente no se puede usar como un operando para un
FULL JOIN
, un operando derecho para unLEFT JOIN
ni un operando izquierdo para unRIGHT JOIN
. - Una referencia de tabla recurrente no se puede usar con el operador
TABLESAMPLE
. - El término recurrente no debe usar ningún operador no determinista.
- El uso de las subconsultas de expresión
IN
yEXISTS
está limitado dentro del término recurrente. Por ejemplo:[NOT] IN
y[NOT] EXISTS
no están permitidos en la cláusulaSELECT
.NOT IN
no está permitido en la cláusulaWHERE
.
Las siguientes reglas se aplican a una subconsulta dentro de un término recurrente:
- Una subconsulta con una referencia de tabla recurrente debe ser una expresión
SELECT
, no una operación establecida, comoUNION ALL
. - Una subconsulta no puede contener, de forma directa o indirecta, una referencia de tabla recurrente en cualquier lugar fuera de su cláusula
FROM
. - Una subconsulta con una referencia de tabla recurrente no puede contener una cláusula
ORDER BY
oLIMIT
. - Una subconsulta con una referencia de tabla recurrente no puede invocar funciones de agregación.
- Una subconsulta con una referencia de tabla recurrente no puede invocar funciones analíticas.
- Una subconsulta con una referencia de tabla recurrente no puede contener la palabra clave
DISTINCT
ni la cláusulaGROUP BY
.
Visibilidad de CTE
La visibilidad de una expresión de tabla común (CTE) dentro de una expresión de consulta se determina si agregas o no la palabra clave RECURSIVE
a la cláusula WITH
en la que se definió el CTE. Puedes obtener más información sobre estas diferencias en las siguientes secciones.
Visibilidad de las CTE en una cláusula WITH
con la palabra clave RECURSIVE
Cuando incluyes la palabra clave RECURSIVE
, las referencias entre las CTE en la cláusula WITH
pueden ir hacia atrás y hacia delante. No se permiten ciclos.
Esto es lo que sucede cuando tienes dos CTE que hacen referencia a sí mismas o entre sí en una cláusula WITH
con la palabra clave RECURSIVE
. Supongamos que A
es el primer CTE y B
es el segundo CTE en la cláusula:
- A hace referencia a A = Válido
- A hace referencia a B = Válido
- B hace referencia a A = Válido
- A hace referencia a B hace referencia a A = No válido (no se permiten ciclos).
A
puede hacer referencia a sí mismo porque se admiten autoreferencias:
WITH RECURSIVE
A AS (SELECT 1 AS n UNION ALL (SELECT n + 1 FROM A WHERE n < 3))
SELECT * FROM A
+---+
| n |
+---+
| 1 |
| 2 |
| 3 |
+---+
A
puede hacer referencia a B
porque las referencias entre CTE pueden reenviarse:
WITH RECURSIVE
A AS (SELECT * FROM B),
B AS (SELECT 1 AS n)
SELECT * FROM B
+---+
| n |
+---+
| 1 |
+---+
B
puede hacer referencia a A
porque las referencias entre CTE pueden revertirse:
WITH RECURSIVE
A AS (SELECT 1 AS n),
B AS (SELECT * FROM A)
SELECT * FROM B
+---+
| n |
+---+
| 1 |
+---+
Esto produce un error. A
y B
hacen referencia entre sí, lo que crea un ciclo:
WITH RECURSIVE
A AS (SELECT * FROM B),
B AS (SELECT * FROM A)
SELECT * FROM B
-- Error
Visibilidad de las CTE en una cláusula WITH
sin la palabra clave RECURSIVE
Cuando no incluyes la palabra clave RECURSIVE
en la cláusula WITH
, las referencias entre las CTE en la cláusula pueden retroceder, pero no reenviarse.
Esto es lo que sucede cuando tienes dos CTE que hacen referencia a sí mismas o entre sí en una cláusula WITH
sin la palabra clave RECURSIVE
. Supongamos que A
es la primera CTE y B
es la segunda CTE en la cláusula:
- A hace referencia a A = No válido
- A hace referencia a B = No válido
- B hace referencia a A = Válido
- A hace referencia a B hace referencia a A = No válido (no se permiten ciclos).
Esto produce un error. A
no puede hacer referencia a sí mismo porque no se admiten las autoreferencias:
WITH
A AS (SELECT 1 AS n UNION ALL (SELECT n + 1 FROM A WHERE n < 3))
SELECT * FROM A
-- Error
Esto produce un error. A
no puede hacer referencia a B
porque las referencias entre CTE pueden retroceder, pero no reenviarse:
WITH
A AS (SELECT * FROM B),
B AS (SELECT 1 AS n)
SELECT * FROM B
-- Error
B
puede hacer referencia a A
porque las referencias entre CTE pueden revertirse:
WITH
A AS (SELECT 1 AS n),
B AS (SELECT * FROM A)
SELECT * FROM B
+---+
| n |
+---+
| 1 |
+---+
Esto produce un error. A
y B
hacen referencia entre sí, lo que crea un ciclo:
WITH
A AS (SELECT * FROM B),
B AS (SELECT * FROM A)
SELECT * FROM B
-- Error
Usa alias
Un alias es un nombre temporal que se le otorga a una tabla, columna o expresión presente en una consulta. Puedes ingresar los alias explícitos en la lista SELECT
o en la cláusula FROM
o BigQuery inferirá un alias implícito para algunas expresiones.
Las expresiones sin alias explícito ni implícito son anónimas y la consulta no puede hacer referencia a ellas por nombre.
Alias explícitos
Puedes ingresar alias explícitos en la cláusula FROM
o en la lista SELECT
.
En una cláusula FROM
, puedes ingresar los alias explícitos para cualquier elemento, incluidas tablas, arreglos, subconsultas y cláusulas UNNEST
, mediante el [AS] alias
. La palabra clave AS
es opcional.
Ejemplo:
SELECT s.FirstName, s2.SongName
FROM Singers AS s, (SELECT * FROM Songs) AS s2;
Puedes ingresar los alias explícitos para cualquier expresión en la lista SELECT
mediante el [AS] alias
. La palabra clave AS
es opcional.
Ejemplo:
SELECT s.FirstName AS name, LOWER(s.FirstName) AS lname
FROM Singers s;
Alias implícitos
En la lista SELECT
, si existe una expresión que no tiene un alias explícito, BigQuery le asigna un alias implícito según las siguientes reglas. Puede haber varias columnas con el mismo alias en la lista SELECT
.
- Para los identificadores, el alias es el identificador. Por ejemplo,
SELECT abc
implicaAS abc
. - Para las expresiones de ruta de acceso, el alias es el último identificador en la ruta. Por ejemplo,
SELECT abc.def.ghi
implicaAS ghi
. - Para el acceso de campo que usa el operador de acceso de campo del miembro “punto”, el alias es el nombre del campo. Por ejemplo,
SELECT (struct_function()).fname
implicaAS fname
.
En los demás casos, no hay un alias implícito, por lo que la columna es anónima y no se puede hacer referencia a ella por nombre. Los datos de esa columna aún se mostrarán y los resultados de la consulta que se muestran pueden tener una etiqueta generada para esa columna, pero la etiqueta no se puede usar como un alias.
En una cláusula FROM
, los from_item
no necesitan tener un alias. Se aplican las siguientes reglas:
- Si existe una expresión que no tiene un alias explícito, BigQuery asigna un alias implícito en estos casos:
-
Para los identificadores, el alias es el identificador. Por ejemplo,
FROM abc
implicaAS abc
. -
Para las expresiones de ruta de acceso, el alias es el último identificador en la ruta. Por ejemplo,
FROM abc.def.ghi
implicaAS ghi
- La columna generada con
WITH OFFSET
tiene el alias implícitooffset
. - Las subconsultas de tabla no tienen alias implícitos.
FROM UNNEST(x)
no tiene un alias implícito.
Visibilidad del alias
Después de ingresar un alias explícito en una consulta, existen restricciones sobre en qué otra parte de la consulta puedes hacer referencia a ese alias. Estas restricciones en la visibilidad de alias son el resultado de las reglas de alcance del nombre de BigQuery.
Visibilidad en la cláusula FROM
BigQuery procesa los alias en una cláusula FROM
de izquierda a derecha y los alias son visibles solo para las expresiones de ruta de acceso posteriores en una cláusula FROM
.
Ejemplo:
Supongamos que la tabla Singers
tenía una columna Concerts
de tipo ARRAY
.
SELECT FirstName
FROM Singers AS s, s.Concerts;
No válido:
SELECT FirstName
FROM s.Concerts, Singers AS s; // INVALID.
Los alias de la cláusula FROM
no son visibles para las subconsultas en la misma cláusula FROM
. Las subconsultas en una cláusula FROM
no pueden contener referencias correlacionadas con otras tablas en la misma cláusula FROM
.
No válido:
SELECT FirstName
FROM Singers AS s, (SELECT (2020 - ReleaseDate) FROM s) // INVALID.
Puedes usar cualquier nombre de columna de una tabla en FROM
como un alias en cualquier parte de la consulta, con o sin calificación con el nombre de la tabla.
Ejemplo:
SELECT FirstName, s.ReleaseDate
FROM Singers s WHERE ReleaseDate = 1975;
Si la cláusula FROM
contiene un alias explícito, debes usarlo en lugar del alias implícito para el resto de la consulta (consulta Alias implícitos). Un alias de tabla es útil por cuestiones de brevedad o para eliminar la ambigüedad en casos como autocombinaciones, en que la misma tabla se analiza varias veces durante el procesamiento de consulta.
Ejemplo:
SELECT * FROM Singers as s, Songs as s2
ORDER BY s.LastName
No válido: ORDER BY
no usa el alias de la tabla:
SELECT * FROM Singers as s, Songs as s2
ORDER BY Singers.LastName; // INVALID.
Visibilidad en la lista SELECT
Los alias en la lista SELECT
son visibles solo para las siguientes cláusulas:
- Cláusula
GROUP BY
- Cláusula
ORDER BY
- Cláusula
HAVING
Ejemplo:
SELECT LastName AS last, SingerID
FROM Singers
ORDER BY last;
Visibilidad en las cláusulas GROUP BY, ORDER BY y HAVING
Las tres cláusulas, GROUP BY
, ORDER BY
y HAVING
, solo pueden hacer referencia a los siguientes valores:
- Tablas en la cláusula
FROM
y cualquiera de sus columnas - Alias de la lista
SELECT
GROUP BY
y ORDER BY
también pueden hacer referencia a un tercer grupo:
- Literales de número entero, que se refieren a elementos de la lista
SELECT
. El número entero1
hace referencia al primer elemento de la listaSELECT
,2
hace referencia al segundo elemento, y así sucesivamente.
Ejemplo:
SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY 1
ORDER BY 2 DESC;
La consulta anterior es equivalente a lo siguiente:
SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY sid
ORDER BY s2id DESC;
Alias duplicados
Se permite la lista o subconsulta SELECT
que contiene varios alias explícitos o implícitos del mismo nombre, siempre que no se haga referencia a él en otro lugar de la consulta, ya que la referencia sería ambigua.
Cuando una lista SELECT
de nivel superior contiene nombres de columnas duplicados y no se especifica una tabla de destino, todas las columnas duplicadas, excepto la primera, se cambian de nombre automáticamente para que sean únicas. Las columnas con nombre nuevo aparecen en el resultado de la consulta.
Ejemplo:
SELECT 1 AS a, 2 AS a;
+---+-----+
| a | a_1 |
+---+-----+
| 1 | 2 |
+---+-----+
No se admite la duplicación de nombres de columnas en una tabla o definición de vista. Las instrucciones con consultas que contienen nombres de columnas duplicados fallarán:
CREATE TABLE my_dataset.my_table AS (SELECT 1 AS a, 2 AS a);
CREATE VIEW my_dataset.my_view AS (SELECT 1 AS a, 2 AS a);
Alias ambiguos
BigQuery muestra un error si el acceso a un nombre es ambiguo, lo que significa que se puede resolver en más de un objeto único en la consulta o en un esquema de tabla, incluido el esquema de una tabla de destino.
Ejemplos:
Esta consulta contiene nombres de columnas que entran en conflicto entre las tablas, ya que Singers
y Songs
tienen una columna llamada SingerID
:
SELECT SingerID
FROM Singers, Songs;
Esta consulta contiene alias que son ambiguos en la cláusula GROUP BY
debido a que están duplicados en la lista SELECT
:
SELECT FirstName AS name, LastName AS name,
FROM Singers
GROUP BY name;
En esta consulta, se incluyen los alias de la lista SELECT
y la cláusula FROM
que son ambiguos, ya que comparten el mismo nombre. Supón que table
tiene columnas x
, y
y z
. z
es del tipo STRUCT y tiene campos v
, w
y x
.
Ejemplo:
SELECT x, z AS T
FROM table AS T
GROUP BY T.x;
El alias T
es ambiguo y producirá un error debido a que T.x
en la cláusula GROUP
BY
podría referirse a table.x
o table.z.x
.
Un nombre no es ambiguo en GROUP BY
, ORDER BY
o HAVING
si este es un nombre de columna y un alias de la lista SELECT
, siempre que el nombre se resuelva en el mismo objeto subyacente.
Ejemplo:
SELECT LastName, BirthYear AS BirthYear
FROM Singers
GROUP BY BirthYear;
El alias BirthYear
no es ambiguo porque se resuelve en la misma columna subyacente, Singers.BirthYear
.
Variables de rango
En BigQuery, una variable de rango es un alias de una expresión de tabla en la cláusula FROM
. A veces, una variable de rango se conoce como un table alias
. Una variable de rango te permite hacer referencia a las filas de una expresión de tabla que se analizan.
Una expresión de tabla representa un elemento de la cláusula FROM
que muestra una tabla.
Los elementos comunes que esta expresión puede representar son tablas, tablas de valores, subconsultas, uniones y uniones con paréntesis.
En general, una variable de rango proporciona una referencia a las filas de una expresión de tabla. Una variable de rango se puede usar para calificar una referencia de columna y, también, identificar la tabla relacionada de forma clara, por ejemplo range_variable.column_1
.
Cuando se hace referencia a una variable de rango por separado sin un sufijo de columna especificado, el resultado de una expresión de tabla es el tipo de fila de la tabla relacionada.
Las tablas de valores tienen tipos de fila explícitos. Por lo tanto, para las variables de rango relacionadas con tablas de valores, el tipo de resultado es el tipo de fila de la tabla de valores. Otras tablas no tienen tipos de fila explícitos y, para estas, el tipo de variable de rango es un STRUCT
definido de forma dinámica en el que se incluyen todas las columnas de la tabla.
Ejemplos
En estos ejemplos, la cláusula WITH
se usa para emular una tabla temporal llamada Grid
. Esta tabla tiene las columnas x
y y
. Una variable de rango llamada Coordinate
hace referencia a la fila actual a medida que se analiza la tabla. Coordinate
se puede usar para acceder a toda la fila o a las columnas de la fila.
En el siguiente ejemplo, se selecciona la columna x
de la variable de rango Coordinate
, que en realidad selecciona la columna x
de la tabla Grid
.
WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate.x FROM Grid AS Coordinate;
+---+
| x |
+---+
| 1 |
+---+
En el siguiente ejemplo, se seleccionan todas las columnas de la variable de rango Coordinate
, que en realidad selecciona todas las columnas de la tabla Grid
.
WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate.* FROM Grid AS Coordinate;
+---+---+
| x | y |
+---+---+
| 1 | 2 |
+---+---+
En el siguiente ejemplo, se selecciona la variable de rango Coordinate
, que es una referencia a las filas de la tabla Grid
. Como Grid
no es una tabla de valores, el tipo de resultado de Coordinate
es un STRUCT
que contiene todas las columnas de Grid
.
WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate FROM Grid AS Coordinate;
+--------------+
| Coordinate |
+--------------+
| {x: 1, y: 2} |
+--------------+
Trabaja con tablas de valores
En BigQuery, una tabla de valores es una tabla en la que el tipo de fila es un valor único. En una tabla normal, cada fila está compuesta por columnas, cada una de las cuales tiene un nombre y un tipo. En una tabla de valores, el tipo de fila es solo un valor único y no existen nombres de columna.
Una consulta producirá una tabla de valores si usa SELECT AS STRUCT
o SELECT AS VALUE
.
Nota: En BigQuery, una consulta solo muestra una tabla de valores con un tipo de STRUCT
.
En contextos en los que se espera una consulta de solo una columna, se puede usar una consulta de tabla de valores en su lugar. Por ejemplo, las subconsultas escalares y las subconsultas de arreglo (consulta las Subconsultas), por lo general, requieren una consulta de una sola columna, pero en BigQuery, también permiten usar una consulta de tabla de valores.
Apéndice A: Ejemplos con datos de muestra
En estos ejemplos, se incluyen declaraciones mediante las que se realizan consultas en las tablas Roster
, TeamMascot
y PlayerStats
.
Cláusula GROUP BY
Ejemplo:
SELECT LastName, SUM(PointsScored)
FROM PlayerStats
GROUP BY LastName;
LastName | SUM |
---|---|
Adams | 7 |
Buchanan | 13 |
Coolidge | 1 |
UNION
El operador UNION
combina los conjuntos de resultados de dos o más declaraciones SELECT
mediante la sincronización de las columnas del conjunto de resultados de cada declaración SELECT
y su concatenación de forma vertical.
Ejemplo:
SELECT Mascot AS X, SchoolID AS Y
FROM TeamMascot
UNION ALL
SELECT LastName, PointsScored
FROM PlayerStats;
Resultados:
X | S |
---|---|
Jaguars | 50 |
Knights | 51 |
Lakers | 52 |
Mustangs | 53 |
Adams | 3 |
Buchanan | 0 |
Coolidge | 1 |
Adams | 4 |
Buchanan | 13 |
INTERSECT
Esta consulta muestra los apellidos que están presentes en Roster y en PlayerStats.
SELECT LastName
FROM Roster
INTERSECT DISTINCT
SELECT LastName
FROM PlayerStats;
Resultados:
LastName |
---|
Adams |
Coolidge |
Buchanan |
EXCEPT
La consulta que aparece a continuación muestra los apellidos de Roster que no están presentes en PlayerStats.
SELECT LastName
FROM Roster
EXCEPT DISTINCT
SELECT LastName
FROM PlayerStats;
Resultados:
LastName |
---|
Eisenhower |
Davis |
Si se invierte el orden de las instrucciones SELECT
, se mostrarán los apellidos de PlayerStats que no están presentes en Roster:
SELECT LastName
FROM PlayerStats
EXCEPT DISTINCT
SELECT LastName
FROM Roster;
Resultados:
(empty)