求股票最长连续上涨天数的sql语句

Question

现有表（股价表）、包含三个字段（股票代码，收盘价、交易日），写出计算股票最长连续上涨了多少天的sql语句。
注：后一天收盘价>前一天收盘价即为涨

Answer 1

看网上有很多解答，思考整理了一下，一个sql可以搞定这个问题。
已经有整理在我的文章中，看排版会好一些。
https://blog.csdn.net/gzb8612/article/details/106696664

2.1 思路

1、获取每天涨跌
2、计算连续上涨的天数

主知识点：窗口函数(lag)、With as、Join + 递归。
多思考，多练习求证，才能培养出一定的sql思维。

3.1 创建Table

create table test111
(
code varchar2(100),
stockdate date,
close number
)

3.2 插入数据

insert into TEST111 (CODE, STOCKDATE, CLOSE, ROWID)
values ('62', to_date('05-01-2015', 'dd-mm-yyyy'), '8.91', null);

insert into TEST111 (CODE, STOCKDATE, CLOSE, ROWID)
values ('62', to_date('06-01-2015', 'dd-mm-yyyy'), '8.31', null);

insert into TEST111 (CODE, STOCKDATE, CLOSE, ROWID)
values ('62', to_date('07-01-2015', 'dd-mm-yyyy'), '8.6', null);

insert into TEST111 (CODE, STOCKDATE, CLOSE, ROWID)
values ('62', to_date('08-01-2015', 'dd-mm-yyyy'), '8.73', null);

insert into TEST111 (CODE, STOCKDATE, CLOSE, ROWID)
values ('62', to_date('09-01-2015', 'dd-mm-yyyy'), '8.82', null);

insert into TEST111 (CODE, STOCKDATE, CLOSE, ROWID)
values ('62', to_date('10-01-2015', 'dd-mm-yyyy'), '8.9', null);

insert into TEST111 (CODE, STOCKDATE, CLOSE, ROWID)
values ('62', to_date('11-01-2015', 'dd-mm-yyyy'), '9', null);

insert into TEST111 (CODE, STOCKDATE, CLOSE, ROWID)
values ('62', to_date('12-01-2015', 'dd-mm-yyyy'), '8.5', null);

insert into TEST111 (CODE, STOCKDATE, CLOSE, ROWID)
values ('62', to_date('13-01-2015', 'dd-mm-yyyy'), '8.6', null);

insert into TEST111 (CODE, STOCKDATE, CLOSE, ROWID)
values ('62', to_date('14-01-2015', 'dd-mm-yyyy'), '8.7', null);

insert into TEST111 (CODE, STOCKDATE, CLOSE, ROWID)
values ('62', to_date('15-01-2015', 'dd-mm-yyyy'), '8.5', null);

insert into TEST111 (CODE, STOCKDATE, CLOSE, ROWID)
values ('62', to_date('16-01-2015', 'dd-mm-yyyy'), '8.5', null);
commit;

3.3 sql1：计算每天的涨跌情况

with xh as
(select t.code,
t.stockdate,
t.close,
nvl(lag(t.close) over(order by t.stockdate), t.close) zt,
nvl(lag(t.stockdate) over(order by t.stockdate), t.stockdate) zr,
t.close - nvl(lag(t.close) over(order by t.stockdate), t.close) blance,
sign(t.close -
nvl(lag(t.close) over(order by t.stockdate), t.close)) blan_sign
from TEST111 t)
select x.code, x.stockdate, x.close, x.zt, x.zr, x.blance, x.blan_sign
from xh x

当blan_sign字段值为1时，表示当天上涨。

3.4 sql2：根据sql1的结果计算连续上涨的时间段

SELECT code, MIN(stockdate), MAX(stockdate)
FROM (
--dataset1
with xh as (select t.code,
t.stockdate,
t.close,
sign(t.close -
nvl(lag(t.close) over(order by t.stockdate),
t.close)) blan_sign

                 from TEST111 t)
     select x.code, x.stockdate, x.close, x.blan_sign
       from xh x
      where x.blan_sign = 1
     --end dataset1
      ) t

      START WITH NOT EXISTS (SELECT 1
                    FROM (
                         --dataset1
                         with xh as (select t.code,
                                            t.stockdate,
                                            t.close,

                                            sign(t.close -
                                                 nvl(lag(t.close)
                                                     over(order by
                                                          t.stockdate),
                                                     t.close)) blan_sign
                                       from TEST111 t)
                           select x.code,
                                  x.stockdate,
                                  x.close,
                                  x.blan_sign
                             from xh x
                            where x.blan_sign = 1
                           --end dataset1
                            ) b
                            WHERE b.stockdate = t.stockdate - 1
                  )
     CONNECT BY PRIOR t.stockdate = t.stockdate - 1
      GROUP BY rownum - LEVEL, code;

有其他解法的小伙伴，一起来探讨~

————————————————
版权声明：本文为CSDN博主「gzb8612」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/gzb8612/article/details/106696664