質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.35%
SQLite

SQLiteはリレーショナルデータベース管理システムの1つで、サーバーではなくライブラリとして使用されている。

Q&A

解決済

3回答

734閲覧

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

yuujiMotoki

総合スコア90

SQLite

SQLiteはリレーショナルデータベース管理システムの1つで、サーバーではなくライブラリとして使用されている。

0グッド

1クリップ

投稿2021/01/27 02:55

編集2021/01/27 03:03

#背景

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

#テーブル 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のラベルで実施したいです。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

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 編集

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

回答3

0

アドバイス頂いた内容で下記のようなSQLにしました
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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

ベストアンサー

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を一意に定めればよいかと思います。

idvaluen10マッチしたカラム
1ZCSXXYYZ/ZD/KDNXX/ZCSXXh40
2ZCSOTQYYZ/ZD/KDNXX/ZCSOTQh40
3KDNXXYYZ/ZD/KDNXX/ZCSOTQh30
3KDNXXYYZ/ZD/KDNXX/ZCSXXh30
3KDNXXYYZ/ZD/KDNXXh30
4ZDYYZ/ZD/KDNXXh20
4ZDYYZ/ZD/KDNXX/ZCSOTQh20
4ZDYYZ/ZD/KDNXX/ZCSXXh20
4ZDYYZ/ZD/h20

投稿2021/01/28 12:38

neko_the_shadow

総合スコア2349

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yuujiMotoki

2021/01/28 14:33

ありがとうございます。別の方にも再帰の使い方を提案頂いていましたが、具体的に教えて頂きましたのでベストアンサーとさせて頂きました。
guest

0

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

以下参考
[SQL] 再帰クエリでツリー(木)構造を扱う

投稿2021/01/27 03:33

編集2021/01/27 03:37
sazi

総合スコア25327

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yuujiMotoki

2021/01/28 00:28 編集

再帰クエリを見てみたのですが、これはテーブルの中の同一カラムを使った自己参照の手法と思いました。今回は親子関係を示すデータが、別の列に配置されており、この再帰にはあてはめられないのかと思いました。もちろん階層構造自体を同一の列に配置するようにデータ構造を見直せばいいのかもしれませんが
sazi

2021/01/28 04:26 編集

再帰はunionを使用する関係上同構造に対する問い合わせが必要ですが、複数テーブルを結合したものでも良いですし、別なカラムをcase式などを用いて同じカラムになるようにすれば良いだけです。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.35%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問