ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

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

Evaluates a prometheus query using data from a TimeSeries table.

## Syntax[​](#syntax "Direct link to Syntax")

```
prometheusQuery('db_name', 'time_series_table', 'promql_query', evaluation_time)
prometheusQuery(db_name.time_series_table, 'promql_query', evaluation_time)
prometheusQuery('time_series_table', 'promql_query', evaluation_time)
```

## Arguments[​](#arguments "Direct link to Arguments")

- `db_name` - The name of the database where a TimeSeries table is located.
- `time_series_table` - The name of a TimeSeries table.
- `promql_query` - A query written in [PromQL syntax](https://prometheus.io/docs/prometheus/latest/querying/basics/).
- `evaluation_time - The evaluation timestamp. To evaluate a query at the current time, use` now()`as`evaluation\_time`.

## Returned value[​](#returned_value "Direct link to Returned value")

The function can returns different columns depending on the result type of the query passed to parameter `promql_query`:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Result Type Result Columns Example|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | vector tags Array(Tuple(String, String)), timestamp TimestampType, value ValueType prometheusQuery(mytable, 'up')|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | matrix tags Array(Tuple(String, String)), time\_series Array(Tuple(TimestampType, ValueType)) prometheusQuery(mytable, 'up[1m]')|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | scalar scalar ValueType prometheusQuery(mytable, '1h30m')|  |  |  | | --- | --- | --- | | string string String prometheusQuery(mytable, '"abc"') | | | | | | | | | | | | | | |

## Example[​](#example "Direct link to Example")

```
SELECT * FROM prometheusQuery(mytable, 'rate(http_requests{job="prometheus"}[10m])[1h:10m]', now())
```

- [Syntax](#syntax)- [Arguments](#arguments)- [Returned value](#returned_value)- [Example](#example)

Was this page helpful?
title_wip

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

Evaluates a prometheus query using data from a TimeSeries table over a range of evaluation times.

## Syntax[​](#syntax "Direct link to Syntax")

```
prometheusQueryRange('db_name', 'time_series_table', 'promql_query', start_time, end_time, step)
prometheusQueryRange(db_name.time_series_table, 'promql_query', start_time, end_time, step)
prometheusQueryRange('time_series_table', 'promql_query', start_time, end_time, step)
```

## Arguments[​](#arguments "Direct link to Arguments")

- `db_name` - The name of the database where a TimeSeries table is located.
- `time_series_table` - The name of a TimeSeries table.
- `promql_query` - A query written in [PromQL syntax](https://prometheus.io/docs/prometheus/latest/querying/basics/).
- `start_time` - The start time of the evaluation range.
- `end_time` - The end time of the evaluation range.
- `step` - The step used to iterate the evaluation time from `start_time` to `end_time` (inclusively).

## Returned value[​](#returned_value "Direct link to Returned value")

The function can returns different columns depending on the result type of the query passed to parameter `promql_query`:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Result Type Result Columns Example|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | vector tags Array(Tuple(String, String)), timestamp TimestampType, value ValueType prometheusQuery(mytable, 'up')|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | matrix tags Array(Tuple(String, String)), time\_series Array(Tuple(TimestampType, ValueType)) prometheusQuery(mytable, 'up[1m]')|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | scalar scalar ValueType prometheusQuery(mytable, '1h30m')|  |  |  | | --- | --- | --- | | string string String prometheusQuery(mytable, '"abc"') | | | | | | | | | | | | | | |

## Example[​](#example "Direct link to Example")

```
SELECT * FROM prometheusQueryRange(mytable, 'rate(http_requests{job="prometheus"}[10m])[1h:10m]', now() - INTERVAL 10 MINUTES, now(), INTERVAL 1 MINUTE)
```

- [Syntax](#syntax)- [Arguments](#arguments)- [Returned value](#returned_value)- [Example](#example)

Was this page helpful?
redis Table Function

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

This table function allows integrating ClickHouse with [Redis](https://redis.io/).

## Syntax[​](#syntax "Direct link to Syntax")

```
redis(host:port, key, structure[, db_index[, password[, pool_size]]])
```

## Arguments[​](#arguments "Direct link to Arguments")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Argument Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `host:port` Redis server address, you can ignore port and default Redis port 6379 will be used.|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `key` any column name in the column list.|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `structure` The schema for the ClickHouse table returned from this function.|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `db_index` Redis db index range from 0 to 15, default is 0.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `password` User password, default is blank string.|  |  |  |  | | --- | --- | --- | --- | | `pool_size` Redis max connection pool size, default is 16.|  |  | | --- | --- | | `primary` must be specified, it supports only one column in the primary key. The primary key will be serialized in binary as a Redis key. | | | | | | | | | | | | | | | |

- columns other than the primary key will be serialized in binary as Redis value in corresponding order.
- queries with key equals or in filtering will be optimized to multi keys lookup from Redis. If queries without filtering key full table scan will happen which is a heavy operation.

[Named collections](/docs/operations/named-collections) are not supported for `redis` table function at the moment.

## Returned value[​](#returned_value "Direct link to Returned value")

A table object with key as Redis key, other columns packaged together as Redis value.

## Usage Example[​](#usage-example "Direct link to Usage Example")

Read from Redis:

```
SELECT * FROM redis(
    'redis1:6379',
    'key',
    'key String, v1 String, v2 UInt32'
)
```

Insert into Redis:

```
INSERT INTO TABLE FUNCTION redis(
    'redis1:6379',
    'key',
    'key String, v1 String, v2 UInt32') values ('1', '1', 1);
```

## Related[​](#related "Direct link to Related")

- [The `Redis` table engine](/docs/engines/table-engines/integrations/redis)
- [Using redis as a dictionary source](/docs/sql-reference/statements/create/dictionary/sources/redis)

- [Syntax](#syntax)- [Arguments](#arguments)- [Returned value](#returned_value)- [Usage Example](#usage-example)- [Related](#related)

Was this page helpful?
title_wip

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

Allows to perform queries on data stored in a [SQLite](/docs/engines/database-engines/sqlite) database.

## Syntax[​](#syntax "Direct link to Syntax")

```
sqlite('db_path', 'table_name')
```

## Arguments[​](#arguments "Direct link to Arguments")

- `db_path` — Path to a file with an SQLite database. [String](/docs/sql-reference/data-types/string).
- `table_name` — Name of a table in the SQLite database. [String](/docs/sql-reference/data-types/string).

## Returned value[​](#returned_value "Direct link to Returned value")

- A table object with the same columns as in the original `SQLite` table.

## Example[​](#example "Direct link to Example")

Query:

```
SELECT * FROM sqlite('sqlite.db', 'table1') ORDER BY col2;
```

Result:

```
┌─col1──┬─col2─┐
│ line1 │    1 │
│ line2 │    2 │
│ line3 │    3 │
└───────┴──────┘
```

## Related[​](#related "Direct link to Related")

- [SQLite](/docs/engines/table-engines/integrations/sqlite) table engine
- [SQLite database engine](/docs/engines/database-engines/sqlite) — Data types support section

- [Syntax](#syntax)- [Arguments](#arguments)- [Returned value](#returned_value)- [Example](#example)- [Related](#related)

Was this page helpful?
title_wip

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

`timeSeriesData(db_name.time_series_table)` - Returns the [data](/docs/engines/table-engines/special/time_series#data-table) table
used by table `db_name.time_series_table` whose table engine is [TimeSeries](/docs/engines/table-engines/special/time_series):

```
CREATE TABLE db_name.time_series_table ENGINE=TimeSeries DATA data_table
```

The function also works if the *data* table is inner:

```
CREATE TABLE db_name.time_series_table ENGINE=TimeSeries DATA INNER UUID '01234567-89ab-cdef-0123-456789abcdef'
```

The following queries are equivalent:

```
SELECT * FROM timeSeriesData(db_name.time_series_table);
SELECT * FROM timeSeriesData('db_name.time_series_table');
SELECT * FROM timeSeriesData('db_name', 'time_series_table');
```

Was this page helpful?
title_wip

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

`timeSeriesMetrics(db_name.time_series_table)` - Returns the [metrics](/docs/engines/table-engines/special/time_series#metrics-table) table
used by table `db_name.time_series_table` whose table engine is the [TimeSeries](/docs/engines/table-engines/special/time_series) engine:

```
CREATE TABLE db_name.time_series_table ENGINE=TimeSeries METRICS metrics_table
```

The function also works if the *metrics* table is inner:

```
CREATE TABLE db_name.time_series_table ENGINE=TimeSeries METRICS INNER UUID '01234567-89ab-cdef-0123-456789abcdef'
```

The following queries are equivalent:

```
SELECT * FROM timeSeriesMetrics(db_name.time_series_table);
SELECT * FROM timeSeriesMetrics('db_name.time_series_table');
SELECT * FROM timeSeriesMetrics('db_name', 'time_series_table');
```

Was this page helpful?
title_wip

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

Reads time series from a TimeSeries table filtered by a selector and with timestamps in a specified interval.
This function is similar to [range selectors](https://prometheus.io/docs/prometheus/latest/querying/basics/#range-vector-selectors) but it's used to implement [instant selectors](https://prometheus.io/docs/prometheus/latest/querying/basics/#instant-vector-selectors) too.

## Syntax[​](#syntax "Direct link to Syntax")

```
timeSeriesSelector('db_name', 'time_series_table', 'instant_query', min_time, max_time)
timeSeriesSelector(db_name.time_series_table, 'instant_query', min_time, max_time)
timeSeriesSelector('time_series_table', 'instant_query', min_time, max_time)
```

## Arguments[​](#arguments "Direct link to Arguments")

- `db_name` - The name of the database where a TimeSeries table is located.
- `time_series_table` - The name of a TimeSeries table.
- `instant_query` - An instant selector written in [PromQL syntax](https://prometheus.io/docs/prometheus/latest/querying/basics/#instant-vector-selectors), without `@` or `offset` modifiers.
- `min\_time - Start timestamp, inclusive.
- `max\_time - End timestamp, inclusive.

## Returned value[​](#returned_value "Direct link to Returned value")

The function returns three columns:

- `id` - Contains the identifiers of time series matching the specified selector.
- `timestamp` - Contains timestamps.
- `value` - Contains values.

There is no specific order for returned data.

## Example[​](#example "Direct link to Example")

```
SELECT * FROM timeSeriesSelector(mytable, 'http_requests{job="prometheus"}', now() - INTERVAL 10 MINUTES, now())
```

- [Syntax](#syntax)- [Arguments](#arguments)- [Returned value](#returned_value)- [Example](#example)

Was this page helpful?
title_wip

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

`timeSeriesTags(db_name.time_series_table)` - Returns the [tags](/docs/engines/table-engines/special/time_series#tags-table) table
used by table `db_name.time_series_table` whose table engine is the [TimeSeries](/docs/engines/table-engines/special/time_series) engine:

```
CREATE TABLE db_name.time_series_table ENGINE=TimeSeries TAGS tags_table
```

The function also works if the *tags* table is inner:

```
CREATE TABLE db_name.time_series_table ENGINE=TimeSeries TAGS INNER UUID '01234567-89ab-cdef-0123-456789abcdef'
```

The following queries are equivalent:

```
SELECT * FROM timeSeriesTags(db_name.time_series_table);
SELECT * FROM timeSeriesTags('db_name.time_series_table');
SELECT * FROM timeSeriesTags('db_name', 'time_series_table');
```

Was this page helpful?
title_wip

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

Turns a subquery into a table. The function implements views (see [CREATE VIEW](/docs/sql-reference/statements/create/view)). The resulting table does not store data, but only stores the specified `SELECT` query. When reading from the table, ClickHouse executes the query and deletes all unnecessary columns from the result.

## Syntax[​](#syntax "Direct link to Syntax")

```
view(subquery)
```

## Arguments[​](#arguments "Direct link to Arguments")

- `subquery` — `SELECT` query.

## Returned value[​](#returned_value "Direct link to Returned value")

- A table.

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

Input table:

```
┌─id─┬─name─────┬─days─┐
│  1 │ January  │   31 │
│  2 │ February │   29 │
│  3 │ March    │   31 │
│  4 │ April    │   30 │
└────┴──────────┴──────┘
```

Query:

```
SELECT * FROM view(SELECT name FROM months);
```

Result:

```
┌─name─────┐
│ January  │
│ February │
│ March    │
│ April    │
└──────────┘
```

You can use the `view` function as a parameter of the [remote](/docs/sql-reference/table-functions/remote) and [cluster](/docs/sql-reference/table-functions/cluster) table functions:

```
SELECT * FROM remote(`127.0.0.1`, view(SELECT a, b, c FROM table_name));
```

```
SELECT * FROM cluster(`cluster_name`, view(SELECT a, b, c FROM table_name));
```

## Related[​](#related "Direct link to Related")

- [View Table Engine](/docs/engines/table-engines/special/view)

- [Syntax](#syntax)- [Arguments](#arguments)- [Returned value](#returned_value)- [Examples](#examples)- [Related](#related)

Was this page helpful?
title_wip

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

Experimental feature. [Learn more.](/docs/beta-and-experimental-features#experimental-features)

The table function allows to read data from the YTsaurus cluster.

## Syntax[​](#syntax "Direct link to Syntax")

```
ytsaurus(http_proxy_url, cypress_path, oauth_token, format)
```

References

This is an experimental feature that may change in backwards-incompatible ways in the future releases.
Enable usage of the YTsaurus table function
with [allow\_experimental\_ytsaurus\_table\_function](/docs/operations/settings/settings#allow_experimental_ytsaurus_table_engine) setting.
Input the command `set allow_experimental_ytsaurus_table_function = 1`.

## Arguments[​](#arguments "Direct link to Arguments")

- `http_proxy_url` — URL to the YTsaurus http proxy.
- `cypress_path` — Cypress path to the data source.
- `oauth_token` — OAuth token.
- `format` — The [format](/docs/interfaces/formats) of the data source.

**Returned value**

A table with the specified structure for reading data in the specified ytsaurus cypress path in YTsaurus cluster.

**See Also**

- [ytsaurus engine](/docs/engines/table-engines/integrations/ytsaurus)

- [Syntax](#syntax)- [Arguments](#arguments)

Was this page helpful?
title_wip

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

- `zeros(N)` – Returns a table with the single 'zero' column (UInt8) that contains the integer 0 `N` times
- `zeros_mt(N)` – The same as `zeros`, but uses multiple threads.

This function is used for test purposes as the fastest method to generate many rows. Similar to the `system.zeros` and `system.zeros_mt` system tables.

The following queries are equivalent:

```
SELECT * FROM zeros(10);
SELECT * FROM system.zeros LIMIT 10;
SELECT * FROM zeros_mt(10);
SELECT * FROM system.zeros_mt LIMIT 10;
```

```
┌─zero─┐
│    0 │
│    0 │
│    0 │
│    0 │
│    0 │
│    0 │
│    0 │
│    0 │
│    0 │
│    0 │
└──────┘
```

Was this page helpful?
Window functions

<https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/01591_window_functions.sql>

### Postgres Docs[​](#postgres-docs "Direct link to Postgres Docs")

<https://www.postgresql.org/docs/current/sql-select.html#SQL-WINDOW>

<https://www.postgresql.org/docs/devel/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS>

<https://www.postgresql.org/docs/devel/functions-window.html>

<https://www.postgresql.org/docs/devel/tutorial-window.html>

### MySQL Docs[​](#mysql-docs "Direct link to MySQL Docs")

<https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html>

<https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html>

<https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html>

## Related Content[​](#related-content "Direct link to Related Content")

- Blog: [Working with time series data in ClickHouse](https://clickhouse.com/blog/working-with-time-series-data-and-functions-ClickHouse)
- Blog: [Window and array functions for Git commit sequences](https://clickhouse.com/blog/clickhouse-window-array-functions-git-commits)
- Blog: [Getting Data Into ClickHouse - Part 3 - Using S3](https://clickhouse.com/blog/getting-data-into-clickhouse-part-3-s3)

- [Standard window functions](#standard-window-functions)- [ClickHouse-specific window functions](#clickhouse-specific-window-functions)
    - [nonNegativeDerivative(metric\_column, timestamp\_column[, INTERVAL X UNITS])](#nonnegativederivativemetric_column-timestamp_column-interval-x-units)- [Syntax](#syntax)
      - [Functions](#functions)- [Examples](#examples)
        - [Numbering rows](#numbering-rows)- [Aggregation functions](#aggregation-functions)- [Partitioning by column](#partitioning-by-column)- [Frame bounding](#frame-bounding)- [Real world examples](#real-world-examples)
          - [Maximum/total salary per department](#maximumtotal-salary-per-department)- [Cumulative sum](#cumulative-sum)- [Moving / sliding average (per 3 rows)](#moving--sliding-average-per-3-rows)- [Moving / sliding average (per 10 seconds)](#moving--sliding-average-per-10-seconds)- [Moving / sliding average (per 10 days)](#moving--sliding-average-per-10-days)- [References](#references)
            - [GitHub Issues](#github-issues)- [Tests](#tests)- [Postgres Docs](#postgres-docs)- [MySQL Docs](#mysql-docs)- [Related Content](#related-content)

Was this page helpful?
title_wip

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

Computes the cumulative distribution of a value within a group of values, i.e., the percentage of rows with values less than or equal to the current row's value. Can be used to determine relative standing of a value within a partition.

**Syntax**

```
cume_dist ()
  OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column]
        [RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING]] | [window_name])
FROM table_name
WINDOW window_name as ([PARTITION BY grouping_column] [ORDER BY sorting_column] RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
```

The default and required window frame definition is `RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`.

For more detail on window function syntax see: [Window Functions - Syntax](/docs/sql-reference/window-functions#syntax).

**Returned value**

- The relative rank of the current row. The return type is Float64 in the range [0, 1]. [Float64](/docs/sql-reference/data-types/float).

**Example**

The following example calculates the cumulative distribution of salaries within a team:

Query:

```
CREATE TABLE salaries
(
    `team` String,
    `player` String,
    `salary` UInt32,
    `position` String
)
Engine = Memory;

INSERT INTO salaries FORMAT Values
    ('Port Elizabeth Barbarians', 'Gary Chen', 195000, 'F'),
    ('New Coreystad Archdukes', 'Charles Juarez', 190000, 'F'),
    ('Port Elizabeth Barbarians', 'Michael Stanley', 150000, 'D'),
    ('New Coreystad Archdukes', 'Scott Harrison', 150000, 'D'),
    ('Port Elizabeth Barbarians', 'Robert George', 195000, 'M'),
    ('South Hampton Seagulls', 'Douglas Benson', 150000, 'M'),
    ('South Hampton Seagulls', 'James Henderson', 140000, 'M');
```

```
SELECT player, salary,
       cume_dist() OVER (ORDER BY salary DESC) AS cume_dist
FROM salaries;
```

Result:

```
   ┌─player──────────┬─salary─┬───────────cume_dist─┐
1. │ Robert George   │ 195000 │  0.2857142857142857 │
2. │ Gary Chen       │ 195000 │  0.2857142857142857 │
3. │ Charles Juarez  │ 190000 │ 0.42857142857142855 │
4. │ Douglas Benson  │ 150000 │  0.8571428571428571 │
5. │ Michael Stanley │ 150000 │  0.8571428571428571 │
6. │ Scott Harrison  │ 150000 │  0.8571428571428571 │
7. │ James Henderson │ 140000 │                   1 │
   └─────────────────┴────────┴─────────────────────┘
```

**Implementation Details**

The `cume_dist()` function calculates the relative position using the following formula:

```
cume_dist = (number of rows ≤ current row value) / (total number of rows in partition)
```

Rows with equal values (peers) receive the same cumulative distribution value, which corresponds to the highest position of the peer group.

Was this page helpful?