代码之家 › 专栏 › 技术社区 › Nicholas Tong

查找最小+最大值和相关列

greatest-n-per-group postgresql sql

Nicholas Tong · 技术社区 · 8 年前

列表项
按日期分组
对于每个组,找到最低买入价格和与之相关的交易所
查找最大售价及其相关交易所

从我所阅读的内容来看,我可以使用行,或者有更好的解决方案吗?

(使用postgresql)

编辑:

|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 |

解决方案输出应为

2 回复 | 直到 8 年前

a_horse_with_no_name 8 年前

好的,多亏了其他一些问题,我终于拿到了。。

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

MiguelKVidal 8 年前

我使用CTE尝试了一种不同的方法:

WITH MinMaxPrice AS (
    SELECT 
        createdAt, MIN(buyPrice) AS MinBuyPrice, MAX(sellPrice) AS MaxSellPrice
    FROM quotes
    GROUP BY createdAt
)
SELECT
    MinMaxPrice.createdAt,
    MinMaxPrice.MinBuyPrice,
    qBuy.buyVolume,
    qBuy.exchange AS buyExchange,
    MinMaxPrice.MaxSellPrice,
    qSell.sellVolume,
    qSell.exchange AS sellExchange,
    MinMaxPrice.MinBuyPrice - MinMaxPrice.MaxSellPrice AS spread
FROM MinMaxPrice
INNER JOIN quotes qBuy  ON MinMaxPrice.createdAt = q.createdAt AND MinMaxPrice.MinBuyPrice  = q.buyPrice
INNER JOIN quotes qSell ON MinMaxPrice.createdAt = q.createdAt AND MinMaxPrice.MaxSellPrice = q.sellPrice
;

如果你遇到任何问题,请告诉我,我会解决它。