はじめに
仕事中の息抜きや、暇つぶし用にちょっとしたSQLクイズをどうぞ。
データの説明
basketsテーブルに以下のデータが入っています。
basket_numberは籠の番号で、籠ごとに、中に入っている果物の数だけレコードが存在します。
| basket_number | fruit |
|---|---|
| 1 | りんご |
| 1 | バナナ |
| 1 | ぶどう |
| 2 | バナナ |
| 2 | いちご |
| 2 | メロン |
| 3 | りんご |
| 3 | ぶどう |
| 4 | りんご |
| 4 | バナナ |
| 4 | メロン |
| 4 | ぶどう |
| 5 | バナナ |
| 5 | メロン |
| 5 | ぶどう |
問題
中に（りんご、バナナ、ぶどう）の組み合わせで果物が入っている籠の番号を取得するSQLを書いてください。
前提事項
- ちょうど（りんご、バナナ、ぶどう）の組み合わせが入っている籠を対象とします。（りんご、バナナ、メロン、ぶどう）のように余計な果物が入っている対象外です。
制約事項
- できるだけ標準SQLベースで書いてください。
- できるだけDISTINCTは使わないでください。
期待結果
| basket_number |
|---|
| 1 |
DDL/DML
ローカル環境やDB Fiddleなどの環境で以下のSQLを流してください。
（動作確認は、DB Fiddle上のPostgreSQL 11で行いました）
DB Fiddleで試す場合、左側のSchema SQLというペインに下記のSQLをコピー&ペーストし、右側のQuery SQLというペインにクエリを記述します。画面上部ナビバーのRunをクリックすると結果が表示されます。
CREATE TABLE baskets (
basket_number integer,
fruit varchar(50)
);
INSERT INTO baskets VALUES (1, 'りんご');
INSERT INTO baskets VALUES (1, 'バナナ');
INSERT INTO baskets VALUES (1, 'ぶどう');
INSERT INTO baskets VALUES (2, 'バナナ');
INSERT INTO baskets VALUES (2, 'いちご');
INSERT INTO baskets VALUES (2, 'メロン');
INSERT INTO baskets VALUES (3, 'りんご');
INSERT INTO baskets VALUES (3, 'ぶどう');
INSERT INTO baskets VALUES (4, 'りんご');
INSERT INTO baskets VALUES (4, 'バナナ');
INSERT INTO baskets VALUES (4, 'メロン');
INSERT INTO baskets VALUES (4, 'ぶどう');
INSERT INTO baskets VALUES (5, 'バナナ');
INSERT INTO baskets VALUES (5, 'メロン');
INSERT INTO baskets VALUES (5, 'ぶどう');
解答例
SQLの実現方法は色々あると思いますが、以下に一例を示します。
select
basket_number
from
baskets
group by
basket_number
having
COUNT(1) = 3
and SUM(
case when fruit in (
'りんご', 'バナナ', 'ぶどう'
) then 1 else 0 end
) = 3
以下、上記SQLの解説となります。
今回のポイントは、列ではなく行方向のAND条件となることと、組み合わせがちょうど合致するものだけを抽出しないといけない点です。
行方向のAND条件対応
以下はSQL初心者がやりそうな(?)誤答例です。
select
basket_number
from
baskets
where
fruit = 'りんご' and fruit = 'バナナ' and fruit = 'ぶどう'
group by
basket_number;
fruit = 'りんご' and fruit = 'バナナ' and fruit = 'ぶどう' を同時に満たすレコードは存在しえません。
そこで、工夫が必要となります。basket_numberでGROUP BYしたグループの中において、fruitが'りんご', 'バナナ', 'ぶどう'のいずれかに合致した明細レコード数を数えて、それが3に等しければ'りんご', 'バナナ', 'ぶどう'を含むはずです。
group by
basket_number
having
SUM(
case when fruit in (
'りんご', 'バナナ', 'ぶどう'
) then 1 else 0 end
) = 3
組合せがちょうど合致するもの
先ほどの条件だと、'りんご', 'バナナ', 'メロン', 'ぶどう'が入っている4番の籠も条件に当てはまってしまいます。
これを除外するには、籠の中の果物の総数を見る必要があり、COUNT(1) = 3の条件を加えてあげます。
having
COUNT(1) = 3
and SUM(
case when fruit in (
'りんご', 'バナナ', 'ぶどう'
) then 1 else 0 end
) = 3
おわりに
暇つぶしになったでしょうか？
よい別解があったら是非コメント欄で教えてください！