MySQL查询将值与上一行的值进行比较 [英] MySQL query comparing values to previous rows' values
问题描述
我一直在搜索，但一直无法找到解决方案-我知道它是可行的，但我只是还没有所需的忍者SQL技能....
I've been searching but have been unable to find a solution to this--I know it's do-able but I just don't have the ninja SQL skills I need (yet)....
我正在寻找解决此问题的方法:我有2个与股市数据相关的表.第一个是带有ID和股票行情自动收录器代码(ID，SYMBOL)的简单股票代码列表.第二张表包含每只股票的历史价格数据. (ID，DATE，OPEN，HIGH，LOW，CLOSE，VOLUME).
I'm looking for a solution to this issue: I have a 2 tables related to stock market data. The first is a simple list of stock symbols with an ID and stock ticker symbol (ID,SYMBOL). The second table contains historical price data for each of the stocks. (ID, DATE, OPEN, HIGH, LOW, CLOSE, VOLUME).
我正在尝试找出如何查询最近收盘价高于其5个交易日前的收盘价的股票.我不能只是做日期数学运算，因为这些股票不是每天都交易(在周末和节假日没有交易，有些股票在正常交易日可能没有交易).因此，我只需要比较每个符号的最近行和第五行的收盘价.
I'm trying to figure out how to query for stocks that have the most recent CLOSE price that is greater than their CLOSE price 5 trading-days ago. I can't just do date math because the stocks don't trade every day (no trading on weekends & holidays, as well as some stocks may not trade on a normal trading day). Thus, I just need to compare the CLOSE price from most recent row and the 5th row proceeding it for each symbol.
我这里有示例表和数据: http://sqlfiddle.com/#!2/5fe76/2
I have sample tables and data here: http://sqlfiddle.com/#!2/5fe76/2
CREATE TABLE `STOCKS` (
`ID` int,
`SYMBOL` varchar(10)
);
INSERT INTO `STOCKS` (`ID`,`SYMBOL`)
VALUES
(1, 'AA'),
(2, 'ADT'),
(3, 'AEO'),
(4, 'AFA');
CREATE TABLE `PRICES` (
`ID` int,
`DATE` date,
`OPEN` decimal(6,2),
`HIGH` decimal(6,2),
`LOW` decimal(6,2),
`CLOSE` decimal(6,2),
`VOLUME` bigint
);
INSERT INTO `PRICES` (`ID`,`DATE`,`OPEN`,`HIGH`,`LOW`,`CLOSE`,`VOLUME`) VALUES
(1, '2014-11-06', 16.37, 16.42, 16.15, 16.37, 14200400),
(1, '2014-11-05', 16.68, 16.69, 16.17, 16.26, 18198200),
(1, '2014-11-04', 16.85, 16.87, 16.43, 16.56, 13182800),
(1, '2014-11-03', 16.78, 17.03, 16.65, 16.93, 15938500),
(1, '2014-10-31', 16.43, 16.76, 16.24, 16.76, 18618300),
(1, '2014-10-30', 16.17, 16.36, 15.83, 16.22, 17854400),
(1, '2014-10-29', 16.58, 16.70, 16.05, 16.27, 31173000),
(1, '2014-10-28', 16.5, 16.65, 16.41, 16.60, 12305900),
(1, '2014-10-27', 16.56, 16.57, 16.31, 16.38, 15452900),
(1, '2014-10-24', 16.33, 16.57, 16.22, 16.55, 12840200),
(2, '2014-11-06', 35.9, 36.12, 35.75, 36.07, 1018100),
(2, '2014-11-05', 35.68, 35.99, 35.37, 35.96, 1101500),
(2, '2014-11-04', 35.13, 35.69, 35.02, 35.49, 819100),
(2, '2014-11-03', 35.81, 35.99, 35.27, 35.32, 1304500),
(2, '2014-10-31', 35.79, 35.86, 35.46, 35.84, 1319400),
(2, '2014-10-30', 34.7, 35.34, 34.66, 35.19, 1201800),
(2, '2014-10-29', 35.06, 35.56, 34.5, 34.92, 1359000),
(2, '2014-10-28', 34.32, 35.17, 34.15, 35.07, 1301800),
(2, '2014-10-27', 34.2, 34.2, 33.66, 34.1, 662600),
(2, '2014-10-24', 34.02, 34.54, 33.95, 34.5, 750600),
(3, '2014-11-06', 13.27, 13.92, 13.25, 13.82, 6518000),
(3, '2014-11-05', 12.95, 13.27, 12.74, 13.22, 8716700),
(3, '2014-11-04', 12.85, 12.94, 12.65, 12.89, 4541200),
(3, '2014-11-03', 12.91, 13.12, 12.73, 12.89, 4299100),
(3, '2014-10-31', 13.2, 13.23, 12.83, 12.87, 7274700),
(3, '2014-10-30', 12.83, 12.91, 12.68, 12.86, 4444300),
(3, '2014-10-29', 13.02, 13.20, 12.79, 12.91, 2974900),
(3, '2014-10-28', 12.87, 13.10, 12.52, 13.04, 7365600),
(3, '2014-10-27', 12.84, 13.00, 12.67, 12.92, 6647900),
(3, '2014-10-24', 13.26, 13.29, 12.60, 12.92, 12803300),
(4, '2014-11-06', 24.59, 24.59, 24.49, 24.55, 20400),
(4, '2014-11-05', 24.81, 24.9, 24.81, 24.88, 11800),
(4, '2014-11-04', 24.87, 24.88, 24.76, 24.88, 10600),
(4, '2014-11-03', 24.85, 24.88, 24.76, 24.81, 18100),
(4, '2014-10-31', 24.82, 24.85, 24.77, 24.78, 8100),
(4, '2014-10-30', 24.83, 24.87, 24.74, 24.79, 13900),
(4, '2014-10-29', 24.86, 24.86, 24.78, 24.81, 5500),
(4, '2014-10-28', 24.85, 24.85, 24.80, 24.84, 10600),
(4, '2014-10-27', 24.68, 24.85, 24.68, 24.85, 7700),
(4, '2014-10-24', 24.67, 24.82, 24.59, 24.82, 9300);
查询的伪代码将如下所示: 找到符号谁的最近收盘价大于5个交易日之前的收盘价"
Pseudo code for the query would be something like this: "Find symbols whos most recent closing prices is greater than the closing price 5 trading-days earlier"
我要创建的查询应导致以下结果:
The query I'd like to create should result in the following:
Date Symbol Close Close(-5)
2014-11-06 AA 16.37 16.22
2014-11-06 ADT 36.07 35.19
2014-11-06 AEO 13.82 12.86
(符号"AFA"将不匹配，因为它最近的收盘价是24.55，而之前的收盘价是24.75之前有5行)
(the symbol 'AFA' would not match as it's recent close is 24.55 and 5 rows prior it was 24.75)
推荐答案
您可以使用相关子查询在5天前获得价格.实际上，您可以以相同的方式获得最新价格.因此，这可能是正确的路径:
You can get the price 5 days ago using a correlated subquery. In fact, you can get the most recent price the same way. So, this might be the right path:
select s.*,
(select p.close
from prices p
where p.id = s.id
order by date desc
limit 1
) as Close,
(select p.close
from prices p
where p.id = s.id and p.date <= date(now()) - interval 5 day
order by date desc
limit 1
) as Close_5
from stocks s
having Close > Close_5;
这篇关于MySQL查询将值与上一行的值进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
