SQL 分析大盘走势

下表（stock）记录了某指数过去一段时间的收盘价，我们要从这张表中找出收盘价持续上涨的日期。

deal_date    price  
----------  --------
2020-11-20      3377
2020-11-23      3414
2020-11-24      3402
2020-11-25      3362
2020-11-26      3369
2020-11-27      3408
2020-11-30      3391
2020-12-01      3451
2020-12-02      3449

期望得到的结果 >>>

deal_date                min_price  max_price  
-----------------------  ---------  -----------
2020-11-20 ~ 2020-11-23       3377         3414
2020-11-25 ~ 2020-11-27       3362         3408
2020-11-30 ~ 2020-12-01       3391         3451

一种常规的解决思路是：

将当前行的值和上一行的值作比较，我们就能知道当前行的值是增加了还是减少了，或者保持不变。

那怎么把单调递增的序列归到同一组呢？给序列中的第一个值分配唯一的序号，序列中的其它值的序号保持和第一个值的序号一致。

在 SQL 中，使用窗口函数 lag() 可以在当前行获取到上一行某个字段的值。窗口函数 sum() 可以实现累加的操作，要给满足某种规则下的多行数据分配同一个序号，只需要给这些数据行增加一个新字段，对于新字段，除了第一行的值是目标序号，其它行的值都为 0 ，然后对新字段做累加操作就能做到同一个序列的序号一致。

完整的 SQL ：

--获取前一行的 price
x0 AS 
(SELECT 
  *,
  lag (price) over (
ORDER BY deal_date) AS last_price 
FROM
  stock),
-- 如果是递增，则将标识字段设置为 0，其它的设置为 1
x1 AS 
(SELECT 
  *,
  IF(price - last_price > 0, 0, 1) AS inc 
FROM
  x0),
-- 累加标识字段，将递增序列的行归为一组
x2 AS 
(SELECT 
  *,
  SUM(inc) over (
ORDER BY deal_date) AS g 
FROM
  x1),
-- 过滤掉非单调递增的组（只有一行数据）
x3 AS 
(SELECT 
  g,
  CONCAT_WS(' ~ ', MIN(deal_date), MAX(deal_date)) AS deal_date,
  MIN(price) AS min_price,
  MAX(price) AS max_price 
FROM
  x2 
GROUP BY g 
HAVING COUNT(*) >= 2) 
SELECT 
  deal_date,
  min_price,
  max_price 
FROM
  x3 

另一种非常规的思路是：

找到所有日期的组合数据，筛选较后的交易日的价格大于较前的交易日的价格的组合。如果两个交易日之间的所有交易日期的价格都是单调递增的，则这是一个单调递增组合。最后，剔除那些交易日期被包含的组合。

获取之后的交易日的价格比之前的交易日的价格高的所有组合：

SELECT 
  a.deal_date AS start_date,
  b.deal_date AS end_date,
  a.price AS start_price,
  b.price AS end_price 
FROM
  stock a 
  INNER JOIN stock b 
    ON b.deal_date > a.deal_date 
    AND a.price < b.price 
ORDER BY a.deal_date,
  b.deal_date;
  
-- 数据太多，只放了部分结果

start_date  end_date    start_price  end_price  
----------  ----------  -----------  -----------
2020-11-20  2020-11-23         3377         3414
2020-11-20  2020-11-24         3377         3402
2020-11-20  2020-11-27         3377         3408
2020-11-20  2020-11-30         3377         3391
2020-11-20  2020-12-01         3377         3451
2020-11-20  2020-12-02         3377         3449
2020-11-23  2020-12-01         3414         3451
2020-11-23  2020-12-02         3414         3449
...
2020-11-30  2020-12-01         3391         3451
2020-11-30  2020-12-02         3391         3449

对于"2020-11-20 ~ 2020-12-02" 这个组合，怎么知道这个组合之间的所有交易日期的价格是否都是单调递增的呢？

任意取出在这个组合内的两个交易日的价格，如果这两个交易日期的价格不在这个组合的价格之内，或者交易日期在前的价格大于等于交易日期靠后的价格，则这个组合不是单调递增的。

2020-12-01 的价格是 3451，大于 2020-12-02 的价格，因此"2020-11-20 ~ 2020-12-02" 不是价格单调递增的组合。

完整的 SQL ：

x0 AS 
(SELECT 
  a.deal_date AS start_date,
  b.deal_date AS end_date,
  a.price AS start_price,
  b.price AS end_price 
FROM
  stock a 
  INNER JOIN stock b 
    ON b.deal_date > a.deal_date 
    AND a.price < b.price 
    AND NOT EXISTS 
    (SELECT 
      NULL 
    FROM
      stock c,
      stock d 
    -- c 和 d 的交易日在 a 和 b 的交易日期的范围内 
    WHERE c.deal_date > a.deal_date 
      AND c.deal_date < b.deal_date 
      AND d.deal_date > a.deal_date 
      AND d.deal_date < b.deal_date 
    -- 非单调递增的判断规则
      AND (
        (
          d.deal_date > c.deal_date 
          AND d.price <= c.price
        ) 
        OR (
          c.price <= a.price 
          OR c.price >= b.price
        ) 
        OR (
          d.price <= a.price 
          OR d.price >= b.price
        )
      ))) 
SELECT 
  MIN(start_date) AS start_date,
  end_date,
  MIN(start_price) AS start_price,
  MAX(end_price) AS end_price 
FROM
  (SELECT 
    start_date,
    MAX(end_date) AS end_date,
    MIN(start_price) AS start_price,
    MAX(end_price) AS end_price 
  FROM
    x0 
  GROUP BY start_date) x1 
GROUP BY end_date