Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE binance_trades (ts timestamp without time zone, val int, price numeric,quantity int, direction int,instrument text);

INSERT INTO binance_trades
SELECT ts,round(random()*10),random()*42,round(random()*8),round(random()*5), upper(substr(md5(random()::text), 0, 7))
FROM generate_series('2021-08-19 23:58:42','2021-08-20 01:01:00',interval'1 sec') j(ts);

CREATE INDEX idx_binance_trades_ts_instrument ON binance_trades USING btree (ts,instrument);
3739 rows affected

SELECT
  date_trunc('second',ts)-
    MOD(EXTRACT(SECOND FROM date_trunc('second',ts))::int,5)*interval'1 sec' AS ts_5sec,
  MAX(price) high,
  MIN(price) low,
  (SUM(price * price * quantity) / SUM(price * quantity)) midpoint,
  SUM(price * quantity) volume,
  (SUM(direction * price * quantity) / SUM(price * quantity)) direction
FROM binance_trades
WHERE ts BETWEEN '2021-08-19 22:50:00' AND '2021-08-20 01:00:00'
GROUP BY 1 ORDER BY 1
LIMIT 5;


ts_5sec
high
low
midpoint
volume
direction


2021-08-19 23:58:40
19.4175605679183
1.58210037925253
15.7328062791091565032762687134
164.80942022154340
3.0000000000000000

2021-08-19 23:58:45
34.8469286344095
21.6269136566934
28.38915380302443283987246604
473.0890419150706
2.6973861019970847

2021-08-19 23:58:50
38.9258233661435
13.4160865104718
27.76460173030516337772247894
408.7994977185185
2.4405742375522531

2021-08-19 23:58:55
20.4039131231827
2.9195696379207
16.8138017955122213089386543853
303.38503158394801
3.4600606493373676

2021-08-19 23:59:00
41.0874445570243
15.9274618969322
32.28447178790957729681185695
594.8520833045087
2.6166112009746786


WITH j AS (
  SELECT i AS win_start,tsrange(i,i+interval'5sec') AS ts_win
  FROM generate_series(
    (SELECT min(date_trunc('second',ts)) FROM binance_trades),
    (SELECT max(date_trunc('second',ts)) FROM binance_trades),interval'5 sec') j (i))
SELECT
    j.win_start ts,
    MAX(price) high,
    MIN(price) low,
    (SUM(price * price * quantity) / SUM(price * quantity)) midpoint,
    SUM(price * quantity) volume,
    (SUM(direction * price * quantity) / SUM(price * quantity)) direction
FROM j
JOIN binance_trades b ON ts_win @> b.ts
WHERE j.win_start BETWEEN '2021-08-19 22:50:00' AND '2021-08-20 01:00:00'
GROUP BY j.win_start ORDER BY j.win_start
LIMIT 5;


ts
high
low
midpoint
volume
direction


2021-08-19 23:58:42
28.2948101905285
1.58210037925253
22.7630092540785620666265207741
456.61319365254530
2.7947056646623468

2021-08-19 23:58:47
34.8469286344095
21.6269136566934
29.71596384407779720884434839
321.1857557012442
2.6688771731463236

2021-08-19 23:58:52
38.9258233661435
13.4160865104718
23.98325354139889921250662752
479.6993696692175
2.9540224470016886

2021-08-19 23:58:57
39.1664772231327
2.9195696379207
28.7542161226954227325547985643
320.27198232560141
2.7694360226731461

2021-08-19 23:59:02
41.0874445570243
3.1537349220081
27.91380957851129923237243370
429.5276606555524
2.5512899163885811


CREATE UNLOGGED TABLE scalper_5s_intervals AS
SELECT i AS win_start,tsrange(i,i+interval'5sec') AS ts_win
FROM generate_series(
  (SELECT min(date_trunc('second',ts)) FROM binance_trades),
  (SELECT max(date_trunc('second',ts)) FROM binance_trades),interval'5 sec') j (i);

CREATE INDEX idx_ts_5sec ON scalper_5s_intervals USING gist (ts_win);
CREATE INDEX idx_ts_5sec_winstart ON scalper_5s_intervals USING btree(win_start);
  
SELECT
    j.win_start ts,
    MAX(price) high,
    MIN(price) low,
    (SUM(price * price * quantity) / SUM(price * quantity)) midpoint,
    SUM(price * quantity) volume,
    (SUM(direction * price * quantity) / SUM(price * quantity)) direction
FROM scalper_5s_intervals j
JOIN binance_trades b ON ts_win @> b.ts
WHERE j.win_start BETWEEN '2021-08-19 22:50:00' AND '2021-08-20 01:00:00'
GROUP BY j.win_start ORDER BY j.win_start
LIMIT 5; 
748 rows affected



ts
high
low
midpoint
volume
direction


2021-08-19 23:58:42
28.2948101905285
1.58210037925253
22.7630092540785620666265207741
456.61319365254530
2.7947056646623468

2021-08-19 23:58:47
34.8469286344095
21.6269136566934
29.71596384407779720884434839
321.1857557012442
2.6688771731463236

2021-08-19 23:58:52
38.9258233661435
13.4160865104718
23.98325354139889921250662752
479.6993696692175
2.9540224470016886

2021-08-19 23:58:57
39.1664772231327
2.9195696379207
28.7542161226954227325547985643
320.27198232560141
2.7694360226731461

2021-08-19 23:59:02
41.0874445570243
3.1537349220081
27.91380957851129923237243370
429.5276606555524
2.5512899163885811

ts_5sec	high	low	midpoint	volume	direction
2021-08-19 23:58:40	19.4175605679183	1.58210037925253	15.7328062791091565032762687134	164.80942022154340	3.0000000000000000
2021-08-19 23:58:45	34.8469286344095	21.6269136566934	28.38915380302443283987246604	473.0890419150706	2.6973861019970847
2021-08-19 23:58:50	38.9258233661435	13.4160865104718	27.76460173030516337772247894	408.7994977185185	2.4405742375522531
2021-08-19 23:58:55	20.4039131231827	2.9195696379207	16.8138017955122213089386543853	303.38503158394801	3.4600606493373676
2021-08-19 23:59:00	41.0874445570243	15.9274618969322	32.28447178790957729681185695	594.8520833045087	2.6166112009746786

ts	high	low	midpoint	volume	direction
2021-08-19 23:58:42	28.2948101905285	1.58210037925253	22.7630092540785620666265207741	456.61319365254530	2.7947056646623468
2021-08-19 23:58:47	34.8469286344095	21.6269136566934	29.71596384407779720884434839	321.1857557012442	2.6688771731463236
2021-08-19 23:58:52	38.9258233661435	13.4160865104718	23.98325354139889921250662752	479.6993696692175	2.9540224470016886
2021-08-19 23:58:57	39.1664772231327	2.9195696379207	28.7542161226954227325547985643	320.27198232560141	2.7694360226731461
2021-08-19 23:59:02	41.0874445570243	3.1537349220081	27.91380957851129923237243370	429.5276606555524	2.5512899163885811