Funciones de agregación aproximada en SQL estándar

Las funciones de agregación aproximada son escalables en términos de tiempo y uso de memoria, pero generan resultados aproximados en lugar de resultados exactos. Las funciones de agregación aproximada requieren menos memoria que las funciones de agregación exacta, como COUNT(DISTINCT ...), pero también generan incertidumbre estadística. Esto hace que la agregación aproximada sea apropiada en grandes flujos de datos en los que el uso de memoria lineal no es práctico y para los datos que ya son aproximados.

Las funciones de agregación aproximadas de esta sección funcionan directamente en los datos de entrada, en lugar de una estimación intermedia de los datos. Estas funciones no permiten que los usuarios especifiquen la precisión de la estimación con esbozos. Si deseas especificar la precisión con esbozos, consulta lo siguiente:

APPROX_COUNT_DISTINCT

APPROX_COUNT_DISTINCT(expression)

Descripción

Muestra el resultado aproximado para COUNT(DISTINCT expression). El valor que se mostró es una estimación estadística, no es necesariamente el valor real.

Esta función es menos precisa que COUNT(DISTINCT expression), pero funciona mejor en entradas grandes.

Tipos de argumentos admitidos

Cualquier tipo de datos excepto: ARRAY STRUCT

Tipos de datos mostrados

INT64

Ejemplos

SELECT APPROX_COUNT_DISTINCT(x) as approx_distinct
FROM UNNEST([0, 1, 1, 2, 3, 5]) as x;

+-----------------+
| approx_distinct |
+-----------------+
| 5               |
+-----------------+

APPROX_QUANTILES

APPROX_QUANTILES([DISTINCT] expression, number [{IGNORE|RESPECT} NULLS])

Descripción

Muestra los límites aproximados para un grupo de valores expression, en los que number representa la cantidad de cuantiles que se crearán. Esta función muestra un arreglo de number + 1 elementos, en el que el primer elemento es el mínimo aproximado y el último es el máximo aproximado.

Tipos de argumentos admitidos

expression puede ser cualquier tipo de datos admitido, excepto: ARRAY STRUCT

number debe ser INT64.

Cláusulas opcionales

Las cláusulas se aplican en el siguiente orden:

  1. DISTINCT: Cada valor distinto de expression se agrega solo una vez en el resultado.
  2. IGNORE NULLS o RESPECT NULLS: si se especifica IGNORE NULLS o si no se especifica ninguno, los valores NULL se excluyen del resultado. Si se especifica RESPECT NULLS, los valores NULL se incluyen en el resultado. Se genera un error si un arreglo en el resultado final de la consulta contiene un elemento NULL.

Tipos de datos mostrados

Un ARRAY del tipo especificado por el parámetro expression.

Muestra NULL si no hay filas de entrada o si expression se evalúa como NULL en todas las filas.

Ejemplos

SELECT APPROX_QUANTILES(x, 2) AS approx_quantiles
FROM UNNEST([1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [1, 5, 10]       |
+------------------+

SELECT APPROX_QUANTILES(x, 100)[OFFSET(90)] AS percentile_90
FROM UNNEST([1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) AS x;

+---------------+
| percentile_90 |
+---------------+
| 9             |
+---------------+

SELECT APPROX_QUANTILES(DISTINCT x, 2) AS approx_quantiles
FROM UNNEST([1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [1, 6, 10]       |
+------------------+

SELECT FORMAT("%T", APPROX_QUANTILES(x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [NULL, 4, 10]    |
+------------------+

SELECT FORMAT("%T", APPROX_QUANTILES(DISTINCT x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [NULL, 6, 10]    |
+------------------+

APPROX_TOP_COUNT

APPROX_TOP_COUNT(expression, number)

Descripción

Muestra los elementos principales aproximados de expression. El parámetro number especifica la cantidad de elementos que se muestran.

Tipos de argumentos admitidos

expression puede ser de cualquier tipo de datos que admita la cláusula GROUP BY.

number debe ser INT64.

Tipos de datos mostrados

Un ARRAY del tipo STRUCT. El STRUCT contiene dos campos. El primer campo (llamado value) contiene un valor de entrada. El segundo campo (llamado count) contiene un INT64 que especifica la cantidad de veces que se mostró el valor.

Muestra NULL si no hay filas de entrada.

Ejemplos

SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST(["apple", "apple", "pear", "pear", "pear", "banana"]) as x;

+-------------------------+
| approx_top_count        |
+-------------------------+
| [{pear, 3}, {apple, 2}] |
+-------------------------+

Manejo de NULL

APPROX_TOP_COUNT no ignora los NULL de la entrada. Por ejemplo:

SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST([NULL, "pear", "pear", "pear", "apple", NULL]) as x;

+------------------------+
| approx_top_count       |
+------------------------+
| [{pear, 3}, {NULL, 2}] |
+------------------------+

APPROX_TOP_SUM

APPROX_TOP_SUM(expression, weight, number)

Descripción

Muestra los elementos principales aproximados de expression, en función de la suma de un weight asignado. El parámetro number especifica la cantidad de elementos que se muestran.

Si la entrada weight es negativa o si es NaN, esta función muestra un error.

Tipos de argumentos admitidos

expression puede ser de cualquier tipo de datos que admita la cláusula GROUP BY.

weight debe ser una de las siguientes opciones:

  • INT64
  • NUMERIC
  • FLOAT64

number debe ser INT64.

Tipos de datos mostrados

Un ARRAY del tipo STRUCT. El STRUCT contiene dos campos: value y sum. El campo value contiene el valor de la expresión de entrada. El campo sum es del mismo tipo que weight y es la suma aproximada del peso de entrada asociado con el campo value.

Muestra NULL si no hay filas de entrada.

Ejemplos

SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([
  STRUCT("apple" AS x, 3 AS weight),
  ("pear", 2),
  ("apple", 0),
  ("banana", 5),
  ("pear", 4)
]);

+--------------------------+
| approx_top_sum           |
+--------------------------+
| [{pear, 6}, {banana, 5}] |
+--------------------------+

Manejo de NULL

APPROX_TOP_SUM no ignora los valores NULL de los parámetros expression y weight.

SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, NULL AS weight), ("pear", 0), ("pear", NULL)]);

+----------------------------+
| approx_top_sum             |
+----------------------------+
| [{pear, 0}, {apple, NULL}] |
+----------------------------+

SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, 2)]);

+-------------------------+
| approx_top_sum          |
+-------------------------+
| [{NULL, 2}, {apple, 0}] |
+-------------------------+

SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, NULL)]);

+----------------------------+
| approx_top_sum             |
+----------------------------+
| [{apple, 0}, {NULL, NULL}] |
+----------------------------+