9-70 単調増加している期間を求める

SQLパズル

MyStockテーブル
deal_date   price
----------  -----
2007-01-06   1000
2007-01-08   1050
2007-01-09    990
2007-01-12    900
2007-01-13    880
2007-01-14    870
2007-01-16    920
2007-01-17   1000
2007-12-20    200
2007-12-21    300
2007-12-22    400
2007-12-23    100
2007-12-25    200
2007-12-27    300

priceが単調増加している期間を求める。

出力結果
start_date  end_date
----------  ----------
2007-01-06  2007-01-08
2007-01-14  2007-01-17
2007-12-20  2007-12-22
2007-12-23  2007-12-27

達人に学ぶ SQL徹底指南書の171ページを参考にさせていただきました

データ作成スクリプト

create table MyStock(deal_date,price) as
select date '2007-01-06',1000 from dual union
select date '2007-01-08',1050 from dual union
select date '2007-01-09', 990 from dual union
select date '2007-01-12', 900 from dual union
select date '2007-01-13', 880 from dual union
select date '2007-01-14', 870 from dual union
select date '2007-01-16', 920 from dual union
select date '2007-01-17',1000 from dual union
select date '2007-12-20', 200 from dual union
select date '2007-12-21', 300 from dual union
select date '2007-12-22', 400 from dual union
select date '2007-12-23', 100 from dual union
select date '2007-12-25', 200 from dual union
select date '2007-12-27', 300 from dual;

SQL

select min(deal_date),max(deal_date)
from (select deal_date,price,
      sum(case when isUp = 0
                and willUp = 1
               then 1 else 0 end) over(order by deal_date) as GID
      from (select deal_date,price,
            case when Lag (price) over(order by deal_date)
                    < price then 1 else 0 end as isUp,
            case when Lead(price) over(order by deal_date)
                    > price then 1 else 0 end as willUp
            from MyStock)
      where 1 in(isUp,willUp))
group by GID
order by min(deal_date);

解説

前よりもpriceが増加したかを表すisUp
次にpriceが増加するかを表すwillUp
をcase式で求めてます。

---