ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Metric Lazy materialization off Lazy materialization on Improvement|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Elapsed time 219.071 sec 0.139 sec ~1576× faster|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | Data read 71.38 GB 1.81 GB ~40× less|  |  |  |  | | --- | --- | --- | --- | | Peak memory 1.11 GiB 3.80 MiB ~300× less | | | | | | | | | | | | | | | |

## How to confirm lazy materialization in the query execution plan[​](#confirm-lazy-materialization "Direct link to How to confirm lazy materialization in the query execution plan")

You can observe usage of lazy materialization for the previous query by inspecting the query’s logical execution plan using the [`EXPLAIN`](/docs/sql-reference/statements/explain) clause:

```
EXPLAIN actions = 1
SELECT
    helpful_votes,
    product_title,
    review_headline,
    review_body
FROM amazon.amazon_reviews
ORDER BY helpful_votes DESC
LIMIT 3
SETTINGS
    query_plan_optimize_lazy_materialization = true;
```

```
...
# highlight-next-line
Lazily read columns: review_headline, review_body, product_title
  Limit
    Sorting
      ReadFromMergeTree
```

You can read the operator plan from bottom to top and observe that ClickHouse defers reading the three large String columns until after sorting and limiting.

- [Overview](#overview)- [A worked example](#worked-example)- [How to confirm lazy materialization in the query execution plan](#confirm-lazy-materialization)

Was this page helpful?
Primary indexes

We can also observe that most of the data was skipped by simply running the query:

```
SELECT max(price)
FROM uk.uk_price_paid_simple
WHERE (town = 'LONDON') AND (street = 'OXFORD STREET');
```

```
   ┌─max(price)─┐
1. │  263100000 │ -- 263.10 million
   └────────────┘

1 row in set. Elapsed: 0.010 sec. Processed 24.58 thousand rows, 159.04 KB (2.53 million rows/s., 16.35 MB/s.)
Peak memory usage: 13.00 MiB.
```

As shown above, only around 25,000 rows were processed out of approximately 30 million rows in the example table:

```
SELECT count() FROM uk.uk_price_paid_simple;
```

```
   ┌──count()─┐
1. │ 29556244 │ -- 29.56 million
   └──────────┘
```

## Key takeaways[​](#key-takeaways "Direct link to Key takeaways")

- **Sparse primary indexes** help ClickHouse skip unnecessary data by identifying which granules might contain rows matching query conditions on primary key columns.
- Each index stores only the primary key values from the **first row of every granule** (a granule has 8,192 rows by default), making it compact enough to fit in memory.
- **Each data part** in a MergeTree table has its **own primary index**, which is used independently during query execution.
- During queries, the index allows ClickHouse to **skip granules**, reducing I/O and memory usage while accelerating performance.
- You can **inspect index contents** using the `mergeTreeIndex` table function and monitor index usage with the `EXPLAIN` clause.

## Where to find more information[​](#where-to-find-more-information "Direct link to Where to find more information")

For a deeper look at how sparse primary indexes work in ClickHouse, including how they differ from traditional database indexes and best practices for using them, check out our detailed indexing [deep dive](/docs/guides/best-practices/sparse-primary-indexes).

If you're interested in how ClickHouse processes data selected by the primary index scan in a highly parallel way, see the query parallelism guide [here](/docs/optimize/query-parallelism).

- [How does the sparse primary index work in ClickHouse?](#how-does-the-sparse-primary-index-work-in-clickHouse)
  - [Sparse primary index creation](#sparse-primary-index-creation)- [Primary index usage](#primary-index-usage)- [Monitoring primary indexes](#monitoring-primary-indexes)- [Key takeaways](#key-takeaways)- [Where to find more information](#where-to-find-more-information)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/sre/index.md)

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Page Description|  |  |  |  | | --- | --- | --- | --- | | [Users and Roles](/docs/operations/access-rights) Learn more about how ClickHouse supports access control management based on RBAC approach.|  |  | | --- | --- | | [External Authenticators](/docs/operations/external-authenticators) Learn more about how OSS ClickHouse supports authenticating and managing users using external services. | | | | | |

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/index.md)

ClickHouse supports a declarative query language based on SQL that is identical to the ANSI SQL standard in many cases.

Supported queries include GROUP BY, ORDER BY, subqueries in FROM, JOIN clause, IN operator, window functions and scalar subqueries.

---

[#### Statements

A list of available SQL statements in ClickHouse](/docs/sql-reference/statements)[#### Database and Table Engines

Engines determine where and how your data is stored](/docs/engines/table-engines)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/index.md)

ClickHouse supports all standard SQL aggregate functions ([sum](/docs/sql-reference/aggregate-functions/reference/sum), [avg](/docs/sql-reference/aggregate-functions/reference/avg), [min](/docs/sql-reference/aggregate-functions/reference/min), [max](/docs/sql-reference/aggregate-functions/reference/max), [count](/docs/sql-reference/aggregate-functions/reference/count)), as well as a wide range of other aggregate functions.
title_wip

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
aggThrow

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/aggThrow.md)

## aggThrow[​](#aggThrow "Direct link to aggThrow")

Introduced in: v20.1.0

This function can be used for the purpose of testing exception safety. It will throw an exception on creation with the specified probability.

**Syntax**

```
aggThrow(throw_prob)
```

**Arguments**

- `throw_prob` — Probability to throw on creation. [`Float64`](/docs/sql-reference/data-types/float)

**Returned value**

Returns an exception: `Code: 503. DB::Exception: Aggregate function aggThrow has thrown exception successfully`.

**Examples**

**Usage example**

```
SELECT number % 2 AS even, aggThrow(number) FROM numbers(10) GROUP BY even;
```

```
Received exception:
Code: 503. DB::Exception: Aggregate function aggThrow has thrown exception successfully: While executing AggregatingTransform. (AGGREGATE_FUNCTION_THROW)
```

- [aggThrow](#aggThrow)

Was this page helpful?
analysisOfVariance

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/analysisOfVariance.md)

## analysisOfVariance[​](#analysisOfVariance "Direct link to analysisOfVariance")

Introduced in: v22.10.0

Provides a statistical test for one-way analysis of variance (ANOVA test). It is a test over several groups of normally distributed observations to find out whether all groups have the same mean or not.

Note

Groups are enumerated starting from 0 and there should be at least two groups to perform a test.
There should be at least one group with the number of observations greater than one.

**Syntax**

```
analysisOfVariance(val, group_no)
```

**Aliases**: `anova`

**Arguments**

- `val` — Value. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)
- `group_no` — Group number that `val` belongs to. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)

**Returned value**

Returns a tuple with the F-statistic and p-value. [`Tuple(Float64, Float64)`](/docs/sql-reference/data-types/tuple)

**Examples**

**Basic usage**

```
SELECT analysisOfVariance(number, number % 2) FROM numbers(1048575);
```

```
┌─analysisOfVariance(number, modulo(number, 2))─┐
│ (0,1)                                         │
└───────────────────────────────────────────────┘
```

- [analysisOfVariance](#analysisOfVariance)

Was this page helpful?
any

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/any.md)

## any[​](#any "Direct link to any")

Introduced in: v1.1.0

Selects the first encountered value of a column.

Note

As a query can be executed in arbitrary order, the result of this function is non-deterministic. If you need an arbitrary but deterministic result, use functions min or max.

By default, the function never returns NULL, i.e. ignores NULL values in the input column.
However, if the function is used with the `RESPECT NULLS` modifier, it returns the first value reads no matter if NULL or not.

**Implementation details**

In some cases, you can rely on the order of execution.
This applies to cases when `SELECT` comes from a subquery that uses `ORDER BY`.

When a `SELECT` query has the `GROUP BY` clause or at least one aggregate function, ClickHouse (in contrast to MySQL) requires that all expressions in the `SELECT`, `HAVING`, and `ORDER BY` clauses be calculated from keys or from aggregate functions.
In other words, each column selected from the table must be used either in keys or inside aggregate functions.
To get behavior like in MySQL, you can put the other columns in the `any` aggregate function.

Note

The return type of the function is the same as the input, except for LowCardinality which is discarded.
This means that given no rows as input it will return the default value of that type (0 for integers, or Null for a Nullable() column).
You might use the -OrNull combinator to modify this behaviour.

**Syntax**

```
any(column)[ RESPECT NULLS]
```

**Aliases**: `any_value`, `first_value`

**Arguments**

- `column` — The column name. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns the first value encountered.
[`Any`](/docs/sql-reference/data-types)

**Examples**

**Usage example**

```
CREATE TABLE tab (city Nullable(String)) ENGINE=Memory;
INSERT INTO tab (city) VALUES (NULL), ('Amsterdam'), ('New York'), ('Tokyo'), ('Valencia'), (NULL);
SELECT any(city), anyRespectNulls(city) FROM tab;
```

```
┌─any(city)─┬─anyRespectNulls(city)─┐
│ Amsterdam │ ᴺᵁᴸᴸ                  │
└───────────┴───────────────────────┘
```

- [any](#any)

Was this page helpful?
anyHeavy

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/anyHeavy.md)

## anyHeavy[​](#anyHeavy "Direct link to anyHeavy")

Introduced in: v1.1.0

Selects a frequently occurring value using the [heavy hitters](https://doi.org/10.1145/762471.762473) algorithm.
If there is a value that occurs more than in half the cases in each of the query's execution threads, this value is returned.
Normally, the result is nondeterministic.

**Syntax**

```
anyHeavy(column)
```

**Arguments**

- `column` — The column name. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a frequently occurring value. The result is nondeterministic. [`Any`](/docs/sql-reference/data-types)

**Examples**

**Usage example**

```
SELECT anyHeavy(AirlineID) AS res
FROM ontime;
```

```
┌───res─┐
│ 19690 │
└───────┘
```

- [anyHeavy](#anyHeavy)

Was this page helpful?
anyLast

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/anyLast.md)

## anyLast[​](#anyLast "Direct link to anyLast")

Introduced in: v1.1.0

Selects the last encountered value of a column.

Note

As a query can be executed in arbitrary order, the result of this function is non-deterministic.
If you need an arbitrary but deterministic result, use functions [min](/docs/sql-reference/aggregate-functions/reference/min) or [max](/docs/sql-reference/aggregate-functions/reference/max).

By default, the function never returns NULL, i.e. ignores NULL values in the input column.
However, if the function is used with the `RESPECT NULLS` modifier, it returns the last value reads no matter if NULL or not.

**Syntax**

```
anyLast(column) [RESPECT NULLS]
```

**Aliases**: `last_value`

**Arguments**

- `column` — The column name. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns the last value encountered. [`Any`](/docs/sql-reference/data-types)

**Examples**

**Usage example**

```
CREATE TABLE tab(city Nullable(String)) ENGINE=Memory;
INSERT INTO tab (city) VALUES ('Amsterdam'), (NULL), ('New York'), ('Tokyo'), ('Valencia'), (NULL);
SELECT anyLast(city), anyLastRespectNulls(city) FROM tab;
```

```
┌─anyLast(city)─┬─anyLastRespectNulls(city)─┐
│ Valencia      │ ᴺᵁᴸᴸ                      │
└───────────────┴───────────────────────────┘
```

- [anyLast](#anyLast)

Was this page helpful?
approx_top_k

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/approx_top_k.md)

## approx\_top\_k[​](#approx_top_k "Direct link to approx_top_k")

Introduced in: v1.1.0

Returns an array of the approximately most frequent values and their counts in the specified column.
The resulting array is sorted in descending order of approximate frequency of values (not by the values themselves).

This function does not provide a guaranteed result.
In certain situations, errors might occur and it might return frequent values that aren't the most frequent values.

**Syntax**

```
approx_top_k(N[, reserved])(column)
```

**Aliases**: `approx_top_count`

**Parameters**

- `N` — The number of elements to return. Default value: `10`. Maximum value of `N = 65536`. [`UInt64`](/docs/sql-reference/data-types/int-uint)
- `reserved` — Optional. Defines how many cells reserved for values. If `uniq(column) > reserved`, the result will be approximate. Default value: `N * 3`. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Arguments**

- `column` — The name of the column for which to find the most frequent values. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns an array of the approximately most frequent values and their counts, sorted in descending order of approximate frequency. [`Array`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT approx_top_k(2)(k)
FROM VALUES('k Char, w UInt64', ('y', 1), ('y', 1), ('x', 5), ('y', 1), ('z', 10));
```

```
┌─approx_top_k(2)(k)────┐
│ [('y',3,0),('x',1,0)] │
└───────────────────────┘
```

**See Also**

- [topK](/docs/sql-reference/aggregate-functions/reference/topk)
- [topKWeighted](/docs/sql-reference/aggregate-functions/reference/topkweighted)
- [approx\_top\_sum](/docs/sql-reference/aggregate-functions/reference/approxtopsum)

- [approx\_top\_k](#approx_top_k)

Was this page helpful?
approx_top_sum

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/approx_top_sum.md)

## approx\_top\_sum[​](#approx_top_sum "Direct link to approx_top_sum")

Introduced in: v1.1.0

Returns an array of the approximately most frequent values and their counts in the specified column.
The resulting array is sorted in descending order of approximate frequency of values (not by the values themselves).
Additionally, the weight of the value is taken into account.

This function does not provide a guaranteed result.
In certain situations, errors might occur and it might return frequent values that aren't the most frequent values.

**See Also**

- [topK](/docs/sql-reference/aggregate-functions/reference/topk)
- [topKWeighted](/docs/sql-reference/aggregate-functions/reference/topkweighted)
- [approx\_top\_k](/docs/sql-reference/aggregate-functions/reference/approxtopk)

**Syntax**

```
approx_top_sum(N[, reserved])(column, weight)
```

**Parameters**

- `N` — The number of elements to return. Optional. Default value: 10. [`UInt64`](/docs/sql-reference/data-types/int-uint)
- `reserved` — Optional. Defines, how many cells reserved for values. If `uniq(column) > reserved`, result of topK function will be approximate. Default value: `N * 3`. Maximum value of `N = 65536`. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Arguments**

- `column` — The name of the column for which to find the most frequent values. [`String`](/docs/sql-reference/data-types/string)
- `weight` — The weight. Every value is accounted `weight` times for frequency calculation. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an array of the approximately most frequent values and their counts, sorted in descending order of approximate frequency. [`Array`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT approx_top_sum(2)(k, w)
FROM VALUES('k Char, w UInt64', ('y', 1), ('y', 1), ('x', 5), ('y', 1), ('z', 10));
```

```
┌─approx_top_sum(2)(k, w)─┐
│ [('z',10,0),('x',5,0)]  │
└─────────────────────────┘
```

**See Also**

- [topK](/docs/sql-reference/aggregate-functions/reference/topk)
- [topKWeighted](/docs/sql-reference/aggregate-functions/reference/topkweighted)
- [approx\_top\_k](/docs/sql-reference/aggregate-functions/reference/approxtopk)

- [approx\_top\_sum](#approx_top_sum)

Was this page helpful?
argAndMax

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/reference/argAndMax.md)

## argAndMax[​](#argAndMax "Direct link to argAndMax")

Introduced in: v1.1.0

Calculates the `arg` and `val` value for a maximum `val` value.
If there are multiple rows with equal `val` being the maximum, which of the associated `arg` and `val` is returned is not deterministic.
Both parts the `arg` and the `max` behave as [aggregate functions](/docs/sql-reference/aggregate-functions), they both [skip `Null`](/docs/sql-reference/aggregate-functions#null-processing) during processing and return not `Null` values if not `Null` values are available.

Note

The only difference with `argMax` is that `argAndMax` returns both argument and value.

**See also**

- [argMax](/docs/sql-reference/aggregate-functions/reference/argmax)
- [Tuple](/docs/sql-reference/data-types/tuple)

**Syntax**

```
argAndMax(arg, val)
```

**Arguments**

- `arg` — Argument for which to find the maximum value. [`const String`](/docs/sql-reference/data-types/string)
- `val` — The maximum value. [`(U)Int8/16/32/64`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`Tuple`](/docs/sql-reference/data-types/tuple)

**Returned value**

Returns a tuple containing the `arg` value that corresponds to maximum `val` value and the maximum `val` value. [`Tuple`](/docs/sql-reference/data-types/tuple)

**Examples**

**Basic usage**

```
SELECT argAndMax(user, salary) FROM salary;
```

```
┌─argAndMax(user, salary)─┐
│ ('director',5000)       │
└─────────────────────────┘
```

**Extended example with NULL handling**

```
CREATE TABLE test
(
    a Nullable(String),
    b Nullable(Int64)
)
ENGINE = Memory AS
SELECT *
FROM VALUES(('a', 1), ('b', 2), ('c', 2), (NULL, 3), (NULL, NULL), ('d', NULL));

SELECT argMax(a, b), argAndMax(a, b), max(b) FROM test;
```

```
┌─argMax(a, b)─┬─argAndMax(a, b)─┬─max(b)─┐
│ b            │ ('b',2)         │      3 │
└──────────────┴─────────────────┴────────┘
```

**Using Tuple in arguments**

```
SELECT argAndMax(a, (b,a)) FROM test;
```

```
┌─argAndMax(a, (b, a))─┐
│ ('c',(2,'c'))        │
└──────────────────────┘
```

**See also**

- [argMax](/docs/sql-reference/aggregate-functions/reference/argmax)
- [Tuple](/docs/sql-reference/data-types/tuple)

- [argAndMax](#argAndMax)

Was this page helpful?