SQL自连接精准计算股票连续上涨天数

先说结论：直接用 LAG() 或者普通的窗口函数去算连续上涨天数，十有八九会翻车。窗口函数的问题在于，它只管按查询结果集的物理顺序往前翻一行，根本不校验你取到的“前一天”到底是不是真正的上一个交易日——节假日、停牌导致的日期空缺，它全当没看见。结果就是，把休市三天后偶然的上涨，误判成“连续四天上涨”。而 SELF JOIN 的优势，恰恰在于能通过连接条件显式地要求“前一天必须存在且是交易日”，逻辑上更可控、更严谨。

为什么不能直接用LAG()或窗口函数算连续天数？

根源在于：窗口函数对“连续性”的理解是物理行序，而非业务日历。你查到的上一行，可能是三天前的数据——中间那些不存在的交易日（比如周末、法定节假日），窗口函数不会替你补上。它只是机械地取排序后相邻的那一行。LAG() 不会替你做任何“日期是否真正连续”的校验。而 SELF JOIN 不同，你可以把“日期差1天”写进连接条件里，系统自然会强制要求两个交易日严格紧邻。这才是实现“连续”语义的正确姿势。

SELF JOIN 需要哪些基础表结构和条件？

假设你有一张 stock_price 表，至少包含 stock_code、trade_date（DATE 类型）和 close_price。关键不在 join 本身，而在连接条件怎么设计。这里有几个硬性约束：

• 必须用 INNER JOIN——只要某一天缺失，链条立刻断裂，这才能保证结果绝对干净。
• 连接条件必须写成带日期加减的形式，比如 MySQL 里 t1.trade_date = t2.trade_date + INTERVAL 1 DAY，PostgreSQL 或 Oracle 则用 t1.trade_date = t2.trade_date + 1（前提是 trade_date 是日期类型）。
• 上涨判断 t1.close_price > t2.close_price 必须放到 ON 子句里，而不是 WHERE——否则连接时会漏掉首日上涨的情况。
• 记得加上联合索引 (stock_code, trade_date)，不然后面多层自连接会慢到怀疑人生。

怎么递归查出“最长连续上涨段”的长度？

SELF JOIN 一次只能看两天的关系。要算连续 N 天，理论上可以写 N 重自连接——但那样做下去，写出来的 SQL 会又长又臭，根本没法维护。更实用的方案是用递归 CTE（MySQL 8.0+ 或 PostgreSQL 都支持）。举个例子：

WITH RECURSIVE up_chain AS (
  -- 初始：每个交易日作为潜在起点，前提是它之前没有满足上涨条件的交易日
  SELECT stock_code, trade_date, close_price, 1 AS days
  FROM stock_price sp1
  WHERE NOT EXISTS (
    SELECT 1 FROM stock_price sp2 
    WHERE sp2.stock_code = sp1.stock_code
      AND sp2.trade_date = sp1.trade_date - INTERVAL 1 DAY
      AND sp2.close_price < sp1.close_price
  )
  UNION ALL
  -- 递归：在已找到的上涨序列上继续延伸
  SELECT uc.stock_code, sp.trade_date, sp.close_price, uc.days + 1
  FROM up_chain uc
  JOIN stock_price sp
     ON uc.stock_code = sp.stock_code
     AND sp.trade_date = uc.trade_date + INTERVAL 1 DAY
     AND sp.close_price > uc.close_price
)
SELECT stock_code, MAX(days) AS max_consecutive_up_days
FROM up_chain
GROUP BY stock_code;

注意：递归 CTE 默认深度可能不够。在 MySQL 中需要设置 cte_max_recursion_depth，PostgreSQL 则用 MAX_RECURSION 1000 来调整。

实际跑出来天数对不上？先检查这三件事

数据质量永远是第一拦路虎。常见偏差几乎都来自这几处：

• trade_date 是字符串还是 DATE？字符串比较（比如 '2023-01-02' > '2023-01-01'）可能因为字符排序规则而出错，必须转成日期类型。
• 复权问题：如果直接用了收盘价（close_price）而不是前复权价（adj_close），除权日次日价格跳空下跌会被误判成“上涨中断”。统一用 adj_close 才能保证价格序列的连续性。
• 同一天多笔记录：比如分笔成交汇总到日线表时没去重，会导致自连接爆炸式膨胀。先用 GROUP BY stock_code, trade_date 去重，不然结果会乱成一锅粥。

一句话总结：连续上涨天数，本质是“日期连续性”和“价格单调性”的交集。SELF JOIN 胜在能精确控制这两个条件的耦合方式，但写法确实比窗口函数绕很多。真要跑全市场，建议按 stock_code 分批处理，避免一次递归太深，搞崩整个数据库。