今天我试着玩了一下货币,给了PostgreSQL一个帮助我的机会。
我在PostgreSQL数据库中有一个表,它有三个字段:
CREATE TABLE IF NOT EXISTS binance (
date TIMESTAMP,
symbol VARCHAR(20),
price REAL
)
此表从 10 秒更新到 10 秒,包含 ~250 行。间隔之间的符号始终相同。例如数据:
+----------------------------+--------+-------+
| date | symbol | price |
+----------------------------+--------+-------+
| 2018-01-18 00:00:00.000000 | x | 12 |
| 2018-01-18 00:00:00.000120 | y | 15 |
| 2018-01-18 00:00:00.000200 | z | 19 |
| 2018-01-18 00:00:10.080000 | x | 14 |
| 2018-01-18 00:00:10.123000 | y | 16 |
| 2018-01-18 00:00:10.130000 | z | 20 |
+----------------------------+--------+-------+
现在,我想做的是获取每个交易品种在过去 5 分钟内增长了多少(百分比)。
让我们以一个符号为例(ETHBTC
)。此交易品种在过去 5 分钟内的数据如下所示:
+----------------------------+--------+----------+
| date | symbol | price |
+----------------------------+--------+----------+
| 2018-01-19 22:59:10.000000 | ETHBTC | 0.09082 |
| 2018-01-19 22:58:59.000000 | ETHBTC | 0.0907 |
| 2018-01-19 22:58:47.000000 | ETHBTC | 0.090693 |
| 2018-01-19 22:58:35.000000 | ETHBTC | 0.090697 |
| 2018-01-19 22:58:24.000000 | ETHBTC | 0.090712 |
| 2018-01-19 22:58:11.000000 | ETHBTC | 0.090682 |
| 2018-01-19 22:57:59.000000 | ETHBTC | 0.090774 |
| 2018-01-19 22:57:48.000000 | ETHBTC | 0.090672 |
| 2018-01-19 22:57:35.000000 | ETHBTC | 0.09075 |
| 2018-01-19 22:57:24.000000 | ETHBTC | 0.090727 |
| 2018-01-19 22:57:12.000000 | ETHBTC | 0.090705 |
| 2018-01-19 22:57:00.000000 | ETHBTC | 0.090707 |
| 2018-01-19 22:56:49.000000 | ETHBTC | 0.090646 |
| 2018-01-19 22:56:37.000000 | ETHBTC | 0.090645 |
| 2018-01-19 22:56:25.000000 | ETHBTC | 0.090636 |
| 2018-01-19 22:56:13.000000 | ETHBTC | 0.090696 |
| 2018-01-19 22:56:00.000000 | ETHBTC | 0.090698 |
| 2018-01-19 22:55:48.000000 | ETHBTC | 0.090693 |
| 2018-01-19 22:55:37.000000 | ETHBTC | 0.090698 |
| 2018-01-19 22:55:25.000000 | ETHBTC | 0.090601 |
| 2018-01-19 22:55:13.000000 | ETHBTC | 0.090644 |
| 2018-01-19 22:55:01.000000 | ETHBTC | 0.0906 |
| 2018-01-19 22:54:49.000000 | ETHBTC | 0.0906 |
| 2018-01-19 22:54:37.000000 | ETHBTC | 0.09062 |
| 2018-01-19 22:54:25.000000 | ETHBTC | 0.090693 |
+----------------------------+--------+----------+
为了选择此数据,我使用以下查询:
SELECT *
FROM binance
WHERE date >= NOW() AT TIME ZONE 'EET' - INTERVAL '5 minutes'
AND symbol = 'ETHBTC'
ORDER BY date DESC;
我想做的是找出每个符号:
- 最后一个值与 10 年前的值之间的百分比
- 最后一个值与 1 分钟前的值之间的百分比
- 最后一个值与 5 分钟前的值之间的百分比
现在,我有点纠结于这样的查询应该是什么样子。更重要的是,IDK 这是否重要,但查询是从 Python 中运行的,所以我可能无法利用完整的 PostgreSQL 功能。
演示
Rextester在线演示:http://rextester.com/QNVGU31219
.SQL
以下是将最新价格与 1 分钟前价格进行比较的 SQL:
WITH cte AS
(SELECT price,
ABS(EXTRACT(EPOCH FROM (
SELECT date - (SELECT MAX(date) - INTERVAL '1 minute' FROM binance))))
AS secs_from_prev_timestamp
FROM binance
WHERE symbol = 'ETHBTC')
SELECT price /
(SELECT price FROM binance
WHERE symbol = 'ETHBTC' AND date = (SELECT MAX(date) FROM binance))
* 100.0 AS percentage_difference
FROM cte
WHERE secs_from_prev_timestamp = (SELECT MIN(secs_from_prev_timestamp) FROM cte);
以上可以简单地更改以与不同间隔前的价格进行比较,例如更改为INTERVAL '5 minutes'
而不是INTERVAL '1 minute'
,或者通过将两个引用更改为'ETHBTC'
到不同的交易品种来给出不同交易品种的结果。
解释
棘手的一点是获得之前的价格。这是通过使用公用表表达式 (CTE) 完成的,该表达式列出了所有价格以及距离所需时间戳的秒数。使用绝对值函数(ABS
),因此无论它是大于还是小于目标时间戳,都将找到最近的一个。
结果
在上面的一个示例中,查询给出的结果为99.848...%
。这是从0.090682 / 0.09082 * 100.0
制定的,其中0.09082
是最新价格,0.090682
是一分钟前的价格。
以上是基于对"百分比差异"含义的假设,但可以计算其他百分比 - 例如0.09082
比0.090682
高0.152%
。(如果我对百分比差异的解释不是您所追求的,请在评论中回复,我会相应地更新答案。
更新 - "全部执行"查询
在阅读了您对 Dan 的答案的评论后,您希望使用单个查询获得所有这些结果,我在下面发布了一个应该可以完成所需的操作。Rextester 演示在这里: http://rextester.com/QDUN45907
WITH cte2 AS
(WITH cte1 AS
(SELECT symbol,
price,
ABS(EXTRACT(EPOCH FROM (
SELECT date - (SELECT MAX(date) - INTERVAL '10 seconds' FROM binance))))
AS secs_from_latest_minus_10,
ABS(EXTRACT(EPOCH FROM (
SELECT date - (SELECT MAX(date) - INTERVAL '1 minute' FROM binance))))
AS secs_from_latest_minus_60,
ABS(EXTRACT(EPOCH FROM (
SELECT date - (SELECT MAX(date) - INTERVAL '5 minutes' FROM binance))))
AS secs_from_latest_minus_300
FROM binance)
SELECT symbol,
(SELECT price AS latest_price
FROM binance b2
WHERE b2.symbol = b.symbol AND date = (SELECT MAX(date) FROM binance)),
(SELECT price AS price_latest_minus_10
FROM cte1
WHERE cte1.symbol = b.symbol AND secs_from_latest_minus_10 =
(SELECT MIN(secs_from_latest_minus_10) FROM cte1)),
(SELECT price AS price_latest_minus_60
FROM cte1
WHERE cte1.symbol = b.symbol AND secs_from_latest_minus_60 =
(SELECT MIN(secs_from_latest_minus_60) FROM cte1)),
(SELECT price AS price_latest_minus_500
FROM cte1
WHERE cte1.symbol = b.symbol AND secs_from_latest_minus_60 =
(SELECT MIN(secs_from_latest_minus_60) FROM cte1))
FROM binance b
GROUP BY symbol)
SELECT symbol,
price_latest_minus_10 / latest_price * 100.0 AS percentage_diff_10_secs_ago,
price_latest_minus_60 / latest_price * 100.0 AS percentage_diff_1_minute_ago,
price_latest_minus_500 / latest_price * 100.0 AS percentage_diff_5_minutes_ago
FROM cte2;
要连续三次获得相对百分比,您必须每次都加入每个案例,在本例中为 10 秒/1 分钟/5 分钟。
这是查询,请注意,JOIN
是ON
id。您需要主键或唯一值才能使此JOIN
正常工作:
-- Overall SELECT, '*' includes 5min
SELECT a.*,b."1min",c."10sec"
FROM
-- First we select the group with most rows, that are <=5min
(SELECT *,
-- Formula for the percentage
100*price/last_value(price)
OVER (PARTITION BY symbol
ORDER BY date DESC rows between unbounded preceding and
unbounded following) AS "5min"
FROM test
WHERE date >= NOW() AT TIME ZONE 'EET' - INTERVAL '5 minutes'
ORDER BY symbol,date DESC)a
LEFT JOIN
-- Join with 1 minute query
(SELECT *,
-- Formula for the percentage
100*price/last_value(price)
OVER (PARTITION BY symbol
ORDER BY date DESC rows between unbounded preceding and
unbounded following) AS "1min"
FROM test
WHERE date >= NOW() AT TIME ZONE 'EET' - INTERVAL '1 minutes'
ORDER BY symbol,date DESC)b
-- join with id (primary or unique)
ON a.id = b.id
-- Join with 30 seconds query
LEFT JOIN
(SELECT *,
-- Formula for the percentage
100*price/last_value(price)
OVER (PARTITION BY symbol
ORDER BY date DESC rows between unbounded preceding and
unbounded following) AS "10sec"
FROM test
WHERE date >= NOW() AT TIME ZONE 'EET' - INTERVAL '30 seconds'
ORDER BY symbol,date DESC)c
-- join with id (primary or unique)
ON a.id=c.id
在此查询中,您可以根据需要更改百分比和时间的公式。如果您希望百分比相对于另一个值(如主价格),则必须将其包含在每个查询中并添加到公式中,而不是last_value(price) OVER...
。请记住,实际公式获取相对于查询中最旧行的百分比。
百分比排名:
此查询给出查询中行从 0 到 1 的百分比,第一行为 0,最后一行为 1。
例如:
date |symbol |price | percentage
-----------+--------+------+-------------
2017-01-05 | 1 | 0.5 | 1
2017-01-04 | 1 | 1.5 | 0.5
2017-01-03 | 1 | 1 | 0
2017-01-05 | 2 | 1 | 1
2017-01-04 | 2 | 3 | 0.5
2017-01-03 | 2 | 2 | 0
这是查询:
SELECT *,
-- this makes a column with the percentage per row
percent_rank() OVER (PARTITION BY symbol ORDER BY date) AS percent
FROM binance
WHERE date >= NOW() AT TIME ZONE 'EET' - INTERVAL '5 minutes'
ORDER BY symbol,date DESC;
相对百分比:
此查询显示有关数据集价格的最旧值的百分比。
例如:
date | symbol |price | percentage
-----------+--------+------------
2017-01-05 | 1 | 0.5 | 50
2017-01-04 | 1 | 1.5 | 150
2017-01-03 | 1 | 1 | 100
2017-01-05 | 2 | 1 | 50
2017-01-04 | 2 | 3 | 150
2017-01-03 | 2 | 2 | 100
查询为:
SELECT *,
-- Formula to get the percentage taking the price from the oldest date:
100*price/last_value(price) OVER (PARTITION BY symbol ORDER BY date DESC rows between unbounded preceding and unbounded following) AS percentage
FROM binance
WHERE date >= NOW() AT TIME ZONE 'EET' - INTERVAL '5 minutes'
ORDER BY symbol,date DESC;