نحوه تشخیص درصد تشابه دو عبارت

[Javad_raouf]

با سلام
توضیخ مختصر: در کل هدفم اینه که از روی عنوان مطالب تکراری تشخیص بدم یک چیزی تو مایه های سایت های خبری.
---------------
فرض کنید یک جدول دارم به نام tbl که شامل چندین رکورد است و شامل ستونی است به نام Title
همچنین عبارتی دارم به نام NewTitle
می خوام به ازای هر سطر از جدولم بفهمم فیلد Title چقدر شبیه به NewTitle است
نحوه تشخیص تشابه دو عبارت به این صورت است که باید بررسی کنیم که چند درصد از کلمات NewTitle در Title و چند درصد از کلمات Title در NewTitle وجود دارد. میانگین این دو درصد میشه جواب مورد نظر
مثلا درصد تشابه برای "سلام خوبی" با "خوبی سلام" صد در صد است:لبخندساده:
"سلام عزیزم" با "خوبی سلام" 50 درصد تشابه دارند
"سلام سلام عزیزم" با "سلام عزیزم" 100 درصد تشابه دارند:متفکر:
همانطور که می بینید الگوریتم یکم باگ دارد پس اگر الگوریتم بهتری هم به نظرتون میرسه لطفا دریغ نکنید:لبخندساده:
به نظر خودم باید اول یک تابع بنویسم که دو تا عبارت رو بگیره و درصد تشابه این دو عبارت رو برگردونه و در کوئری خودم از این تابع استفاده کنم مثل این:
Select *,dbo.GetDarsad(Title,@NewTitle) as Darsad From tbl
ولی برای نوشتن تابعش مشکل دارم البته می تونم با While بنویسم ولی در کل فکر می کنم در تعداد رکورد بالا سرعت رو بد جور بیاره پایین:خجالت:
لطفا اگر راه ساده تر یا اصولی تر می شناسید بیان کنید

[tooraj_azizi_1035]

سلام
100% از نظر شما زمانی است که تعداد کلمات و خود کلمات فارغ از ترتیب مانند هم باشند.
و درصد های دیگر هم نسبت کلمات برابر به کل کلمات است مثلاً 1 تقسیم بر 2 که معادل 50% هست.
شما قراره درصد رو به ازای همه رکوردها روی یک فیلد خاص بدست بیارید.
یا باید خودتون یک تابع T-SQL بنویسید یا در .NET بیایید یک CLR Function بنویسید:
public static float GetLikelihoodPercent( string phrase, string match)
{
string[] first=phrase.Split(' ');
string second[] = match.Split(' ');

if(first.Intersect(second).Count()==first.Count())
return 100;
else
{

int qtyEquals= first.Intersect(second).Count();

return qtyEquals *1.0 /first.Count();
}

}

توضیح:
phrase مقدار فیلد رو میگیره و match هم معادل همون NewTitle هست که قراره درصد تشابهش ر در جدول به ازای هر رکورد بدست بیارید.
ابتدا هر دو رو به کلمه می شکونیم و مرتب می کنیم و بعد با Intersect اشتراک را بدست آورده و اگر برابر تعداد المان های first باشد در این صورت 100% بازگشت داده می شود.
در غیر اینصورت تعداد اعضای مشترک دو مجموعه رو با Intersect بدست میاریم و تقسیم بر تعداد کل کلمات فیلد جاری می کنیم.


ایجاد قدم به قدم CLR Function:
CLR Scalar-Valued Functions (http://msdn.microsoft.com/en-us/library/ms131043.aspx)


Hope this helps.

[یوسف زالی]

البته با Soundex هم می شد یک کارایی کرد، اما خیلی دقیق نیست.
به نظر من هم بهتره که از روش تابعی استفاده بشه که دوستمون گفتند.

[محمد سلیم آبادی]

بر اساس گفته های شما ترتیب و تکرار کلمات در رشته اهمیت ندارد. یعنی "علی حسن" با "حسن حسن علی" صد در صد تطابق داره. برای بدست آوردن این در صد از فرمول زیر استفاده میکنیم
تعداد کلمات متشابه تقسیم بر تعدا کل کلمات ضرب در 100

فرض کنید الگوی ما "رضا حسن" هست و سه مقدار در جدولتان دارید به این شکل
1 - محمد رضا جواد علی
2 - رضا حسن علی
3 - حسن رضا

حالا میزان تطابق آن ها با الگو:
1 - 25%
2 - 66%
3 - 100%

و اما الگوریتم.
ابتدا نیاز هست که هر دو رشته را به کلمات تشکیل دهنده اش تجزیه کنیم. این کار را من توسط یک الگوریتم بازگشتی (recursive cte) همراه با یک TVF انجام داده ام. سپس کلمات الگو را با کلمات رشته join میکنیم تا اشتراکات بدست بیاید سپس بر اساس کد هر سطر گروه بندی میکنیم تا تعداد این اشتراکات بدست آید و در نهایت از فرمولی که ذکر کردم درصد مورد نظر حاصل میشود.

ابتدا این TVF را ایجاد کنید:
CREATE FUNCTION Splliters (@S VARCHAR(4000))
RETURNS TABLE
RETURN
WITH CTE AS
(
SELECT 1 rnk,
1 start,
CHARINDEX(' ', @s) - 1 ed

UNION ALL

SELECT rnk + 1,
ed + 2,
CHARINDEX(' ', @s, ed + 2) - 1
FROM CTE
WHERE CHARINDEX(' ', @s, ed + 2) > 0
)
SELECT rnk, SUBSTRING(@s, start, ed - start + 1) AS word
FROM CTE


سپس کوئری زیر را اجرا کنید. توجه داشته باشید که من برای تست یک جدول متغیری ایجاد کردم و سه سطر فوق الذکر را در آن درج کردم تا نتیجه را بررسی کنم. نیاز هست که نام جدول و سایر مقادیر را متناسب با نام جدولتان تغییر دهید.

declare @t table (id int ,v varchar(500))
insert @t values (1,'mohammd javad reza ali reza reza'),(2,'reza hasan ali'), (3,'hasan reza')



select t.id, count(a.word) * 1. / cnt * 100
from @t t
cross apply
(
select word , count(word) over(partition by id) as cnt
from dbo.Splliters(v+' ')
group by word
)a
inner join
(
select distinct word
from dbo.splliters('reza hasan ')
)b
on a.word = b.word
group by t.id, a.cnt

[Javad_raouf]

از دوستان متشکرم ولی اگه قرار بود تو C# بنویسم که مشکل نداشتم این کد باید در سمت SQL باشه
از شما آقای سلیم آبادی بسیار متشکرم
ولب فقط یک سوال دیگه دارم چون من می خوام این کار را علاوه بر روی Windows Application بر روی یک وبسایت مشابه هم پیاده سازی کنم می خواستم بدونم دستوراتی که نوشتید بدون نیاز به تغییر در MySQL هم جواب می ده؟

[محمد سلیم آبادی]

نه متاسفانه من از تمام امکانات خاص SQL Server برای بدست آوردن نتیجه کمک گرفتم.

[Javad_raouf]

پس لطفا این تاپیک رو هم مشاهده کنید:
نحوه تشخیص درصد تشابه دو عبارت (http://barnamenevis.org/showthread.php?412828-%D9%86%D8%AD%D9%88%D9%87-%D8%AA%D8%B4%D8%AE%DB%8C%D8%B5-%D8%AF%D8%B1%D8%B5%D8%AF-%D8%AA%D8%B4%D8%A7%D8%A8%D9%87-%D8%AF%D9%88-%D8%B9%D8%A8%D8%A7%D8%B1%D8%AA)

[cherchil_hra]

شما می توانید به جای اینکه رشته الگو و تمام سطرهای جدول رو به لغت تبدیل کنید، فقط یکبار از تابع spliters برای پیدا کردن لغت های رشته الگوتون استفاده کنید (حالا در اونجا می توانید از while هم استفاده کنید)

و از خروجی آن در دستورات زیر استفاده کنید:

SELECT t.title,
1.0 * SUM(CAST(CAST(CHARINDEX(' '+c.word+' ' , ' '+t.title+' ', 1) AS BIT) AS INT))
/ (LEN(t.title) -LEN(REPLACE(t.title, ' ', '')) + 1) * 100
FROM dbo.Splliters(جمله الگو) c
CROSS JOIN MyTable t
GROUP BY t.title


MyTable جدول عناوین شما
ضرب در 1.0 برای تبدیل خروجی به اعشار (تقسیم به صورت اعشاری حساب شود)

بدست آوردن تعداد کلمات:
(LEN(t.title) -LEN(REPLACE(t.title, ' ', '')) + 1)

پیدا کردن لغت در رشته مورد نظر:
SUM(CAST(CAST(CHARINDEX(' '+c.word+' ' , ' '+t.title+' ', 1) AS BIT) AS INT)

علت تبدیل به نوع bit برای بدست آوردن خروجی 1 یا صفر هست و برای استفاده از sum تبدیل به int می کنیم.
کلمات و جمله رو به اضافه ی ' ' (فاصله) می کنیم تا کلمه به کلمه جستجو بشود

در MYSQL بجای CHARINDEX می توانید از INTSTR (http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_instr) یا LOCATE (http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_locate) و بجای Len از char_length (http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_char-length) استفاده کنید.

موفق باشید.

[محمد سلیم آبادی]

بله. نیازی به تجزیه همزمان الگو و مقادیر موجود در رشته نیست.

برای بدست آوردن خروجی صفر و یا یک میشه از شرط CASE استفاده نمود چیزی شبیه به این (البته اگر خوانایی بیشتر مد نظر است):
SUM(CASE WHEN CHARINDEX(' '+c.word+' ' , ' '+t.title+' ') > 0 THEN 1 ELSE 0 END)

و صد البته معلوم است که قصد شما معرفی یک روش ابتکاری و حرفه ایست. خب پیشنهاد من استفاده از تابع SIGN است. در این حالت نیاز به دو CAST از بین میره، کد کوتاه تر میشه و خوانایی افزایش پیدا میکنه یعنی چیزی شبیه به این:
SUM(SIGN(CHARINDEX(' '+c.word+' ' , ' '+t.title+' ')))

می تونیم برای خلوت تر کردن عبارات جلوی ماده ی select بخشی از کار را به شرط join واگذار کنیم:
SELECT t.title,
1.0 * COUNT(word) / (LEN(t.title) -LEN(REPLACE(t.title, ' ', '')) + 1) * 100
FROM MyTable t
LEFT OUTER JOIN dbo.Splliters(@p) c
ON ' ' + t.title + ' ' LIKE '% ' + c.word + ' %'
GROUP BY t.title