Sintaxis de las consultas

Organízate con las colecciones Guarda y clasifica el contenido según tus preferencias.

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 SQL estándar de Google para 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 SELECTque 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 operador UNNEST
  • 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 arrays.

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,
      z STRUCT<a INT64, b INT64>>>[
        (1, 'foo', (10, 11)),
        (3, 'bar', (20, 21))]);

+---+-----+----------+
| x | y   | z        |
+---+-----+----------+
| 1 | foo | {10, 11} |
| 3 | bar | {20, 21} |
+---+-----+----------+

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 NULLs de la siguiente manera:

  • NULL y los arrays vacíos no producen filas.
  • Un array que contiene NULLs produce filas que contienen valores NULL.

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 con FOR SYSTEM_TIME AS OF, aunque los usuarios pueden usar PIVOT en una entrada de subconsulta que usa FOR SYSTEM_TIME AS OF.
  • Una cláusula WITH OFFSET que precede de inmediato al operador PIVOT 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. El from_item debe seguir estas reglas.
  • pivot_operator: La operación pivot que se realiza en un from_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 que input_column coincida con un valor particular en pivot_column. Cada agregación correspondiente a un valor pivot_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 en input_column para que los valores se puedan comparar. Es posible tener un valor en pivot_column que no coincida con un valor en input_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 use SELECT 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áusula PIVOT 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 entradas NULL.
  • Si usas COUNT, puedes usar * como argumento.

Reglas para input_column:

  • 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.

Reglas para pivot_column:

  • 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 de NULL 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:
  • Los números positivos están precedidos por _.
  • Los números negativos van precedidos por minus_.
  • Se reemplaza un punto decimal por _point_.
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:
  • Si el campo se llama: <field_name>_<pivot_column_name_for_field_name>.
  • Si el campo no tiene nombre: <pivot_column_name_for_field_name>.

<pivot_column_name_for_field_name> se determina mediante la aplicación recursiva de las reglas de esta tabla. Si no hay una regla disponible para ningún campo STRUCT, toda la columna dinámica no tendrá nombre.

Debido a la coerción de tipo implícita de los valores de lista IN al tipo <value-expression>, los nombres de los campos deben estar presentes en input_column a fin de tener un efecto en los nombres de los columnas dinámicas.

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 con FOR SYSTEM_TIME AS OF, aunque los usuarios pueden usar UNPIVOT en una entrada de subconsulta que en sí usa FOR SYSTEM_TIME AS OF.
  • Una cláusula WITH OFFSET que precede de inmediato al operador UNPIVOT no está permitida.
  • Las agregaciones de PIVOT no se pueden revertir con UNPIVOT.

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. El from_item debe seguir estas reglas.
  • unpivot_operator: La operación pivot que se realiza en un from_item.

Definiciones de unpivot_operator:

  • INCLUDE NULLS: Agrega filas con valores NULL al resultado.
  • EXCLUDE NULLS: No agrega filas con valores NULL al resultado. De forma predeterminada, UNPIVOT excluye las filas con valores NULL.
  • single_column_unpivot: Rota columnas en un values_column y en un name_column.
  • multi_column_unpivot: Rota columnas en varios values_column y name_column.
  • unpivot_alias: Es un alias para los resultados de la operación UNPIVOT. 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 de columns_to_unpivot. Sigue estas reglas cuando crees una columna de valores.
  • name_column: Es una columna que contiene los nombres de columna de columns_to_unpivot. Sigue estas reglas cuando crees una columna de nombres.
  • columns_to_unpivot: Las columnas de from_item a fin de propagar values_column y name_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 en name_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 de columns_to_unpivot. Sigue estas reglas cuando crees una columna de valores.
  • name_column: Es un conjunto de columnas para contener los nombres de columna de columns_to_unpivot Sigue estas reglas cuando crees una columna de nombres.
  • column_sets_to_unpivot: Las columnas de from_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 de name_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) genera col1_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áusula UNPIVOT.

Reglas para unpivot_operator:

  • 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.

Reglas para values_column:

  • No puede ser un nombre que se usa para una name_column o una unpivot_column.
  • Puede tener el mismo nombre que una columna de from_item.

Reglas para name_column:

  • No puede ser un nombre que se usa para una values_column o una unpivot_column.
  • Puede tener el mismo nombre que una columna de from_item.

Reglas para unpivot_column:

  • 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.

Reglas para row_value_alias:

  • Puede ser un literal STRING o INT64.
  • 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 el row_value_alias para cada unpivot_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. SQL estándar de Google 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 coincidentes de ambos from_items que cumplen con la condición JOIN. 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 una LEFT JOIN regular. Cuando no hay filas unidas, se genera una fila con valores NULL 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 una INNER 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ón
  • HAVING
  • WINDOW
  • QUALIFY
  • DISTINCT
  • ORDER BY
  • LIMIT

El orden de evaluación no siempre coincide con el orden de sintaxis.

La cláusula WHERE solo hace 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ón
  • HAVING
  • WINDOW
  • QUALIFY
  • DISTINCT
  • ORDER BY
  • LIMIT

El orden de evaluación no siempre coincide con el orden de sintaxis.

La cláusula HAVING hace referencia a columnas disponibles a través de la cláusula FROM, así como a 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 de expression en orden ascendente o descendente. ASC es el valor predeterminado. Si no se especifica un ordenamiento de valores nulos mediante NULLS FIRST o NULLS 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. Debe haber una función analítica en la cláusula QUALIFY o 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ón
  • HAVING
  • WINDOW
  • QUALIFY
  • DISTINCT
  • ORDER BY
  • LIMIT

El orden de evaluación no siempre coincide con el orden de sintaxis.

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