sql - 如何在 ClickHouse 中按时间段分组并用 null/0 填充缺失数据

Question

假设我有一个给定的时间范围。为了解释起见，让我们考虑一些简单的事情，比如 2018 年全年。我想从 ClickHouse 查询数据作为每个季度的总和聚合，因此结果应该是 4 行。

问题是我只有两个季度的数据，因此当使用GROUP BYquarter时，只返回两行。

SELECT
     toStartOfQuarter(created_at) AS time,
     sum(metric) metric
 FROM mytable
 WHERE
     created_at >= toDate(1514761200) AND created_at >= toDateTime(1514761200)
    AND
     created_at <= toDate(1546210800) AND created_at <= toDateTime(1546210800)
 GROUP BY time
 ORDER BY time

1514761200 – 2018-01-01
1546210800 – 2018-12-31

这将返回:

time       metric
2018-01-01 345
2018-04-01 123

我需要:

time       metric
2018-01-01 345
2018-04-01 123
2018-07-01 0
2018-10-01 0

这是简化的示例，但在实际用例中，聚合将是例如。 5 分钟而不是一刻钟，并且 GROUP BY 至少还有一个属性，例如 GROUP BY attribute1, time，因此所需的结果是

time        metric  attribute1
2018-01-01  345     1
2018-01-01  345     2
2018-04-01  123     1
2018-04-01  123     2
2018-07-01  0       1
2018-07-01  0       2
2018-10-01  0       1
2018-10-01  0       2

有没有办法以某种方式填充整个给定的间隔？就像 InfluxDB 有用于组或 TimescaleDb 的 fill 参数 time_bucket()函数 generate_series()我尝试搜索 ClickHouse 文档和 github 问题，似乎这还没有实现，所以问题可能是是否有任何解决方法。

Answer 1

从 ClickHouse 19.14 开始，您可以使用 WITH FILL条款。它可以这样填满宿舍:

WITH
    (
        SELECT toRelativeQuarterNum(toDate('1970-01-01'))
    ) AS init
SELECT
    -- build the date from the relative quarter number
    toDate('1970-01-01') + toIntervalQuarter(q - init) AS time,
    metric
FROM
(
    SELECT
        toRelativeQuarterNum(created_at) AS q,
        sum(rand()) AS metric
    FROM
    (
        -- generate some dates and metrics values with gaps
        SELECT toDate(arrayJoin(range(1514761200, 1546210800, ((60 * 60) * 24) * 180))) AS created_at
    )
    GROUP BY q
    ORDER BY q ASC WITH FILL FROM toRelativeQuarterNum(toDate(1514761200)) TO toRelativeQuarterNum(toDate(1546210800)) STEP 1
)

┌───────time─┬─────metric─┐
│ 2018-01-01 │ 2950782089 │
│ 2018-04-01 │ 2972073797 │
│ 2018-07-01 │          0 │
│ 2018-10-01 │  179581958 │
└────────────┴────────────┘