二つの条件選択テーブルを参照しお互いの条件のマッチング率を算出してソートしたい

Question

実現したいこと

PHP&MySQLで求人企業と求職者のマッチングサイトを構築しています。
よくある絞り込み検索ではなく求人企業側と求職ユーザー側で設定した条件に一致している度合いでランク付けしたいのです。
絞り込まずにすべての求人情報に対しどの程度マッチしているかを表示しその度合いでソートしたいと言う事です。
例えば条件の選択肢が100件あったとして全てマッチしていれば100、半分だったら50などのように
数値を算出しソートしたいのです。
いいアイデアが浮かばず困っていますのでご教授お願いいたします。

例えば条件の選択肢として（WEB側でチェックボックスで登録）
地域：東京、埼玉、千葉、神奈川
業種：和食店、洋食店、中華店、アジア
が有った場合「求人企業マッチングテーブル」と「求人企業マッチングテーブル」に下記のようにデータが登録されます。

求人企業マッチングテーブル

企業ID	区分	選 択
１	業種	和食店
１	業種	洋食店
１	地域	東京
２	業種	中華店
２	地域	神奈川
３	業種	和食店
３	業種	洋食店
３	業種	中華店
３	業種	アジア
３	地域	東京
３	地域	千葉
３	地域	神奈川

求職者マッチングテーブル

求職ID	区分	選 択
１	業種	和食店
１	業種	洋食店
１	業種	中華店
１	地域	東京
１	地域	千葉
１	地域	神奈川
２	業種	洋食店
２	業種	中華店
２	地域	東京

この時
下記のようなデータを作りたいです

求職ID	企業ID	選択数	マッチ	マッチ率
１	１	６	３	５０
１	２	６	２	３３
１	３	６	６	１００
２	１	３	２	６７
２	２	３	１	３３
２	３	３	３	１００

で求職者マイページで求人情報一覧を見たとき
下記のような順番でソートしたいのです。

求職ID１の場合

求職ID	企業ID	選択数	マッチ	マッチ率
１	３	６	６	１００
１	１	６	３	５０
１	２	６	２	３３

求職ID２の場合

求職ID	企業ID	選択数	マッチ	マッチ率
２	３	３	３	１００
２	１	３	２	６７
２	２	３	１	３３

DB：MySQL5.7
言語：PHP7.2

Comments

[Orlofsky]

変なテーブル設計ですね。
カラムの 区分 と 選択 ではなくて、業種 と 地域 にすればSQLはシンプルになるかと。

Answer 1

たとえば

create table company(cid int,category varchar(10),selection varchar(10));
insert into company values
(1,'業種','和食店'),
(1,'業種','洋食店'),
(1,'地域','東京'),
(2,'業種','中華店'),
(2,'地域','神奈川'),
(3,'業種','和食店'),
(3,'業種','洋食店'),
(3,'業種','中華店'),
(3,'業種','アジア'),
(3,'地域','東京'),
(3,'地域','千葉'),
(3,'地域','神奈川');

create table user(uid int,category varchar(10),selection varchar(10));
insert into user values
(1,'業種','和食店'),
(1,'業種','洋食店'),
(1,'業種','中華店'),
(1,'地域','東京 '),
(1,'地域','千葉 '),
(1,'地域','神奈川'),
(2,'業種','洋食店'),
(2,'業種','中華店'),
(2,'地域','東京');

• 集計

select uid,cid
,(select count(*) from user where uid=t1.uid) as u_select 
,count(*) as c_match
,round(count(*)/(select count(*) from user where uid=t1.uid)*100,0) as ratio
from user as t1
left join company as t2
on t1.category=t2.category and t1.selection=t2.selection
group by uid,cid

ソートはratioをorder by でdescしてください

Comments

[yambejp]

普通、業種や地域はorでヒットさせるんじゃないかと・・・

Answer 2

「絞り込まずにすべての求人情報に対しどの程度マッチしているかを表示」
とのことなので、マッチ率０％も表示したいってことですかね。

であれば、その場合、yambejp様のSQLに、以下を追加。

create table m_company(cid int);
insert into m_company values
(1),(2),(3);

create table m_user(uid int);
insert into m_user values
(1),(2);

そして、以下のようなSQL。

SELECT	V1.uid
	,	V1.cid
    ,	V1.u_select
    ,	V1.c_match
    ,	ROUND( ( V1.c_match / V1.u_select ) * 100 ) as rate
FROM	(
			SELECT	T1.uid
				,	T2.cid
				,	(
						select	count('X')
						from	user T3
						where	T3.uid	= T1.uid
					) as u_select
				,	(
						SELECT	count('X')
                        FROM	user T4
									LEFT JOIN company T5 ON(
											T5.category		= T4.category
                                        AND	T5.selection	= T4.selection
                                    )
						WHERE	T4.uid	= T1.uid
							AND	T5.cid	= T2.cid
					) as c_match
			FROM	m_user T1
				,	m_company T2
		) V1
ORDER BY V1.uid, ( V1.c_match / V1.u_select ) desc
;