> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.
# 基础操作 - 时间序列
> ClickHouse 中的基础时间序列操作。
ClickHouse 提供了多种处理时间序列数据的方法，让您能够跨不同时间段对数据点进行聚合、分组和分析。
本节将介绍处理时序数据时常用的一些基础操作。
常见操作包括按时间间隔对数据分组、处理时间序列数据中的空缺，以及计算不同时间段之间的变化。
这些操作都可以通过标准 SQL 语法结合 ClickHouse 内置的时间函数来完成。
接下来，我们将使用 Wikistat (Wikipedia 页面浏览数据) 数据集来了解 ClickHouse 的时间序列查询能力：
```sql theme={null}
CREATE TABLE wikistat
(
`time` DateTime,
`project` String,
`subproject` String,
`path` String,
`hits` UInt64
)
ENGINE = MergeTree
ORDER BY (time);
```
我们来向这个表插入 10 亿条记录：
```sql theme={null}
INSERT INTO wikistat
SELECT *
FROM s3('https://ClickHouse-public-datasets.s3.amazonaws.com/wikistat/partitioned/wikistat*.native.zst')
LIMIT 1e9;
```
## 按时间桶聚合
最常见的需求是按时间段对数据进行聚合，例如获取每天的总点击量：
```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()`](/docs/zh/reference/functions/regular-functions/type-conversion-functions#toDate) 函数，它会将指定时间转换为日期类型。或者，我们也可以按小时分组，并按特定日期进行过滤：
```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()`](/docs/zh/reference/functions/regular-functions/date-time-functions#toStartOfHour) 函数会将给定时间转换为该小时的开始时刻。
你也可以按年、季度、月或天分组。
## 自定义分组时间间隔
我们甚至可以按任意时间间隔进行分组，例如使用 [`toStartOfInterval()`](/docs/zh/reference/functions/regular-functions/date-time-functions#toStartOfInterval) 函数按 5 分钟间隔分组。
假设我们想按 4 小时间隔分组。
我们可以使用 [`INTERVAL`](/docs/zh/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()`](/docs/zh/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 │
└─────────────────────┴─────────┘
```
## 填充空组
很多情况下，我们处理的是存在部分时间间隔缺失的稀疏数据，这会导致出现空桶。以下示例按 1 小时时间间隔对数据分组，输出的统计结果中会有一些小时缺少值：
```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`](/docs/zh/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 │
└─────────────────────┴───────────┘
```
## 滚动时间窗口
有时，我们关注的不是时间间隔的起点 (例如一天或一小时的开始) ，而是滚动窗口的时间间隔。
假设我们想了解某个窗口内的总点击量，不是按自然日统计，而是基于从下午 6 点开始偏移的 24 小时时间段。
我们可以使用 [`date_diff()`](/docs/zh/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 │
└─────┴───────────┘
```