股票最长连续上涨区间的起始日期和结束日期

问题描述库表 stock 记录多支股票的每日收盘价字段为股票代码 CODE、交易日期 DT、收盘价 CL。现在要针对指定的目标股票找出它历史上连续上涨天数最多的那个区间并给出该区间的起始日期和结束日期。源数据stock 表只列出目标股票 CODE 100046 的记录已按 DT 升序CODEDTCL1000462009-01-01 00:00:003.891000462009-01-02 00:00:003.931000462009-01-05 00:00:003.781000462009-01-06 00:00:004.041000462009-01-07 00:00:003.771000462009-01-08 00:00:004.021000462009-01-09 00:00:004.421000462009-01-12 00:00:004.861000462009-01-13 00:00:004.441000462009-01-14 00:00:004.881000462009-01-15 00:00:004.981000462009-01-16 00:00:004.99期望结果NoRisingDays 是区间编号NoRisingDaysstart_dateend_date22009-01-07 00:00:002009-01-12 00:00:0032009-01-13 00:00:002009-01-16 00:00:001-02 收盘价由 3.89 涨到 3.93这是连续上涨01-05 由 3.93 跌到 3.78上涨中断区间编号从 1 开始递增新的一段从这里起步。编号 2 这一段2009-01-07 到 2009-01-12共 4 个交易日。起点 01-07 的 3.77 低于 01-06 的 4.04随后 4.02、4.42、4.86 一路走高期间没有一天下跌。编号 3 这一段2009-01-13 到 2009-01-16也是 4 个交易日。起点 01-13 的 4.44 低于 01-12 的 4.86随后 4.88、4.98、4.99 连续上涨长度与编号 2 并列最长。SQLazy 分步实现续上涨的本质是“不下跌的一段一段”。先把数据按日期排好序再用分段功能盯着收盘价收盘价一旦变小就说明上一段上涨结束了组号加一开始新的一段。这样每一条记录都拿到了自己所属的上涨区间号。接着按区间号分组数一下行数行数就是这段连续上涨的天数。最后挑出天数最大的那个区间取它的第一条日期和最后一条日期就是答案。【点击在线运行本例】下面逐一解释这些步骤。NameAnchorStatementt1stockfilter CODE 100046t2t1sort DT asct3t2segment CL down as NoRisingDayst4t3compute count DT as ContinuousDays; partition NoRisingDayst5t4rank ContinuousDays maxresultt5summarize min DT as start_date, max DT as end_date; group NoRisingDays第 1 步取出目标股票filter CODE 100046从 stock 表里筛出 CODE 等于 100046 的记录后面的计算只针对这一只股票。比较用的是符号“”不是 SQL 里的“”。第 2 步按日期升序排序sort DT asc把筛选后的记录按交易日 DT 升序排好。分段是按记录顺序一段一段走的顺序不对分出来的区间就没有意义。第 3 步收盘价变小就新开一段segment CL down as NoRisingDays这是本题的核心一步。分段功能针对收盘价 CL 使用固定条件“变小down”当某一天的收盘价比前一天低时组号加一不低上涨或持平时组号不变。生成的组号写入新列 NoRisingDays同一段内的记录组号相同组号相同的一批记录就是一段连续的上涨行情。这里不需要写 CL CL[-1] 这样的跨行比较也不用管第一条记录没有前一天怎么办“变小”这个条件本身就表达了“跌了就另起一段”的业务含义。第 4 步数出每段有几天compute count DT as ContinuousDays; partition NoRisingDays计算列功能在 NoRisingDays 分区内统计记录条数聚合算法 count 写在被聚合项 DT 之前结果写入新列 ContinuousDays。分区内的每一行都会填上本段的总天数所以这一步之后每条记录都带着“我所在的这段涨了几天”。因为 partition 已经按区间号分好了组后面按 ContinuousDays 取最大值就等价于“找出最长的那个上涨区间”。第 5 步挑出最长的那一段rank ContinuousDays max排名功能按 ContinuousDays 取最大值对应的记录。最大值对应的记录可能不止一条此时会返回全部并列的记录所以并列最长的那几段都会保留下来不会因为并列而漏掉一段。这一步只筛选记录不改变记录顺序。第 6 步取每段的起止日期summarize min DT as start_date, max DT as end_date; group NoRisingDays汇总功能按 NoRisingDays 分组组内取 DT 的最小值命名为 start_date取 DT 的最大值命名为 end_date。因为上一步已经把记录限定在最长的那一段或并列的几段里所以这里算出来的就是最长连续上涨区间的起始日期和结束日期。汇总的聚合算法写在被聚合项之前写成 min DT 和 max DT。编译生成 SQL确认上述 6 步逻辑后SQLazy 编译器自动生成原生 SQL这里是 MySQL 语法WITH t2 AS ( SELECT CODE, DT, CL FROM stock WHERE CODE 100046 ), t3 AS ( SELECT CODE, DT, CL , SUM(CASE WHEN CL col__1 THEN 1 ELSE 0 END) OVER (ORDER BY CASE WHEN DT IS NULL THEN 1 ELSE 0 END, DT ASC ROWS UNBOUNDED PRECEDING) 1 AS NoRisingDays FROM ( SELECT t2.*, LAG(CL, 1) OVER (ORDER BY CASE WHEN DT IS NULL THEN 1 ELSE 0 END, DT ASC) AS col__1 FROM t2 ) sub__2 ), t4 AS ( SELECT CODE, DT, CL, NoRisingDays , COUNT(DT) OVER (PARTITION BY NoRisingDays ) AS ContinuousDays FROM t3 ), sub__6 AS ( SELECT sub__5.*, RANK() OVER (ORDER BY ContinuousDays DESC) AS col_4 FROM t4 sub__5 ) SELECT CODE, DT, CL, NoRisingDays, ContinuousDays FROM sub__6 WHERE col_4 1SQLazy 让你用业务语言描述逻辑而不是用 SQL 语法写嵌套查询。这道“最长连续上涨区间”的题核心只有一句话收盘价变小就新开一段。SQLazy 用 segment CL down 直接表达“跌了就另起一段”而手写 SQL 要先构造 LAG 比较出涨跌标志再用 SUM 累计出区间号两层窗口函数套在一起边界和空值都得自己兜住。SQLazy 的价值在于分步。整个计算被拆成取数、排序、分段、计数、取最长、汇总六步每一步都是一张可以直接查看的中间结果表第 3 步之后能看到区间号第 4 步之后能看到每段几天最长的那一段是哪一段一目了然不需要把整条 SQL 跑完再去猜中间算错了没有。SQLazy 的另一个特点是把“先算出每段天数、再挑最大、再取日期”这种自然的业务顺序直接写成代码顺序compute 在分区内计数rank 取最大值对应的记录summarize 在组内取最小和最大日期。汇总类功能里聚合算法写在被聚合项之前count DT、min DT、max DT读到代码就能对上业务说法。分区分段的能力让这类“按区间算长度、再选区间”的问题不必写自连接partition 把同一段记录归到一组组内计数和组内取首尾日期都是现成的功能。SQLazy 负责把这套逻辑编译成目标数据库能跑的 SQL同一段代码换一个数据库也不用改写法。