如何在ClickHouse中按时间段分组并用null / 0s填充丢失的数据

小编典典

如何在ClickHouse中按时间段分组并用null / 0s填充丢失的数据

sql

假设我有一个给定的时间范围。为了进行说明，让我们考虑一些简单的事情，例如2018年全年。我想从ClickHouse查询数据作为每个季度的总和，因此结果应为4行。

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

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

有没有办法填充整个给定间隔？就像InfluxDBfill对group或TimescaleDb的time_bucket()函数有论据，generate_series()
我试图搜索ClickHouse文档和github问题，似乎还没有实现，所以问题也许是是否有任何解决方法。

阅读 1869

2021-03-23

共1个答案

小编典典

您可以使用“数字”功能生成零值。然后使用UNION ALL将查询和零值连接起来，并且已经根据获取的数据进行了GROUP BY。

因此，您的查询将如下所示：

SELECT SUM(metric),
       time
  FROM (
        SELECT toStartOfQuarter(toDate(1514761200+number*30*24*3600))  time,
               toUInt16(0) AS metric
          FROM numbers(30)

     UNION ALL

          SELECT toStartOfQuarter(created_at) AS time,
               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

注意toUInt16（0）-零值必须与以下类型相同 metrics

2021-03-23