gpt4 book ai didi

mysql - 仍未解决 : how to calculate price change (using prices from DIFFERENT dates) in MySQL?

转载 作者:行者123 更新时间:2023-11-29 02:02:44 27 4
gpt4 key购买 nike

考虑下表:

sp100_id  _date        open  close  bullishness
-----------------------------------------------
1 2011-03-14 100 110 2.23
1 2011-03-15 115 100 1.00
1 2011-03-16 110 110 0.85
2 2011-03-14 90 85 0.99
2 2011-03-15 95 90 0.30
2 2011-03-16 92 100 4.66
3 2011-03-14 200 220 1.50
3 2011-03-15 250 210 1.75
3 2011-03-16 200 150 0.80

我想计算 2011-03-142011-03- 之间每只股票的 % 返回牛市 16,按 % return DESC 和 return sp100_id% returnaverage bullishness 对它们进行排名>。我认为以下查询可以解决问题:

SELECT 
sp100_id,
AVG(bullishness) as bullishness,
((`close`-`open`) / `open`) as return_pct
FROM
stocks
WHERE _date = BETWEEN '2011-03-14' AND '2011-03-16'
ORDER BY return_pct DESC

但是,它似乎总是只返回一行。然而,预期的输出是:

sp100_id return_pct                average bullishness
-----------------------------------------------------------
3 (150-200)/200 = -0.250 (1.50+1.75+0.80)/3 = 4.05
2 (100-90)/90 = 0.110 (0.99+0.30+4.66)/3 = 1.98
1 (110-100)/100 = 0.100 (2.23+1.00+0.85)/3 = 4.08

我做错了什么?

最佳答案

您必须在查询中使用GROUP BY:

SELECT 
sp100_id,
AVG(bullishness) as bullishness,
(((SELECT `close` FROM stocks s2
WHERE s2.sp100_id=s1.sp100_id ORDER BY _date DESC LIMIT 1)
-(SELECT `open` FROM stocks s3
WHERE s3.sp100_id=s1.sp100_id ORDER BY _date ASC LIMIT 1)) /
(SELECT `open` FROM stocks s3
WHERE s3.sp100_id=s1.sp100_id ORDER BY _date ASC LIMIT 1)) as return_pct
FROM
stocks s1
WHERE _date BETWEEN '2011-03-14' AND '2011-03-16'
GROUP BY sp100_id
ORDER BY return_pct DESC

没有 GROUP BY,它只返回一个结果行,因为 AVG 将所有记录组合在一起。

我使用带有 ORDER BYLIMIT 1 的子查询修复了 return_pct 的计算。

文档:GROUP BY (Aggregate) Functions

SQLfiddle

关于mysql - 仍未解决 : how to calculate price change (using prices from DIFFERENT dates) in MySQL?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/12063996/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com