当前位置: 首页 > 面试题库 >

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

景唯
2023-03-14
问题内容

假设我有一个给定的时间范围。为了进行说明,让我们考虑一些简单的事情,例如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问题,似乎还没有实现,所以问题也许是是否有任何解决方法。


问题答案:

您可以使用“数字”功能生成零值。然后使用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



 类似资料:
  • 我尝试了这个,但代码没有编译。

  • 问题内容: 我知道该怎么做,但是我发誓您可以像[[0] = {0,0,0,0};那样填写;你是怎么做到的?我确实尝试过Google,但没有任何帮助。 问题答案: 您也可以将其作为声明的一部分:

  • 假设我有一个整数数组,如和一个简单的字符串,如。我如何循环通过数组和填充每一个值。假设是这样的: 输出: 到目前为止,我已经尝试了以下内容: 但是,它在print语句中给出了。解决这个问题最好的方法是什么?

  • 问题内容: 我有一个像这样的熊猫数据框“ df”: 它丢失了一些行,我想像这样填补中间的空白: 有python方式可以做到这一点吗? 问题答案: 您需要构造完整索引,然后使用数据框的方法。像这样 然后,您可以使用该方法将NaN设置为所需的值。 更新(2014年6月) 只是必须自己重新审视一下……在当前版本的熊猫中,有一个函数可以从可迭代的笛卡尔积中构建。因此上述解决方案可能变为: 我认为这很优雅。

  • 问题内容: 我正在使用2个步骤的应用程序。 扫描日志并将其中的数据持久保存在数据库中。 从数据库读取数据并可视化数据。 第一步或多或少已经完成。我尝试通过第二步来解释背景和要求。 数据库中的每一行都包含一些信息,例如logdate,logfilename,LogType,logMessage等。因此,我想例如编写SQL来总结每天的给定LogType。 这是列: 我想如果我想显示Type = TDB

  • 问题内容: 我从日志文件中获取了一些数据,想按分钟对条目进行分组: df: 我尝试了以下选项: 级别太高,想要汇总。 失败失败。 工作正常,并返回一个对象进行进一步处理,例如: grouped.Source.value_counts() 2012-01-01 12:30:00 source1 1 2012-01-01 12:31:00 source2 2 source1 2 2012-01-01