我的目标是检测加密货币的价格趋势。为此,我获得了多种货币与多个报价符号的 OHLC 定价数据(例如比特币/美元、比特币/欧元、莱特币/美元等的蜡烛图)。为了完成趋势计算,我使用超级趋势指标。我有以下 2 个表:
-- This is where price candles AKA OHLC data gets stored. Interval can be '1h', '4h', '1d', etc.
CREATE TABLE ohlc (
id integer DEFAULT nextval('ohlc_id_seq'::regclass) PRIMARY KEY,
open numeric(65,30) NOT NULL,
high numeric(65,30) NOT NULL,
low numeric(65,30) NOT NULL,
close numeric(65,30) NOT NULL,
coinid character varying(255) NOT NULL REFERENCES coin(id) ON DELETE RESTRICT ON UPDATE CASCADE,
closetime timestamp(3) without time zone NOT NULL,
quotesymbol text NOT NULL,
interval text NOT NULL
);
-- This is a caching table that gets automatically populated by a stored procedure that runs AFTER every ohlc insert. "trend" can be 'UP' or 'DOWN'.
CREATE TABLE supertrend (
id integer DEFAULT nextval('supertrend_id_seq'::regclass) PRIMARY KEY,
coinid character varying(255) NOT NULL REFERENCES coin(id) ON DELETE RESTRICT ON UPDATE CASCADE,
quotesymbol text NOT NULL,
date timestamp(3) without time zone NOT NULL,
trend text NOT NULL,
interval text NOT NULL
);
超级趋势表的示例数据:
科尼德 | 引用符号 | 日期 | 趋势 | 间隔 |
---|---|---|---|---|
“比特币” | '美元' | '2024-04-18 00:00:00' | '向上' | ‘1d’ |
“比特币” | '美元' | '2024-04-17 00:00:00' | '向上' | ‘1d’ |
“比特币” | '美元' | '2024-04-16 00:00:00' | '向上' | ‘1d’ |
“比特币” | '美元' | '2024-04-15 00:00:00' | '向下' | ‘1d’ |
“比特币” | '欧元' | '2024-04-18 00:00:00' | '向上' | ‘1d’ |
“比特币” | '欧元' | '2024-04-17 00:00:00' | '向上' | ‘1d’ |
“比特币” | '欧元' | '2024-04-16 00:00:00' | '向下' | ‘1d’ |
“比特币” | '欧元' | '2024-04-15 00:00:00' | '向下' | ‘1d’ |
“比特币” | '中国新年' | '2024-04-18 00:00:00' | '向下' | ‘1d’ |
“比特币” | '中国新年' | '2024-04-17 00:00:00' | '向上' | ‘1d’ |
“比特币” | '中国新年' | '2024-04-16 00:00:00' | '向上' | ‘1d’ |
“比特币” | '中国新年' | '2024-04-15 00:00:00' | '向上' | ‘1d’ |
“莱特币” | '美元' | '2024-04-18 00:00:00' | '向下' | ‘1d’ |
“莱特币” | '美元' | '2024-04-17 00:00:00' | '向上' | ‘1d’ |
“莱特币” | '美元' | '2024-04-16 00:00:00' | '向下' | ‘1d’ |
“莱特币” | '美元' | '2024-04-15 00:00:00' | '向下' | ‘1d’ |
“比特币” | '美元' | '2024-04-18 00:00:00' | '向上' | ‘4小时’ |
“比特币” | '美元' | '2024-04-17 20:00:00' | '向上' | ‘4小时’ |
“比特币” | '美元' | '2024-04-17 16:00:00' | '向下' | ‘4小时’ |
假设我想查询ORDER BY date DESC
间隔 = '1d'(1 天)的每个 coinid/quotesymbol 的最新 ( ) 趋势。或者换句话说WHERE interval = '1d' GROUP BY coinid, quotesymbol
,我想知道趋势趋势,那么在多少个时间段内该趋势已经相同。
鉴于上面的示例超级趋势表数据,我的结果应如下所示:
科尼德 | 引用符号 | 最新趋势 | 趋势_条纹 |
---|---|---|---|
“比特币” | '美元' | '向上' | 3 |
“比特币” | '欧元' | '向上' | 2 |
“比特币” | '中国新年' | '向下' | 1 |
“莱特币” | '美元' | '向下' | 1 |
为了澄清上述结果:“比特币”、“美元”连续 3 个周期为“上涨”,因为比特币/美元的趋势在 4 月 18 日、4 月 17 日和 4 月 16 日上涨。而在 4 月 15 日为“下跌”,请参阅示例超级趋势表。
此外,我想知道所有报价符号的聚合超级趋势(通过模式函数)。结果应该是这样的:
科尼德 | 最新趋势 | 趋势_条纹 |
---|---|---|
“比特币” | '向上' | 2 |
“莱特币” | '向下' | 1 |
为了澄清上述结果:最新的比特币/美元趋势为“上涨”,比特币/欧元为“欧元”为“上涨”,比特币/人民币为“下跌”。mode 函数采用 ('UP', 'UP', 'DOWN') 并返回最频繁出现的值 ('UP')。
考虑到超级趋势表有数百万或数十亿行,我如何才能有效地查询这些数据?
我正在考虑物化视图,但这些超级趋势的最低可能间隔是 1 分钟,并且可能会在不同的时间写入,因此所需的刷新率可能太高。
我试图避免使用像 Redis 这样的单独的缓存解决方案来保持所需的架构/环境的数量简单,但我想这可能是一个选项,特别是对于计数器。