10-48 最後に0になった日からの件数

SQLパズル

販売履歴テーブル
商品コード   販売日      販売数
----------  ----------  ------
11111       2003/10/28      90
11111       2003/10/25      30
11111       2003/10/23       0
11111       2003/10/22      10
11111       2003/10/21       0
11111       2003/10/20      10
22222       2003/10/22      10

商品コードごとの、
販売数が、最後に0になった日からのレコード数を出力する

販売数が0になった日がない場合は、その商品コードのレコード数を出力する

出力結果
商品コード   件数
----------  ----
11111          2
22222          1

データ作成スクリプト

create table 販売履歴(
商品コード char(5),
販売日     date,
販売数     number(2));

insert into 販売履歴 values('11111',to_date('2003/10/28','YYYY/MM/DD'),90);
insert into 販売履歴 values('11111',to_date('2003/10/25','YYYY/MM/DD'),30);
insert into 販売履歴 values('11111',to_date('2003/10/23','YYYY/MM/DD'), 0);
insert into 販売履歴 values('11111',to_date('2003/10/22','YYYY/MM/DD'),10);
insert into 販売履歴 values('11111',to_date('2003/10/21','YYYY/MM/DD'), 0);
insert into 販売履歴 values('11111',to_date('2003/10/20','YYYY/MM/DD'),10);
insert into 販売履歴 values('22222',to_date('2003/10/22','YYYY/MM/DD'),10);
commit;

SQL

--■■■相関サブクエリを使う方法1■■■
select 商品コード,count(*) as 件数
from 販売履歴 a
where 販売日 > (select max(b.販売日) from 販売履歴 b
                 where b.商品コード = a.商品コード
                   and b.販売数 = 0)
  or not exists(select 1 from 販売履歴 b
                 where b.商品コード = a.商品コード
                   and b.販売数 = 0)
group by 商品コード
order by 商品コード;

--■■■相関サブクエリを使う方法2■■■
select 商品コード,count(*) as 件数
from 販売履歴 a
where not exists(select 1 from 販売履歴 b
                 where b.商品コード = a.商品コード
                   and b.販売日 >= a.販売日
                   and b.販売数 = 0)
group by 商品コード
order by 商品コード;

--■■■分析関数を使う方法■■■
select 商品コード,count(*) as 件数
from (select 商品コード,販売日,
      max(decode(販売数,0,販売日)) over(partition by 商品コード) as 販売数が0の日
      from 販売履歴)
where 販売日 > 販売数が0の日
   or 販売数が0の日 is null
group by 商品コード
order by 商品コード;

--■■■分析関数とignore nullsを使う方法1(10g以降)■■■
select 商品コード,count(*) as 件数
from (select 商品コード,販売日,
      Last_Value(decode(販売数,0,販売日) ignore nulls)
      over(partition by 商品コード order by 販売日
      Rows between Unbounded Preceding and Unbounded Following) as 販売数が0の日
      from 販売履歴)
where 販売日 > 販売数が0の日
   or 販売数が0の日 is null
group by 商品コード
order by 商品コード;

--■■■分析関数とignore nullsを使う方法2(10g以降)■■■
select 商品コード,sum(IsCount) as 件数
from (select 商品コード,
      case when 販売日 <=
      Last_Value(decode(販売数,0,販売日) ignore nulls)
      over(partition by 商品コード order by 販売日
      Rows between Unbounded Preceding and Unbounded Following)
      then 0 else 1 end as IsCount
      from 販売履歴)
group by 商品コード
order by 商品コード;

解説

分析関数を使う方法では、
Max関数とdecode関数を組み合わせて、
販売数が0の日のなかで、最大の日を取得してます。

---