clickhouse build OHLC on the fly

Question

I found in stackoverflow how to generate OHLC by minutes

clickhouse downsample into OHLC time bar intervals

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

Can you help me and tell how to exclude the last minute in result query.

This is necessary so that later i can create a materialized view and generate OHLC on the fly, Otherwise, i will need to insert data for the entire minute interval in batches

example :

create table ticks_data
(
    symbol       String,
    datetime_msc DateTime64(3),
    price        Float64,
    volume       UInt64
)
engine = MergeTree PARTITION BY toYYYYMM(datetime_msc)
ORDER BY (symbol, datetime_msc);

INSERT INTO ticks_data (symbol, datetime_msc, price, volume)
VALUES ('EURUSD', '2022-06-03 18:01:51.265',1.07084,0);
INSERT INTO ticks_data (symbol, datetime_msc, price, volume)
VALUES ('EURUSD', '2022-06-03 18:01:51.027',1.071429,0);
INSERT INTO ticks_data (symbol, datetime_msc, price, volume)
VALUES ('EURUSD', '2022-06-03 18:01:51.948',1.07089,0);

CREATE TABLE charts
(

    symbol       String,
    datetime     DATETIME,
    high         Float64,
    low          Float64,
    vol          UInt64,
    open         Float64,
    close        Float64
)
ENGINE = AggregatingMergeTree
order by (symbol,datetime);

insert into table charts
SELECT
  symbol,
  datetime,
  max(price) AS high,
  min(price) AS low,
  sum(volume) AS vol,
  arrayElement(arraySort((x,y)->y,groupArray(price), groupArray(datetime_msc)), 1) AS open,
  arrayElement(arraySort((x,y)->y, groupArray(price), groupArray(datetime_msc)), -1) AS close
FROM ticks_data
GROUP BY symbol, toStartOfMinute(datetime_msc) AS datetime
ORDER BY datetime;


CREATE MATERIALIZED VIEW charts_MV to charts
AS
SELECT
  symbol,
  datetime,
  max(price) AS high,
  min(price) AS low,
  sum(volume) AS vol,
  arrayElement(arraySort((x,y)->y,groupArray(price), groupArray(datetime_msc)), 1) AS open,
  arrayElement(arraySort((x,y)->y, groupArray(price), groupArray(datetime_msc)), -1) AS close
FROM ticks_data
GROUP BY symbol, toStartOfMinute(datetime_msc) AS datetime
ORDER BY datetime;

INSERT INTO ticks_data (symbol, datetime_msc, price, volume)
VALUES ('EURUSD', '2022-06-03 18:01:54.265',1.07098,0)

as result we have two not aggregated string for one minute

1. such MatView are impossible (you cannot cut the last minute). https://den-crane.github.io/Everything_you_should_know_about_materialized_views_commented.pdf — Denny Crane, Mar 06 '23 at 12:34
2. You don't need to cut the last minute. MatView are able to calculate it without cutting the last minute. — Denny Crane, Mar 06 '23 at 12:35
i will write data by second(may be more often). how matview will calculate OHLC on last minute? — Pavel, Mar 06 '23 at 13:01
see https://clickhouse.com/blog/using-materialized-views-in-clickhouse Clickhouse merges are able to calculate OHLC on last minute — Denny Crane, Mar 06 '23 at 13:04
from this doc: " if GROUP BY is set, data is aggregated during insertion, but only within a single packet of inserted data" — Pavel, Mar 06 '23 at 13:06
I think you should try to play with and realize the magic of MERGES.https://kb.altinity.com/altinity-kb-schema-design/materialized-views/ — Denny Crane, Mar 06 '23 at 13:26
I would use AggregatingMergeTree. But SummingMergeTree will also work. — Denny Crane, Mar 06 '23 at 13:29
@DennyCrane aggregation does not work when I add data one by one, AggregatingMergeTree not worked on MV in this case, i see additional not aggregated string in MV. I can't use SummingMergeTree I have min max avg in select, may be i can use Live view for this case? — Pavel, Mar 07 '23 at 09:28
All answers are in https://clickhouse.com/blog/using-materialized-views-in-clickhouse read about aggregateFunction and simpleAggregateFunction — Denny Crane, Mar 07 '23 at 13:13
@DennyCrane in top message, I added an example in which result data are not aggregated — Pavel, Mar 08 '23 at 07:09

Denny Crane · Accepted Answer · 2023-03-08T14:07:01.363

Again, read the articles from the links above.

Check this video

You don't understand AggregateFunction (SimpleAggregateFunction) conception.

Example of an Aggregated Materialized View

Also check argMin/argMax functions.

CREATE TABLE charts
(
    symbol       String,
    datetime     DateTime,
    high         SimpleAggregateFunction(max, Float64),
    low          SimpleAggregateFunction(min, Float64),
    vol          SimpleAggregateFunction(sum, UInt64),
    open         AggregateFunction(argMin, Float64, DateTime64(3)),
    close        AggregateFunction(argMax, Float64, DateTime64(3))
)
ENGINE = AggregatingMergeTree
order by (symbol,datetime);

CREATE MATERIALIZED VIEW charts_MV to charts
AS
SELECT
  symbol,
  toStartOfMinute(datetime_msc) AS datetime,
  max(price) AS high,
  min(price) AS low,
  sum(volume) AS vol,
  argMinState(price, datetime_msc) open,
  argMaxState(price, datetime_msc) close
FROM ticks_data
GROUP BY symbol, datetime;

SELECT
    symbol,
    datetime,
    max(high) AS high,
    min(low) AS low,
    sum(vol) AS vol,
    argMinMerge(open) AS open,
    argMaxMerge(close) AS close
FROM charts
GROUP BY
    symbol,
    datetime

┌─symbol─┬────────────datetime─┬─────high─┬─────low─┬─vol─┬─────open─┬───close─┐
│ EURUSD │ 2022-06-03 18:01:00 │ 1.071429 │ 1.07084 │   0 │ 1.071429 │ 1.07098 │
└────────┴─────────────────────┴──────────┴─────────┴─────┴──────────┴─────────┘

thank you very much, I will definitely study all the suggested links and continue to study the clickhouse tell me if there is a very large data flow to the ticks_data table. How fast will this aggregation work? and waste resources? — Pavel, Mar 08 '23 at 15:51
Materialized views are insert trigger. Insert returns control to the client when all related Materialized views are got data successfully. — Denny Crane, Mar 08 '23 at 16:05
In your case insert will be twice slower because of MatView. — Denny Crane, Mar 08 '23 at 16:06

clickhouse build OHLC on the fly

1 Answers1