Relational Data - Aggregate Functions - Reference - Count

From FojiSoft Docs

Counts the number of rows or not-NULL values.

ClickHouse supports the following syntaxes for count:

  • count(expr) or COUNT(DISTINCT expr).
  • count() or COUNT(*). The count() syntax is ClickHouse-specific.

Arguments

The function can take:

Returned value

  • If the function is called without parameters it counts the number of rows.
  • If the expression is passed, then the function counts how many times this expression returned not null. If the expression returns a Nullable-type value, then the result of count stays not Nullable. The function returns 0 if the expression returned NULL for all the rows.

In both cases the type of the returned value is UInt64.

Details

ClickHouse supports the COUNT(DISTINCT ...) syntax. The behavior of this construction depends on the count_distinct_implementation setting. It defines which of the uniq* functions is used to perform the operation. The default is the uniqExact function.

The SELECT count() FROM table query is optimized by default using metadata from MergeTree. If you need to use row-level security, disable optimization using the optimize_trivial_count_query setting.

However SELECT count(nullable_column) FROM table query can be optimized by enabling the optimize_functions_to_subcolumns setting. With optimize_functions_to_subcolumns = 1 the function reads only null subcolumn instead of reading and processing the whole column data. The query SELECT count(n) FROM table transforms to SELECT sum(NOT n.null) FROM table.

Improving COUNT(DISTINCT expr) performance

If your COUNT(DISTINCT expr) query is slow, consider adding a GROUP BY clause as this improves parallelization. You can also use a projection to create an index on the target column used with COUNT(DISTINCT target_col).

Examples

Example 1: 

SELECT count() FROM t
┌─count()─┐
│       5 │
└─────────┘

Example 2: 

SELECT name, value FROM system.settings WHERE name = 'count_distinct_implementation'
┌─name──────────────────────────┬─value─────┐
│ count_distinct_implementation │ uniqExact │
└───────────────────────────────┴───────────┘
SELECT count(DISTINCT num) FROM t
┌─uniqExact(num)─┐
│              3 │
└────────────────┘

This example shows that count(DISTINCT num) is performed by the uniqExact function according to the count_distinct_implementation setting value.

Retrieved from "https://docs.foji.io/index.php?title=Relational_Data_-_Aggregate_Functions_-_Reference_-_Count&oldid=1817"