10-340 最新の2レコード以上連続した期間を出力

SQLパズル

EventTable
dayC        Val
----------  ---
2005-01-18    7
2005-01-25   18
2005-01-27   18
2005-01-29   18
2005-01-30   18
2005-07-22    5
2006-01-04    5
2006-06-20   10
2007-04-16   18    ←出力対象
2007-05-30   18    ←出力対象
2008-01-06   30
2009-02-22   18

最新の同じValが2レコード以上連続した期間を出力する。

出力結果
dayC        Val
----------  ---
2007-04-16   18
2007-05-30   18

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table EventTable(dayC,Val) as
select date '2005-01-18', 7 from dual union
select date '2005-01-25',18 from dual union
select date '2005-01-27',18 from dual union
select date '2005-01-29',18 from dual union
select date '2005-01-30',18 from dual union
select date '2005-07-22', 5 from dual union
select date '2006-01-04', 5 from dual union
select date '2006-06-20',10 from dual union
select date '2007-04-16',18 from dual union
select date '2007-05-30',18 from dual union
select date '2008-01-06',30 from dual union
select date '2009-02-22',18 from dual;

SQL

--■■■分析関数を何度も使う方法■■■
select dayC,Val
from (select dayC,Val,GID,
      max(GID) over() as maxGID
      from (select dayC,Val,GID,
            count(*) over(partition by GID) as cnt
            from (select dayC,Val,
                  sum(willSum) over(order by dayC) as GID
                  from (select dayC,Val,
                        case when Val
                                = Lag(Val) over(order by dayC)
                             then 0 else 1 end as willSum
                        from EventTable)))
      where cnt > 1)
where GID = maxGID;

--■■■旅人算メソッドとwith句を使う方法■■■
with tmp as(
select dayC,Val,
 Row_Number() over(order by dayC)
-Row_Number() over(partition by Val
                   order by dayC) as gap
  from EventTable)
select dayC,Val
  from tmp
 where (Val,gap)
     = (select
        max(Val) Keep(Dense_Rank Last order by max(dayC)),
        max(gap) Keep(Dense_Rank Last order by max(dayC))
          from tmp
        group by Val,gap
        having count(*) > 1);

解説

旅人算メソッドを使うかで試行錯誤しました。

ネストした集約関数を使って、かつ、Last関数を使えるのは知りませんでした。
集約関数の幅が広がりますね :-)

select deptno,sal
  from scott.emp
order by 1,2;

deptno   sal
------  ----
    10  1300
    10  2450
    10  5000
    20   800
    20  1100
    20  2975
    20  3000
    20  3000
    30   950
    30  1250
    30  1250
    30  1500
    30  1600
    30  2850

select avg(max(sal)) as nestAggUsage1,
max(deptno) Keep(Dense_Rank First order by max(sal)) as nestAggUsage2
  from scott.emp
group by deptno;

nestAggUsage1  nestAggUsage2
-------------  -------------
    3616.6667             30

***********************************************************************
こんな最頻値の求め方も可能になりますね。
2-3-5 モード(最頻値)を取得

select max(Col1) Keep(Dense_Rank Last order by count(*)) as 最頻値,
max(count(*)) as 最頻値のレコード数
from 最頻値テーブル
group by Col1;

最頻値  最頻値のレコード数
------  ------------------
    60                   4