Chris.Hansen: Import ClickHouse Docs: Wed Aug 28 2024 14:43:29 GMT-0400 (Eastern Daylight Time)

2024-08-28T18:43:30Z

Import ClickHouse Docs: Wed Aug 28 2024 14:43:29 GMT-0400 (Eastern Daylight Time)

New page

Aggregate functions work in the [http://www.sql-tutorial.com/sql-aggregate-functions-sql-tutorial normal] way as expected by database experts.

ClickHouse also supports:

* [[Relational_Data_-_Aggregate_Functions_-_Parametric_Functions#aggregate_functions_parametric|Parametric aggregate functions]], which accept other parameters in addition to columns.
* [[Relational_Data_-_Aggregate_Functions_-_Combinators#aggregate_functions_combinators|Combinators]], which change the behavior of aggregate functions.

<span id="null-processing"></span>
== NULL Processing ==

During aggregation, all <code>NULL</code> arguments are skipped. If the aggregation has several arguments it will ignore any row in which one or more of them are NULL.

There is an exception to this rule, which are the functions [[Relational_Data_-_Aggregate_Functions_-_Reference_-_First_Value|<code>first_value</code>]], [[Relational_Data_-_Aggregate_Functions_-_Reference_-_Last_Value|<code>last_value</code>]] and their aliases (<code>any</code> and <code>anyLast</code> respectively) when followed by the modifier <code>RESPECT NULLS</code>. For example, <code>FIRST_VALUE(b) RESPECT NULLS</code>.

'''Examples:'''

Consider this table:

<syntaxhighlight lang="text">┌─x─┬────y─┐
│ 1 │ 2 │
│ 2 │ ᴺᵁᴸᴸ │
│ 3 │ 2 │
│ 3 │ 3 │
│ 3 │ ᴺᵁᴸᴸ │
└───┴──────┘</syntaxhighlight>
Let’s say you need to total the values in the <code>y</code> column:

<syntaxhighlight lang="sql">SELECT sum(y) FROM t_null_big</syntaxhighlight>
<syntaxhighlight lang="text">┌─sum(y)─┐
│ 7 │
└────────┘</syntaxhighlight>
Now you can use the <code>groupArray</code> function to create an array from the <code>y</code> column:

<syntaxhighlight lang="sql">SELECT groupArray(y) FROM t_null_big</syntaxhighlight>
<syntaxhighlight lang="text">┌─groupArray(y)─┐
│ [2,2,3] │
└───────────────┘</syntaxhighlight>
<code>groupArray</code> does not include <code>NULL</code> in the resulting array.

You can use [[Relational_Data_-_Functions_-_Functions_For_Nulls#coalesce|COALESCE]] to change NULL into a value that makes sense in your use case. For example: <code>avg(COALESCE(column, 0))</code> with use the column value in the aggregation or zero if NULL:

<syntaxhighlight lang="sql">SELECT
avg(y),
avg(coalesce(y, 0))
FROM t_null_big</syntaxhighlight>
<syntaxhighlight lang="text">┌─────────────avg(y)─┬─avg(coalesce(y, 0))─┐
│ 2.3333333333333335 │ 1.4 │
└────────────────────┴─────────────────────┘</syntaxhighlight>
Also you can use [[Relational_Data_-_Data_Types_-_Tuple|Tuple]] to work around NULL skipping behavior. The a <code>Tuple</code> that contains only a <code>NULL</code> value is not <code>NULL</code>, so the aggregate functions won’t skip that row because of that <code>NULL</code> value.

<syntaxhighlight lang="sql">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] │
└───────────────┴───────────────────────────────────────┘</syntaxhighlight>
Note that aggregations are skipped when the columns are used as arguments to an aggregated function. For example [[Relational_Data_-_Aggregate_Functions_-_Reference_-_Count|<code>count</code>]] without parameters (<code>count()</code>) or with constant ones (<code>count(1)</code>) will count all rows in the block (independently of the value of the GROUP BY column as it’s not an argument), while <code>count(column)</code> will only return the number of rows where column is not NULL.

<syntaxhighlight lang="sql">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 │
└──────┴─────────┴──────────┘</syntaxhighlight>
And here is an example of of first_value with <code>RESPECT NULLS</code> where we can see that NULL inputs are respected and it will return the first value read, whether it’s NULL or not:

<syntaxhighlight lang="sql">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 │ ᴺᵁᴸᴸ │
└───────┴───────┴───────────────────┴─────────────────────┘</syntaxhighlight>
[[Category:Relational_Data]]

Relational Data - Aggregate Functions - Revision history

Chris.Hansen: Import ClickHouse Docs: Wed Aug 28 2024 14:43:29 GMT-0400 (Eastern Daylight Time)