7-55 having句で相関サブクエリ

SQLパズル

商品管理テーブル
商品ID  日付      個数
------  --------  ----
     A  20051201     1
     A  20051201     2
     A  20051202     2
     B  20051203     5
     B  20051203     6
     B  20051204    17
     C  20051205     5
     C  20051205     6
     C  20051206    11

商品管理テーブルから、
商品IDごとの、
個数の合計が最大の日の、個数の合計と、件数を出力する。

出力結果
商品ID  日付      個数  件数
------  --------  ----  ----
     A  20051201     3     2
     B  20051204    17     1
     C  20051205    11     2
     C  20051206    11     1

データ作成スクリプト

create table 商品管理(
商品ID char(1),
日付   number(8),
個数   number(2));

insert into 商品管理 values('A',20051201, 1);
insert into 商品管理 values('A',20051201, 2);
insert into 商品管理 values('A',20051202, 2);
insert into 商品管理 values('B',20051203, 5);
insert into 商品管理 values('B',20051203, 6);
insert into 商品管理 values('B',20051204,17);
insert into 商品管理 values('C',20051205, 5);
insert into 商品管理 values('C',20051205, 6);
insert into 商品管理 values('C',20051206,11);
commit;

SQL

--■■■all述語を使う方法■■■
select 商品ID,日付,sum(個数) as 個数,count(*) as 件数
from 商品管理 a
group by 商品ID,日付
having sum(個数) >= all(select sum(個数) from 商品管理 b
                         where b.商品ID = a.商品ID
                      group by 日付)
order by 商品ID;

--■■■集合関数のネストを使う方法■■■
select 商品ID,日付,sum(個数) as 個数,count(*) as 件数
from 商品管理 a
group by 商品ID,日付
having sum(個数) = (select max(sum(個数)) from 商品管理 b
                     where b.商品ID = a.商品ID
                    group by 日付)
order by 商品ID;

--■■■分析関数を使う方法■■■
select distinct 商品ID,日付,個数,件数
from (select 商品ID,日付,個数,件数,max(個数) over(partition by 商品ID) as Max個数
        from (select 商品ID,日付,sum(個数) over(partition by 商品ID,日付) as 個数,
              count(*) over(partition by 商品ID,日付) as 件数
                from 商品管理))
 where 個数=Max個数
order by 商品ID;

--■■■グループ化と分析関数を使う方法■■■
select 商品ID,日付,個数,件数
from (select 商品ID,日付,sum(個数) as 個数,count(*) as 件数,
      max(sum(個数)) over(partition by 商品ID) as max個数
        from 商品管理
      group by 商品ID,日付)
 where 個数 = max個数;

解説

havingでの相関サブクエリで、個数の合計が最大の日かチェックする方法や、
分析関数で、個数の合計が最大の日かチェックする方法があります。

---