group-by - clickhouse 下采样到 OHLC 时间条间隔

Question

对于表，例如包含一个日期，价格时间序列，每一个价格，例如毫秒，如何将其下采样为具有时间间隔的打开高低关闭(ohlc)行组，例如分钟？

Answer 1

虽然带数组的选项可以工作，但这里最简单的选项是使用 group by timeintervals 和 min 的组合。 , max , argMin , argMax聚合函数。

SELECT 
  id,
  minute,
  max(value) AS high,
  min(value) AS low,
  avg(value) AS avg,
  argMin(value, timestamp) AS first,
  argMax(value, timestamp) AS last
FROM security
GROUP BY id, toStartOfMinute(timestamp) AS minute
ORDER BY minute