ClickHouse/clickhouse-docs-embeddings huggingface dataset:
groupBitmapOr

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

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

Introduced in: v20.1.0

Calculates the OR of a bitmap column and returns it's cardinality.
If suffix combinator [`-State`](/docs/sql-reference/aggregate-functions/combinators#-state) is added, then it returns a bitmap object.
This is equivalent to `groupBitmapMerge` ([`groupBitmap`](/docs/sql-reference/aggregate-functions/reference/groupbitmap) with the [`-Merge`](/docs/sql-reference/aggregate-functions/combinators#-merge) combinator suffix).

**Syntax**

```
groupBitmapOr(expr)
groupBitmapOrState(expr)
```

**Arguments**

- `expr` — Expression that results in an `AggregateFunction(groupBitmap, UInt*)` type. [`AggregateFunction(groupBitmap, UInt*)`](/docs/sql-reference/data-types/aggregatefunction)

**Returned value**

Returns a count of type `UInt64`, or a bitmap object when using `-State`. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
CREATE TABLE bitmap_column_expr_test2
(
    tag_id String,
    z AggregateFunction(groupBitmap, UInt32)
)
ENGINE = MergeTree
ORDER BY tag_id;

INSERT INTO bitmap_column_expr_test2 VALUES ('tag1', bitmapBuild(cast([1,2,3,4,5,6,7,8,9,10] AS Array(UInt32))));
INSERT INTO bitmap_column_expr_test2 VALUES ('tag2', bitmapBuild(cast([6,7,8,9,10,11,12,13,14,15] AS Array(UInt32))));
INSERT INTO bitmap_column_expr_test2 VALUES ('tag3', bitmapBuild(cast([2,4,6,8,10,12] AS Array(UInt32))));

SELECT groupBitmapOr(z) FROM bitmap_column_expr_test2 WHERE like(tag_id, 'tag%');
```

```
┌─groupBitmapOr(z)─┐
│             15   │
└──────────────────┘
```

**Using -State combinator**

```
SELECT arraySort(bitmapToArray(groupBitmapOrState(z))) FROM bitmap_column_expr_test2 WHERE like(tag_id, 'tag%');
```

```
┌─arraySort(bitmapToArray(groupBitmapOrState(z)))─┐
│ [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15]           │
└─────────────────────────────────────────────────┘
```

- [groupBitmapOr](#groupBitmapOr)

Was this page helpful?
groupBitmapXor

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

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

Introduced in: v20.1.0

Calculates the XOR of a bitmap column and returns it's cardinality.
If suffix combinator [`-State`](/docs/sql-reference/aggregate-functions/combinators#-state) is added, then it returns a bitmap object.

**Syntax**

```
groupBitmapXor(expr)
groupBitmapXorState(expr)
```

**Arguments**

- `expr` — Expression that results in an `AggregateFunction(groupBitmap, UInt*)` type. [`AggregateFunction(groupBitmap, UInt*)`](/docs/sql-reference/data-types/aggregatefunction)

**Returned value**

Returns a count of type `UInt64`, or a bitmap object when using `-State`. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
CREATE TABLE bitmap_column_expr_test2
(
    tag_id String,
    z AggregateFunction(groupBitmap, UInt32)
)
ENGINE = MergeTree
ORDER BY tag_id;

INSERT INTO bitmap_column_expr_test2 VALUES ('tag1', bitmapBuild(cast([1,2,3,4,5,6,7,8,9,10] AS Array(UInt32))));
INSERT INTO bitmap_column_expr_test2 VALUES ('tag2', bitmapBuild(cast([6,7,8,9,10,11,12,13,14,15] AS Array(UInt32))));
INSERT INTO bitmap_column_expr_test2 VALUES ('tag3', bitmapBuild(cast([2,4,6,8,10,12] AS Array(UInt32))));

SELECT groupBitmapXor(z) FROM bitmap_column_expr_test2 WHERE like(tag_id, 'tag%');
```

```
┌─groupBitmapXor(z)─┐
│              10   │
└───────────────────┘
```

**Using -State combinator**

```
SELECT arraySort(bitmapToArray(groupBitmapXorState(z))) FROM bitmap_column_expr_test2 WHERE like(tag_id, 'tag%');
```

```
┌─arraySort(bitmapToArray(groupBitmapXorState(z)))─┐
│ [1, 3, 5, 6, 8, 10, 11, 13, 14, 15]              │
└──────────────────────────────────────────────────┘
```

- [groupBitmapXor](#groupBitmapXor)

Was this page helpful?
groupBitOr

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

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

Introduced in: v1.1.0

Applies bitwise OR for series of numbers.

**Syntax**

```
groupBitOr(expr)
```

**Aliases**: `BIT_OR`

**Arguments**

- `expr` — Expression of `(U)Int*` type. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a value of `(U)Int*` type. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Bitwise OR example**

```
CREATE TABLE t (num UInt32) ENGINE = Memory;
INSERT INTO t VALUES (44), (28), (13), (85);

-- Test data:
-- binary     decimal
-- 00101100 = 44
-- 00011100 = 28
-- 00001101 = 13
-- 01010101 = 85

SELECT groupBitOr(num) FROM t;
```

```
-- Result:
-- binary     decimal
-- 01111101 = 125

┌─groupBitOr(num)─┐
│             125 │
└─────────────────┘
```

- [groupBitOr](#groupBitOr)

Was this page helpful?
groupBitXor

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

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

Introduced in: v1.1.0

Applies bitwise XOR for series of numbers.

**Syntax**

```
groupBitXor(expr)
```

**Aliases**: `BIT_XOR`

**Arguments**

- `expr` — Expression of `(U)Int*` type. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a value of `(U)Int*` type. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Bitwise XOR example**

```
CREATE TABLE t (num UInt32) ENGINE = Memory;
INSERT INTO t VALUES (44), (28), (13), (85);

-- Test data:
-- binary     decimal
-- 00101100 = 44
-- 00011100 = 28
-- 00001101 = 13
-- 01010101 = 85

SELECT groupBitXor(num) FROM t;
```

```
-- Result:
-- binary     decimal
-- 01101000 = 104

┌─groupBitXor(num)─┐
│              104 │
└──────────────────┘
```

- [groupBitXor](#groupBitXor)

Was this page helpful?
groupConcat

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

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

Introduced in: v24.8.0

Calculates a concatenated string from a group of strings, optionally separated by a delimiter, and optionally limited by a maximum number of elements.

Note

If delimiter is specified without limit, it must be the first parameter. If both delimiter and limit are specified, delimiter must precede limit.

Also, if different delimiters are specified as parameters and arguments, the delimiter from arguments will be used only.

**Syntax**

```
groupConcat[(delimiter [, limit])](expression)
```

**Aliases**: `group_concat`

**Parameters**

- `delimiter` — A string that will be used to separate concatenated values. This parameter is optional and defaults to an empty string if not specified. [`String`](/docs/sql-reference/data-types/string)
- `limit` — A positive integer specifying the maximum number of elements to concatenate. If more elements are present, excess elements are ignored. This parameter is optional. [`UInt*`](/docs/sql-reference/data-types/int-uint)

**Arguments**

- `expression` — The expression or column name that outputs strings to be concatenated. [`String`](/docs/sql-reference/data-types/string)
- `delimiter` — A string that will be used to separate concatenated values. This parameter is optional and defaults to an empty string or delimiter from parameters if not specified. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a string consisting of the concatenated values of the column or expression. If the group has no elements or only null elements, and the function does not specify a handling for only null values, the result is a nullable string with a null value. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Basic usage without a delimiter**

```
SELECT groupConcat(Name) FROM Employees;
```

```
JohnJaneBob
```

**Using comma as a delimiter (parameter syntax)**

```
SELECT groupConcat(', ')(Name) FROM Employees;
```

```
John, Jane, Bob
```

**Using comma as a delimiter (argument syntax)**

```
SELECT groupConcat(Name, ', ') FROM Employees;
```

```
John, Jane, Bob
```

**Limiting the number of concatenated elements**

```
SELECT groupConcat(', ', 2)(Name) FROM Employees;
```

```
John, Jane
```

- [groupConcat](#groupConcat)

Was this page helpful?
groupUniqArray

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

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

Introduced in: v1.1.0

Creates an array from different argument values.
The memory consumption of this function is the same as for the [`uniqExact`](/docs/sql-reference/aggregate-functions/reference/uniqexact) function.

**Syntax**

```
groupUniqArray(x)
groupUniqArray(max_size)(x)
```

**Parameters**

- `max_size` — Limits the size of the resulting array to `max_size` elements. `groupUniqArray(1)(x)` is equivalent to `[any(x)]`. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Arguments**

- `x` — Expression. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns an array of unique values. [`Array`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
CREATE TABLE t (x UInt8) ENGINE = Memory;
INSERT INTO t VALUES (1), (2), (1), (3), (2), (4);

SELECT groupUniqArray(x) FROM t;
```

```
┌─groupUniqArray(x)─┐
│ [1,2,3,4]         │
└───────────────────┘
```

**With max\_size parameter**

```
SELECT groupUniqArray(2)(x) FROM t;
```

```
┌─groupUniqArray(2)(x)─┐
│ [1,2]                │
└──────────────────────┘
```

- [groupUniqArray](#groupUniqArray)

Was this page helpful?
kurtPop

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

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

Introduced in: v20.1.0

Computes the [kurtosis](https://en.wikipedia.org/wiki/Kurtosis) of a sequence.

**Syntax**

```
kurtPop(expr)
```

**Arguments**

- `expr` — [Expression](/docs/sql-reference/syntax#expressions) returning a number. [`(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 the kurtosis of the given distribution. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Computing kurtosis**

```
CREATE TABLE test_data (x Float64) ENGINE = Memory;
INSERT INTO test_data VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

SELECT kurtPop(x) FROM test_data;
```

```
┌─────────kurtPop(x)─┐
│ 1.7757575757575756 │
└────────────────────┘
```

- [kurtPop](#kurtPop)

Was this page helpful?
kurtSamp

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

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

Introduced in: v20.1.0

Computes the [sample kurtosis](https://en.wikipedia.org/wiki/Kurtosis) of a sequence.

It represents an unbiased estimate of the kurtosis of a random variable if passed values form its sample.

**Syntax**

```
kurtSamp(expr)
```

**Arguments**

- `expr` — [Expression](/docs/sql-reference/syntax#expressions) returning a number. [`(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 the kurtosis of the given distribution. If `n <= 1` (`n` is a size of the sample), then the function returns `nan`. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Computing sample kurtosis**

```
CREATE TABLE test_data (x Float64) ENGINE = Memory;
INSERT INTO test_data VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

SELECT kurtSamp(x) FROM test_data;
```

```
┌────────kurtSamp(x)─┐
│ 1.4383636363636365 │
└────────────────────┘
```

- [kurtSamp](#kurtSamp)

Was this page helpful?
largestTriangleThreeBuckets

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

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

Introduced in: v23.10.0

Applies the [Largest-Triangle-Three-Buckets](https://skemman.is/bitstream/1946/15343/3/SS_MSthesis.pdf) algorithm to the input data.
The algorithm is used for downsampling time series data for visualization.
It is designed to operate on series sorted by x coordinate.
It works by dividing the sorted series into buckets and then finding the largest triangle in each bucket.
The number of buckets is equal to the number of points in the resulting series.
The function will sort data by `x` and then apply the downsampling algorithm to the sorted data.

NaNs are ignored in the provided series, meaning that any NaN values will be excluded from the analysis.
This ensures that the function operates only on valid numerical data.

**Syntax**

```
largestTriangleThreeBuckets(n)(x, y)
```

**Aliases**: `lttb`

**Parameters**

- `n` — Number of points in the resulting series. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Arguments**

- `x` — x coordinate. [`(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) or [`Date`](/docs/sql-reference/data-types/date) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `y` — y coordinate. [`(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) or [`Date`](/docs/sql-reference/data-types/date) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Returned value**

Returns an array of tuples with two elements.. [`Array(Tuple(Float64, Float64))`](/docs/sql-reference/data-types/array)

**Examples**

**Downsampling time series data**

```
CREATE TABLE largestTriangleThreeBuckets_test (x Float64, y Float64) ENGINE = Memory;
INSERT INTO largestTriangleThreeBuckets_test VALUES
    (1.0, 10.0), (2.0, 20.0), (3.0, 15.0), (8.0, 60.0), (9.0, 55.0),
    (10.0, 70.0), (4.0, 30.0), (5.0, 40.0), (6.0, 35.0), (7.0, 50.0);

SELECT largestTriangleThreeBuckets(4)(x, y) FROM largestTriangleThreeBuckets_test;
```

```
┌────────largestTriangleThreeBuckets(4)(x, y)───────────┐
│           [(1,10),(3,15),(9,55),(10,70)]              │
└───────────────────────────────────────────────────────┘
```

- [largestTriangleThreeBuckets](#largestTriangleThreeBuckets)

Was this page helpful?
title_wip

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

Selects the last encountered value, similar to `anyLast`, but could accept NULL.
Mostly it should be used with [Window Functions](/docs/sql-reference/window-functions).
Without Window Functions the result will be random if the source stream is not ordered.

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

```
CREATE TABLE test_data
(
    a Int64,
    b Nullable(Int64)
)
ENGINE = Memory;

INSERT INTO test_data (a, b) VALUES (1,null), (2,3), (4, 5), (6,null)
```

### Example 1[​](#example1 "Direct link to Example 1")

The NULL value is ignored at default.

```
SELECT last_value(b) FROM test_data
```

```
┌─last_value_ignore_nulls(b)─┐
│                          5 │
└────────────────────────────┘
```

### Example 2[​](#example2 "Direct link to Example 2")

The NULL value is ignored.

```
SELECT last_value(b) ignore nulls FROM test_data
```

```
┌─last_value_ignore_nulls(b)─┐
│                          5 │
└────────────────────────────┘
```

### Example 3[​](#example3 "Direct link to Example 3")

The NULL value is accepted.

```
SELECT last_value(b) respect nulls FROM test_data
```

```
┌─last_value_respect_nulls(b)─┐
│                        ᴺᵁᴸᴸ │
└─────────────────────────────┘
```

### Example 4[​](#example4 "Direct link to Example 4")

Stabilized result using the sub-query with `ORDER BY`.

```
SELECT
    last_value_respect_nulls(b),
    last_value(b)
FROM
(
    SELECT *
    FROM test_data
    ORDER BY a ASC
)
```

```
┌─last_value_respect_nulls(b)─┬─last_value(b)─┐
│                        ᴺᵁᴸᴸ │             5 │
└─────────────────────────────┴───────────────┘
```

- [examples](#examples)
  - [Example 1](#example1)- [Example 2](#example2)- [Example 3](#example3)- [Example 4](#example4)

Was this page helpful?
mannWhitneyUTest

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

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

Introduced in: v21.1.0

Applies the Mann-Whitney rank test to samples from two populations.

Values of both samples are in the `sample_data` column.
If `sample_index` equals to 0 then the value in that row belongs to the sample from the first population.
Otherwise it belongs to the sample from the second population.
The null hypothesis is that two populations are stochastically equal.
Also one-sided hypotheses can be tested.
This test does not assume that data have normal distribution.

**Syntax**

```
mannWhitneyUTest[(alternative[, continuity_correction])](sample_data, sample_index)
```

**Parameters**

- `alternative` — Optional. Alternative hypothesis. 'two-sided' (default): two populations are not stochastically equal. 'greater': values in the first sample are stochastically greater than those in the second sample. 'less': values in the first sample are stochastically less than those in the second sample. [`String`](/docs/sql-reference/data-types/string)
- `continuity_correction` — Optional. If not 0 then continuity correction in the normal approximation for the p-value is applied. The default value is 1. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Arguments**

- `sample_data` — Sample data. [`(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)
- `sample_index` — Sample index. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a tuple with two elements: calculated U-statistic and calculated p-value. [`Tuple(Float64, Float64)`](/docs/sql-reference/data-types/tuple)

**Examples**

**Mann-Whitney U test example**

```
CREATE TABLE mww_ttest (sample_data Float64, sample_index UInt8) ENGINE = Memory;
INSERT INTO mww_ttest VALUES (10, 0), (11, 0), (12, 0), (1, 1), (2, 1), (3, 1);

SELECT mannWhitneyUTest('greater')(sample_data, sample_index) FROM mww_ttest;
```

```
┌─mannWhitneyUTest('greater')(sample_data, sample_index)─┐
│ (9,0.04042779918503192)                                │
└────────────────────────────────────────────────────────┘
```

**See Also**

- [Mann–Whitney U test](https://en.wikipedia.org/wiki/Mann%E2%80%93Whitney_U_test)
- [Stochastic ordering](https://en.wikipedia.org/wiki/Stochastic_ordering)

- [mannWhitneyUTest](#mannWhitneyUTest)

Was this page helpful?
max

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

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

Introduced in: v1.1.0

Aggregate function that calculates the maximum across a group of values.

**Syntax**

```
max(column)
```

**Arguments**

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

**Returned value**

The maximum value across the group with type equal to that of the input. [`Any`](/docs/sql-reference/data-types)

**Examples**

**Simple max example**

```
CREATE TABLE employees (name String, salary UInt32) ENGINE = Memory;
INSERT INTO employees VALUES ('Alice', 3000), ('Bob', 4000), ('Charlie', 3500);

SELECT max(salary) FROM employees;
```

```
┌─max(salary)─┐
│        4000 │
└─────────────┘
```

**Max with GROUP BY**

```
CREATE TABLE sales (department String, revenue UInt32) ENGINE = Memory;
INSERT INTO sales VALUES ('Engineering', 100000), ('Engineering', 120000), ('Marketing', 80000), ('Marketing', 90000);

SELECT department, max(revenue) FROM sales GROUP BY department ORDER BY department;
```

```
┌─department──┬─max(revenue)─┐
│ Engineering │       120000 │
│ Marketing   │        90000 │
└─────────────┴──────────────┘
```

**Note about non-aggregate maximum**

```
-- If you need non-aggregate function to choose a maximum of two values, see greatest():
SELECT greatest(a, b) FROM table;
```

- [max](#max)

Was this page helpful?
maxIntersections

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

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

Introduced in: v20.1.0

Aggregate function that calculates the maximum number of times that a group of intervals intersects each other (if all the intervals intersect at least once).

**Syntax**

```
maxIntersections(start_column, end_column)
```

**Arguments**

- `start_column` — A numeric column that represents the start of each interval. If `start_column` is `NULL` or 0 then the interval will be skipped. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)
- `end_column` — A numeric column that represents the end of each interval. If `end_column` is `NULL` or 0 then the interval will be skipped. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)

**Returned value**

Returns the maximum number of intersected intervals. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Calculating maximum intersections**

```
CREATE TABLE my_events (
    start UInt32,
    end UInt32
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO my_events VALUES
(1, 3),
(1, 6),
(2, 5),
(3, 7);

SELECT maxIntersections(start, end) FROM my_events;
```

```
┌─maxIntersections(start, end)─┐
│                            3 │
└──────────────────────────────┘
```

- [maxIntersections](#maxIntersections)

Was this page helpful?
maxIntersectionsPosition

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

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

Introduced in: v1.1.0

Aggregate function that calculates the positions of the occurrences of the [`maxIntersections`](/docs/sql-reference/aggregate-functions/reference/maxintersections) function.

**Syntax**

```
maxIntersectionsPosition(start_column, end_column)
```

**Arguments**

- `start_column` — A numeric column that represents the start of each interval. If `start_column` is `NULL` or 0 then the interval will be skipped. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)
- `end_column` — A numeric column that represents the end of each interval. If `end_column` is `NULL` or 0 then the interval will be skipped. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)

**Returned value**

Returns the start positions of the maximum number of intersected intervals. [`Any`](/docs/sql-reference/data-types)

**Examples**

**Finding maximum intersections position**

```
CREATE TABLE my_events (
    start UInt32,
    end UInt32
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO my_events VALUES
(1, 3),
(1, 6),
(2, 5),
(3, 7);

SELECT maxIntersectionsPosition(start, end) FROM my_events;
```

```
┌─maxIntersectionsPosition(start, end)─┐
│                                    2 │
└──────────────────────────────────────┘
```

- [maxIntersectionsPosition](#maxIntersectionsPosition)

Was this page helpful?