SQL カンマ区切りのデータに対して除外判定をしたい

Question

データ

下記のデータがあります。

goods_id	excluded_goods_id
1	1,5,10,11
2	1,5,10,11
11	1,5,10,11

実現したいこと

goods_idがexcluded_goods_idに含まれていれば、そのレコードを除外したいです。
このデータだとgoods_id=2のレコードだけが残るようにしたいです。
where句でどのように記載すればよろしいでしょうか。
ご教示の程、よろしくお願いいたします。

試したこと

下記のようにexcluded_goods_idのカンマを除いて、goods_idとlikeでヒットさせようとしましたが、1が1と11にヒットしてしまいうまくいきませんでした。
where 151011 like '%1%'

補足情報（FW/ツールのバージョンなど）

MySQLとBigQueryを使用しております。

Answer 1

正規化を無視したテーブル設計は砂上の楼閣です。

第1正規化で繰り返しを排除しましょう。関係データベースの正規化とは？

Comments

[sysder]

ご回答ありがとうございます。
goods_idとexcluded_goods_idは別のテーブルにあって、goods_idは購入ログ、excluded_goods_idのデータは企画担当者からいただくCSVデータになっています（両者を結びつけるキーはあります）。

[Orlofsky]

CSVをそのままテーブルにするのではなく、正規化したテーブルに分ければ済むことです。

大手SIerが作った正規化を無視した(汎用機のCOBOLのフォーマットをできるだけいじらずにOracle Databaseに移行した)テーブルでグループ関数すら使えないすざましい設計を見たことがあります。

Answer 2

mysqlならfind_in_setでしょう

create table tbl(id int primary key,goods_id int,excluded_goods_id varchar(30));
insert into tbl values
(1,1,'1,5,10,11'),
(2,2,'1,5,10,11'),
(3,11,'1,5,10,11');

select * from tbl where not find_in_set(goods_id,excluded_goods_id)

追記

json型をつかっていいならこの方が楽かもしれません

create table tbl(id int primary key,goods_id int,excluded_goods_id json);
insert into tbl values
(1,1,'[1,5,10,11]'),
(2,2,'[1,5,10,11]'),
(3,11,'[1,5,10,11]');

select * from tbl where not JSON_CONTAINS(excluded_goods_id,goods_id)

Comments

[sysder]

ご回答ありがとうございます。
こちらシンプルで良いですね。参考にさせていただきます。

[sysder]

json型のご回答もありがとうございます。
こちらも参考にさせていただきます。

Answer 3

SPLIT()とUNNEST()を使うとか、REGEXP_EXTRACT_ALLで正規表現での抽出とかで可能だと思います。
以下はSPLITを使用したイメージです。

select * from tbl
where not exists(
       select 1 from unnest(split(excluded_goods_id)) as item
       where item=goods_id
     )

Comments

[sysder]

ご回答ありがとうございます。
unnestをすると行が膨れそうですが、試してみます。

Answer 4

他の方の回答にもあるように正規化されることをつよくお勧めします。

が、とりあえず対処法としては、区切り文字も含めてlikeで部分一致判定すればいいでしょう。

where not "," & excluded_goods_id & "," like "%," & goods_id  & ",%"  

Comments

[sysder]

ご回答ありがとうございます。
なるほど。カンマも含めて検索させるのですね。これはMySQLでもBigQueryでも使えそうです。