因此,数据库表头是:
Date | buyPrice | sellPrice | buyVolume | sellVolume | exchange
我试图:
从我正在阅读的内容中,我可以使用行还是有更好的解决方案?
(使用postgresql)
编辑:如果我们假设与以下数据进行3次交换:
|Date | buyPrice | sellPrice | buyVolume | sellVolume | exchange | |1-1-2017 | 1 | 1 | 1 | 1 | exchangeA | |1-1-2017 | 2 | 1 | 2 | 1 | exchangeB | |1-1-2017 | 3 | 1 | 3 | 1 | exchangeC |
解决方案输出应为
| Date | buyPrice | buyVolume | buyExchange | sellPrice | sellVolume | sellExchange | | 1-1-2017 | 1 | 1 | exchangeA | 3 | 1 | exchangeC
好的,由于其他一些问题,我设法做到了。
SELECT t1.createdAt, t1.sellPrice, t1.exchangeId AS sellExchange, t2.buyPrice, t2.exchangeId AS buyExchange, t1.sellPrice - t2.buyPrice AS spread,
CASE
WHEN t1.sellVolume < t2.buyVolume THEN t1.sellVolume
ELSE t2.buyVolume
END AS minVolume
FROM
(SELECT a.createdAt, a.sellPrice, a.sellVolume, a.exchangeid, a.quoteId
FROM quotes a
INNER JOIN (
SELECT createdAt, max(sellPrice) AS sellPrice
FROM quotes
GROUP BY createdAt
) b ON a.createdAt = b.createdAt AND a.sellPrice = b.sellPrice)
t1 INNER JOIN
(SELECT a.createdAt, a.buyPrice, a.buyvolume, a.exchangeid, a.quoteId
FROM quotes a
INNER JOIN (
SELECT createdAt, min(buyPrice) AS buyPrice
FROM quotes
GROUP BY createdAt
) b ON a.createdAt = b.createdAt AND a.buyPrice = b.buyPrice)
t2 ON t1.createdAt = t2.createdAt
将返回 createdAt | sellPrice | sellExchange | buyPrice | buyExchange | volume| spread
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句