ClickHouse/clickhouse-docs-embeddings huggingface dataset:
covarPop

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

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

Introduced in: v1.1.0

Calculates the population covariance:

Σ(x−xˉ)(y−yˉ)n\frac{\Sigma{(x - \bar{x})(y - \bar{y})}}{n}nΣ(x−xˉ)(y−yˉ​)​
  

Note

This function uses a numerically unstable algorithm. If you need [numerical stability](https://en.wikipedia.org/wiki/Numerical_stability) in calculations, use the [`covarPopStable`](/docs/sql-reference/aggregate-functions/reference/covarpopstable) function. It works slower but provides a lower computational error.

**Syntax**

```
covarPop(x, y)
```

**Aliases**: `COVAR_POP`

**Arguments**

- `x` — First variable. [`(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)
- `y` — Second variable. [`(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 population covariance between `x` and `y`. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Basic population covariance calculation**

```
DROP TABLE IF EXISTS series;
CREATE TABLE series(i UInt32, x_value Float64, y_value Float64) ENGINE = Memory;
INSERT INTO series(i, x_value, y_value) VALUES (1, 5.6, -4.4),(2, -9.6, 3),(3, -1.3, -4),(4, 5.3, 9.7),(5, 4.4, 0.037),(6, -8.6, -7.8),(7, 5.1, 9.3),(8, 7.9, -3.6),(9, -8.2, 0.62),(10, -3, 7.3);

SELECT covarPop(x_value, y_value)
FROM series
```

```
┌─covarPop(x_value, y_value)─┐
│                   6.485648 │
└────────────────────────────┘
```

- [covarPop](#covarPop)

Was this page helpful?
covarPopMatrix

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

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

Introduced in: v23.2.0

Returns the population covariance matrix over N variables.

**Syntax**

```
covarPopMatrix(x1[, x2, ...])
```

**Arguments**

- `x1[, x2, ...]` — A variable number of parameters. [`(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 population covariance matrix. [`Array(Array(Float64))`](/docs/sql-reference/data-types/array)

**Examples**

**Basic population covariance matrix calculation**

```
DROP TABLE IF EXISTS test;
CREATE TABLE test
(
    a UInt32,
    b Float64,
    c Float64,
    d Float64
)
ENGINE = Memory;
INSERT INTO test(a, b, c, d) VALUES (1, 5.6, -4.4, 2.6), (2, -9.6, 3, 3.3), (3, -1.3, -4, 1.2), (4, 5.3, 9.7, 2.3), (5, 4.4, 0.037, 1.222), (6, -8.6, -7.8, 2.1233), (7, 5.1, 9.3, 8.1222), (8, 7.9, -3.6, 9.837), (9, -8.2, 0.62, 8.43555), (10, -3, 7.3, 6.762);

SELECT arrayMap(x -> round(x, 3), arrayJoin(covarPopMatrix(a, b, c, d))) AS covarPopMatrix
FROM test
```

```
┌─covarPopMatrix────────────┐
│ [8.25,-1.76,4.08,6.748]   │
│ [-1.76,41.07,6.486,2.132] │
│ [4.08,6.486,34.21,4.755]  │
│ [6.748,2.132,4.755,9.93]  │
└───────────────────────────┘
```

- [covarPopMatrix](#covarPopMatrix)

Was this page helpful?
covarPopStable

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

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

Introduced in: v1.1.0

Calculates the population covariance:

Σ(x−xˉ)(y−yˉ)n\frac{\Sigma{(x - \bar{x})(y - \bar{y})}}{n}nΣ(x−xˉ)(y−yˉ​)​
  

It is similar to the [`covarPop`](/docs/sql-reference/aggregate-functions/reference/covarpop) function, but uses a numerically stable algorithm. As a result, `covarPopStable` is slower than `covarPop` but produces a more accurate result.

**Syntax**

```
covarPopStable(x, y)
```

**Arguments**

- `x` — First variable. [`(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)
- `y` — Second variable. [`(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 population covariance between `x` and `y`. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Basic population covariance calculation with stable algorithm**

```
DROP TABLE IF EXISTS series;
CREATE TABLE series(i UInt32, x_value Float64, y_value Float64) ENGINE = Memory;
INSERT INTO series(i, x_value, y_value) VALUES (1, 5.6,-4.4),(2, -9.6,3),(3, -1.3,-4),(4, 5.3,9.7),(5, 4.4,0.037),(6, -8.6,-7.8),(7, 5.1,9.3),(8, 7.9,-3.6),(9, -8.2,0.62),(10, -3,7.3);

SELECT covarPopStable(x_value, y_value)
FROM series
```

```
┌─covarPopStable(x_value, y_value)─┐
│                         6.485648 │
└──────────────────────────────────┘
```

- [covarPopStable](#covarPopStable)

Was this page helpful?
covarSamp

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

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

Introduced in: v1.1.0

Calculates the sample covariance:

Σ(x−xˉ)(y−yˉ)n−1\frac{\Sigma{(x - \bar{x})(y - \bar{y})}}{n - 1}n−1Σ(x−xˉ)(y−yˉ​)​

Note

This function uses a numerically unstable algorithm. If you need [numerical stability](https://en.wikipedia.org/wiki/Numerical_stability) in calculations, use the [`covarSampStable`](/docs/sql-reference/aggregate-functions/reference/covarsampstable) function.
It works slower but provides a lower computational error.

**Syntax**

```
covarSamp(x, y)
```

**Aliases**: `COVAR_SAMP`

**Arguments**

- `x` — First variable. [`(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)
- `y` — Second variable. [`(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 sample covariance between `x` and `y`. For `n <= 1`, `nan` is returned. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Basic sample covariance calculation**

```
DROP TABLE IF EXISTS series;
CREATE TABLE series(i UInt32, x_value Float64, y_value Float64) ENGINE = Memory;
INSERT INTO series(i, x_value, y_value) VALUES (1, 5.6,-4.4),(2, -9.6,3),(3, -1.3,-4),(4, 5.3,9.7),(5, 4.4,0.037),(6, -8.6,-7.8),(7, 5.1,9.3),(8, 7.9,-3.6),(9, -8.2,0.62),(10, -3,7.3);

SELECT covarSamp(x_value, y_value)
FROM series
```

```
┌─covarSamp(x_value, y_value)─┐
│           7.206275555555556 │
└─────────────────────────────┘
```

**Single value returns NaN**

```
SELECT covarSamp(x_value, y_value)
FROM series LIMIT 1
```

```
┌─covarSamp(x_value, y_value)─┐
│                         nan │
└─────────────────────────────┘
```

- [covarSamp](#covarSamp)

Was this page helpful?
covarSampMatrix

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

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

Introduced in: v23.2.0

Returns the sample covariance matrix over N variables.

**Syntax**

```
covarSampMatrix(x1[, x2, ...])
```

**Arguments**

- `x1[, x2, ...]` — One or more parameters over which to compute the sample covariance matrix. [`(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 sample covariance matrix. [`Array(Array(Float64))`](/docs/sql-reference/data-types/array)

**Examples**

**Basic sample covariance matrix calculation**

```
DROP TABLE IF EXISTS test;
CREATE TABLE test
(
    a UInt32,
    b Float64,
    c Float64,
    d Float64
)
ENGINE = Memory;
INSERT INTO test(a, b, c, d) VALUES (1, 5.6, -4.4, 2.6), (2, -9.6, 3, 3.3), (3, -1.3, -4, 1.2), (4, 5.3, 9.7, 2.3), (5, 4.4, 0.037, 1.222), (6, -8.6, -7.8, 2.1233), (7, 5.1, 9.3, 8.1222), (8, 7.9, -3.6, 9.837), (9, -8.2, 0.62, 8.43555), (10, -3, 7.3, 6.762);

SELECT arrayMap(x -> round(x, 3), arrayJoin(covarSampMatrix(a, b, c, d))) AS covarSampMatrix
FROM test
```

```
┌─covarSampMatrix─────────────┐
│ [9.167,-1.956,4.534,7.498]  │
│ [-1.956,45.634,7.206,2.369] │
│ [4.534,7.206,38.011,5.283]  │
│ [7.498,2.369,5.283,11.034]  │
└─────────────────────────────┘
```

- [covarSampMatrix](#covarSampMatrix)

Was this page helpful?
covarSampStable

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

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

Introduced in: v1.1.0

Calculates the sample covariance:

Σ(x−xˉ)(y−yˉ)n−1\frac{\Sigma{(x - \bar{x})(y - \bar{y})}}{n - 1}n−1Σ(x−xˉ)(y−yˉ​)​
  

It is similar to [`covarSamp`](/docs/sql-reference/aggregate-functions/reference/covarsamp) but uses a numerically stable algorithm.
As a result, `covarSampStable` is slower than `covarSamp` but provides a lower computational error.

**Syntax**

```
covarSampStable(x, y)
```

**Arguments**

- `x` — First variable. [`(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)
- `y` — Second variable. [`(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 sample covariance between `x` and `y`. For `n <= 1`, `inf` is returned. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Basic sample covariance calculation with stable algorithm**

```
DROP TABLE IF EXISTS series;
CREATE TABLE series(i UInt32, x_value Float64, y_value Float64) ENGINE = Memory;
INSERT INTO series(i, x_value, y_value) VALUES (1, 5.6,-4.4),(2, -9.6,3),(3, -1.3,-4),(4, 5.3,9.7),(5, 4.4,0.037),(6, -8.6,-7.8),(7, 5.1,9.3),(8, 7.9,-3.6),(9, -8.2,0.62),(10, -3,7.3);

SELECT covarSampStable(x_value, y_value)
FROM
(
    SELECT
        x_value,
        y_value
    FROM series
);
```

```
┌─covarSampStable(x_value, y_value)─┐
│                 7.206275555555556 │
└───────────────────────────────────┘
```

**Single value returns inf**

```
SELECT covarSampStable(x_value, y_value)
FROM
(
    SELECT
        x_value,
        y_value
    FROM series LIMIT 1
);
```

```
┌─covarSampStable(x_value, y_value)─┐
│                               inf │
└───────────────────────────────────┘
```

- [covarSampStable](#covarSampStable)

Was this page helpful?
cramersV

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

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

Introduced in: v22.1.0

[Cramer's V](https://en.wikipedia.org/wiki/Cram%C3%A9r%27s_V) (sometimes referred to as Cramer's phi) is a measure of association between two columns in a table.
The result of the `cramersV` function ranges from 0 (corresponding to no association between the variables) to 1 and can reach 1 only when each value is completely determined by the other.
It may be viewed as the association between two variables as a percentage of their maximum possible variation.

Note

For a bias corrected version of Cramer's V see: [cramersVBiasCorrected](/docs/sql-reference/aggregate-functions/reference/cramersvbiascorrected)

**Syntax**

```
cramersV(column1, column2)
```

**Arguments**

- `column1` — First column to be compared. [`(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)
- `column2` — Second column to be compared. [`(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 value between 0 (corresponding to no association between the columns' values) to 1 (complete association). [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**No association between columns**

```
SELECT
    cramersV(a, b)
FROM
    (
        SELECT
            number % 3 AS a,
            number % 5 AS b
        FROM
            numbers(150)
    )
```

```
┌─cramersV(a, b)─┐
│              0 │
└────────────────┘
```

**High association between columns**

```
SELECT
    cramersV(a, b)
FROM
    (
        SELECT
            number % 10 AS a,
            number % 5 AS b
        FROM
            numbers(150)
    )
```

```
┌─────cramersV(a, b)─┐
│ 0.8944271909999159 │
└────────────────────┘
```

- [cramersV](#cramersV)

Was this page helpful?
cramersVBiasCorrected

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

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

Introduced in: v22.1.0

Cramer's V is a measure of association between two columns in a table.
The result of the [`cramersV` function](/docs/sql-reference/aggregate-functions/reference/cramersv) ranges from 0 (corresponding to no association between the variables) to 1 and can reach 1 only when each value is completely determined by the other.
The function can be heavily biased, so this version of Cramer's V uses the [bias correction](https://en.wikipedia.org/wiki/Cram%C3%A9r%27s_V#Bias_correction).

**Syntax**

```
cramersVBiasCorrected(column1, column2)
```

**Arguments**

- `column1` — First column to be compared. [`Any`](/docs/sql-reference/data-types)
- `column2` — Second column to be compared. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns a value between 0 (corresponding to no association between the columns' values) to 1 (complete association). [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Comparison with regular cramersV**

```
SELECT
    cramersV(a, b),
    cramersVBiasCorrected(a, b)
FROM
    (
        SELECT
            number % 10 AS a,
            number % 4 AS b
        FROM
            numbers(150)
    )
```

```
┌──────cramersV(a, b)─┬─cramersVBiasCorrected(a, b)─┐
│ 0.41171788506213564 │         0.33369281784141364 │
└─────────────────────┴─────────────────────────────┘
```

- [cramersVBiasCorrected](#cramersVBiasCorrected)

Was this page helpful?
deltaSum

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

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

Introduced in: v21.3.0

Sums the arithmetic difference between consecutive rows.
If the difference is negative, it is ignored.

Tip

The underlying data must be sorted for this function to work properly.
If you would like to use this function in a [materialized view](/docs/sql-reference/statements/create/view#materialized-view), you most likely want to use the [`deltaSumTimestamp`](/docs/sql-reference/aggregate-functions/reference/deltasumtimestamp) function instead.

See also:

- [`runningDifference`](/docs/sql-reference/functions/other-functions#runningDifference)

**Syntax**

```
deltaSum(x1[, x2, ...])
```

**Arguments**

- `x1[, x2, ...]` — One or more input values. [`Integer`](/docs/sql-reference/data-types/int-uint) or [`Float`](/docs/sql-reference/data-types/float)

**Returned value**

Returns a gained arithmetic difference of the input values. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)

**Examples**

**Basic usage with positive differences**

```
SELECT deltaSum(arrayJoin([1, 2, 3]))
```

```
┌─deltaSum(arrayJoin([1, 2, 3]))─┐
│                              2 │
└────────────────────────────────┘
```

**Mixed values with negative differences ignored**

```
SELECT deltaSum(arrayJoin([1, 2, 3, 0, 3, 4, 2, 3]))
```

```
┌─deltaSum(arrayJoin([1, 2, 3, 0, 3, 4, 2, 3]))─┐
│                                             7 │
└───────────────────────────────────────────────┘
```

**Float values**

```
SELECT deltaSum(arrayJoin([2.25, 3, 4.5]))
```

```
┌─deltaSum(arrayJoin([2.25, 3, 4.5]))─┐
│                                2.25 │
└─────────────────────────────────────┘
```

**See also**

- [`runningDifference`](/docs/sql-reference/functions/other-functions#runningDifference)

- [deltaSum](#deltaSum)

Was this page helpful?
deltaSumTimestamp

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

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

Introduced in: v21.6.0

Adds the difference between consecutive rows.
If the difference is negative, it is ignored.

This function is primarily for [materialized views](/docs/sql-reference/statements/create/view#materialized-view) that store data ordered by some time bucket-aligned timestamp, for example, a `toStartOfMinute` bucket.
Because the rows in such a materialized view will all have the same timestamp, it is impossible for them to be merged in the correct order, without storing the original, unrounded timestamp value.
The `deltaSumTimestamp` function keeps track of the original `timestamp` of the values it's seen, so the values (states) of the function are correctly computed during merging of parts.

To calculate the delta sum across an ordered collection you can simply use the [`deltaSum`](/docs/sql-reference/aggregate-functions/reference/deltasum) function.

**Syntax**

```
deltaSumTimestamp(value, timestamp)
```

**Arguments**

- `value` — Input values. [`(U)Int*`](/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)
- `timestamp` — The parameter for order values. [`(U)Int*`](/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)

**Returned value**

Returns accumulated differences between consecutive values, ordered by the `timestamp` parameter. [`(U)Int*`](/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)

**Examples**

**Basic usage with timestamp ordering**

```
SELECT deltaSumTimestamp(value, timestamp)
FROM (SELECT number AS timestamp, [0, 4, 8, 3, 0, 0, 0, 1, 3, 5][number] AS value FROM numbers(1, 10))
```

```
┌─deltaSumTimestamp(value, timestamp)─┐
│                                  13 │
└─────────────────────────────────────┘
```

- [deltaSumTimestamp](#deltaSumTimestamp)

Was this page helpful?
distinctJSONPathsAndTypes

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

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

Introduced in: v24.9.0

Calculates the list of distinct paths and their types stored in [JSON](https://clickhouse.com/docs/sql-reference/data-types/newjson) column.

Note

If JSON declaration contains paths with specified types, these paths will be always included in the result of `distinctJSONPaths/distinctJSONPathsAndTypes` functions even if input data didn't have values for these paths.

**Syntax**

```
distinctJSONPathsAndTypes(json)
```

**Arguments**

- `json` — JSON column. [`JSON`](/docs/sql-reference/data-types/newjson)

**Returned value**

Returns the sorted map of paths and types. [`Map(String, Array(String))`](/docs/sql-reference/data-types/map)

**Examples**

**Basic usage with mixed types**

```
DROP TABLE IF EXISTS test_json;
CREATE TABLE test_json(json JSON) ENGINE = Memory;
INSERT INTO test_json VALUES ('{"a" : 42, "b" : "Hello"}'), ('{"b" : [1, 2, 3], "c" : {"d" : {"e" : "2020-01-01"}}}'), ('{"a" : 43, "c" : {"d" : {"f" : [{"g" : 42}]}}}');

SELECT distinctJSONPathsAndTypes(json) FROM test_json;
```

```
┌─distinctJSONPathsAndTypes(json)───────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ {'a':['Int64'],'b':['Array(Nullable(Int64))','String'],'c.d.e':['Date'],'c.d.f':['Array(JSON(max_dynamic_types=16, max_dynamic_paths=256))']} │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```

**With declared JSON paths**

```
DROP TABLE IF EXISTS test_json;
CREATE TABLE test_json(json JSON(a UInt32)) ENGINE = Memory;
INSERT INTO test_json VALUES ('{"b" : "Hello"}'), ('{"b" : "World", "c" : [1, 2, 3]}');

SELECT distinctJSONPathsAndTypes(json) FROM test_json;
```

```
┌─distinctJSONPathsAndTypes(json)────────────────────────────────┐
│ {'a':['UInt32'],'b':['String'],'c':['Array(Nullable(Int64))']} │
└────────────────────────────────────────────────────────────────┘
```

- [distinctJSONPathsAndTypes](#distinctJSONPathsAndTypes)

Was this page helpful?
distinctDynamicTypes

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

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

Introduced in: v24.9.0

Calculates the list of distinct data types stored in [Dynamic](https://clickhouse.com/docs/sql-reference/data-types/dynamic) column.

**Syntax**

```
distinctDynamicTypes(dynamic)
```

**Arguments**

- `dynamic` — Dynamic column. [`Dynamic`](/docs/sql-reference/data-types/dynamic)

**Returned value**

Returns the sorted list of data type names. [`Array(String)`](/docs/sql-reference/data-types/array)

**Examples**

**Basic usage with mixed types**

```
DROP TABLE IF EXISTS test_dynamic;
CREATE TABLE test_dynamic(d Dynamic) ENGINE = Memory;
INSERT INTO test_dynamic VALUES (42), (NULL), ('Hello'), ([1, 2, 3]), ('2020-01-01'), (map(1, 2)), (43), ([4, 5]), (NULL), ('World'), (map(3, 4));

SELECT distinctDynamicTypes(d) FROM test_dynamic;
```

```
┌─distinctDynamicTypes(d)──────────────────────────────────────────┐
│ ['Array(Int64)', 'Date', 'Int64', 'Map(UInt8, UInt8)', 'String'] │
└──────────────────────────────────────────────────────────────────┘
```

- [distinctDynamicTypes](#distinctDynamicTypes)

Was this page helpful?
distinctJSONPaths

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

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

Introduced in: v24.9.0

Calculates a list of distinct paths stored in a [JSON](https://clickhouse.com/docs/sql-reference/data-types/newjson) column.

**Syntax**

```
distinctJSONPaths(json)
```

**Arguments**

- `json` — JSON column. [`JSON`](/docs/sql-reference/data-types/newjson)

**Returned value**

Returns the sorted list of paths. [`Array(String)`](/docs/sql-reference/data-types/array)

**Examples**

**Basic usage with nested JSON**

```
DROP TABLE IF EXISTS test_json;
CREATE TABLE test_json(json JSON) ENGINE = Memory;
INSERT INTO test_json VALUES ('{"a" : 42, "b" : "Hello"}'), ('{"b" : [1, 2, 3], "c" : {"d" : {"e" : "2020-01-01"}}}'), ('{"a" : 43, "c" : {"d" : {"f" : [{"g" : 42}]}}}');

SELECT distinctJSONPaths(json) FROM test_json;
```

```
┌─distinctJSONPaths(json)───┐
│ ['a','b','c.d.e','c.d.f'] │
└───────────────────────────┘
```

**With declared JSON paths**

```
DROP TABLE IF EXISTS test_json;
CREATE TABLE test_json(json JSON) ENGINE = Memory;
INSERT INTO test_json VALUES ('{"a" : 42, "b" : "Hello"}'), ('{"b" : [1, 2, 3], "c" : {"d" : {"e" : "2020-01-01"}}}'), ('{"a" : 43, "c" : {"d" : {"f" : [{"g" : 42}]}}}')

SELECT distinctJSONPaths(json) FROM test_json;
```

```
┌─distinctJSONPaths(json)─┐
│ ['a','b','c']           │
└─────────────────────────┘
```

- [distinctJSONPaths](#distinctJSONPaths)

Was this page helpful?