postgreSQLで、あるデータがあるとき、他のデータもまとめて取ってきたい
■目的
タイトルだけでは解らないので具体例を挙げていきます。
テーブル(洋菓子、和菓子、人名)のデータがあるとき、
人名が「多田」の「洋菓子、和菓子(例:クレープ、えびせんべい)」のセットがあれば、
他の、人名が異なる「洋菓子、和菓子(クレープ、えびせんべい)」のセットも取ってきたい、
でも、「洋菓子、和菓子(例:クッキー、麩菓子)」のセットが「多田」しか無いときは取ってきたくない。
という場合の実装方法メモです。
■参考
重複している列Aを含む全列取得
https://qiita.com/necoyama3/items/4c24defd6f504366aebe
IN句で複数条件指定
http://blog.fusic.co.jp/archives/1765/
結合で迷ったら(例充実)
http://sak.cool.coocan.jp/w_sak3/doc/sysbrd/psql_k08.htm
重複レコードを削除
https://kanamelogic.com/20180407/redundant-data2/
■実装
CREATE TABLE okashi (yougashi text, wagashi text, name text);
データ投入
INSERT INTO okashi VALUES ('クッキー', '温泉饅頭', '沼田'); INSERT INTO okashi VALUES ('クッキー', 'ゆべし', '沼田'); INSERT INTO okashi VALUES ('ショートケーキ', '温泉饅頭', '葛城'); INSERT INTO okashi VALUES ('マカロン', 'みたらし団子', '大津'); INSERT INTO okashi VALUES ('マカロン', 'ゆべし', '葛城'); INSERT INTO okashi VALUES ('チョコケーキ', '桜餅', '石田'); INSERT INTO okashi VALUES ('チョコケーキ', '桜餅', '大津'); INSERT INTO okashi VALUES ('チョコケーキ', '桜餅', '安藤'); INSERT INTO okashi VALUES ('クレープ', 'えびせんべい', '多田'); INSERT INTO okashi VALUES ('クレープ', 'えびせんべい', '多田'); INSERT INTO okashi VALUES ('チョコケーキ', '桜餅', '多田');
期待する結果
人名が「多田」の時、
('チョコケーキ', '桜餅', '石田');
('チョコケーキ', '桜餅', '大津');
('チョコケーキ', '桜餅', '安藤');
('チョコケーキ', '桜餅', '多田');
('クレープ', 'えびせんべい', '多田');
('クレープ', 'えびせんべい', '多田');
準備
CREATE TABLE okashi_only (yougashi text, wagashi text, name text); CREATE TABLE okashi_next (yougashi text, wagashi text, name text);
①人名「多田」を含むレコードを抽出し、テーブル「okashi_only」に挿入する
INSERT INTO okashi_only (yougashi, wagashi, name) SELECT * FROM okashi WHERE name LIKE '%多田%' AND (yougashi, wagashi) IN ( SELECT yougashi, wagashi FROM okashi GROUP BY yougashi, wagashi HAVING COUNT(yougashi) >= 2 AND COUNT(wagashi) >= 2 ); select * FROM okashi_only; クレープ | えびせんべい | 多田 クレープ | えびせんべい | 多田 チョコケーキ | 桜餅 | 多田
テーブル「okashi_only」に挿入したいので、INSERT INTOから始まります。
今回は都合でname LIKEを使っていますがname = '多田'で充分です。
多田の他に、多田と同じ洋菓子、和菓子の組み合わせを持つレコードがあるもののみ抽出したいので、(yougashi, wagashi)についてIN句を用います。
IN句の中について、yougashi, wagashiの2つについてGROUP BYし、HAVING句でCOUNTし、2以上の物を取ってきています。
②洋菓子と和菓子の組み合わせを一意に集約します。
これは、このままだと「クレープ、えびせんべい」について、③を実行したとき、この組み合わせを持つログを2回取ってきてしまうためです。
DELETE FROM okashi_only oo1 WHERE EXISTS ( SELECT * FROM okashi_only oo2 WHERE oo2.yougashi = oo1.yougashi AND oo2.wagashi = oo1.wagashi AND oo2.ctid > oo1.ctid ); select * FROM okashi_only; クレープ | えびせんべい | 多田 チョコケーキ | 桜餅 | 多田
レコードを削除するのでDELETEから始まります。
イメージとしては、okashi_onlyテーブルと全く同じテーブルをイメージして、比較して、yougashiが同じかつwagashiが同じものが存在したら削除する、という感じでしょうか。
ctidは、テーブル内における、行バージョンの物理的位置を表します(updateやvacuumで変化するので注意)。
③okashi_onlyテーブルのyougashiとwagashiの組み合わせが一致するログをokashiテーブルから抽出し、データをokashi_nextテーブルに挿入する
INSERT INTO okashi_next (yougashi, wagashi, name) SELECT okashi.yougashi, okashi.wagashi, okashi.name FROM okashi_only, okashi WHERE okashi.yougashi = okashi_only.yougashi AND okashi.wagashi = okashi_only.wagashi ; select * FROM okashi_next; クレープ | えびせんべい | 多田 クレープ | えびせんべい | 多田 チョコケーキ | 桜餅 | 多田 チョコケーキ | 桜餅 | 石田 チョコケーキ | 桜餅 | 大津 チョコケーキ | 桜餅 | 安藤
以上で期待する結果が得られました。
おまけ
手順②で重複排除せず③を行った場合の結果
=# SELECT * FROM okashi_next ; クレープ | えびせんべい | 多田 クレープ | えびせんべい | 多田 クレープ | えびせんべい | 多田 クレープ | えびせんべい | 多田 チョコケーキ | 桜餅 | 多田 チョコケーキ | 桜餅 | 石田 チョコケーキ | 桜餅 | 大津 チョコケーキ | 桜餅 | 安藤