MySQL查询：将值与前面行的值进行比较

Question

我一直在搜索但无法找到解决方法，我知道这是可行的，但我还没有所需的SQL技能（目前）......

我的问题是：我有两个与股票市场数据相关的表。第一个是一个简单的股票符号列表，其中包含ID和股票代号（ID，SYMBOL）。第二个表包含每只股票的历史价格数据。（ID，DATE，OPEN，HIGH，LOW，CLOSE，VOLUME）。

我正在尝试查找具有最近CLOSE价格大于其5个交易日前的CLOSE价格的股票的查询方法。我不能仅仅进行日期计算，因为股票不是每天都交易（周末和节假日不交易，某些股票可能在正常交易日不交易）。因此，我只需要比较每个符号的最近行和其之前第5行的CLOSE价格。

我在这里有样本表格和数据： 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个交易日收盘价的股票符号”

我想创建的查询应该得出以下结果：

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，在5行之前是24.75）

- Paul

---

所以你想找到每只股票的最新价格，但是只保留那些比5天前价格更高的股票？但是如果5天前没有价格呢！？！ - Strawberry

既然符号是独特且（大概）不会改变的，为什么你要存储ID而不是符号本身呢？！ - Strawberry

我猜我明白为什么结果中有ADT和AEO，但不知道AA是怎么回事。 - Strawberry

对于你的第一个问题...我在原始描述中可能没有表达清楚。我想保留那些价格高于5个交易日前的股票，而不是5个日历天前的股票。交易日简单地定义为PRICE表中前5行。 - Paul

符号名称有时会更改，因此存储一个 ID。 - Paul

Answer 1

您可以使用相关子查询获取5天前的价格。实际上，您也可以使用同样的方法获取最新的价格。因此，这可能是正确的路径：

  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;

- Gordon Linoff

---

Gordon - 感谢您的回答。不幸的是，基于日期的偏移量并不适用于每种情况，因为有些日子，如周末和节假日，并非交易日。子查询需要选择第五个最近的收盘价 - 这就是让我困惑的部分 :) - Paul

@保罗，我认为你应该相应地修改你的问题。 - Strawberry

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - Gordon Linoff

@GordonLinoff 原问题中提到“日期计算”无法使用，因为日期之间有空隙。所以我认为这不是一个“日期问题”。由于我无法解决此问题，也许我没有问正确的问题；有什么建议可以使问题更清楚吗？简化一下... 我需要选择最近收盘价大于5行前的收盘价的股票代码（根据日期排序，但每行中的日期不是连续的天数——日期之间存在“间隔”）。如何使问题更明确，任何指导都将不胜感激，谢谢！ :) - Paul