一个SQL获取某股票连续上涨的天数

1.1 问题

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

现有网上的解答多数是手工拼接数据，这里来个纯正的sql解决方案。

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;

接下去请自便，O(∩_∩)O哈哈~