Combinadores de funções de agregação

O nome de uma função de agregação pode receber um sufixo. Isso altera a forma como a função de agregação opera.

O sufixo -If pode ser acrescentado ao nome de qualquer função de agregação. Nesse caso, a função de agregação aceita um argumento extra — uma condição (tipo Uint8). A função de agregação processa apenas as linhas que atendem à condição. Se a condição não for atendida nenhuma vez, ela retorna um valor padrão (geralmente zeros ou strings vazias).

Exemplos: sumIf(column, cond), countIf(cond), avgIf(x, cond), quantilesTimingIf(level1, level2)(x, cond), argMinIf(arg, val, cond) e assim por diante.

Com funções de agregação condicionais, você pode calcular agregados para várias condições ao mesmo tempo, sem usar subconsultas nem JOINs. Por exemplo, funções de agregação condicionais podem ser usadas para implementar a funcionalidade de comparação de segmentos.

O sufixo -Array pode ser anexado a qualquer função de agregação. Nesse caso, a função de agregação recebe argumentos do tipo 'Array(T)' (arrays) em vez de argumentos do tipo 'T'. Se a função de agregação aceitar vários argumentos, eles deverão ser arrays de comprimentos iguais. Ao processar arrays, a função de agregação funciona como a função de agregação original sobre todos os elementos do array.

Exemplo 1: sumArray(arr) - Soma todos os elementos de todos os arrays 'arr'. Neste exemplo, também poderia ser escrito de forma mais simples: sum(arraySum(arr)).

Exemplo 2: uniqArray(arr) – Conta o número de elementos únicos em todos os arrays 'arr'. Isso também poderia ser feito de uma forma mais simples: uniq(arrayJoin(arr)), mas nem sempre é possível adicionar 'arrayJoin' a uma consulta.

-If e -Array podem ser combinados. No entanto, 'Array' deve vir primeiro e, depois, 'If'. Exemplos: uniqArrayIf(arr, cond), quantilesTimingArrayIf(level1, level2)(arr, cond). Devido a essa ordem, o argumento 'cond' não será um array.

O sufixo -Map pode ser anexado a qualquer função de agregação. Isso cria uma função de agregação que recebe o tipo Map como argumento e agrega separadamente os valores de cada chave do map usando a função de agregação especificada. O resultado também é do tipo Map.

Exemplo

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;

┌

Ao aplicar este combinador, a função de agregação retorna o mesmo valor, mas com um tipo diferente. Trata-se de uma SimpleAggregateFunction(…) que pode ser armazenada em uma tabela para funcionar com tabelas AggregatingMergeTree.

Sintaxe

<aggFunction>SimpleState(x)

Argumentos

• x — Parâmetros da função de agregação.

Valores retornados

O valor de uma função de agregação do tipo SimpleAggregateFunction(...).

Exemplo

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

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

Se você aplicar este combinador, a função de agregação não retorna o valor resultante (como o número de valores únicos da função uniq), mas um estado intermediário da agregação (para uniq, isso é a tabela hash usada para calcular o número de valores únicos). Trata-se de um AggregateFunction(...) que pode ser usado em processamento adicional ou armazenado em uma tabela para concluir a agregação depois.

Para trabalhar com esses estados, use:

• AggregatingMergeTree como mecanismo de tabela.
• A função finalizeAggregation.
• A função runningAccumulate.
• O combinador -Merge.
• O combinador -MergeState.

Se você aplicar este combinador, a função de agregação usa o estado intermediário de agregação como argumento, combina os estados para finalizar a agregação e retorna o valor resultante.

Mescla os estados intermediários de agregação da mesma forma que o combinador -Merge. No entanto, não retorna o valor resultante, mas sim um estado intermediário de agregação, semelhante ao combinador -State.

Converte uma função de agregação para tabelas em uma função de agregação para arrays, que agrega os elementos correspondentes dos arrays e retorna um array de resultados. Por exemplo, sumForEach para os arrays [1, 2], [3, 4, 5] e [6, 7] retorna o resultado [10, 13, 5] após somar os elementos correspondentes dos arrays.

O sufixo -Tuple pode ser acrescentado a qualquer função de agregação. A função combinada recebe um argumento do tipo Tuple para cada argumento da função de agregação subjacente; todas as tuplas devem ter o mesmo número de elementos. A agregação é aplicada independentemente em cada posição do elemento, recebendo o elemento correspondente de cada Tuple, e retorna uma Tuple com os resultados.

Se a primeira Tuple de entrada tiver nomes de elementos explícitos, eles serão preservados no resultado.

Funções de agregação que tratam valores NULL por conta própria (anyRespectNulls, anyLastRespectNulls, o modificador RESPECT NULLS) não oferecem suporte ao tipo Nullable(Tuple(...)) como argumento; use elementos Nullable em vez disso.

Sintaxe

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

Argumentos

• tuple1[, tuple2, ...] — Colunas do tipo Tuple, uma para cada argumento da função de agregação subjacente, todas com o mesmo número de elementos. Cada elemento deve ser de um tipo compatível com a função de agregação subjacente na posição correspondente do argumento.

Valores retornados

• Uma Tuple contendo o resultado da aplicação da função de agregação a cada elemento independentemente.

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

Exemplo

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 com 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 com uma função de agregação com múltiplos argumentos: cada argumento Tuple fornece um argumento para a função subjacente, e os elementos são combinados de acordo com a posição:

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 e b1 são anticorrelacionados, enquanto a2 e b2 são proporcionais, portanto o resultado é (-1, 1).

-Tuple pode ser combinado com outros combinadores, como -If. Por exemplo: sumTupleIf(tuple_column, cond).

Cada combinação exclusiva de argumentos será agregada apenas uma vez. Valores repetidos são ignorados. Exemplos: sum(DISTINCT x) (ou sumDistinct(x)), groupArray(DISTINCT x) (ou groupArrayDistinct(x)), corrStable(DISTINCT x, y) (ou corrStableDistinct(x, y)) e assim por diante.

Altera o comportamento de uma função de agregação.

Se uma função de agregação não tiver valores de entrada, com este combinador ela retorna o valor padrão do tipo de dado de retorno. Aplica-se a funções de agregação que podem receber dados de entrada vazios.

-OrDefault pode ser usado com outros combinadores.

Sintaxe

<aggFunction>OrDefault(x)

Argumentos

• x — Parâmetros da função de agregação.

Valores retornados

Retorna o valor padrão do tipo de retorno da função de agregação se não houver nada para agregar.

O tipo depende da função de agregação usada.

Exemplo

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

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

Também é possível usar -OrDefault com outros combinadores. Isso é útil quando a função de agregação não aceita uma entrada vazia.

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

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

Altera o comportamento de uma função agregada.

Esse combinador converte o resultado de uma função agregada para o tipo de dado Nullable. Se a função agregada não tiver valores para calcular, ela retornará NULL.

-OrNull pode ser usado com outros combinadores.

Sintaxe

<aggFunction>OrNull(x)

Argumentos

• x — Parâmetros da função de agregação.

Valores retornados

• O resultado da função de agregação, convertido para o tipo de dado Nullable.
• NULL, se não houver nada para agregar.

Tipo: Nullable(tipo de retorno da função de agregação).

Exemplo

Adicione -orNull ao final do nome da função de agregação.

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

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

Além disso, -OrNull também pode ser usado com outros combinadores. Isso é útil quando a função de agregação não aceita entrada vazia.

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

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

Permite dividir os dados em grupos e, em seguida, agregá-los separadamente em cada grupo. Os grupos são criados ao dividir os valores de uma coluna em intervalos.

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

Argumentos

• start — Valor inicial de todo o intervalo necessário para os valores de resampling_key.
• stop — Valor final de todo o intervalo necessário para os valores de resampling_key. O intervalo completo não inclui o valor stop [start, stop).
• step — Passo para separar todo o intervalo em subintervalos. A aggFunction é executada em cada um desses subintervalos de forma independente.
• resampling_key — Coluna cujos valores são usados para separar os dados em intervalos.
• aggFunction_params — Parâmetros de aggFunction.

Valores retornados

• Array de resultados de aggFunction para cada subintervalo.

Exemplo

Considere a tabela people com os seguintes dados:

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

Vamos obter os nomes das pessoas cujas idades estão nos intervalos [30,60) e [60,75). Como usamos representação inteira para a idade, obtemos idades nos intervalos [30, 59] e [60,74].

Para agregar nomes em um Array, usamos a função de agregação groupArray. Ela recebe um argumento. No nosso caso, é a coluna name. A função groupArrayResample deve usar a coluna age para agregar nomes por idade. Para definir os intervalos necessários, passamos os argumentos 30, 75, 30 para a função groupArrayResample.

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

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

Considere os resultados.

John ficou fora da amostra porque é jovem demais. As outras pessoas foram distribuídas de acordo com os intervalos de idade especificados.

Agora, vamos contar o número total de pessoas e a média salarial delas nos intervalos de idade 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] │
└────────┴───────────────────────────┘

O sufixo -ArgMin pode ser anexado ao nome de qualquer função de agregação. Nesse caso, a função de agregação aceita um argumento adicional, que deve ser qualquer expressão comparável. A função de agregação processa apenas as linhas que têm o valor mínimo da expressão extra especificada.

Exemplos: sumArgMin(column, expr), countArgMin(expr), avgArgMin(x, expr) e assim por diante.

Semelhante ao sufixo -ArgMin, mas processa apenas as linhas com o valor máximo da expressão extra especificada.

• Blog: Uso de combinadores de agregação no ClickHouse