在 MySQL 中查找同一列中两个值之间的差异 [英] Finding the difference between two values in the same column in MySQL
问题描述
我有一个简单的表,其中包含 MySQL 中的股价:
I have a simple table called that contains share prices in MySQL:
Table `share_prices`
+----------+-------+---------------------+
| stock_id | price | date |
+----------+-------+---------------------+
| 1 | 0.05 | 2010-02-24 01:00:00 |
| 2 | 3.25 | 2010-02-24 01:00:00 |
| 3 | 3.30 | 2010-02-24 01:00:00 |
| 1 | 0.50 | 2010-02-23 23:00:00 |
| 2 | 1.90 | 2010-02-23 23:00:00 |
| 3 | 2.10 | 2010-02-23 23:00:00 |
| 1 | 1.00 | 2010-02-23 19:00:00 |
| 2 | 1.00 | 2010-02-23 19:00:00 |
| 3 | 1.00 | 2010-02-23 19:00:00 |
+----------+-------+---------------------+
每次更新股价时,都会在表格中插入一个新行.
Every time a share price is updated, a new row is inserted into the table.
使用这种结构,如何返回显示过去 24 小时内价格变化的查询?
想要的结果是:
+----------+------+------+------------+
| stock_id | then | now | difference |
+----------+------+------+------------+
| 3 | 1.00 | 3.30 | 2.30 |
| 2 | 1.00 | 3.25 | 2.25 |
| 1 | 1.00 | 0.05 | -0.95 |
+----------+------+------+------------+
解决这个问题的最佳方法是什么?某种加入?子查询?
What's the best way to go about this? Some kind of join? A sub-query?
我认为我的目标是基本上查询一次以获得then
,再次查询以获得now
,然后以某种方式将它们最后粘合在一起.
What I think I'm aiming for is to essentially query once to get then
, query again to get now
and then somehow glue it all together at the end.
我也需要考虑负面变化.
I need to account for negative changes too.
推荐答案
好的,回到家,并且能够解决这个问题.
Ok, got home, and was able to figure this out.
SELECT stock_id, t1.price AS `then`, t2.price AS `now`, ROUND(t2.price - t1.price, 2) AS `difference`
FROM (
SELECT stock_id, price, date FROM share_prices sp
WHERE date = (SELECT MIN(date) FROM share_prices sp2
WHERE date BETWEEN '2010/02/23 10:00:00'
AND '2010/02/24 10:00:00'
AND sp2.stock_id = sp.stock_id)
) t1
JOIN
(
SELECT stock_id, price, date FROM share_prices sp
WHERE date = (SELECT MAX(date) FROM share_prices sp2
WHERE date BETWEEN '2010/02/23 10:00:00'
AND '2010/02/24 10:00:00'
AND sp2.stock_id = sp.stock_id)
) t2 USING(stock_id)
ORDER BY `difference` DESC
使用来自 2 个子查询的结果,每个子查询都有自己的子查询,分别针对该范围的第一个和最后一个记录.
Uses the results from 2 subqueries, each with their own subquery to the first and last, respectively, record for that range.
我使用 integer
作为 stock_id
,float
作为 price
和 timestamp
对于日期,因为其他数据类型可能存在问题(尤其是 MIN 和 MAX).
I was using integer
for stock_id
, float
for price
and timestamp
for date, since there may be issues (notably with the MIN and MAX) with other data types.
这篇关于在 MySQL 中查找同一列中两个值之间的差异的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!