Combinadores de funciones de agregación

Al nombre de una función de agregación se le puede añadir un sufijo. Esto cambia la forma en que funciona la función de agregación.

-If

El sufijo -If puede añadirse al nombre de cualquier función de agregación. En este caso, la función de agregación acepta un argumento adicional: una condición (de tipo Uint8). La función de agregación procesa solo las filas que cumplen la condición. Si la condición no se cumple ni una sola vez, devuelve un valor predeterminado (normalmente ceros o cadenas vacías). Ejemplos: sumIf(column, cond), countIf(cond), avgIf(x, cond), quantilesTimingIf(level1, level2)(x, cond), argMinIf(arg, val, cond) y así sucesivamente. Con las funciones de agregación condicionales, puede calcular agregaciones para varias condiciones a la vez sin usar subconsultas ni JOINs. Por ejemplo, las funciones de agregación condicionales pueden usarse para implementar la funcionalidad de comparación de segmentos.

-Array

El sufijo -Array puede añadirse a cualquier función de agregación. En este caso, la función de agregación toma argumentos del tipo Array(T) (arrays) en lugar de argumentos del tipo T. Si la función de agregación acepta varios argumentos, estos deben ser arrays de la misma longitud. Al procesar arrays, la función de agregación funciona como la función de agregación original sobre todos los elementos del array. Ejemplo 1: sumArray(arr) - Suma todos los elementos de todos los arrays arr. En este ejemplo, podría haberse escrito de forma más simple: sum(arraySum(arr)). Ejemplo 2: uniqArray(arr) – Cuenta el número de elementos únicos en todos los arrays arr. Esto podría hacerse de una forma más sencilla: uniq(arrayJoin(arr)), pero no siempre es posible añadir arrayJoin a una consulta. -If y -Array pueden combinarse. Sin embargo, Array debe ir primero y luego If. Ejemplos: uniqArrayIf(arr, cond), quantilesTimingArrayIf(level1, level2)(arr, cond). Debido a este orden, el argumento cond no será un array.

-Map

El sufijo -Map puede añadirse a cualquier función de agregación. Esto crea una función de agregación que toma un tipo Map como argumento y agrega por separado los valores de cada clave del mapa mediante la función de agregación especificada. El resultado también es de tipo Map. Ejemplo

CREATE TABLE map_map(
    date Date,
    timeslot DateTime,
    status Map(String, UInt64)
) ENGINE = MergeTree
ORDER BY ();

INSERT INTO map_map VALUES
    ('2000-01-01', '2000-01-01 00:00:00', (['a', 'b', 'c'], [10, 10, 10])),
    ('2000-01-01', '2000-01-01 00:00:00', (['c', 'd', 'e'], [10, 10, 10])),
    ('2000-01-01', '2000-01-01 00:01:00', (['d', 'e', 'f'], [10, 10, 10])),
    ('2000-01-01', '2000-01-01 00:01:00', (['f', 'g', 'g'], [10, 10, 10]));

SELECT
    timeslot,
    sumMap(status),
    avgMap(status),
    minMap(status)
FROM map_map
GROUP BY timeslot;

┌────────────timeslot─┬─sumMap(status)───────────────────────┬─avgMap(status)───────────────────────┬─minMap(status)───────────────────────┐
│ 2000-01-01 00:00:00 │ {'a':10,'b':10,'c':20,'d':10,'e':10} │ {'a':10,'b':10,'c':10,'d':10,'e':10} │ {'a':10,'b':10,'c':10,'d':10,'e':10} │
│ 2000-01-01 00:01:00 │ {'d':10,'e':10,'f':20,'g':20}        │ {'d':10,'e':10,'f':10,'g':10}        │ {'d':10,'e':10,'f':10,'g':10}        │
└─────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┘

-SimpleState

Si se aplica este combinador, la función de agregación devuelve el mismo valor, pero con un tipo distinto. Se trata de una SimpleAggregateFunction(…) que puede almacenarse en una tabla para usarla con tablas AggregatingMergeTree. Sintaxis

<aggFunction>SimpleState(x)

Argumentos

x — Parámetros de la función de agregación.

Valores devueltos El valor de una función de agregación del tipo SimpleAggregateFunction(...). Ejemplo

Query

WITH anySimpleState(number) AS c SELECT toTypeName(c), c FROM numbers(1);

Response

┌─toTypeName(c)────────────────────────┬─c─┐
│ SimpleAggregateFunction(any, UInt64) │ 0 │
└──────────────────────────────────────┴───┘

-State

Si se aplica este combinador, la función de agregación no devuelve el valor resultante (como el número de valores únicos de la función uniq), sino un estado intermedio de la agregación (para uniq, se trata de la tabla hash para calcular el número de valores únicos). Se trata de un AggregateFunction(...) que puede usarse para su posterior procesamiento o almacenarse en una tabla para completar la agregación más adelante.

Tenga en cuenta que -MapState no es invariante para los mismos datos, ya que el orden de los datos en el estado intermedio puede cambiar, aunque esto no afecta a la ingestión de esos datos.

Para trabajar con estos estados, use:

el motor de tabla AggregatingMergeTree.
la función finalizeAggregation.
la función runningAccumulate.
el combinador -Merge.
el combinador -MergeState.

-Merge

Si se aplica este combinador, la función de agregación toma como argumento el estado intermedio de agregación, combina los estados para completar la agregación y devuelve el valor resultante.

-MergeState

Combina los estados intermedios de agregación de la misma manera que el combinador -Merge. Sin embargo, no devuelve el valor resultante, sino un estado intermedio de agregación, similar al combinador -State.

-ForEach

Convierte una función de agregación para tablas en una función de agregación para arrays que agrega los elementos correspondientes de los arrays y devuelve un array de resultados. Por ejemplo, sumForEach para los arrays [1, 2], [3, 4, 5]and[6, 7] devuelve el resultado [10, 13, 5] después de sumar los elementos correspondientes de los arrays.

-Tuple

El sufijo -Tuple puede añadirse a cualquier función de agregación. La función combinada recibe un argumento de tipo Tuple por cada argumento de la función de agregación subyacente; todas las tuplas deben tener el mismo número de elementos. La agregación se aplica de forma independiente en cada posición de los elementos: recibe el elemento correspondiente de cada Tuple y devuelve una Tuple con los resultados. Si la primera Tuple de entrada tiene nombres de elementos explícitos, se conservan en el resultado. Las funciones de agregación que manejan por sí mismas los valores NULL (anyRespectNulls, anyLastRespectNulls, el modificador RESPECT NULLS) no admiten el tipo Nullable(Tuple(...)) como argumento; use elementos Nullable en su lugar. Sintaxis

<aggFunction>Tuple(tuple1[, tuple2, ...])

Argumentos

tuple1[, tuple2, ...] — Columnas de tipo Tuple, una por cada argumento de la función de agregación subyacente, todas con el mismo número de elementos. Cada elemento debe ser de un tipo admitido por la función de agregación subyacente en esa posición del argumento.

Valores devueltos

Un Tuple que contiene el resultado de aplicar la función de agregación a cada elemento por separado.

Tipo: Tuple(aggFunction(element1), aggFunction(element2), ...). Ejemplo Consulta:

SELECT sumTuple(t) FROM
(
    SELECT tuple(toInt64(1), toFloat64(2.5)) AS t
    UNION ALL
    SELECT tuple(toInt64(3), toFloat64(4.5))
    UNION ALL
    SELECT tuple(toInt64(5), toFloat64(6.5))
);

Resultado:

┌─sumTuple(t)─┐
│ (9,13.5)    │
└─────────────┘

Uso junto con GROUP BY:

SELECT
    k,
    avgTuple(t)
FROM
(
    SELECT
        number % 2 AS k,
        tuple(toInt64(number), toFloat64(number) * 1.5) AS t
    FROM numbers(6)
)
GROUP BY k
ORDER BY k;

┌─k─┬─avgTuple(t)─┐
│ 0 │ (2,3)       │
│ 1 │ (3,4.5)     │
└───┴─────────────┘

Uso con una función de agregación con varios argumentos: cada argumento Tuple aporta uno de los argumentos de la función subyacente, y los elementos se emparejan según su posición:

corrTuple((a1, a2), (b1, b2)) = (corr(a1, b1), corr(a2, b2))

SELECT corrTuple((a1, a2), (b1, b2))
FROM
(
    SELECT
        toFloat64(number) AS a1,
        toFloat64(number * 2) AS a2,
        toFloat64(100 - number) AS b1,
        toFloat64(number * 3) AS b2
    FROM numbers(10)
);

┌─corrTuple((a1, a2), (b1, b2))─┐
│ (-1,1)                        │
└───────────────────────────────┘

a1 y b1 están anticorrelacionados, mientras que a2 y b2 son proporcionales, por lo que el resultado es (-1, 1). -Tuple se puede combinar con otros combinadores, como -If. Por ejemplo: sumTupleIf(tuple_column, cond).

-Distinct

Cada combinación distinta de argumentos se agregará una sola vez. Se ignoran los valores repetidos. Ejemplos: sum(DISTINCT x) (o sumDistinct(x)), groupArray(DISTINCT x) (o groupArrayDistinct(x)), corrStable(DISTINCT x, y) (o corrStableDistinct(x, y)) y así sucesivamente.

-OrDefault

Cambia el comportamiento de una función de agregación. Si una función de agregación no tiene valores de entrada, con este combinador devuelve el valor predeterminado de su tipo de dato de retorno. Se aplica a las funciones de agregación que pueden aceptar datos de entrada vacíos. -OrDefault puede usarse con otros combinadores. Sintaxis

<aggFunction>OrDefault(x)

Argumentos

x — Parámetros de la función de agregación.

Valores devueltos Devuelve el valor predeterminado del tipo de retorno de una función de agregación si no hay nada que agregar. El tipo depende de la función de agregación utilizada. Ejemplo

Query

SELECT avg(number), avgOrDefault(number) FROM numbers(0)

Response

┌─avg(number)─┬─avgOrDefault(number)─┐
│         nan │                    0 │
└─────────────┴──────────────────────┘

Además, -OrDefault también puede usarse con otros combinadores. Resulta útil cuando la función de agregación no admite una entrada vacía.

Query

SELECT avgOrDefaultIf(x, x > 10)
FROM
(
    SELECT toDecimal32(1.23, 2) AS x
)

Response

┌─avgOrDefaultIf(x, greater(x, 10))─┐
│                              0.00 │
└───────────────────────────────────┘

-OrNull

Cambia el comportamiento de una función de agregación. Este combinador convierte el resultado de una función de agregación al tipo de dato Nullable. Si la función de agregación no tiene valores sobre los que calcular, devuelve NULL. -OrNull puede usarse con otros combinadores. Sintaxis

<aggFunction>OrNull(x)

Argumentos

x — Parámetros de la función de agregación.

Valores devueltos

El resultado de la función de agregación, convertido al tipo de dato Nullable.
NULL, si no hay nada que agregar.

Tipo: Nullable(tipo de retorno de la función de agregación). Ejemplo Añada -orNull al final de la función de agregación.

Query

SELECT sumOrNull(number), toTypeName(sumOrNull(number)) FROM numbers(10) WHERE number > 10

Response

┌─sumOrNull(number)─┬─toTypeName(sumOrNull(number))─┐
│              ᴺᵁᴸᴸ │ Nullable(UInt64)              │
└───────────────────┴───────────────────────────────┘

Además, -OrNull también puede usarse con otros combinadores. Resulta útil cuando la función de agregación no admite entradas vacías.

Query

SELECT avgOrNullIf(x, x > 10)
FROM
(
    SELECT toDecimal32(1.23, 2) AS x
)

Response

┌─avgOrNullIf(x, greater(x, 10))─┐
│                           ᴺᵁᴸᴸ │
└────────────────────────────────┘

-Resample

Permite dividir los datos en grupos y, a continuación, agregarlos por separado. Los grupos se crean al dividir los valores de una columna en intervalos.

<aggFunction>Resample(start, end, step)(<aggFunction_params>, resampling_key)

Argumentos

start — Valor inicial de todo el intervalo requerido para los valores de resampling_key.
stop — Valor final de todo el intervalo requerido para los valores de resampling_key. El intervalo completo no incluye el valor stop: [start, stop). Debe ser mayor que start: un rango que no abarque ningún subintervalo lanza una excepción.
step — Paso para dividir el intervalo completo en subintervalos. aggFunction se ejecuta en cada uno de esos subintervalos de forma independiente.
resampling_key — Columna cuyos valores se utilizan para dividir los datos en intervalos.
aggFunction_params — Parámetros de aggFunction.

Valores devueltos

Array de resultados de aggFunction para cada subintervalo.

Ejemplo Considere la tabla people con los siguientes datos:

┌─name───┬─age─┬─wage─┐
│ John   │  16 │   10 │
│ Alice  │  30 │   15 │
│ Mary   │  35 │    8 │
│ Evelyn │  48 │ 11.5 │
│ David  │  62 │  9.9 │
│ Brian  │  60 │   16 │
└────────┴─────┴──────┘

Obtengamos los nombres de las personas cuya edad se encuentra en los intervalos [30,60) y [60,75). Como usamos una representación entera para la edad, obtenemos edades en los intervalos [30, 59] y [60,74]. Para agrupar los nombres en un Array, usamos la función de agregación groupArray. Acepta un argumento. En nuestro caso, es la columna name. La función groupArrayResample debe usar la columna age para agrupar los nombres por edad. Para definir los intervalos necesarios, pasamos los argumentos 30, 75, 30 a la función groupArrayResample.

SELECT groupArrayResample(30, 75, 30)(name, age) FROM people

┌─groupArrayResample(30, 75, 30)(name, age)─────┐
│ [['Alice','Mary','Evelyn'],['David','Brian']] │
└───────────────────────────────────────────────┘

Observa los resultados. John queda fuera de la muestra porque es demasiado joven. Las demás personas se distribuyen según los intervalos de edad especificados. Ahora contemos el número total de personas y su salario promedio en los intervalos de edad especificados.

SELECT
    countResample(30, 75, 30)(name, age) AS amount,
    avgResample(30, 75, 30)(wage, age) AS avg_wage
FROM people

┌─amount─┬─avg_wage──────────────────┐
│ [3,2]  │ [11.5,12.949999809265137] │
└────────┴───────────────────────────┘

-ArgMin

El sufijo -ArgMin se puede añadir al nombre de cualquier función de agregación. En este caso, la función de agregación admite un argumento adicional, que puede ser cualquier expresión comparable. La función de agregación procesa solo las filas que tienen el valor mínimo de la expresión adicional especificada. Ejemplos: sumArgMin(column, expr), countArgMin(expr), avgArgMin(x, expr) y así sucesivamente.

-ArgMax

Similar al sufijo -ArgMin, pero solo procesa las filas que tienen el valor máximo para la expresión adicional especificada.

Blog: Uso de los combinadores de agregación en ClickHouse

Referencia de SQL

Tipos de datos

Motores

Funciones

Formatos

Configuración

Tablas del sistema

Lagos de datos

-If

-Array

-Map

-SimpleState

-State

-Merge

-MergeState

-ForEach

-Tuple

-Distinct

-OrDefault

-OrNull

-Resample

-ArgMin

-ArgMax

​-If

​-Array

​-Map

​-SimpleState

​-State

​-Merge

​-MergeState

​-ForEach

​-Tuple

​-Distinct

​-OrDefault

​-OrNull

​-Resample

​-ArgMin

​-ArgMax

​Contenido relacionado

-If

-Array

-Map

-SimpleState

-State

-Merge

-MergeState

-ForEach

-Tuple

-Distinct

-OrDefault

-OrNull

-Resample

-ArgMin

-ArgMax

Contenido relacionado