mysql - 使用条件在 MySQL 中检索和计算价格

Question

我想检索/计算不同 Assets 在给定日期的价格，具体取决于交易方。 2000 年之前，我有中间报价，之后我有出价和要价或报价，所以我希望价格是这两个报价的平均值。更准确地说:

SELECT date, price,
CASE WHEN side='' THEN 'price_mid'
WHEN side='A' THEN 'price_ask'
WHEN side='B' THEN 'price_bid'
WHEN side='O' THEN 'price_offer'
END as prices 
FROM table
WHERE asset = 'a';

然后我如何计算新列中的价格，其中 price_mid(2000 年之前)和 (price_bid+price_ask)/2 或 (price_bid+price_offer)/2 之后？

例如:假设我有以下情况:

date    price      prices
1         1       price_mid
2        1.1      price_mid
3        0.9      price_bid
3        1.2      price_ask
4        1.3      price_offer
4        1.1      price_bid

我想要:

date    final_price
 1           1
 2          1.1
 3          1.05
 4          1.2

Answer 1

我知道您只需要某些日期的平均值。也许以下内容可以满足您的需求:

SELECT date, AVG(price) as AvgValue
FROM prices
WHERE date >= 2
AND prices in ('price_ask','price_offer','price_bid')
GROUP BY date
UNION
SELECT date, price as AvgValue
FROM prices
WHERE date < 2
AND prices = 'price_mid'
GROUP BY date
UNION
SELECT date, price as AvgValue
FROM prices p
WHERE date >= 2
AND prices = 'price_mid'
AND NOT EXISTS (SELECT 1 FROM prices p2 where p2.date = p.date AND p2.prices in ('price_ask','price_offer','price_bid'))
GROUP BY date
ORDER BY DATE ASC