5-23 インラインビューをグループ化

SQLパズル

注文テーブル
受注番号       受注日   商品番号  個数  顧客番号
--------  ----------   --------  ----  -------
00000001  2004/05/10   S01001       1  C000003
00000002  2004/05/12   S01002       5  C000002
00000003  2004/05/23   S01003       2  C000004
00000004  2004/05/30   S01003       3  C000003
00000005  2004/05/31   S01001       2  C000004
00000006  2004/06/01   S01004       1  C000002
00000007  2004/06/07   S01005      10  C000002
00000008  2004/06/14   S01002       5  C000004
00000009  2004/06/27   S01005      10  C000004
00000010  2004/07/01   S01001       3  C000003

商品テーブル
商品番号  販売価格
--------  -------
S01001        100
S01002         50
S01003        200
S01004         30
S01005         10

注文テーブルと商品テーブルから、
顧客番号、受注年月ごとの金額の合計を出力する。

出力結果
顧客番号  受注年月   金額
--------  --------  ----
C000002   2004/05    250
C000002   2004/06    130
C000003   2004/05    700
C000003   2004/07    300
C000004   2004/05    600
C000004   2004/06    350

データ作成スクリプト

create table 注文テーブル(
受注番号 char(8),
受注日   date,
商品番号 char(6),
個数     number(2),
顧客番号 char(7));

create table 商品テーブル(
商品番号 char(6),
販売価格 number(3));

insert into 注文テーブル(受注番号,受注日,商品番号,個数,顧客番号)
      select '00000001',to_date('20040510','yyyymmdd'),'S01001', 1,'C000003' from dual
union select '00000002',to_date('20040512','yyyymmdd'),'S01002', 5,'C000002' from dual
union select '00000003',to_date('20040523','yyyymmdd'),'S01003', 2,'C000004' from dual
union select '00000004',to_date('20040530','yyyymmdd'),'S01003', 3,'C000003' from dual
union select '00000005',to_date('20040531','yyyymmdd'),'S01001', 2,'C000004' from dual
union select '00000006',to_date('20040601','yyyymmdd'),'S01004', 1,'C000002' from dual
union select '00000007',to_date('20040607','yyyymmdd'),'S01005',10,'C000002' from dual
union select '00000008',to_date('20040614','yyyymmdd'),'S01002', 5,'C000004' from dual
union select '00000009',to_date('20040627','yyyymmdd'),'S01005',10,'C000004' from dual
union select '00000010',to_date('20040701','yyyymmdd'),'S01001', 3,'C000003' from dual;

insert into 商品テーブル(商品番号,販売価格) values('S01001',100);
insert into 商品テーブル(商品番号,販売価格) values('S01002', 50);
insert into 商品テーブル(商品番号,販売価格) values('S01003',200);
insert into 商品テーブル(商品番号,販売価格) values('S01004', 30);
insert into 商品テーブル(商品番号,販売価格) values('S01005', 10);
commit;

SQL

--■■■結合してグループ化する方法■■■
select a.顧客番号,to_char(a.受注日,'yyyy/mm') as 受注年月,
sum(a.個数 * b.販売価格) as 金額
  from 注文テーブル a,商品テーブル b
 where a.商品番号 = b.商品番号
group by a.顧客番号,to_char(a.受注日,'yyyy/mm')
order by a.顧客番号,to_char(a.受注日,'yyyy/mm');

--■■■インラインビューを使用する方法■■■
select 顧客番号,to_char(受注日,'yyyy/mm') as 受注年月,sum(販売価格合計) as 金額
from (select a.顧客番号,a.受注日,
             a.個数 * (select b.販売価格 from 商品テーブル b
                        where b.商品番号 = a.商品番号) as 販売価格合計
      from 注文テーブル a)
group by 顧客番号,to_char(受注日,'yyyy/mm')
order by 顧客番号,受注年月;

--■■■分析関数を使用する方法■■■
select distinct 顧客番号,to_char(受注日,'yyyy/mm') as 受注年月,
sum(個数 * (select b.販売価格 from 商品テーブル b
             where b.商品番号 = a.商品番号))
     over (partition by 顧客番号,to_char(受注日,'yyyy/mm')) as 金額
from 注文テーブル a
order by 顧客番号,受注年月;

解説

結合してグループ化する方法が一番分かりやすいと思います。

インラインビューを使用する方法では、
インラインビューで集合を作成して、group byによるグループ化と、sum関数を使用してます。

分析関数を使用する方法では、
スカラー問い合わせで、商品テーブルから販売価格を取得して、掛け算した結果を集計してます。

---