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

Posted at 2024-12-05

概要

SQLパズル　p178 42 魚のサンプリング調査の解法２の学習メモです。
innerjoin とグループ化が理解の肝すね。

要件

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 (2, 'fresh water', 1);
INSERT INTO SampleGroups VALUES (2, 'fresh water', 3);
INSERT INTO SampleGroups VALUES (2, 'fresh water', 2);

答えのSQL

-- 42 魚のサンプリング調査　その２
SELECT fish_name,SUM(found_tally) /
                (SELECT COUNT(sample_id)
                   FROM SampleGroups
                WHERE group_id = 1) AS X
FROM Samples SA
    INNER JOIN
	SampleGroups SG
	ON SA.sample_id = SG.sample_id
	WHERE fish_name = 'pike'
		And group_id = 1
		GROUP BY fish_name
	;

--　inner JOIN部分を確認する
SELECT *
FROM Samples SA
    INNER JOIN
	SampleGroups SG
	ON SA.sample_id = SG.sample_id
	;
--　fish_nameに対して部分集合を作る（GROUP句）
-- group_id fish_name毎の 、回数(x_bunbo)、取得匹数（x_bunshi）をSELECT句に加える
SELECT fish_name
	,SUM(found_tally) AS X_bunshi
	,(SELECT COUNT(sample_id)
                   FROM SampleGroups
                WHERE group_id = 1) AS X_bunbo
	,SUM(found_tally) /
                (SELECT COUNT(sample_id)
                   FROM SampleGroups
                WHERE group_id = 1) AS X
FROM Samples SA
    INNER JOIN
	SampleGroups SG
	ON SA.sample_id = SG.sample_id
	WHERE 
	-- group_idを指定しないとSUM(found_tally)が他のgroup_idの値も含めてしまうため。
	group_id = 1
	-- And fish_name = 'pike'
	GROUP BY fish_name
	;



-- 参考
-- WHERE句で結合しても　InnerJoinと同じ結果になる
SELECT *
FROM Samples SA,SampleGroups SG
	WHERE SA.sample_id = SG.sample_id
	;