SQL での重複項目検索

Question

前提・実現したいこと

SQLでの名前のあいまい検索で重複を探したいです。

バージョン

mysql
5.7.22

該当のソースコード

-- auto-generated definition
create table table_name
(
  id  int          null,
  sei varchar(100) null,
  mei varchar(100) null
)
charset = utf8mb4;


insert table_name (id, sei, mei) VALUE (1, '田中', '二郎');
insert table_name (id, sei, mei) VALUE (2, '田中', '二郎2');
insert table_name (id, sei, mei) VALUE (3, '田中', '二郎3');
insert table_name (id, sei, mei) VALUE (4, '田中', '二郎4');
insert table_name (id, sei, mei) VALUE (5, '高橋', '太郎');
insert table_name (id, sei, mei) VALUE (6, '高橋', '太郎2');
insert table_name (id, sei, mei) VALUE (7, '高橋', '太郎3');
insert table_name (id, sei, mei) VALUE (8, '高橋', '太郎4');
insert table_name (id, sei, mei) VALUE (9, '斎藤2', '一郎9');
insert table_name (id, sei, mei) VALUE (10, '斎藤2', '一郎');
insert table_name (id, sei, mei) VALUE (11, '斎藤2', '一郎2');
insert table_name (id, sei, mei) VALUE (12, '斎藤2', '一郎3');
insert table_name (id, sei, mei) VALUE (13, '佐藤2', '四郎');
insert table_name (id, sei, mei) VALUE (14, '佐藤2', '四郎2');
insert table_name (id, sei, mei) VALUE (15, '佐藤2', '四郎3');
insert table_name (id, sei, mei) VALUE (16, '佐藤2', '四郎4');
insert table_name (id, sei, mei) VALUE (17, '佐藤2', '四郎5');
insert table_name (id, sei, mei) VALUE (18, '佐藤2', '四郎6');




SELECT
  CONCAT(sei, mei) as fullname
FROM table_name
GROUP BY fullname
HAVING COUNT(*) > 1;

試したこと

SELECT
  CONCAT(sei, mei) as fullname,
  count(fullname) as count
FROM table_name
GROUP BY fullname
HAVING COUNT(*) > 1;

期待値

id	fullname	count
1	田中二郎	4
10	高橋2太郎	4
1	斎藤2一郎	4
10	佐藤2四郎	5

・条件

sei : が完全一致
mei : 前方一致

上記の名前名前苗字が18レコードあることを確認できるようにしたいです。

Comments

[m.ts10806]

想定しているDBの種類（MySQL、PostgreSQLなど）とバージョンを質問に追記してください。
（DBの種類についてはタグ追加願います）

[m2677024688]

mysql　5.7.22です。

[m.ts10806]

「あいまい検索」とのことですが、「こういうワードが来たらこういう内容を出したい」といった風にサンプルデータと期待する結果を具体的に書かれた方が良いと思います。
fullnameをlikeで検索してcount()ではダメなのか、とか。

[m2677024688]

できるのでしょうか？

[m.ts10806]

何かしら検索ワードを投入するわけではないということですね

[m2677024688]

はいそうなります。
あいまいで申し訳ありませんが、上記のような２レコードを出すことが目標です。

[m.ts10806]

できるかどうかはかなり捻りが必要には思います。
「あいまい検索」というワードは何かべつの表現に変えたほうが良いと思います。
大抵は何かしらキーワードが投入されることを期待します。

[m2677024688]

なるほどですね。
今回のしたいことは重複データで似た名前の人を全て抽出したいです。

[m.ts10806]

「似た」というと結構それこそあいまいになってしまいますね。
例示だと苗字だけ一致する人・・・？か名前＋苗字１文字目が一致　とかになるかもしれませんけど、
法則性を決める必要があると思います。
2名前苗字　だとどうなるかとか、こちらとしてはもっとパターンが欲しいですし、法則も詰められたほうが良いと思います。
プログラムは書いた通りにしか動かないので、仕様をしっかり決めて書かないとイレギュラーパターンで泣くことになります。

[m.ts10806]

あごめんなさい。下記ですね。
sei : が完全一致
mei : 部分一致

[m2677024688]

sqlでは限界がある気がしています。
プログラムで実行しようかと思っています。

[m.ts10806]

というよりmeiの「部分一致」がネックです。
前方一致、後方一致ならまだやりやすいかもしれませんが、「部分一致」となるとそれこそ「苗字だけ合ってればOK」ということにもなりえます。
ここは「どの程度の部分一致を許容するのか」の要件を詰めないとプログラム側でもどうにもできないと思います。

[m2677024688]

自分もそのように定義があいまいだったため、前方一致で進めることにしました。
良い方法はありますでしょうか？

[m.ts10806]

となると「どこまで許容するか」を詰めていく必要があります。
何文字まで合ってればよいのか
太郎と太朗は同じとするのか。太朗太と太郎太は同じとするのか・・とか。
ここは「決め」になってきますので、やはり仕様を詰めるしかないと思います。
近似値を求めるのは数字は割と楽ですが文字となるとかなり困難になると思います。

もっと言えば「これを実現して何がしたいのか」という大目的部分も詰める必要がありそうです。

Answer 1

やりたいことは、FULLTEXTのMATCH AGAINSTな気もしますが、
テーブル仕様が違うので、今回の条件内で、行う場合。

SELECT	T1.id
	,	T1.sei
    ,	T1.mei
    ,	(
			SELECT	COUNT( 'X' )
			FROM	table_name T3
			WHERE	T3.sei	= T1.sei
                AND	T3.mei	like concat( '%'
									,	ifnull(SUBSTR(T1.mei,1,1), '' )
                                    ,	 '%'
									,	ifnull(SUBSTR(T1.mei,2,1), '' )
                                    ,	 '%'
									,	ifnull(SUBSTR(T1.mei,3,1), '' )
                                    ,	 '%'
									,	ifnull(SUBSTR(T1.mei,4,1), '' )
                                    ,	 '%'
									,	ifnull(SUBSTR(T1.mei,5,1), '' )
                                    ,	 '%'
									,	ifnull(SUBSTR(T1.mei,6,1), '' )
                                    )
		) as cnt
FROM	table_name T1
WHERE	EXISTS(
			SELECT	'X'
			FROM	table_name T2
			WHERE	T2.id	<> T1.id
                AND	T2.sei	= T1.sei
                AND	T2.mei	like concat( '%'
									,	ifnull(SUBSTR(T1.mei,1,1), '' )
                                    ,	 '%'
									,	ifnull(SUBSTR(T1.mei,2,1), '' )
                                    ,	 '%'
									,	ifnull(SUBSTR(T1.mei,3,1), '' )
                                    ,	 '%'
									,	ifnull(SUBSTR(T1.mei,4,1), '' )
                                    ,	 '%'
									,	ifnull(SUBSTR(T1.mei,5,1), '' )
                                    ,	 '%'
									,	ifnull(SUBSTR(T1.mei,6,1), '' )
                                    )
			)

実行結果

id	sei	mei	cnt
1	田中	二郎	4
5	高橋	太郎	4
10	斎藤2	一郎	4
13	佐藤2	四郎	6

ちなみに、高橋2なんて結果は出ませんし、佐藤2四朗も6ですよね？
机上の期待値はよく確認してくださいね。

Answer 2

まず「名前苗字：9」「名前2苗字：9」にはならない。8:10です
またグループ処理をしたらどのidをひろうか指定しないとだめ
一番小さいのでよいのでしょうか？

select min(id) as id,concat(sei,'苗字') as fullname,count(*) as cnt from table_name
where mei like '苗字%'
group by fullname 
order by id

sample

• 元データ

create table tbl(
  id  int primary key,
  sei varchar(100) null,
  mei varchar(100) null
);
insert into tbl (id, sei, mei) VALUE
(1, '田中', '二郎'),
(2, '田中', '二郎2'),
(3, '田中', '二郎3'),
(4, '田中', '二郎4'),
(5, '高橋', '太郎'),
(6, '高橋', '太郎2'),
(7, '高橋', '太郎3'),
(8, '高橋', '太郎4'),
(9, '斎藤2', '一郎9'),
(10, '斎藤2', '一郎'),
(11, '斎藤2', '一郎2'),
(12, '斎藤2', '一郎3'),
(13, '斎藤2', '一'),
(14, '斎藤2', '一二三'),
(15, '斎藤2', '二郎');

• 抽出

select min(id) as id,concat(sei,substr(mei,1,1)) as fullname,count(*) as cnt from tbl
group by fullname 
order by id

Comments

[m2677024688]

上記のはサンプルの名前のため苗字%などが使えない状態です。

[yambejp]

一部とはなにかが多分理解できていないのでしょう

小-小山-小山田とあったとき小%と小山%では違う集計です
つまり質問者さんのやろうとしていることは机上の空論なので
SQLでやることではありません

もう少しいろんなパターンを含んだサンプルで考え方をまとめてから
質問されるとよいでしょう

[yambejp]

斎藤が「一郎」だとして
「一二三」がいたら「一」で一緒になるので一緒なの？
つまり１文字目だけあってればよいのでしょうか？

[m2677024688]

はい。
その認識であっております。

[yambejp]

じゃ、斎藤は「斎藤2一」なんですね？
sample確認ください