Funções de agregação

Documentação sobre funções de agregação

As funções de agregação funcionam da forma normal, como especialistas em banco de dados esperam.

O ClickHouse também oferece suporte a:

• Funções de agregação paramétricas, que aceitam outros parâmetros além de colunas.
• Combinadores, que alteram o comportamento das funções de agregação.

​

Processamento de NULL

Durante a agregação, todos os argumentos NULL são ignorados. Se a agregação tiver vários argumentos, ela ignorará qualquer linha em que um ou mais deles sejam NULL.

Há uma exceção a essa regra: as funções first_value, last_value e seus aliases (any e anyLast, respectivamente), quando seguidas do modificador RESPECT NULLS. Por exemplo, FIRST_VALUE(b) RESPECT NULLS.

Exemplos:

Considere esta tabela:

┌─x─┬────y─┐
│ 1 │    2 │
│ 2 │ ᴺᵁᴸᴸ │
│ 3 │    2 │
│ 3 │    3 │
│ 3 │ ᴺᵁᴸᴸ │
└───┴──────┘

Digamos que você precise somar os valores da coluna y:

SELECT sum(y) FROM t_null_big

┌─sum(y)─┐
│      7 │
└────────┘

Agora você pode usar a função groupArray para criar um array a partir da coluna y:

SELECT groupArray(y) FROM t_null_big

┌─groupArray(y)─┐
│ [2,2,3]       │
└───────────────┘

groupArray não inclui NULL no array resultante.

Você pode usar COALESCE para converter NULL em um valor que faça sentido no seu caso de uso. Por exemplo: avg(COALESCE(column, 0)) usará o valor da coluna na agregação ou zero se for NULL:

SELECT
    avg(y),
    avg(coalesce(y, 0))
FROM t_null_big

┌─────────────avg(y)─┬─avg(coalesce(y, 0))─┐
│ 2.3333333333333335 │                 1.4 │
└────────────────────┴─────────────────────┘

Além disso, você pode usar Tuple para contornar o comportamento de ignorar NULL. Um Tuple que contém apenas um valor NULL não é NULL, portanto as funções de agregação não vão ignorar essa linha por causa desse valor NULL.

SELECT
    groupArray(y),
    groupArray(tuple(y)).1
FROM t_null_big;

┌─groupArray(y)─┬─tupleElement(groupArray(tuple(y)), 1)─┐
│ [2,2,3]       │ [2,NULL,2,3,NULL]                     │
└───────────────┴───────────────────────────────────────┘

Observe que as agregações são ignoradas quando as colunas são usadas como argumentos de uma função agregada. Por exemplo, count sem parâmetros (count()) ou com parâmetros constantes (count(1)) contará todas as linhas do bloco (independentemente do valor da coluna em GROUP BY, já que ela não é um argumento), enquanto count(column) retornará apenas o número de linhas em que a coluna não é NULL.

SELECT
    v,
    count(1),
    count(v)
FROM
(
    SELECT if(number < 10, NULL, number % 3) AS v
    FROM numbers(15)
)
GROUP BY v

┌────v─┬─count()─┬─count(v)─┐
│ ᴺᵁᴸᴸ │      10 │        0 │
│    0 │       1 │        1 │
│    1 │       2 │        2 │
│    2 │       2 │        2 │
└──────┴─────────┴──────────┘

E aqui está um exemplo de first_value com RESPECT NULLS, em que podemos ver que os valores de entrada NULL são respeitados e que ele retornará o primeiro valor lido, seja ele NULL ou não:

SELECT
    col || '_' || ((col + 1) * 5 - 1) AS range,
    first_value(odd_or_null) AS first,
    first_value(odd_or_null) IGNORE NULLS as first_ignore_null,
    first_value(odd_or_null) RESPECT NULLS as first_respect_nulls
FROM
(
    SELECT
        intDiv(number, 5) AS col,
        if(number % 2 == 0, NULL, number) AS odd_or_null
    FROM numbers(15)
)
GROUP BY col
ORDER BY col

┌─range─┬─first─┬─first_ignore_null─┬─first_respect_nulls─┐
│ 0_4   │     1 │                 1 │                ᴺᵁᴸᴸ │
│ 1_9   │     5 │                 5 │                   5 │
│ 2_14  │    11 │                11 │                ᴺᵁᴸᴸ │
└───────┴───────┴───────────────────┴─────────────────────┘