9-24 平均が条件を満たす集合

SQLパズル

NewsStandsテーブル
standNo  stand_name
-------  ----------
      1  売店1
      2  売店2
      3  売店3
      4  売店4
      5  売店5
      6  売店6

Salesテーブル
standNo  products_ID  net_sold_qty
-------  -----------  ------------
      1         2667             0
      1         2667             0
      1         2667             1
      1        48632             5
      1         1107             5
      2         2667             1
      2        48632             1
      2         1107             6
      2        12345             4
      3         2667             3
      3        48632             1
      3         1107             4
      4         2667             3
      4        48632             4
      4         1107             4
      5         1107             9
      6         2667             3
      6        48632             1
      6        48632             4
      6        12345             4

NewsStandsテーブルのstandNoに紐づく
SalesテーブルのstandNoのグループで、
products_ID=2667のnet_sold_qtyの平均と、products_IDが48632のnet_sold_qtyの平均が2より大きい
または
products_ID=1107のnet_sold_qtyの平均が5より大きい
を満たす、stand_nameを出力する


出力結果
orderNo
-------
    300

SQLパズル(日本語版)のパズル22 [雑誌] を参考にさせていただきました
SQLパズル 第2版のパズル23 [雑誌と売店] を参考にさせていただきました

データ作成スクリプト

create table NewsStands(
standNo    number(1) primary key,
stand_name varchar2(10) not null);

insert into NewsStands values(1,'売店1');
insert into NewsStands values(2,'売店2');
insert into NewsStands values(3,'売店3');
insert into NewsStands values(4,'売店4');
insert into NewsStands values(5,'売店5');
insert into NewsStands values(6,'売店6');
insert into NewsStands values(7,'売店7');

create table Sales(
standNo      number(1) not null,
products_ID  number(5) not null,
net_sold_qty number(1) not null);

insert into Sales values(1, 2667,0);
insert into Sales values(1, 2667,0);
insert into Sales values(1, 2667,1);
insert into Sales values(1,48632,5);
insert into Sales values(1, 1107,5);
insert into Sales values(2, 2667,1);
insert into Sales values(2,48632,1);
insert into Sales values(2, 1107,6);
insert into Sales values(2,12345,4);
insert into Sales values(3, 2667,3);
insert into Sales values(3,48632,1);
insert into Sales values(3, 1107,4);
insert into Sales values(4, 2667,3);
insert into Sales values(4,48632,4);
insert into Sales values(4, 1107,4);
insert into Sales values(5, 1107,9);
insert into Sales values(6, 2667,3);
insert into Sales values(6,48632,1);
insert into Sales values(6,48632,4);
insert into Sales values(6,12345,4);
commit;

SQL

select a.stand_name
  from NewsStands a,Sales b
 where a.standNo = b.standNo
   and b.products_ID in(2667,48632,1107)
group by a.standNo,a.stand_name
having
2 < all(avg(decode(b.products_ID, 2667,b.net_sold_qty)),
        avg(decode(b.products_ID,48632,b.net_sold_qty)))
or 5 < avg(decode(b.products_ID,1107,b.net_sold_qty))
order by a.standNo;

解説

avg関数とdecode関数を組み合わせて、
任意のproducts_IDの、net_sold_qtyの平均を求めてます

---