> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-home-button.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Базовые операции - временные ряды

> Базовые операции с временными рядами в ClickHouse.

ClickHouse предоставляет несколько способов работы с временными рядами, позволяя агрегировать, группировать и анализировать точки данных за разные периоды времени.
В этом разделе рассматриваются основные операции, которые обычно используются при работе с данными, привязанными ко времени.

К распространённым операциям относятся группировка данных по временным интервалам, обработка пропусков во временных рядах и вычисление изменений между периодами времени.
Эти операции можно выполнять, используя стандартный синтаксис SQL в сочетании со встроенными функциями ClickHouse для работы со временем.

Мы рассмотрим возможности ClickHouse по выполнению запросов к временным рядам на примере набора данных Wikistat (данные о просмотрах страниц Википедии):

```sql theme={null}
CREATE TABLE wikistat
(
    `time` DateTime,
    `project` String,
    `subproject` String,
    `path` String,
    `hits` UInt64
)
ENGINE = MergeTree
ORDER BY (time);
```

Давайте заполним эту таблицу 1 миллиардом записей:

```sql theme={null}
INSERT INTO wikistat 
SELECT *
FROM s3('https://ClickHouse-public-datasets.s3.amazonaws.com/wikistat/partitioned/wikistat*.native.zst') 
LIMIT 1e9;
```

<div id="time-series-aggregating-time-bucket">
  ## Агрегация по временным бакетам
</div>

Самая распространённая задача — агрегировать данные по периодам, например получить общее число хитов за каждый день:

```sql theme={null}
SELECT
    toDate(time) AS date,
    sum(hits) AS hits
FROM wikistat
GROUP BY ALL
ORDER BY date ASC
LIMIT 5;
```

```text theme={null}
┌───────date─┬─────hits─┐
│ 2015-05-01 │ 25524369 │
│ 2015-05-02 │ 25608105 │
│ 2015-05-03 │ 28567101 │
│ 2015-05-04 │ 29229944 │
│ 2015-05-05 │ 29383573 │
└────────────┴──────────┘
```

Здесь мы использовали функцию [`toDate()`](/ru/reference/functions/regular-functions/type-conversion-functions#toDate), которая преобразует указанное время в тип date. Также можно выполнять группировку по часу и применять фильтр по конкретной дате:

```sql theme={null}
SELECT
    toStartOfHour(time) AS hour,
    sum(hits) AS hits    
FROM wikistat
WHERE date(time) = '2015-07-01'
GROUP BY ALL
ORDER BY hour ASC
LIMIT 5;
```

```text theme={null}
┌────────────────hour─┬───hits─┐
│ 2015-07-01 00:00:00 │ 656676 │
│ 2015-07-01 01:00:00 │ 768837 │
│ 2015-07-01 02:00:00 │ 862311 │
│ 2015-07-01 03:00:00 │ 829261 │
│ 2015-07-01 04:00:00 │ 749365 │
└─────────────────────┴────────┘
```

Используемая здесь функция [`toStartOfHour()`](/ru/reference/functions/regular-functions/date-time-functions#toStartOfHour) преобразует указанное время к началу часа.
Вы также можете группировать по году, кварталу, месяцу или дню.

<div id="time-series-custom-grouping-intervals">
  ## Пользовательские интервалы группировки
</div>

Мы можем выполнять группировку даже по произвольным интервалам, например по 5-минутным, с помощью функции [`toStartOfInterval()`](/ru/reference/functions/regular-functions/date-time-functions#toStartOfInterval).

Предположим, что мы хотим группировать по 4-часовым интервалам.
Мы можем указать интервал группировки с помощью конструкции [`INTERVAL`](/ru/reference/data-types/special-data-types/interval):

```sql theme={null}
SELECT
    toStartOfInterval(time, INTERVAL 4 HOUR) AS interval,
    sum(hits) AS hits
FROM wikistat
WHERE date(time) = '2015-07-01'
GROUP BY ALL
ORDER BY interval ASC
LIMIT 6;
```

Или можно использовать функцию [`toIntervalHour()`](/ru/reference/functions/regular-functions/type-conversion-functions#toIntervalHour)

```sql theme={null}
SELECT
    toStartOfInterval(time, toIntervalHour(4)) AS interval,
    sum(hits) AS hits
FROM wikistat
WHERE date(time) = '2015-07-01'
GROUP BY ALL
ORDER BY interval ASC
LIMIT 6;
```

В любом случае получаем следующие результаты:

```text theme={null}
┌────────────interval─┬────hits─┐
│ 2015-07-01 00:00:00 │ 3117085 │
│ 2015-07-01 04:00:00 │ 2928396 │
│ 2015-07-01 08:00:00 │ 2679775 │
│ 2015-07-01 12:00:00 │ 2461324 │
│ 2015-07-01 16:00:00 │ 2823199 │
│ 2015-07-01 20:00:00 │ 2984758 │
└─────────────────────┴─────────┘
```

<div id="time-series-filling-empty-groups">
  ## Заполнение пустых групп
</div>

Во многих случаях мы работаем с разреженными данными, в которых отсутствуют некоторые интервалы. Это приводит к появлению пустых бакетов. Рассмотрим следующий пример, в котором данные группируются по часовым интервалам. В результате получится следующая статистика, в которой для некоторых часов отсутствуют значения:

```sql theme={null}
SELECT
    toStartOfHour(time) AS hour,
    sum(hits)
FROM wikistat
WHERE (project = 'ast') AND (subproject = 'm') AND (date(time) = '2015-07-01')
GROUP BY ALL
ORDER BY hour ASC;
```

```text theme={null}
┌────────────────hour─┬─sum(hits)─┐
│ 2015-07-01 00:00:00 │         3 │ <- пропущенные значения
│ 2015-07-01 02:00:00 │         1 │ <- пропущенные значения
│ 2015-07-01 04:00:00 │         1 │
│ 2015-07-01 05:00:00 │         2 │
│ 2015-07-01 06:00:00 │         1 │
│ 2015-07-01 07:00:00 │         1 │
│ 2015-07-01 08:00:00 │         3 │
│ 2015-07-01 09:00:00 │         2 │ <- пропущенные значения
│ 2015-07-01 12:00:00 │         2 │
│ 2015-07-01 13:00:00 │         4 │
│ 2015-07-01 14:00:00 │         2 │
│ 2015-07-01 15:00:00 │         2 │
│ 2015-07-01 16:00:00 │         2 │
│ 2015-07-01 17:00:00 │         1 │
│ 2015-07-01 18:00:00 │         5 │
│ 2015-07-01 19:00:00 │         5 │
│ 2015-07-01 20:00:00 │         4 │
│ 2015-07-01 21:00:00 │         4 │
│ 2015-07-01 22:00:00 │         2 │
│ 2015-07-01 23:00:00 │         2 │
└─────────────────────┴───────────┘
```

ClickHouse предоставляет [предложение `WITH FILL`](/ru/guides/use-cases/real-time-analytics/time-series/time-series-filling-gaps#with-fill), чтобы решить эту задачу. Оно заполнит все пропущенные часы нулями, чтобы мы могли лучше понять распределение данных во времени:

```sql theme={null}
SELECT
    toStartOfHour(time) AS hour,
    sum(hits)
FROM wikistat
WHERE (project = 'ast') AND (subproject = 'm') AND (date(time) = '2015-07-01')
GROUP BY ALL
ORDER BY hour ASC WITH FILL STEP toIntervalHour(1);
```

```text theme={null}
┌────────────────hour─┬─sum(hits)─┐
│ 2015-07-01 00:00:00 │         3 │
│ 2015-07-01 01:00:00 │         0 │ <- новое значение
│ 2015-07-01 02:00:00 │         1 │
│ 2015-07-01 03:00:00 │         0 │ <- новое значение
│ 2015-07-01 04:00:00 │         1 │
│ 2015-07-01 05:00:00 │         2 │
│ 2015-07-01 06:00:00 │         1 │
│ 2015-07-01 07:00:00 │         1 │
│ 2015-07-01 08:00:00 │         3 │
│ 2015-07-01 09:00:00 │         2 │
│ 2015-07-01 10:00:00 │         0 │ <- новое значение
│ 2015-07-01 11:00:00 │         0 │ <- новое значение
│ 2015-07-01 12:00:00 │         2 │
│ 2015-07-01 13:00:00 │         4 │
│ 2015-07-01 14:00:00 │         2 │
│ 2015-07-01 15:00:00 │         2 │
│ 2015-07-01 16:00:00 │         2 │
│ 2015-07-01 17:00:00 │         1 │
│ 2015-07-01 18:00:00 │         5 │
│ 2015-07-01 19:00:00 │         5 │
│ 2015-07-01 20:00:00 │         4 │
│ 2015-07-01 21:00:00 │         4 │
│ 2015-07-01 22:00:00 │         2 │
│ 2015-07-01 23:00:00 │         2 │
└─────────────────────┴───────────┘
```

<div id="time-series-rolling-time-windows">
  ## Скользящие временные окна
</div>

Иногда нас интересует не начало интервалов (например, начало дня или часа), а сами интервалы окна.
Допустим, мы хотим узнать общее количество hits для окна не по дням, а за 24-часовой период со смещением относительно 18:00.

Мы можем использовать функцию [`date_diff()`](/ru/reference/functions/regular-functions/date-time-functions#timeDiff), чтобы вычислить разницу между опорным временем и временем каждой записи.
В этом случае столбец `day` будет обозначать разницу в днях (например, 1 день назад, 2 дня назад и т. д.):

```sql theme={null}
SELECT    
    dateDiff('day', toDateTime('2015-05-01 18:00:00'), time) AS day,
    sum(hits),
FROM wikistat
GROUP BY ALL
ORDER BY day ASC
LIMIT 5;
```

```text theme={null}
┌─day─┬─sum(hits)─┐
│   0 │  25524369 │
│   1 │  25608105 │
│   2 │  28567101 │
│   3 │  29229944 │
│   4 │  29383573 │
└─────┴───────────┘
```