SQLパズル　p178  42 魚のサンプリング調査の 解法3の学習メモ

# 概要
SQLパズル　p178  42 魚のサンプリング調査の 解法3の学習メモです。
COALESCE式の挙動を理解するのに手間取りました。
大枠はよくあるグループ化と集約関数系の問題です。


# 要件
group_id、fish_name毎の平均取得数【匹/回】を取得したい。
今回の場合だと
group_id=1 fish_name='pike'の平均取得数を取得したい。


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/aa6c5766-0a4e-4127-eb39-dc38557f3dda.png)

# SQL

## DDL
```sql
DROP TABLE IF EXISTS Samples;
CREATE TABLE Samples
(sample_id INTEGER NOT NULL,
 fish_name CHAR(20) NOT NULL,
 found_tally INTEGER NOT NULL,
    PRIMARY KEY (sample_id, fish_name));


DROP TABLE IF EXISTS SampleGroups;
CREATE TABLE SampleGroups
(group_id INTEGER NOT NULL,
 group_descr CHAR(20) NOT NULL,
 sample_id INTEGER NOT NULL,
    PRIMARY KEY (group_id, sample_id));

INSERT INTO Samples VALUES (1, 'minnow', 18);
INSERT INTO Samples VALUES (1, 'pike', 7);
INSERT INTO Samples VALUES (2, 'pike', 4);
INSERT INTO Samples VALUES (2, 'carp', 3);
INSERT INTO Samples VALUES (3, 'carp', 9);

INSERT INTO SampleGroups VALUES (1, 'muddy water', 1);
INSERT INTO SampleGroups VALUES (1, 'muddy water', 2);
INSERT INTO SampleGroups VALUES (1, 'muddy water', 3);
INSERT INTO SampleGroups VALUES (2, 'fresh water', 1);
INSERT INTO SampleGroups VALUES (2, 'fresh water', 3);
INSERT INTO SampleGroups VALUES (2, 'fresh water', 2);


DROP TABLE IF EXISTS Samples_clone;
CREATE TABLE Samples_clone
(sample_id INTEGER NOT NULL,
 fish_name CHAR(20),
 found_tally INTEGER
);
INSERT INTO Samples_clone VALUES (1, 'minnow', 18);
INSERT INTO Samples_clone VALUES (1, 'pike', 7);
INSERT INTO Samples_clone VALUES (2, 'pike', 4);
INSERT INTO Samples_clone VALUES (2, 'carp', 3);
INSERT INTO Samples_clone VALUES (4, null, null);
INSERT INTO Samples_clone VALUES (4, null, null);
INSERT INTO Samples_clone VALUES (4, 'pike', 9);
INSERT INTO Samples_clone VALUES (4, null, 3);
```

## 答えのSQL

```sql
-- 42 魚のサンプリング調査　その３
-- COALESCEを使った解法
-- COALESCEは一匹も取得できなかった場合に対処するために使われる
SELECT COALESCE(fish_name, 'pike'),
        AVG(COALESCE(found_tally,0))
        FROM Samples AS SA
	        RIGHT OUTER JOIN
			SampleGroups AS SG
			ON SA.sample_id = SG.sample_id
		AND SA.fish_name = 'pike'
		WHERE SG.group_id = 1
		GROUP BY COALESCE(fish_name,'pike')
;

-- 外部結合
-- ３回目は１匹も取得されていないのでSamplesのレコードがnullになる
SELECT *
FROM Samples AS SA
	RIGHT OUTER JOIN
	SampleGroups AS SG
	ON SA.sample_id = SG.sample_id
		AND SA.fish_name = 'pike'
	WHERE
	SG.group_id = 1
	;

--  fish_nameで部分集合を作り平均値を求める
-- ※fish_name=null のときは　'pike'の部分集合に含める。
-- 回答のSQLの処理を詳しく把握するためにSELECT句に追加する
SELECT COALESCE(fish_name,'pike'),count(*)
	,AVG(found_tally) AS NG_avg_no_COALESCE
	,AVG(COALESCE(found_tally,0)) AS OK_avg_no_COALESCE
FROM Samples AS SA
	RIGHT OUTER JOIN
	SampleGroups AS SG
	ON SA.sample_id = SG.sample_id
		AND SA.fish_name = 'pike'
	WHERE
	SG.group_id = 1
	GROUP BY COALESCE(fish_name,'pike')
;



--GROUP BY fish_name でグループ化した場合、部分集合がnullとpikeの２つができてしまい
-- 正しい平均取得数を取得できない
SELECT fish_name,count(*)
	,AVG(found_tally) AS NG_avg_no_COALESCE
	,AVG(COALESCE(found_tally,0)) AS OK_avg_no_COALESCE
FROM Samples AS SA
	RIGHT OUTER JOIN
	SampleGroups AS SG
	ON SA.sample_id = SG.sample_id
		AND SA.fish_name = 'pike'
	WHERE
	SG.group_id = 1
	GROUP BY fish_name
;




-- COALESCEの動きを確認する用
-- 今回初めて見るものなので簡単に確認
SELECT COALESCE(fish_name,'nulldesu'),count(*)
FROM Samples_clone
GROUP BY COALESCE(fish_name,'nulldesu')
	;
	

```



![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/b14230ab-1d5f-1395-635e-c057a009c91e.png)


番外編：COALESCEの動きを確認する用

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/dfb83dc5-61b4-478b-ab26-39489daf8398.png)

# 参考

SQLパズル　p178

SQLパズル　サポートページ
https://mickindex.sakura.ne.jp/database/db_support_sqlpuzzle.html#LocalLink-p42

達人に学ぶSQL徹底指南書第２版 該当する問題なし

github差分
https://github.com/RYA234/SQL_Puzzle_Learning/commit/7063438271a2c938e8c41a6abbb2078a3c57e461