SQL inner join on句節の中での条件分岐について

#背景

いま、とあるＡＩ分類アプリケーション開発をやっています。
仕様としては、階層構造になったラベルデータがあり、それを群管理せるために、
子ラベルを親ラベルに振り替える、いわゆる名寄せクエリを検討しています。

#テーブル　nayose_table

nayose_tableはH10→H20→H30→H40というように、
親子関係になっていて、N10,N20,N30,N40には、別のクエリで作成した、
名寄せラベルが入っています。

例えばですが、

H40にあるZCSXXというラベルは、本来はH10のYYZに属していますが、

分類時にYYZというクラスターから分離したいために、
N10には、YYZ/ZD/KDNXX/ZCSXXというラベルに付け替えしています。

H40にあるZAAATQ,ZBBBTQというラベルは、性質が近いために、

分類時にはKDNXXという親のラベルに名寄せしており、
N10には、YYZ/ZD/KDNXXというラベルに付け替えしています。

H30にあるSSSXX,UUUXXというラベルは、性質が近いために、

分類時にはZDという親のラベルに名寄せしており、
N10には、YYZ/ZDというラベルに付け替えしています。

id	H10	H20	H30	H40	N10	　　　　　       N20　　　	 　　　　　　N30	     　　 N40
7	YYZ	ZD	KDNXX	ZCSXX	YYZ/ZD/KDNXX/ZCSXX	ZD/KDNXX/ZCSXX	 　　 KDNXX/ZCSXX  　　ZCSXX	
8	YYZ	ZD	KDNXX	ZCSOTQ	YYZ/ZD/KDNXX/ZCSOTQ	ZD/KDNXX/ZCSOTQ	 　KDNXX/ZCSOTQ 　　ZCSOTQ	
9	YYZ	ZD	KDNXX	ZAAATQ	YYZ/ZD/KDNXX　　　	ZD/KDNXX	 　　　　　 　KDNXX　　　　 　　ZAAATQ
10	YYZ	ZD	KDNXX	ZBBBTQ	YYZ/ZD/KDNXX　　　	ZD/KDNXX	 　　　　　　KDNXX　　　　 　　ZBBBTQ
11	YYZ	ZD	SSSXX	FDSFDS	YYZ/ZD/      　　	ZD	 　　     　　　　SSSXX	          FDSFDS
12	YYZ	ZD	UUUXX	LLKKKD	YYZ/ZD/　　      	ZD	      　　　　　　UUUXX	          LLKKKD

#テーブル　pm_master

マスターテーブルは、valueにラベルが入っていますが、
基本的にはH10，H20,　h30,　H40の、どの階層に属するラベルが入るか
決まっていません。

id   value  　　　　　　　　　　　　　　　　出したいイメージ　　　　　現状
1   ZCSXX  ----->　H40に属するラベル　　　YYZ/ZD/KDNXX/ZCSXX　　　　YYZ/ZD/KDNXX/ZCSXX　　　-->　〇
2   ZCSOTQ  ----->　H40に属するラベル　　 YYZ/ZD/KDNXX/ZCSOTQ　　　 YYZ/ZD/KDNXX/ZCSOTQ　　-->　〇　
3   KDNXX  ----->　H30 に属するラベル     YYZ/ZD/KDNXX　　　　　　　YYZ/ZD/KDNXX/ZCSXX　　　-->　×
4   ZD      ----->　H20 に属するラベル    YYZ/ZD　　　　　　　　　　YYZ/ZD/KDNXX/ZCSXX　　　-->　×

#クエリ

N10に名寄せしたい場合に下記のクエリを書いています。

今のところH10,H20,H30,H40の何れかに合致したものを選んでおり、
重複除外のためGROUP_BYしていますが、ID順で最初に出てくるものに
纏められてしまいます。

sql
1                        SELECT  palcom_master.*,
2                        nayose_table.N10
3
4                FROM db_palcom.palcom_master 
5                inner join 
6                        nayose_table
7                on 
8                   (db_palcom.palcom_master.value like nayose_table.H10 || ',%') or
9                   (db_palcom.palcom_master.value like nayose_table.H20 || ',%') or
10                   (db_palcom.palcom_master.value like nayose_table.H30 || ',%') or
11                   (db_palcom.palcom_master.value like nayose_table.H40 || ',%')
12                
13                GROUP BY palcom_master.id

#　やりたいこと

以下のように階層の下から、順番にサーチしていくような、クエリにできないものでしょうか？

H40でマッチング　　あり　→　ok
　　　　　　　　　　なし　→　H30でマッチング　→　あり　→　ok
　　　　　　　　　　なし　→　H20でマッチング　→　あり　→　ok
　　　　　　　　　　なし　→　H10でマッチング

#補足

最初はINSTR関数で、N10のラベルを'/'でSPLITして、文字列を直接探す方法にしていましたが、
これだと時間がかかりすぎるので、検索はあくまでH10～H40のラベルで実施したいです。

neko_the_shadow

2021/01/27 10:05

たとえばpm_master.valueがKDNXXの場合、単純にJOINすると、nayose_table.idの値が7,8,9,10の行とマッチします。「出したいイメージ」ではこのときYYZ/ZD/KDNXXであるべきだとしていますが、YYZ/ZD/KDNXX/ZCSXX(nayose_table.id=7)やYYZ/ZD/KDNXX/ZCSOTQ(nayose_table.id=8)ではいけない理由というのは何でしょうか?

yuujiMotoki

2021/01/27 11:02 編集

KDNXXはh30のラベルであり、これはZCSXXやZCSOTQのH40で指定されたクラスタより粒度が荒いクラスタです。同じKDNXXの中でもクラスタの性質によって、nayose_table作成クエリで、分類の粒度をユーザが可変できる仕様になっています。

neko_the_shadow

2021/01/27 14:44

質問を変えてみます。たとえばpm_master.valueがKDNXXの場合、nayose_table.idの値が7,8,9,10の行とマッチします。このときN10の値はYYZ/ZD/KDNXX/ZCSXX、YYZ/ZD/KDNXX/ZCSOTQ、YYZ/ZD/KDNXXの3通りです。このうちN10の値としてYYZ/ZD/KDNXXを選択するビジネスロジックはどのようなものでしょうか?

yuujiMotoki

2021/01/28 00:21 編集

少し別な例で概念を説明します　果物を例にしてみます ①果物/青森産/リンゴ/つがるリンゴ　　データ数　１００ ②果物/青森産/リンゴ/しなのリンゴ　　データ数　１００ ③果物/青森産/リンゴ/無名リンゴA　　データ数　３０ ④果物/青森産/リンゴ/無名リンゴB　　データ数　３０上記のように青森産リンゴの中でも、銘柄によっては、生産者、生産量などで特徴が出しやすいものと、そうでない少数派が混在している状況があります。 AI的に言えば、①②はデータ数が多く特徴をハッキリ出しやすいですが、③④についてはデータ数が少なく、裏を返せば、少数派という共通の特徴を有しています　このため③④は上層の分類である青森産のリンゴに名寄せしてしまった方が教師データとしてクラスタを形成しやすくなるからです。　ビジネスロジック的には　果物/青森産/リンゴ/というラベルには、①②は含まれないということを暗黙には明示していることになります

行動規範の内容に同意します

回答3件

アドバイス頂いた内容で下記のようなＳＱＬにしました
CODE列（Nの種別で40→10になるように）でソーティングするサブクエリを入れて、
つぎにGROUP BYで、ID列で重複を無くすようにしています。

多分ソーティングされた後、若い方のデータに限定して出力されると勝手に思っています。

sql
1
2select * from(
3select p.*,n.N10,n.N20,n.N30,n.N40,'H40' as code from pm_master as p inner join nayose_table as n on n.H40 = p.value
4UNION
5select p.*,n.N10,n.N20,n.N30,NULL,'H30' from pm_master as p inner join nayose_table as n on n.H30 = p.value
6UNION
7select p.*,n.N10,n.N20,NULL,NULL,'H20' from pm_master as p inner join nayose_table as n on n.H20 = p.value
8UNION
9select p.*,n.N10,NULL,NULL,NULL,'H10' from pm_master as p inner join nayose_table as n on n.H10 = p.value
10order by code DESC) group by id;
11

投稿2021/02/02 09:09

yuujiMotoki

総合スコア90

ベストアンサー

UNIONを使うと、nayose_tableのどのカラムとマッチしたかをうまく取得できます。

SQL
1SELECT p.*, n.n10, 'h10' FROM pm_master AS p JOIN nayose_table AS n ON p.value = n.h10
2UNION    
3SELECT p.*, n.n10, 'h20' FROM pm_master AS p JOIN nayose_table AS n ON p.value = n.h20
4UNION    
5SELECT p.*, n.n10, 'h30' FROM pm_master AS p JOIN nayose_table AS n ON p.value = n.h30
6UNION    
7SELECT p.*, n.n10, 'h40' FROM pm_master AS p JOIN nayose_table AS n ON p.value = n.h40

このSQLを実行すると、以下のような結果が取得できます。あとは要件にしたがって、idに対するvalueやn10を一意に定めればよいかと思います。

id	value	n10	マッチしたカラム
1	ZCSXX	YYZ/ZD/KDNXX/ZCSXX	h40
2	ZCSOTQ	YYZ/ZD/KDNXX/ZCSOTQ	h40
3	KDNXX	YYZ/ZD/KDNXX/ZCSOTQ	h30
3	KDNXX	YYZ/ZD/KDNXX/ZCSXX	h30
3	KDNXX	YYZ/ZD/KDNXX	h30
4	ZD	YYZ/ZD/KDNXX	h20
4	ZD	YYZ/ZD/KDNXX/ZCSOTQ	h20
4	ZD	YYZ/ZD/KDNXX/ZCSXX	h20
4	ZD	YYZ/ZD/	h20