【SQL練習】group byで複数カラムを指定した場合

題材

題材

平成29年春　データベーススペシャリスト試験　午前Ⅱ　問10

table

直積

select * from stock,shipping_result;

left outer join

select * from stock left outer join shipping_result on stock.code = shipping_result.code;

これは通常のleft outer joinなので、ONで指定した条件に基づいてshipping_resultの表がstockに結合されます。結合なので、上述した直積から条件に合致する値（codeが等しい行）を取り出しているだけです

問題のView

select stock.code, sum(shipping_amount), stock_amount
from stock left outer join shipping_result on stock.code = shipping_result.code
group by stock.code, stock_amount;

ここでのstock_amountの役割がよく分からないのと、2要素でgroup byする意味を理解できなかったことがまとめるきっかけです。

1 codeで集約した場合

select stock.code
from stock left outer join shipping_result on stock.code = shipping_result.code
group by stock.code;

まず当然ですが、stock.codeでのみgroup byし、これだけ表示すると重複のないcodeとなって表示されるのみ

2 codeで集約し集約関数を使用した場合

select stock.code, sum(shipping_amount)
from stock left outer join shipping_result on stock.code = shipping_result.code
group by stock.code;

sum(shipping_amount)を合わせて表示すると、sumに集約する行の合計値が表示されます。例えばS001であれば、100 + 50=150となっています。

3 stock_amountで集約した場合

select stock_amount
from stock left outer join shipping_result on stock.code = shipping_result.code
group by stock_amount;

この操作自体には意味はありません。なぜなら、たまたま在庫数が等しいレコードがあれば集約しているだけで、それが意味的に正しいかどうかは在庫数がそのような管理をされていない限りありえません。

4 codeとstock_amountの両方で集約した場合

select stock.code, stock_amount
from stock left outer join shipping_result on stock.code = shipping_result.code
group by stock.code, stock_amount;

stock.codeと合わせてstock_amountの両方で集約していますが、これでも意味はありません。なぜなら、商品コードに対して一意にstock_amountは決定されるためです。（主キーとは記載されていませんが、商品コードの意味上はそうなるべきでしょう）

そのため、この問題においてはここでのstock_amountの指定はただ単に stock_amount を列として表示させたいがために指定しているものになります。これはgroup byにおいては、集約する列と集約関数以外を表示できない制約から来ています。

group byで複数カラムを指定した場合

本問題では指定した2要素に完全従属性があったので有難みが分かりませんでしたが、group byで2要素A, Bを指定した場合、

(A1, B1), (A1, B2),,,,,

のように取りうる組み合わせで集約されます。一方の要素に集約されて片方の情報が消えることはありません。そのため、それぞれの値を取得したい場合に便利です。

分かりにくいので、仮にstockが下記のようなレコードになったとします。

この場合、以下のような結果が得られます。

select stock.code, stock_amount
from stock left outer join shipping_result on stock.code = shipping_result.code
group by stock_amount, stock.code;

S006は重複していますが、(S006, 300), (S006, 200)で異なるペアとして認識されるので集約されていません。一方で、(S002, 250)は同一なので、一つにレコードに集約されています。

table定義

記載しておくので練習用に自由にどうぞ

create table stock(
    code varchar,
    name varchar,
    stock_amount int
);

create table shipping_result
(
    code varchar,
    date varchar,
    shipping_amount int
);

insert into stock (code, name, stock_amount)values ('S001', 'A', 100);
insert into stock (code, name, stock_amount)values ('S002', 'B', 250);
insert into stock (code, name, stock_amount)values ('S003', 'C', 300);
insert into stock (code, name, stock_amount)values ('S004', 'D', 450);
insert into stock (code, name, stock_amount)values ('S005', 'E', 200);
--insert into stock (code, name, stock_amount)values ('S006', 'G', 300);
--insert into stock (code, name, stock_amount)values ('S002', 'H', 250);

insert into shipping_result (code, date, shipping_amount)values ('S001', '2017-03-01', 50);
insert into shipping_result (code, date, shipping_amount)values ('S003', '2017-03-05', 150);
insert into shipping_result (code, date, shipping_amount)values ('S001', '2017-03-10', 100);
insert into shipping_result (code, date, shipping_amount)values ('S005', '2017-03-15', 100);
insert into shipping_result (code, date, shipping_amount)values ('S005', '2017-03-20', 250);
insert into shipping_result (code, date, shipping_amount)values ('S003', '2017-03-25', 150);