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

Answer 1

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 │
└────────┴─────────────────────┴──────────┴─────────┴─────┴──────────┴─────────┘