9-38 期間内の合計の最大値

SQLパズル

Promotionsテーブル
Promo                    startDate   endDate
-----------------------  ----------  ----------
Christmas Week           1995/12/18  1995/12/25
Feast of St.Fred         1995/02/01  1995/02/07
National Pickle Pageant  1995/11/01  1995/11/07

Salesテーブル
clerk  saledate    amount
-----  ----------  ------
Curly  1995/02/03     260
Curly  1995/02/03     260
Curly  1995/02/04     100
Curly  1995/02/05     400
Curly  1995/12/19     400
Curly  1995/12/20       5
Larry  1995/02/03     260
Larry  1995/02/04     110
Larry  1995/02/05     300
Larry  1995/11/01     150
Larry  1995/11/01     325
Larry  1995/11/02     150
Larry  1995/12/23     260
Larry  1995/12/24      25
Moe    1995/11/01     325
Moe    1995/11/01     999
Moe    1995/11/03     150
Moe    1995/12/18     800
Moe    1995/12/19     100
Moe    1995/12/20     200
Moe    1996/01/04     100

PromotionsテーブルのPromoごとに、
Salesテーブルのamountの合計が最大のclerkを
以下の形式で出力する

出力結果
Promo                    clerk  amountSum
-----------------------  -----  ---------
Christmas Week           Moe         1100
Feast of St.Fred         Curly       1020
National Pickle Pageant  Moe         1474

SQLパズル(日本語版)のパズル42 [大売り出し] を参考にさせていただきました
SQLパズル 第2版のパズル46 [販売促進] を参考にさせていただきました

データ作成スクリプト

create table Promotions(
Promo     varchar2(23) not null,
startDate date not null,
endDate   date not null);

insert into Promotions values('Feast of St.Fred',to_date('1995/02/01','YYYY/MM/DD'),
                                                 to_date('1995/02/07','YYYY/MM/DD'));
insert into Promotions values('National Pickle Pageant',to_date('1995/11/01','YYYY/MM/DD'),
                                                        to_date('1995/11/07','YYYY/MM/DD'));
insert into Promotions values('Christmas Week',to_date('1995/12/18','YYYY/MM/DD'),
                                               to_date('1995/12/25','YYYY/MM/DD'));

create table Sales(
clerk    varchar2(5) not null,
saledate date        not null,
amount   number(3)   not null);

insert into Sales values('Curly',to_date('1995/02/03','YYYY/MM/DD'),260);
insert into Sales values('Curly',to_date('1995/02/03','YYYY/MM/DD'),260);
insert into Sales values('Curly',to_date('1995/02/04','YYYY/MM/DD'),100);
insert into Sales values('Curly',to_date('1995/02/05','YYYY/MM/DD'),400);
insert into Sales values('Curly',to_date('1995/12/19','YYYY/MM/DD'),400);
insert into Sales values('Curly',to_date('1995/12/20','YYYY/MM/DD'),  5);

insert into Sales values('Larry',to_date('1995/02/03','YYYY/MM/DD'),260);
insert into Sales values('Larry',to_date('1995/02/04','YYYY/MM/DD'),110);
insert into Sales values('Larry',to_date('1995/02/05','YYYY/MM/DD'),300);
insert into Sales values('Larry',to_date('1995/11/01','YYYY/MM/DD'),150);
insert into Sales values('Larry',to_date('1995/11/01','YYYY/MM/DD'),325);
insert into Sales values('Larry',to_date('1995/11/02','YYYY/MM/DD'),150);
insert into Sales values('Larry',to_date('1995/12/23','YYYY/MM/DD'),260);
insert into Sales values('Larry',to_date('1995/12/24','YYYY/MM/DD'), 25);

insert into Sales values('Moe'  ,to_date('1995/11/01','YYYY/MM/DD'),325);
insert into Sales values('Moe'  ,to_date('1995/11/01','YYYY/MM/DD'),999);
insert into Sales values('Moe'  ,to_date('1995/11/03','YYYY/MM/DD'),150);
insert into Sales values('Moe'  ,to_date('1995/12/18','YYYY/MM/DD'),800);
insert into Sales values('Moe'  ,to_date('1995/12/19','YYYY/MM/DD'),100);
insert into Sales values('Moe'  ,to_date('1995/12/20','YYYY/MM/DD'),200);
insert into Sales values('Moe'  ,to_date('1996/01/04','YYYY/MM/DD'),100);
commit;

SQL

--■■■分析関数を使う方法■■■
select Promo,clerk,amountSum
from (select a.Promo,b.clerk,sum(b.amount) as amountSum,
      max(sum(b.amount)) over(partition by a.Promo) as maxamountSum
        from Promotions a,Sales b
       where b.saledate between a.startDate and a.endDate
      group by a.Promo,b.clerk)
where amountSum = maxamountSum
order by Promo;

--■■■分析関数を使わない方法1■■■
select a.Promo,b.clerk,sum(b.amount) as amountSum
  from Promotions a,Sales b
 where b.saledate between a.startDate and a.endDate
group by a.Promo,b.clerk
having not exists(select 1
                    from Promotions c,Sales d
                   where c.Promo = a.Promo
                     and d.saledate between c.startDate and c.endDate
                  group by d.clerk
                  having sum(d.amount) > sum(b.amount))
order by Promo;

--■■■分析関数を使わない方法2■■■
select a.Promo,b.clerk,sum(b.amount) as amountSum
  from Promotions a,Sales b
 where b.saledate between a.startDate and a.endDate
group by a.Promo,a.startDate,a.endDate,b.clerk
having sum(b.amount) >= all(select sum(c.amount)
                              from Sales c
                             where c.saledate between a.startDate and a.endDate
                            group by c.clerk)
order by Promo;

解説

group byを記述すると、
select句に縛りがかかりますが、
having句にも同様の縛りがかかります。
もちろん、having句での相関サブクエリにも、同様の縛りがかかります。

分析関数を使わない方法2では、このことをふまえてます

---

group by句

---