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

我一直在寻找，但一直无法找到解决方案——我知道这是可行的，但我只是还没有我需要的忍者 SQL 技能......

我正在寻找此问题的解决方案：我有两个与股市数据相关的表。第一个是带有 ID 和股票代码 (ID,SYMBOL) 的简单股票代码列表。第二个表包含每只股票的历史价格数据。 （ID、日期、开盘价、最高价、最低价、收盘价、交易量）。

我正在尝试找出如何查询最近收盘价高于 5 个交易日前收盘价的股票。我不能只做日期数学，因为股票不会每天交易（周末和节假日不交易，而且有些股票可能不会在正常交易日交易）。因此，我只需要比较每个交易品种的最近一行和第五行的收盘价。

我这里有示例表和数据：http://sqlfiddle.com/#!2/5fe76/2 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）

您可以使用相关子查询获取 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;