我的目标是检测加密货币的价格趋势。为此,我获得了多种货币与多个报价符号的 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 这样的单独的缓存解决方案来保持所需的架构/环境的数量简单,但我想这可能是一个选项,特别是对于计数器。
您可以使用
ROW_NUMBER
来确定最后一个值,然后使用 aGROUP BY
来获取计数。然后在下一步中您可以再次使用 row_number 来确定计数最高的趋势。窗口函数的作用
ROW_NUMBER()
正如其名称所示,它为每一行提供一个 row_number 。使用窗口函数,您可以将行虚拟分组在一起,并为每个组赋予一个 row_number ,这样您的结果集中就可以有多个 1 。在我们的例子中,我们想要每个组的最后一行,所以我们使用顺序DESC
一组中的窗口函数
LAG
针对前一个值,它经常像这里一样使用,将当前值与其前一个值进行比较并检测变化。有关窗口功能的更多信息可以在手册中找到
您可以将
WHERE
要显示的 coinid 添加到子句中。喜欢
WHERE rn = 1 AND "coinid" = 'bitcoin'
第二个查询稍微简单一些,因为首先您检测每个“coinid”、“quotesymbol”和“tend”的变化。
然后,窗口函数 sum 创建组,因为您只对最后一个感兴趣,所以我们只关心 rn =m 1。
您的预期结果让我感到困惑,但是在构建查询后我看到了您的错误
小提琴