Other functions

**Returned value**

Returns a list of values corresponding to the list of keys, or `NULL` if a key is not found. [`Any`](/docs/sql-reference/data-types)

**Examples**

**Usage example**

```
CREATE TABLE db_test.id_val(`id` UInt32, `val` UInt32) ENGINE = Join(ANY, LEFT, id);
INSERT INTO db_test.id_val VALUES (1,11)(2,12)(4,13);

SELECT joinGetOrNull(db_test.id_val, 'val', toUInt32(1)), joinGetOrNull(db_test.id_val, 'val', toUInt32(999));
```

```
┌─joinGetOrNull(db_test.id_val, 'val', toUInt32(1))─┬─joinGetOrNull(db_test.id_val, 'val', toUInt32(999))─┐
│                                                11 │                                                ᴺᵁᴸᴸ │
└───────────────────────────────────────────────────┴─────────────────────────────────────────────────────┘
```

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

Introduced in: v18.12.0

Returns the position of a value in the dictionary of a [LowCardinality](/docs/sql-reference/data-types/lowcardinality) column. Positions start at 1. Since LowCardinality have per-part dictionaries, this function may return different positions for the same value in different parts.

**Syntax**

```
lowCardinalityIndices(col)
```

**Arguments**

- `col` — A low cardinality column. [`LowCardinality`](/docs/sql-reference/data-types/lowcardinality)

**Returned value**

The position of the value in the dictionary of the current part. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage examples**

```
DROP TABLE IF EXISTS test;
CREATE TABLE test (s LowCardinality(String)) ENGINE = Memory;

-- create two parts:

INSERT INTO test VALUES ('ab'), ('cd'), ('ab'), ('ab'), ('df');
INSERT INTO test VALUES ('ef'), ('cd'), ('ab'), ('cd'), ('ef');

SELECT s, lowCardinalityIndices(s) FROM test;
```

```
┌─s──┬─lowCardinalityIndices(s)─┐
│ ab │                        1 │
│ cd │                        2 │
│ ab │                        1 │
│ ab │                        1 │
│ df │                        3 │
└────┴──────────────────────────┘
┌─s──┬─lowCardinalityIndices(s)─┐
│ ef │                        1 │
│ cd │                        2 │
│ ab │                        3 │
│ cd │                        2 │
│ ef │                        1 │
└────┴──────────────────────────┘
```

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

Introduced in: v18.12.0

Returns the dictionary values of a [LowCardinality](/docs/sql-reference/data-types/lowcardinality) column.
If the block is smaller or larger than the dictionary size, the result will be truncated or extended with default values.
Since LowCardinality have per-part dictionaries, this function may return different dictionary values in different parts.

**Syntax**

```
lowCardinalityKeys(col)
```

**Arguments**

- `col` — A low cardinality column. [`LowCardinality`](/docs/sql-reference/data-types/lowcardinality)

**Returned value**

Returns the dictionary keys. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**lowCardinalityKeys**

```
DROP TABLE IF EXISTS test;
CREATE TABLE test (s LowCardinality(String)) ENGINE = Memory;

-- create two parts:

INSERT INTO test VALUES ('ab'), ('cd'), ('ab'), ('ab'), ('df');
INSERT INTO test VALUES ('ef'), ('cd'), ('ab'), ('cd'), ('ef');

SELECT s, lowCardinalityKeys(s) FROM test;
```

```
┌─s──┬─lowCardinalityKeys(s)─┐
│ ef │                       │
│ cd │ ef                    │
│ ab │ cd                    │
│ cd │ ab                    │
│ ef │                       │
└────┴───────────────────────┘
┌─s──┬─lowCardinalityKeys(s)─┐
│ ab │                       │
│ cd │ ab                    │
│ ab │ cd                    │
│ ab │ df                    │
│ df │                       │
└────┴───────────────────────┘
```

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

Introduced in: v1.1.0

Turns a constant into a full column containing a single value.
Full columns and constants are represented differently in memory.
Functions usually execute different code for normal and constant arguments, although the result should typically be the same.
This function can be used to debug this behavior.

**Syntax**

```
materialize(x)
```

**Arguments**

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

**Returned value**

Returns a full column containing the constant value. [`Any`](/docs/sql-reference/data-types)

**Examples**

**Usage example**

```
-- In the example below the `countMatches` function expects a constant second argument.
-- This behaviour can be debugged by using the `materialize` function to turn a constant into a full column,
-- verifying that the function throws an error for a non-constant argument.

SELECT countMatches('foobarfoo', 'foo');
SELECT countMatches('foobarfoo', materialize('foo'));
```

```
2
Code: 44. DB::Exception: Received from localhost:9000. DB::Exception: Illegal type of argument #2 'pattern' of function countMatches, expected constant String, got String
```

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

Introduced in: v23.10.0

Calculates the minimum required sample size for an A/B test comparing means of a continuous metric in two samples.

Uses the formula described in [this article](https://towardsdatascience.com/required-sample-size-for-a-b-testing-6f6608dd330a).
Assumes equal sizes of treatment and control groups.
Returns the required sample size for one group (i.e. the sample size required for the whole experiment is twice the returned value).
Also assumes equal variance of the test metric in treatment and control groups.

**Syntax**

```
minSampleSizeContinuous(baseline, sigma, mde, power, alpha)
```

**Aliases**: `minSampleSizeContinous`

**Arguments**

- `baseline` — Baseline value of a metric. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)
- `sigma` — Baseline standard deviation of a metric. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)
- `mde` — Minimum detectable effect (MDE) as percentage of the baseline value (e.g. for a baseline value 112.25 the MDE 0.03 means an expected change to 112.25 ± 112.25\*0.03). [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)
- `power` — Required statistical power of a test (1 - probability of Type II error). [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)
- `alpha` — Required significance level of a test (probability of Type I error). [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)

**Returned value**

Returns a named Tuple with 3 elements: `minimum_sample_size`, `detect_range_lower` and `detect_range_upper`. These are respectively: the required sample size, the lower bound of the range of values not detectable with the returned required sample size, calculated as `baseline * (1 - mde)`, and the upper bound of the range of values not detectable with the returned required sample size, calculated as `baseline * (1 + mde)` (Float64). [`Tuple(Float64, Float64, Float64)`](/docs/sql-reference/data-types/tuple)

**Examples**

**minSampleSizeContinuous**

```
SELECT minSampleSizeContinuous(112.25, 21.1, 0.03, 0.80, 0.05) AS sample_size
```

```
(616.2931945826209,108.8825,115.6175)
```

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

Introduced in: v22.6.0

Calculates minimum required sample size for an A/B test comparing conversions (proportions) in two samples.

Uses the formula described in [this article](https://towardsdatascience.com/required-sample-size-for-a-b-testing-6f6608dd330a). Assumes equal sizes of treatment and control groups. Returns the sample size required for one group (i.e. the sample size required for the whole experiment is twice the returned value).

**Syntax**

```
minSampleSizeConversion(baseline, mde, power, alpha)
```

**Arguments**