将Clickhouse下采样转换为OHLC时间栏间隔 [英] clickhouse downsample into OHLC time bar intervals

问题描述

对于表格，例如包含日期，价格时间序列以及每个价格，例如毫秒，如何将其降采样为具有时间间隔(例如，时间间隔)的开放高低封闭(ohlc)行的组分钟?

For a table e.g. containing a date, price timeseries with prices every e.g. millisecond, how can this be downsampled into groups of open high low close (ohlc) rows with time interval e.g. minute?

推荐答案

使用数组选项时，最简单的选择是将按时间间隔的组合使用与min，max，argMin， argMax聚合函数.

While option with arrays will work, the simplest option here is to use use combination of group by timeintervals with min, max, argMin, argMax aggregate functions.

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

这篇关于将Clickhouse下采样转换为OHLC时间栏间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！