我有以下表示金融交易的模式：
instrument VARCHAR NOT NULL,
ts TIMESTAMP WITHOUT TIME ZONE NOT NULL,
quantity FLOAT8 NOT NULL,
price FLOAT8 NOT NULL,
direction INTEGER NOT NULL
我有以下查询将其聚合为每个1分钟的蜡烛：
SELECT
date_trunc('minute', ts) ts,
instrument,
(array_agg(price order by ts))[1] open,
MAX(price) high,
MIN(price) low,
(array_agg(price order by ts))[array_upper((array_agg(price order by ts)), 1)] close,
(SUM(price * price * quantity) / SUM(price * quantity)) midpoint,
SUM(price * quantity) volume,
SUM(CASE WHEN direction = 1 THEN price * quantity else 0 END) volume_taker_buy,
count(*) trade_count,
FROM {exchangeName}.{tableName}
WHERE instrument = '{instrument.Ticker}' AND ts BETWEEN '{fromTime}' AND '{toTime}'
GROUP BY date_trunc('minute', ts), instrument
ORDER BY ts
我认为计算的细节不仅简单，而且与问题本身无关，因为问题更多的是从正确的时间范围获取数据。如果有什么不清楚的地方，请告诉我。
如何修改查询以允许任意聚合时间框架？例如，将数据按5秒、30秒、1米、5分钟、15分钟等分组？
目标是通过请求从2022-1-1 00:00:00到2022-1-1 01:00:00的数据(例如，按5秒的切片(来使用查询。
编辑：这里是一些样本数据
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:06:19.119000', 0.001, 42969, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:06:19.018000', 0.008, 42969, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:06:18.816000', 0.092, 42969, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:06:18.442000', 0.002, 42969, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:06:21.651000', 0.1, 42968.99, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:06:21.380000', 0.002, 42969, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:06:21.039000', 0.114, 42969, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:06:20.684000', 0.002, 42969, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:06:20.538000', 0.111, 42969, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:06:20.525000', 0.012, 42968.99, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:06:20.405000', 0.058, 42968.99, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:06:20.266000', 0.001, 42968.99, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:06:19.885000', 0.111, 42969, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:06:19.581000', 0.001, 42969, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:06:22.660000', 0.001, 42969, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:06:22.486000', 0.002, 42968.99, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:06:23.626000', 0.001, 42968.99, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:06:23.503000', 0.001, 42969, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:06:23.374000', 0.006, 42969, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:06:23.271000', 0.123, 42968.99, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:29.099000', 0.001, 42968.92, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:29.022000', 0.001, 42968.93, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:28.783000', 0.088, 42968.93, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:28.564000', 0.023, 42968.92, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:28.521000', 0.009, 42968.93, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:28.462000', 0.065, 42968.92, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:28.251000', 0.005, 42968.92, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:28.059000', 0.605, 42968.93, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:27.864000', 1.346, 42970, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:27.692000', 0.002, 42970.01, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:27.361000', 0.001, 42970.01, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:27.189000', 0.005, 42970.01, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:27.074000', 0.043, 42970.01, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:26.966000', 0.862, 42970.01, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:30.328000', 0.002, 42965.85, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:30.328000', 0.056, 42966.01, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:30.328000', 0.199, 42966.02, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:30.328000', 0.076, 42966.69, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:30.328000', 0.081, 42966.8, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:30.328000', 0.08, 42966.86, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:30.328000', 0.001, 42968, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:30.328000', 0.626, 42968.92, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:30.222000', 0.002, 42968.93, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:30.044000', 0.019, 42968.93, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:29.835000', 0.06, 42968.93, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:29.496000', 0.001, 42968.93, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:29.202000', 0.045, 42968.92, -1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:29.142000', 0.216, 42968.93, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:31.243000', 0.083, 42968.56, 1);
INSERT INTO trades (instrument, ts, quantity, price, direction) VALUES ('BTCUSDT', '2022-01-15 12:07:31.038000', 0.002, 42968.56, 1);
您可以实现一个返回时间的function。您可以extract、hour、minute等等。然后您可以将year、month、day、hour和minute转换为可分组的值，即文本或数字(首选数字(。这会按分钟分组自动解决问题。然而，如果你需要按n分钟分组，假设60(一小时中的分钟数(可以被分组长度整除，你可以应用以下公式来找出记录在哪个组中：
extract(year from timestamp <your value here>) * 10000 + extract(hour from timestamp <your value here>) * 100 + (extract(minute from timestamp <your value here>)) - MOD(extract(minute from timestamp <your value here>), 15))