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 with_query_name AS ( query_expr ) [, ...] ] { select | ( query_expr ) | query_expr set_op query_expr } [ ORDER BY expression [{ ASC | DESC }] [, ...] ] [ LIMIT count [ OFFSET skip_rows ] ] select: SELECT [ AS { typename | STRUCT | VALUE } ] [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| expression [ [ AS ] alias ] } [, ...] [ FROM from_item [, ...] ] [ WHERE bool_expression ] [ GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) } ] [ HAVING bool_expression ] [ WINDOW window_name AS ( window_definition ) [, ...] ] set_op: UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT DISTINCT from_item: { table_name [ [ AS ] alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ] | join | ( query_expr ) [ [ AS ] alias ] | field_path | { UNNEST( array_expression ) | UNNEST( array_path ) | array_path } [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] | with_query_name [ [ AS ] alias ] } join: from_item [ join_type ] JOIN from_item [ { ON bool_expression | USING ( join_column [, ...] ) } ] join_type: { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }
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 elipsis entre corchetes “[, … ]” indica que el elemento anterior se puede repetir en una lista separada por comas.
Lista SELECT
Sintaxis:
SELECT [ AS { typename | STRUCT | VALUE } ] [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| 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 |
+-------+-----------+
SELECT expression
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.
SELECT expression.*
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 una STRUCT.
La siguiente consulta genera una columna de salida para cada columna en la tabla groceries
, con alias como 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 |
+---------+------------+
Modificadores SELECT
Puedes modificar los resultados que se muestran de una consulta SELECT
, de la siguiente manera.
SELECT DISTINCT
Una declaración SELECT DISTINCT
descarta filas duplicadas y muestra solo las filas restantes. SELECT DISTINCT
no puede mostrar columnas de los siguientes tipos:
- STRUCT
- ARRAY
SELECT * EXCEPT
Una declaración SELECT * EXCEPT
especifica los nombres de una o más columnas para excluir 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 |
+-----------+----------+
Nota: SELECT * EXCEPT
no excluye las columnas que no tienen nombres.
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 cláusula 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 |
+----------+-----------+----------+
Nota: SELECT * REPLACE
no reemplaza las columnas que no tienen nombres.
SELECT ALL
Una declaración SELECT ALL
muestra todas las filas, incluidas las filas duplicadas.
SELECT ALL
es el comportamiento predeterminado de SELECT
.
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.
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.
Una consulta producirá una tabla de valores si usa SELECT AS
, mediante una de las sintaxis a continuación:
SELECT AS STRUCT
Sintaxis:
SELECT AS STRUCT expr1 [struct_field_name1] [,... ]
Esto produce una tabla de valores con un tipo de fila STRUCT, en el que los nombres y tipos de campo STRUCT coinciden con los nombres y tipos de columnas generados en la lista SELECT
.
Se permiten columnas anónimas y columnas duplicadas.
Ejemplo:
SELECT AS STRUCT 1 x, 2, 3 x
La consulta anterior produce valores de STRUCT de tipo STRUCT<int64 x, int64, int64
x>.
El primero y el tercer campo tienen el mismo nombre x
y el segundo campo es anónimo.
El ejemplo anterior genera el mismo resultado que esta consulta mediante un constructor de struct:
SELECT AS VALUE STRUCT(1 AS x, 2, 3 AS x)
Ejemplo:
SELECT
ARRAY(SELECT AS STRUCT t.f1, t.f2 WHERE t.f3=true)
FROM
Table t
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 arreglo (consulta las Subconsultas) por lo general, no pueden mostrar varias columnas.
SELECT AS VALUE
SELECT AS VALUE
genera una tabla de valores de cualquier lista SELECT
que produce, con exactitud, 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 a, 2 b) xyz FROM Table;
La consulta anterior produce una tabla con el tipo de fila STRUCT<a int64, b int64>
.
Alias
Consulta Alias para obtener información sobre la sintaxis y la visibilidad de los alias de la lista SELECT
.
Funciones analíticas
Las funciones analíticas y las cláusulas relacionadas con ellas, incluidas OVER
, PARTITION
BY
y WINDOW
, están documentadas en Conceptos de funciones analíticas.
Cláusula FROM
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.
Sintaxis
from_item: { table_name [ [ AS ] alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ] | join | ( query_expr ) [ [ AS ] alias ] | field_path | { UNNEST( array_expression ) | UNNEST( array_path ) | array_path } [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] | with_query_name [ [ AS ] alias ] }
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
, que incluye un arreglo plano o la salida 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 rangos siguientes:
- 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 timestamp_expression
contiene una referencia correlacionada con una columna en la consulta que lo contiene.
SELECT *
FROM t1
WHERE t1.a IN (SELECT t2.a
FROM t2 FOR SYSTEM_TIME AS OF t1.timestamp_column);
join
Consulta Tipos de JOIN, a continuación.
select
( select ) [ [ AS ] alias ]
es una subconsulta de tabla.
field_path
En la cláusula FROM
, field_path
es cualquier ruta que se resuelve 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
que incluyen lo siguiente:
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 terminar 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.
Nota: Si una ruta de acceso tiene solo un nombre, se interpreta como una tabla.
Para solucionar esto, une la ruta con UNNEST
o usa la ruta de acceso calificada por completo.
UNNEST
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 otra parte de la consulta. Los ARRAYS
con estos tipos de elementos muestran varias columnas:
- STRUCT
UNNEST
destruye el orden de los elementos en la entrada ARRAY
. Usa la cláusula opcional WITH OFFSET
para mostrar una segunda columna con los índices del elemento del arreglo (consulta a continuación).
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 poner 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} |
+---+-----+--------------+
El ARRAY desanidado puede ser explícito o implícito. En el desanidado explícito, array_expression
debe mostrar un valor ARRAY, pero no necesita resolverse en un ARRAY y 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 una estructura de datos, de manera arbitraria, en más profundidad, 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 nombrado de un ARRAY.
UNNEST
trata los NULL de la siguiente manera:
- Los NULL y ARRAY vacíos no producen filas.
- Un ARRAY que contiene NULL produce filas que contienen valores NULL.
La cláusula opcional OFFSET
WITH
muestra una columna aparte que contiene el valor de “desplazamiento” (es decir, el conteo comienza en cero) de cada fila que genera la operación UNNEST
. Esta columna tiene un alias
opcional, el alias predeterminado es el desplazamiento.
Ejemplo:
SELECT * FROM UNNEST ( ) WITH OFFSET AS num;
Consulta Arrays topic
para obtener más formas de usar UNNEST
, incluida la construcción, la compactación y el filtrado.
with_query_name
Los nombres de consulta en una cláusula WITH
(consulta la Cláusula WITH ) actúan como nombres de tablas temporales a las que puedes hacer referencia en cualquier parte de la cláusula FROM
. En el siguiente ejemplo, subQ1
y subQ2
son with_query_names
.
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 por la duración de la consulta, a menos que califiques el nombre de la tabla, p. ej., dataset.Roster
o project.dataset.Roster
.
Subconsultas
Una subconsulta es una consulta que aparece dentro de otra declaración y se escribe entre paréntesis. También se las conoce como “sub-SELECT” o “SELECT anidadas”. La sintaxis completa de SELECT
es válida en las subconsultas.
Existen dos tipos de subconsultas:
- Subconsultas de expresión, que puedes usar en una consulta en la que las expresiones sean válidas. Las subconsultas de expresión muestran un solo valor.
- Subconsultas de tabla, que solo puedes usar en una cláusula
FROM
. La consulta externa trata el resultado de la subconsulta como una tabla.
Ten en cuenta que debe haber paréntesis en ambos tipos de subconsultas.
Ejemplo:
SELECT AVG ( PointsScored )
FROM
( SELECT PointsScored
FROM Stats
WHERE SchoolID = 77 )
De manera opcional, una subconsulta de tabla puede tener un alias.
Ejemplo:
SELECT r.LastName
FROM
( SELECT * FROM Roster) AS r;
Alias
Consulta los Alias para obtener información sobre la sintaxis y la visibilidad de los alias de la cláusula FROM
.
Tipos de JOIN
Sintaxis
join: from_item [ join_type ] JOIN from_item [ ON bool_expression | USING ( join_column [, ...] ) ] join_type: { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }
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.
Todas las cláusulas JOIN
requieren un join_type
.
Una cláusula JOIN
requiere una condición JOIN, a menos que una de las siguientes condiciones sea verdadera:
join_type
esCROSS
.- Uno o ambos de los
from_item
no es una tabla, p. ej., unarray_path
ofield_path
.
[INNER] JOIN
Un INNER JOIN
, o simplemente JOIN
, calcula de manera efectiva el producto cartesiano de los dos from_item
y descarta todas las filas que no cumplen la condición JOIN. “De manera efectiva” significa que es posible implementar una INNER JOIN
sin calcular el producto cartesiano.
CROSS JOIN
CROSS JOIN
muestra el producto cartesiano de los dos from_item
. En otras palabras, retiene todas las filas de ambos from_item
y combina cada fila del primer from_item
con cada fila del segundo from_item
.
CROSS JOIN con coma
CROSS JOIN
se puede escribir de forma explícita (consulta la sección anterior) o de forma implícita con una coma para separar los from_item
.
Ejemplo de una “CROSS JOIN con coma” implícita:
SELECT * FROM Roster, TeamMascot;
Este es el equivalente explícito de la CROSS JOIN:
SELECT * FROM Roster CROSS JOIN TeamMascot;
No puedes escribir CROSS JOIN con comas entre paréntesis.
No válido: CROSS JOIN con coma entre paréntesis:
SELECT * FROM t CROSS JOIN (Roster, TeamMascot); // INVALID.
Consulta Secuencias de JOIN para obtener detalles sobre cómo se comporta una CROSS JOIN con coma en una secuencia de JOIN.
FULL [OUTER] JOIN
Un FULL OUTER JOIN
(o simplemente FULL JOIN
) muestra todos los campos para todas las filas en ambos from_item
que cumplen la condición JOIN.
FULL
indica que se muestran todas las filas de ambos from_item
, incluso si no cumplen 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
.
LEFT [OUTER] JOIN
El resultado de una LEFT OUTER JOIN
(o simplemente LEFT JOIN
) para dos from_item
siempre retiene 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 unión.
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, la fila mostrará con NULL todas las columnas del from_item
de la derecha. Las filas del from_item
de la derecha que no se unen a ninguna fila del from_item
de la izquierda se descartan.
RIGHT [OUTER] JOIN
El resultado de una RIGHT OUTER JOIN
(o simplemente RIGHT JOIN
) es similar y simétrico al de LEFT OUTER JOIN
.
Cláusula ON
La cláusula ON
contiene una bool_expression
. Una fila combinada (el resultado de la unión de dos filas) cumple la condición JOIN si bool_expression
muestra TRUE.
Ejemplo:
SELECT * FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;
Cláusula USING
La cláusula USING
requiere una column_list
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 JOIN si la comparación de igualdad muestra TRUE.
En la mayoría de los casos, una declaración con la palabra clave USING
es equivalente a usar la palabra clave ON
. Por ejemplo, la declaración:
SELECT FirstName
FROM Roster INNER JOIN PlayerStats
USING (LastName);
es equivalente a lo siguiente:
SELECT FirstName
FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;
Los resultados de las consultas con USING
difieren de las consultas que usan ON
cuando usas SELECT *
. A modo de ejemplo, considera la consulta siguiente:
SELECT * FROM Roster INNER JOIN PlayerStats
USING (LastName);
Esta declaración muestra las filas de Roster
y PlayerStats
en las que Roster.LastName
es lo mismo que PlayerStats.LastName
. Los resultados incluyen una sola columna LastName
.
Por otro lado, considera la consulta siguiente:
SELECT * FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;
Esta declaración muestra las filas de Roster
y PlayerStats
en las que Roster.LastName
es lo mismo que PlayerStats.LastName
. Los resultados incluyen dos columnas LastName
; una de Roster
y una de PlayerStats
.
Secuencias de JOIN
La cláusula FROM
puede contener varias cláusulas JOIN
en una secuencia.
Ejemplo:
SELECT * FROM a LEFT JOIN b ON TRUE LEFT JOIN c ON TRUE;
en el que a
, b
, y c
son cualquier from_item
. Las JOIN están vinculadas de izquierda a derecha, pero puedes insertar paréntesis para agruparlas en un orden diferente.
Considera las siguientes consultas: A (sin paréntesis) y B (con paréntesis) son equivalentes entre sí, pero no con C. La FULL JOIN
en negrita se une primero.
A.
SELECT * FROM Roster FULL JOIN TeamMascot USING (SchoolID) FULL JOIN PlayerStats USING (LastName);
B.
SELECT * FROM ( (Roster FULL JOIN TeamMascot USING (SchoolID)) FULL JOIN PlayerStats USING (LastName));
C.
SELECT * FROM (Roster FULL JOIN (TeamMascot FULL JOIN PlayerStats USING (LastName)) USING (SchoolID)) ;
Cuando las CROSS JOIN con coma están presentes en una consulta con una secuencia de JOIN, se agrupan de izquierda a derecha como otros tipos de JOIN
.
Ejemplo:
SELECT * FROM a JOIN b ON TRUE, b JOIN c ON TRUE;
La consulta anterior es equivalente a
SELECT * FROM ((a JOIN b ON TRUE) CROSS JOIN b) JOIN c ON TRUE);
No puede haber una RIGHT JOIN
o FULL JOIN
después de una unión de comas.
No válido: RIGHT JOIN
después de una CROSS JOIN con comas:
SELECT * FROM Roster, TeamMascot RIGHT JOIN PlayerStats ON TRUE; // INVALID.
Cláusula WHERE
Sintaxis
WHERE bool_expression
La cláusula WHERE
filtra las filas mediante la evaluación de cada fila con bool_expression
y descarta todas las filas que no muestren TRUE (es decir, las filas que muestren FALSE o NULL).
Ejemplo:
SELECT * FROM Roster
WHERE SchoolID = 52;
La bool_expression
puede contener varias subcondiciones.
Ejemplo:
SELECT * FROM Roster
WHERE STARTS_WITH(LastName, "Mc") OR STARTS_WITH(LastName, "Mac");
No puedes hacer referencia a los alias de columna de la lista SELECT
en la cláusula WHERE
.
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, esta consulta:
SELECT * FROM Roster INNER JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
es equivalente a lo siguiente:
SELECT * FROM Roster CROSS JOIN TeamMascot
WHERE Roster.SchoolID = TeamMascot.SchoolID;
Cláusula GROUP BY
Sintaxis
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 hacer 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 tener 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 completa (a, b)
. El resultado incluirá los resultados de GROUP BY
para los conjuntos de agrupación (a, b)
, (a)
y ()
, que incluyen 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
Sintaxis
HAVING bool_expression
La cláusula HAVING
es similar a la cláusula WHERE
: filtra las filas que no muestran TRUE cuando se evalúan contra la bool_expression
.
Al igual que con la cláusula WHERE
, bool_expression
puede ser cualquier expresión que muestre un valor booleano y puede contener varias subcondiciones.
La cláusula HAVING
se distingue de la cláusula WHERE
en cuanto a los siguientes factores:
- La cláusula
HAVING
requiereGROUP BY
o la agregación para estar presente en la consulta. - La cláusula
HAVING
se produce después deGROUP BY
y la agregación y antes deORDER BY
. Esto significa que la cláusulaHAVING
se evalúa una vez en cada fila agregada en el conjunto de resultados. Esto se distingue de la cláusulaWHERE
, que se evalúa antes deGROUP BY
y la agregación.
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 los 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 formas siguientes:
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
Sintaxis
ORDER BY expression [{ ASC | DESC }] [, ...]
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. La dirección de clasificación predeterminada es ASC
, que clasifica los resultados en el orden ascendente de los valores de expression
. DESC
ordena los resultados en orden descendente. 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.
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;
Las siguientes reglas se aplican cuando se ordenan los valores:
- NULL: en el contexto de la cláusula
ORDER BY
, los NULL son los valores mínimos posibles; es decir, los NULL aparecen primeros en el ordenASC
y últimos en el ordenDESC
. - Tipos de datos de punto flotante: consulta Semántica de punto flotante sobre cómo ordenar y agrupar.
Cuando se usa junto con configurar operadores, la cláusula ORDER BY
se aplica al conjunto de resultados de toda la consulta; no se aplica 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
ORDER BY LastName;
SELECT SUM(PointsScored), LastName
FROM PlayerStats
ORDER BY 2;
Configurar operadores
Sintaxis
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):
- Para
UNION ALL
, R aparece exactamente m + n veces en el resultado. - Para
UNION DISTINCT
,DISTINCT
se calcula después de calcularUNION
, por lo que R aparece exactamente una vez. - Para
INTERSECT DISTINCT
,DISTINCT
se calcula después de calcular el resultado anterior. - Para
EXCEPT DISTINCT
, la fila R aparece una vez en la salida 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 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 configuración, 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 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.
Cláusula LIMIT y cláusula OFFSET
Sintaxis
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 configuración, se aplica LIMIT
después de evaluar esa operación.
OFFSET
especifica un skip_rows
no negativo de tipo INT64 y solo se considerarán las filas de ese desplazamiento en la tabla.
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
.
Cláusula WITH
La cláusula WITH
contiene una o más subconsultas con nombre que se ejecutan cada vez que una declaración SELECT
posterior hace referencia a estas. Cualquier cláusula o subconsulta puede hacer referencia a subconsultas que definas en la cláusula WITH
. Esto incluye cualquier declaración SELECT
al lado de un operador de conjunto, como UNION
.
La cláusula WITH
es útil sobre todo para facilitar la lectura, ya que BigQuery no materializa el resultado de las consultas dentro de la cláusula WITH
. Si una consulta aparece en más de una cláusula WITH
, se ejecuta en cada cláusula.
Ejemplo:
WITH subQ1 AS (SELECT SchoolID FROM Roster),
subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2;
Otra función útil de la cláusula WITH
es dividir las consultas más complejas en una declaración WITH
SELECT
y cláusulas WITH
, en que la alternativa menos deseable es escribir subconsultas de tablas anidadas. Si una cláusula WITH
contiene varias subconsultas, los nombres de las subconsultas no pueden repetirse.
BigQuery admite cláusulas WITH
en las subconsultas, como subconsultas de tabla, subconsultas de expresión, etcétera.
WITH q1 AS (my_query)
SELECT *
FROM
(WITH q2 AS (SELECT * FROM q1) SELECT * FROM q2)
Las siguientes son reglas de alcance para las cláusulas WITH
:
- Los alias tienen un alcance para que los alias ingresados en una cláusula
WITH
sean visibles solo en las subconsultas posteriores en la misma cláusulaWITH
y en la consulta de la cláusulaWITH
. - Los alias ingresados en la misma cláusula
WITH
deben ser únicos, pero el mismo alias se puede usar en varias cláusulasWITH
en la misma consulta. El alias local anula cualquier alias externo en cualquier lugar en el que el alias local sea visible. - Las subconsultas con alias en una cláusula
WITH
nunca se pueden correlacionar. Ninguna columna de afuera de la consulta es visible. Los únicos nombres externos que son visibles son los otros aliasWITH
que se ingresaron con anterioridad en la misma cláusulaWITH
.
Este es un ejemplo de una declaración que usa alias en subconsultas WITH
:
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.
BigQuery no es compatible con WITH RECURSIVE
.
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.
Sintaxis del alias explícito
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, matrices, 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;
Visibilidad del alias explícito
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.
Alias de 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 mesa Singers
tenía una columna de 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 usar el alias explícito 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.
SELECCIONA alias de lista
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;
Alias explícitos en las cláusulas GROUP BY, ORDER BY y HAVING
Estas tres cláusulas, GROUP BY
, ORDER BY
y HAVING
, solo pueden hacer referencia a los valores siguientes:
- 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
se refiere al primer elemento de la listaSELECT
,2
se refiere al segundo elemento, etcétera.
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 ambiguos
BigQuery mostrará un error si un nombre es ambiguo, lo que significa que puede resolverse en más de un objeto único.
Ejemplos:
Esta consulta contiene nombres de columnas que entran en conflicto entre 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;
La ambigüedad entre un nombre de columna de la cláusula FROM
y un alias de lista SELECT
en GROUP BY
:
SELECT UPPER(LastName) AS LastName
FROM Singers
GROUP BY LastName;
La consulta anterior es ambigua y producirá un error, ya que LastName
en la cláusula GROUP BY
podría referirse a la columna original LastName
en Singers
o podría referirse al alias AS LastName
, cuyo valor es UPPER(LastName)
.
Las mismas reglas de ambigüedad se aplican en las expresiones de la ruta de acceso. Considera la siguiente consulta en la que la table
tiene las columnas x
y, también y
, y la columna z
es de tipo STRUCT y tiene campos v
, w
, y x
.
Ejemplo:
SELECT x, z AS T
FROM table 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 es un nombre de columna y un alias de 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
.
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 la ruta de acceso, el alias es el último identificador en la ruta de acceso. 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
, no se requiere que from_item
tenga 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 la ruta de acceso, el alias es el último identificador en la ruta de acceso. 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.
Apéndice A: ejemplos con datos de muestra
Tablas de muestra
Las siguientes tres tablas contienen datos de muestra sobre atletas, sus escuelas y los puntos que obtienen durante la temporada. Estas tablas se usarán para ilustrar el comportamiento de diferentes cláusulas de consulta.
Tabla Roster:
LastName | SchoolID |
---|---|
Adams | 50 |
Buchanan | 52 |
Coolidge | 52 |
Davis | 51 |
Eisenhower | 77 |
En la tabla Roster, se incluye una lista de nombres de los jugadores (LastName) y el ID único asignado a su escuela (SchoolID).
Tabla PlayerStats:
LastName | OpponentID | PointsScored |
---|---|---|
Adams | 51 | 3 |
Buchanan | 77 | 0 |
Coolidge | 77 | 1 |
Adams | 52 | 4 |
Buchanan | 50 | 13 |
En la tabla PlayerStats, se incluye una lista de nombres de jugadores (LastName) y el ID único asignado al oponente contra el que jugaron en un partido determinado (OpponentID) y la cantidad de puntos que el atleta obtuvo en ese juego (PointsScored).
Tabla TeamMascot:
SchoolId | Mascot |
---|---|
50 | Jaguars |
51 | Knights |
52 | Lakers |
53 | Mustangs |
En la tabla TeamMascot, se incluye una lista de ID escolares únicos (SchoolID) y la mascota de esa escuela (Mascot).
Tipos de JOIN
1) [INNER] JOIN
Ejemplo:
SELECT * FROM Roster JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
Resultados:
LastName | Roster.SchoolId | TeamMascot.SchoolId | Mascot |
---|---|---|---|
Adams | 50 | 50 | Jaguars |
Buchanan | 52 | 52 | Lakers |
Coolidge | 52 | 52 | Lakers |
Davis | 51 | 51 | Knights |
2) CROSS JOIN
Ejemplo:
SELECT * FROM Roster CROSS JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
Resultados:
LastName | Roster.SchoolId | TeamMascot.SchoolId | Mascot |
---|---|---|---|
Adams | 50 | 50 | Jaguars |
Adams | 50 | 51 | Knights |
Adams | 50 | 52 | Lakers |
Adams | 50 | 53 | Mustangs |
Buchanan | 52 | 50 | Jaguars |
Buchanan | 52 | 51 | Knights |
Buchanan | 52 | 52 | Lakers |
Buchanan | 52 | 53 | Mustangs |
Coolidge | 52 | 50 | Jaguars |
Coolidge | 52 | 51 | Knights |
Coolidge | 52 | 52 | Lakers |
Coolidge | 52 | 53 | Mustangs |
Davis | 51 | 50 | Jaguars |
Davis | 51 | 51 | Knights |
Davis | 51 | 52 | Lakers |
Davis | 51 | 53 | Mustangs |
Eisenhower | 77 | 50 | Jaguars |
Eisenhower | 77 | 51 | Knights |
Eisenhower | 77 | 52 | Lakers |
Eisenhower | 77 | 53 | Mustangs |
3) FULL [OUTER] JOIN
Ejemplo:
SELECT * FROM Roster FULL JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
LastName | Roster.SchoolId | TeamMascot.SchoolId | Mascot |
---|---|---|---|
Adams | 50 | 50 | Jaguars |
Buchanan | 52 | 52 | Lakers |
Coolidge | 52 | 52 | Lakers |
Davis | 51 | 51 | Knights |
Eisenhower | 77 | NULL | NULL |
NULL | NULL | 53 | Mustangs |
4) LEFT [OUTER] JOIN
Ejemplo:
SELECT * FROM Roster LEFT JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
Resultados:
LastName | Roster.SchoolId | TeamMascot.SchoolId | Mascot |
---|---|---|---|
Adams | 50 | 50 | Jaguars |
Buchanan | 52 | 52 | Lakers |
Coolidge | 52 | 52 | Lakers |
Davis | 51 | 51 | Knights |
Eisenhower | 77 | NULL | NULL |
5) RIGHT [OUTER] JOIN
Ejemplo:
SELECT * FROM Roster RIGHT JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
Resultados:
LastName | Roster.SchoolId | TeamMascot.SchoolId | Mascot |
---|---|---|---|
Adams | 50 | 50 | Jaguars |
Davis | 51 | 51 | Knights |
Coolidge | 52 | 52 | Lakers |
Buchanan | 52 | 52 | Lakers |
NULL | NULL | 53 | Mustangs |
Cláusula GROUP BY
Ejemplo:
SELECT LastName, SUM(PointsScored)
FROM PlayerStats
GROUP BY LastName;
LastName | SUM |
---|---|
Adams | 7 |
Buchanan | 13 |
Coolidge | 1 |
Configurar operadores
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 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 declaraciones 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)