10-225 旅人算と存在肯定命題

SQLパズル

gradeTable
name  mydate      grade
----  ----------  -----
Bill  2008-01-28  C
Bill  2007-12-03  C
Bill  2007-10-08  B
Bill  2007-08-13  B
Bill  2007-06-18  B
Bill  2007-04-23  F
Bill  2007-02-26  F
Bill  2007-01-01  F
Bill  2006-11-06  F
Bill  2006-09-11  C
Bill  2006-07-17  C
Bill  2006-05-22  F
Bill  2006-03-27  F
Bill  2006-01-30  F
Jane  2008-02-08  B
Jane  2007-12-28  B
Jane  2007-11-16  F
Jane  2007-10-05  F
Jane  2007-08-24  C
Jane  2007-07-13  C
Jane  2007-06-01  C
Jane  2007-04-20  C
Jane  2007-03-09  A
Jane  2007-01-26  F
Jane  2006-12-15  B
Jane  2006-11-03  B
Jane  2006-09-22  F
Jane  2006-08-11  F
Jane  2006-06-30  F
Jane  2006-05-19  F
Jane  2006-04-07  C
Jane  2006-02-24  C
Jane  2006-01-13  C
Mike  1999-11-22  F
Mike  1999-10-20  F
Mike  1999-09-18  F
Tom   1970-05-18  F
Tom   1970-04-18  A
Tom   1970-03-18  A

nameごとで、
gradeがFの日付の最新と
それが、何回連続するかを求める。

出力結果
name  recentDay   cnt
----  ----------  ---
Bill  2007-04-23    4
Jane  2007-11-16    2
Mike  1999-11-22    3
Tom   1970-05-18    1

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

データ作成スクリプト

create table gradeTable(name,mydate,grade) as
select 'Bill',to_date('2008-01-28','yyyy-mm-dd'),'C' from dual union
select 'Bill',to_date('2007-12-03','yyyy-mm-dd'),'C' from dual union
select 'Bill',to_date('2007-10-08','yyyy-mm-dd'),'B' from dual union
select 'Bill',to_date('2007-08-13','yyyy-mm-dd'),'B' from dual union
select 'Bill',to_date('2007-06-18','yyyy-mm-dd'),'B' from dual union
select 'Bill',to_date('2007-04-23','yyyy-mm-dd'),'F' from dual union
select 'Bill',to_date('2007-02-26','yyyy-mm-dd'),'F' from dual union
select 'Bill',to_date('2007-01-01','yyyy-mm-dd'),'F' from dual union
select 'Bill',to_date('2006-11-06','yyyy-mm-dd'),'F' from dual union
select 'Bill',to_date('2006-09-11','yyyy-mm-dd'),'C' from dual union
select 'Bill',to_date('2006-07-17','yyyy-mm-dd'),'C' from dual union
select 'Bill',to_date('2006-05-22','yyyy-mm-dd'),'F' from dual union
select 'Bill',to_date('2006-03-27','yyyy-mm-dd'),'F' from dual union
select 'Bill',to_date('2006-01-30','yyyy-mm-dd'),'F' from dual union
select 'Jane',to_date('2008-02-08','yyyy-mm-dd'),'B' from dual union
select 'Jane',to_date('2007-12-28','yyyy-mm-dd'),'B' from dual union
select 'Jane',to_date('2007-11-16','yyyy-mm-dd'),'F' from dual union
select 'Jane',to_date('2007-10-05','yyyy-mm-dd'),'F' from dual union
select 'Jane',to_date('2007-08-24','yyyy-mm-dd'),'C' from dual union
select 'Jane',to_date('2007-07-13','yyyy-mm-dd'),'C' from dual union
select 'Jane',to_date('2007-06-01','yyyy-mm-dd'),'C' from dual union
select 'Jane',to_date('2007-04-20','yyyy-mm-dd'),'C' from dual union
select 'Jane',to_date('2007-03-09','yyyy-mm-dd'),'A' from dual union
select 'Jane',to_date('2007-01-26','yyyy-mm-dd'),'F' from dual union
select 'Jane',to_date('2006-12-15','yyyy-mm-dd'),'B' from dual union
select 'Jane',to_date('2006-11-03','yyyy-mm-dd'),'B' from dual union
select 'Jane',to_date('2006-09-22','yyyy-mm-dd'),'F' from dual union
select 'Jane',to_date('2006-08-11','yyyy-mm-dd'),'F' from dual union
select 'Jane',to_date('2006-06-30','yyyy-mm-dd'),'F' from dual union
select 'Jane',to_date('2006-05-19','yyyy-mm-dd'),'F' from dual union
select 'Jane',to_date('2006-04-07','yyyy-mm-dd'),'C' from dual union
select 'Jane',to_date('2006-02-24','yyyy-mm-dd'),'C' from dual union
select 'Jane',to_date('2006-01-13','yyyy-mm-dd'),'C' from dual union
select 'Mike',to_date('1999-11-22','yyyy-mm-dd'),'F' from dual union
select 'Mike',to_date('1999-10-20','yyyy-mm-dd'),'F' from dual union
select 'Mike',to_date('1999-09-18','yyyy-mm-dd'),'F' from dual union
select 'Tom' ,to_date('1970-05-18','yyyy-mm-dd'),'F' from dual union
select 'Tom' ,to_date('1970-04-18','yyyy-mm-dd'),'A' from dual union
select 'Tom' ,to_date('1970-03-18','yyyy-mm-dd'),'A' from dual;

SQL

--■■■分析関数を使う方法1(10g以降)■■■
select name,max(mydate) as recentDay,count(*) as cnt
from (select name,mydate,grade,
      First_Value(case when grade = 'F' then mydate end ignore nulls)
      over(partition by name order by mydate desc) as targetDate,
        Row_number() over(partition by name       order by mydate desc)
      - Row_number() over(partition by name,grade order by mydate desc) as distance
        from gradeTable)
where grade = 'F'
group by name,distance
having max(case when mydate = targetDate then 1 else 0 end) = 1
order by name;

--■■■分析関数を使う方法2■■■
select name,max(mydate) as recentDay,count(*) as cnt
from (select name,mydate,grade,
      max(decode(grade,'F',mydate)) over(partition by name) as targetDate,
        Row_number() over(partition by name       order by mydate desc)
      - Row_number() over(partition by name,grade order by mydate desc) as distance
        from gradeTable)
where grade = 'F'
group by name,distance
having max(case when mydate = targetDate then 1 else 0 end) = 1
order by name;

--■■■分析関数を使う方法3■■■
select name,max(mydate) as recentDay,count(*) as cnt
from (select name,mydate,grade,
      max(mydate) over(partition by name,grade) as targetDate,
        Row_number() over(partition by name       order by mydate desc)
      - Row_number() over(partition by name,grade order by mydate desc) as distance
        from gradeTable)
where grade = 'F'
group by name,distance
having max(case when mydate = targetDate then 1 else 0 end) = 1
order by name;

--■■■分析関数を使う方法4■■■
select name,max(mydate) as recentDay,count(*) as cnt
from (select name,mydate,grade,
      max(mydate) over(partition by name,grade) as targetDate,
        Row_number() over(partition by name       order by mydate desc)
      - Row_number() over(partition by name,grade order by mydate desc) as distance
        from gradeTable)
where grade = 'F'
group by name,distance
having max(mydate) = max(targetDate)
order by name;

解説

旅人算の感覚と、存在肯定命題を組み合わせて使ってます。

分析関数を使う方法1のように、
First_Value関数とcase式とignore nullsは使う必要がなかったですねぇ。

分析関数を使う方法4のように、
max(targetDate)と比較してもいいです。

---