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

Posted at 2024-12-06

概要

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

要件

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

SQL

DDL

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

-- 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')
	;