9-62 製品の対象年齢の範囲

SQLパズル

PriceByAgeテーブル
product_id  low_age  high_age
----------  -------  --------
Product_1         5        15
Product_1        16        60
Product_1        65       150
Product_2         1         5
Product_2         6        70
Product_2        71       150
Product_3         1        15
Product_3        10       150
Product_3        20       150
Product_3        30       150
Product_4         1       150
Product_5         1       130
Product_5       131       150
Product_6         1       130
Product_6       135       150

product_idごとで、low_ageからhigh_ageまでの範囲が、
1才から150才までを網羅するproduct_idを出力する。

出力結果
product_id
----------
Product_2
Product_3
Product_4
Product_5

SQLパズル 第2版のパズル65 [製品の対象年齢の範囲] を参考にさせていただきました

データ作成スクリプト

create table PriceByAge(
product_id char(9),
low_age    number(3),
high_age   number(3) not null,
check(low_age < high_age),
primary key (product_id,low_age));

insert into PriceByAge values('Product_1',  5, 15);
insert into PriceByAge values('Product_1', 16, 60);
insert into PriceByAge values('Product_1', 65,150);
insert into PriceByAge values('Product_2',  1,  5);
insert into PriceByAge values('Product_2',  6, 70);
insert into PriceByAge values('Product_2', 71,150);
insert into PriceByAge values('Product_3',  1, 15);
insert into PriceByAge values('Product_3', 10,150);
insert into PriceByAge values('Product_3', 20,150);
insert into PriceByAge values('Product_3', 30,150);
insert into PriceByAge values('Product_4',  1,150);
insert into PriceByAge values('Product_5',  1,130);
insert into PriceByAge values('Product_5',131,150);
insert into PriceByAge values('Product_6',  1,130);
insert into PriceByAge values('Product_6',135,150);
commit;

SQL

--■■■連番テーブルを使う方法■■■
select a.product_id
  from PriceByAge a,(select RowNum as Counter
                       from all_objects
                      where RowNum <= 150) b
 where b.Counter between a.low_age and a.high_age
group by a.product_id
having count(distinct b.Counter) = 150
order by a.product_id;

--■■■再帰with句を使う方法(11gR2以降)■■■
with rec(product_id,low_age,high_age,ValAge) as(
select product_id,low_age,high_age,low_age
  from PriceByAge
union all
select product_id,low_age,high_age,ValAge+1
  from rec
 where ValAge+1 <= high_age)
select product_id
  from rec
 where ValAge between 1 and 150
group by product_id
having count(distinct ValAge) = 150
order by product_id;

--■■■表関数を使う方法■■■
create or replace Package Pack09_62 Is
    type PrintType is record(
    product_id PriceByAge.product_id%type);

    type PrintTypeSet is table of PrintType;
end;
/

create or replace function OutputPrice return Pack09_62.PrintTypeSet PipeLined is
    out_rec Pack09_62.PrintType;

    type AgeArrayType is table of boolean index by binary_integer;
    AgeArray AgeArrayType;
    willOut boolean;

    procedure ArrayInit is
    begin
        for I in 1..150 Loop
            AgeArray(I) := false;
        end Loop;
    end;

begin
    ArrayInit;
    for rec in (select product_id,low_age,high_age,
                case Lead(product_id) over(order by product_id,low_age)
                when product_id then 0 else 1 end as NextBreak
                  from PriceByAge
                 order by product_id,low_age) Loop

            for I in rec.low_age..rec.high_age Loop
                AgeArray(I) := true;
            end Loop;

        if rec.NextBreak = 1 then
            willOut := true;
            for I in 1..150 Loop
                willOut := willOut and AgeArray(I);
            end Loop;
            ArrayInit;

            if willOut then
                out_rec.product_id := rec.product_id;
                pipe row(out_rec);
            end if;
        end if;
    end Loop;
end;
/

select product_id
  from table(OutputPrice)
order by product_id;

解説

連番テーブルを使うと楽でしょう。

なお、年齢が
1才から150才ではなく
10才から40才という場合は、case式を使います。

select a.product_id
  from PriceByAge a,(select RowNum as Counter
                       from all_objects
                      where RowNum <= 150) b
 where b.Counter between a.low_age and a.high_age
group by a.product_id
having count(distinct case when b.Counter between 10 and 40
                           then b.Counter end) = (40-10)+1
order by a.product_id;

---