نهمین چالش SQL (جستجوی الگو + ترکیبات)

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

مقدمه
قبل از طرح مساله لازم هست یک مقدمه ای داشته باشیم.
فرض کنید رشته ی " I am a master of science student in Sharif University" را به عنوان ورودی به ما میدهند.
تمام ترکیبات یک کلمه ای، دو کلمه ای، .... تا n کلمه ای عبارت فوق به این شرط که جایگاه کلمه k ام در زیر رشته یکی بعد از جایگاه کلمه k-1 در عبارت باشد (به غیر از کلمه اول) بصورت زیر نمایش داده میشود:

97374

مثال 1 :حال اگر جمله ی "I am a student"را داشته باشیم میخواهیم بررسی کنیم که جمله با کدام یک از ترکیبات که بیشتین کلمه را دارد match میشود.
پاسخ:
که جواب برابر است یکبار match شدن با ترکیب I am a
حال باید بررسی کنیم که کلمات باقی با چه ترکیباتی match میشود که جواب برابر است با:یکبار با مچ شدن با ترکیب student.

مثال 2 :وضعیت match شدن جمله "I study English in a University,I like English"
جواب: دوبار مچ شدن با ترکیب I و یکبار مچ شدن با ترکیب University

مثال 3:وضعیت matching با جمله "book I am a master a master student in master student in" بر اساس رشته "I am a master of science student in Sharif University"
جواب:
یکبار مچ شدن با ترکیب I am a master(در اینجا زیر رشته مذکور رو از رشته اصلی حذف میکنیم و سپس به مرحله بعد می رویم)
یکبار مچ شدن با ترکیب a master (این زیر رشته را نیز مثل مرحله قبل از زیر رشته انتقال یافته به این مرحله حذف می کنیم)
دوبار مچ شدن با ترکیب student in
یکبار مچ شدن با ترکیب master

مساله:
به ما یک جدول که حاوی دو ستون یکی PK و دیگری دارای مقدار رشته ایست که در آن یکسری جملات درج شده اند می دهند. در کنار جدول یک مقدار رشته ای دیگر نیز به عنوان ورودی داده خواهد شد که باید جستجو و مقایسه بر اساس تمام ترکیبات آن رشته صورت گیرد.
خروجی مورد نظر به دست آوردن تعداد match شدن ها با ترکیبات رشته ورودی به ازای هر سطر از جدول می باشد.
مساله تماما بایستی توسط یک کوئری انجام شود(کوئری میتواند شامل چندین عبارت بازگشتی و غیره باشد)

97375

[cherchil_hra]

جدول مورد استفاده به نام MyValue و فیلدهای id و title
MyWords
ابتدا تمام لغات شناسایی شده و جدا می شوند (براساس space نه چیزه دیگری:بامزه:)

Result
نسبت دادن ID با استفاده از row_Number

mix
ترکیب حالت های مختلف طبق شرط گفته شده و براساس ID های ایجاد شده مرحله قبلی.
چون با یک id میشه تمام حالت ها رو پیدا کرد پس id=1 یا id=2 ...

mixAndID
ID دادن به سطرها براساس بیشترین تعداد کلمات و اولویت در جمله مطابق شرط. داده های جدول اول هم با شرط حذف می کنیم.

Compare
بررسی جدول مورد نظر با ترکیبات مختلف

بدست آوردن تعداد تغییرات :

LEN(REPLACE(replace(Temp,'$','*'),(SELECT ' ' + word+' ' FROM mixAndId WHERE id = Contour + 1),'$'))
- LEN(REPLACE(REPLACE(replace(Temp,'$','*'),(SELECT ' ' + word+' ' FROM mixAndId WHERE id = Contour + 1),'$'),'$','')) AS c





DECLARE @a VARCHAR(500) = ' I am a master of science student in Sharif University';


WITH MyWords AS(
SELECT 1 AS [StartPoint],
1 AS [EndPoint],
@a AS word
UNION ALL

SELECT CASE SUBSTRING(@a, [EndPoint], 1)
WHEN ' ' THEN [EndPoint] + 1
ELSE [StartPoint]
END AS [StartPoint],
[EndPoint] + 1,
CASE SUBSTRING(@a, [EndPoint], 1)
WHEN ' ' THEN LTRIM(RTRIM(SUBSTRING(@a, [StartPoint], [EndPoint] -[StartPoint])) )
ELSE ''
END AS word
FROM MyWords
WHERE [EndPoint] <= LEN(@a) + 1
)
,Result AS (
SELECT ROW_NUMBER() OVER(ORDER BY [StartPoint]) ID,
word
FROM MyWords
WHERE word <> ''
AND NOT([StartPoint] = 1 AND [ENDPOINT] = 1)
)
,countWord AS( SELECT COUNT(*) c FROM result )
,mix AS (
SELECT 1 AS StartID,
0 AS EndID,
ID,
1 AS [count],
word
FROM Result

UNION ALL

SELECT CASE
WHEN EndID + 1 = (SELECT c FROM countWord) THEN startID + 1
ELSE startID
END AS StartID,
CASE
WHEN EndID + 1 = (SELECT c FROM countWord ) THEN 1
ELSE EndID + 1
END AS [EndID],
ID,
(endid + 1) -(startid) + 1 AS [count],
CAST(RTRIM(LTRIM( (
SELECT word + ' '
FROM Result
WHERE id BETWEEN startID AND EndID + 1
FOR XML PATH('')
) ) ) AS VARCHAR(500)) AS word
FROM mix
WHERE startID <= ( SELECT c FROM countWord ) AND (id = 1)
)

,mixAndID AS (
SELECT DISTINCT word AS word,
[count],
ROW_NUMBER() OVER(ORDER BY [COUNT] DESC, StartID) ID
FROM mix
WHERE NOT(word IS NULL)
AND NOT(StartID = 1 AND endid = 0)
)
,CountMix AS( SELECT COUNT(ID) c FROM mixAndID)
,Compare AS(
SELECT id,
0 AS Contour,
CAST(' ' + ltrim(rtrim(Title)) + ' ' AS VARCHAR(500)) AS Total,
CAST(' ' + LTRIM(RTRIM(Title)) + ' ' AS VARCHAR(500)) AS temp,
CAST(' ' AS VARCHAR(500)) AS MyWord,
0 AS c
FROM MyValue
UNION ALL
SELECT id,
Contour + 1,
Total,
CAST(REPLACE(temp,(SELECT (word + ' ')FROM mixAndId WHERE Id = Contour + 1 ),' $ ') AS VARCHAR(500)) temp,
(
SELECT word
FROM mixAndId
WHERE id = Contour + 1
) AS MyWord,
LEN(REPLACE(replace(Temp,'$','*'),(SELECT ' ' + word+' ' FROM mixAndId WHERE id = Contour + 1),'$'))
- LEN(REPLACE(REPLACE(replace(Temp,'$','*'),(SELECT ' ' + word+' ' FROM mixAndId WHERE id = Contour + 1),'$'),'$','')) AS c
FROM compare
WHERE Contour <= ( SELECT c FROM CountMix )
)


SELECT id,total
,(SELECT myword+' ('+CAST(c AS VARCHAR(5))+') , ' FROM compare AS b
WHERE c > 0 and a.id=b.id
FOR XML PATH('')
)
FROM Compare AS a
WHERE c > 0
GROUP BY id,total




صد البته کامل نیست! خسته شدم.

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

ماشااله! خدا قوت!
یه گذرا راه حلتون رو دیدم، قسمت تولید ترکیبات (MIX شما) جالب بود. از خروجی راه حلتون راضیم از نظر من مورد قبوله ولی با داده ورودی زیر خروجی مطلوب حاصل نمیشه. نمیدونم این خروجی به دلیل عدم شناخت دقیق شما از مساله هست یا مشکل به کوئریتون بر میگرده. فرض میگیریم که الگوی ما هست I go to school
وقتی زیر رشته I go از رشته حذف میشه بین I و go بعدی فاصله ای بیش از یک کاراکتر حاصل میشه. یا به عبارت دیگر I و go پشت سر هم نیستند چرا که بین آنها یک برش خورده است.
خروجی مورد نظر در زیر قابل مشاهده است. ولی در خروجی کوئری شما برای ترکیب I فقط 4 تطابق نشون میده. در صورتی که باید 7 تطابق باشه.


RowID String List
----------- ----------------------------------------------------------------------
3 I I I I I I I I go go go to to go(1)-go to(1)-I(7)-I go(1)-to(1)



شرح راه حل بنده:
فرض میکنیم که علائم ویرایشی(نقطه، کاما، سمیکلن...) ابتدا از رشته حذف شده اند .
راه حل از سه فاز تشکیل شده است که هر فاز نسبت به فاز قبلی ساده تر خواهد.
· فاز اول (تولید تمام ترکیبات توسط الگوریتم مجموعه گرا)
· فاز دوم (بررسی تطابق الگوی ترکیبات با رشته ها توسط الگوریتم بازگشتی)
· فاز سوم (الحاق ترکیبات تطابق یافته بافته)

ابتدا ما باید کشف کنیم که با داشتن یک جمله n کلمه ای چند ترکیب میتونیم داشته باشیم و اینکه تعداد ترکیبات k کلمه ای برابر چند است.
اگر رشته ما حاوی n کلمه باشد تعداد ترکیبات n کلمه ای برابر است با 1، تعداد ترکیبات n-1 کلمه ای برابر است با 2 و نهایتا تعداد ترکیبات 1 کلمه ای برابر است با n، با جمع این ترکیبات خواهیم داشت:1+2+3+..+n که مساویست با (n+1)*n/2

توضیح یک ترفند که بارها در راه حل مورد استعمال قرار گرفته است(بدست آوردم تعداد کلمات موجود در یک رشته):
همانطور که میدانیم کلمات در یک جمله با space از هم تفکیک می شوند. پس برای محاسبه تعداد کلمات کافیست اختلاف طول رشته را از طول رشته ای که کاراکتر های space از آن حذف شده است را بدست آورده و با 1 جمع کنیم یعنی:


LEN(@p) - LEN(REPLACE(@p,' ','')) + 1
در فاز دوم با الگوریتم بازگشتی که کاملا مشابه دو حلقه تو در تو است که حلقه خارجی به تعداد تمام ترکیبات اجرا میشه و در حلقه داخلی تا زمانی که تطابق بین رشته و الگو(ترکیب) یافت شود ادامه خواهد یافت.


نام جدول Strings
نام ستون کلید RowID
نام ستون رشته String
DECLARE @P VARCHAR(500) = 'I go to school';

WITH Number(nbr) AS
(
SELECT TOP(500) ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM sys.columns
),
--▄▄▄▄▄▄▄▄▄ FIRST PHASE: Generating Combinations by Cartesian Product ▄▄▄▄▄▄▄▄▄▄
GeneratingTable AS
(
SELECT N1.nbr AS words_cnt,
N2.nbr AS start_point,
N1.nbr + N2.nbr - 1 AS end_point
FROM (
SELECT LEN(@p) - LEN(REPLACE(@p,' ','')) + 1 AS words_nbr
) AS D
INNER JOIN Number N1
ON N1.nbr <= D.words_nbr
INNER JOIN Number N2
ON N2.nbr <= D.words_nbr - N1.nbr + 1
),
WordsStartPoint AS
(
SELECT word_nbr,
MIN (nbr) as start_point,
CHARINDEX(' ', @p + ' ', MIN(nbr) + 1) - MIN(nbr) AS word_length,
CHARINDEX(' ', @p + ' ', MIN(nbr) + 1) - 1 AS end_point
FROM (
SELECT nbr - LEN(REPLACE(LEFT(@p, nbr), ' ', '')) + 1 as word_nbr,
nbr
FROM Number
WHERE nbr <= LEN(@p)
)D
WHERE RIGHT(LEFT(@p, nbr), 1) <> ' '
GROUP BY word_nbr
),
Combinations AS
(
SELECT SUBSTRING(@p , W1.start_point,
W2.start_point + W2.word_length - W1.start_point) AS combs,
ROW_NUMBER() OVER(ORDER BY GT.words_cnt DESC, GT.end_point) AS combs_rank
FROM GeneratingTable AS GT
INNER JOIN WordsStartPoint AS W1
ON GT.start_point = W1.word_nbr
INNER JOIN WordsStartPoint AS W2
ON GT.end_point = W2.word_nbr
),
--▄▄▄▄▄▄▄▄▄▄ SECOND PHASE: Pattern Matching by Recursion Algorithms ▄▄▄▄▄▄▄▄▄

Recursion (ID, strg, [str], cnt, cm) as
(
SELECT RowID,
CAST(string AS VARCHAR(500)),
CAST(string AS VARCHAR(500)),
1,
CAST('' AS VARCHAR(500))
FROM (SELECT RowID, ' ' + string + ' ' FROM Strings) AS S(RowID, string)
JOIN Combinations c
ON CHARINDEX(' ' + Combs + ' ', s.string) > 0
GROUP BY S.rowID, string


UNION ALL

SELECT ID,
CAST(CASE WHEN CHARINDEX(' ' + combs + ' ', strg) > 0
THEN STUFF(strg, CHARINDEX(' ' + combs + ' ', strg), LEN(combs) + 1, '')
ELSE strg
END AS VARCHAR(500)),
strg,
CASE WHEN CHARINDEX(' ' + combs + ' ',
STUFF(strg, CHARINDEX(' ' + combs + ' ', strg), LEN(combs) + 1, ' ')
) > 0
THEN cnt
ELSE cnt + 1
END,
combs
FROM Recursion AS R
INNER JOIN
Combinations AS PR
ON R.cnt = PR.combs_rank
AND ID = ID
WHERE combs_rank <=
(LEN(@p) - LEN(REPLACE(@p,' ','')) + 1 + 1)*(LEN(@p) - LEN(REPLACE(@p,' ','')) + 1)/2
)
--▄▄▄▄▄▄▄▄▄▄ THIRD PHASE: Concatenating Results ▄▄▄▄▄▄▄▄▄
SELECT S.*, STUFF(D.list, 1, 1, '') AS List
FROM Strings S
CROSS APPLY (
SELECT '-' + cm + '(' + CAST(SUM(CASE WHEN LEN(strg) < LEN([str])
THEN 1 ELSE 0
END) AS VARCHAR(50)) + ')'
FROM Recursion R
GROUP BY id, cm
HAVING SUM(CASE WHEN LEN(strg) < LEN([str]) THEN 1 ELSE 0 END) > 0
AND id = S.rowID
ORDER BY id, cm
FOR XML PATH('')
)D(list)
WHERE List IS NOT NULL;

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

اینم مقایسه راه حلها(روی 19 تا سطر):
سرعت اجرای کوئری شما بالاتر هست

/*---------------CPU-------READS-------DURATION
cherchil_hra 78 20135 114
masalim 156 2298 161
*/---------------------------------------------

[cherchil_hra]

مشکل اول :
چون می خوام کلمه به کلمه جایگزین کنم با replace به مشکل می خورم، مثلا برای
select REPLACE(' hra hra hra hra ',' hra ',' * ')-- خروجي * hra * hra
و برای حل این مشکل از replace دوباره استفاده می کنمselect REPLACE(REPLACE (' hra hra hra hra ',' hra ',' * '),' hra ',' * ')-- خروجي * * * * البته space اول و آخر رو نباید فراموش کرد.

مشکل دوم مربوط به len هستش که کاراکتر space آخر جمله رو حساب نمی کنه: select LEN('I am a student')--14
select LEN('I am a student ')--14
پس بجاش از DATALENGTH استفاده کردم:select DATALENGTH('I am a student')--14
select DATALENGTH('I am a student ')--17

در نهایت برای اینکه بفهمم چندتا کاراکتر حذف شده، از این روش استفاده می کنم:DATALENGTH(string) - DATALENGTH(replace(REPLACE(string,word,' '),word,' '))

که این قسمت میشه:DATALENGTH(temp) - DATALENGTH
(replace(
REPLACE(Temp,(SELECT ' ' + word+' ' FROM mixAndId WHERE id = Countour + 1),' ')
,(SELECT ' ' + word+' ' FROM mixAndId WHERE id = Countour + 1)
,' '))

محاسبه تعداد کلمات رو در قسمت final انجام میدم.

خوب برای اینکه یه حالی هم به سرعتش داده باشم، به جای اینکه کنتور از یک شروع بشه با این روش حساب میشه :
1.از جدول mixAndID اون ترکیباتی رو بیار که طول رشته شون برابر یا کمتر از مقدار رشته اصلی باشه
2. و تعداد کلماتشون برابر و یا کمتر از تعداد کلمات رشته اصلی باشه
3. در نهایت کوچکترین id رو از این نتایج انتخاب کن

(SELECT min(id) FROM mixAndID
WHERE [len]<=LEN(ltrim(rtrim(mv.Title)))
AND
[Count]<=LEN(LTRIM(rtrim(mv.Title)))-LEN(REPLACE(LTRIM(rtrim(mv.Title)),' ',''))+1
) AS countour که توی 1000 رکورد یه کاهش تقریبا 25 ثانیه ای رو ایجاد میکنه


استفاده از شرط CASE when (DATALENGTH(temp)>=DATALENGTH(myword) ) قبل از replace کردن هست. این رو قبل از شرط بالا گذاشته بودم که باعث کاهش زمان کوئری شد، ولی با استفاده از روش گفته شده این شرط روی 2000 رکورد من، باعث کاهش 1 ثانیه از زمان کوئری شد. پس گذاشتم بمونه

قسمت final:
توی قسمت final هم طبق توصیه شما از خود جدول myvalue استفاده کردم که مسلما تاثیر بسیار خوبی داره. خوب برای اینکه تعداد کلمات تطابق داده شده رو بدست بیاریم، تعداد کاراکترهای حذف شده (c) تقسیم میشه بر طول کلمه.


در کل:
DECLARE @a VARCHAR(500) =' I am a master of science student in Sharif University manager go '
, @Top INT =500;


;WITH MyWords([StartPoint],[EndPoint],word) AS(
SELECT 1,1,@a
UNION ALL

SELECT CASE SUBSTRING(@a, [EndPoint], 1)
WHEN ' ' THEN [EndPoint] + 1
ELSE [StartPoint]
END AS [StartPoint],
[EndPoint] + 1,
CASE SUBSTRING(@a, [EndPoint], 1)
WHEN ' ' THEN LTRIM(RTRIM(SUBSTRING(@a, [StartPoint], [EndPoint] -[StartPoint])) )
ELSE ''
END AS word
FROM MyWords
WHERE [EndPoint] <= LEN(@a) + 1
)
,Result (ID,Word) AS (
SELECT ROW_NUMBER() OVER(ORDER BY [StartPoint]) ID,
word
FROM MyWords
WHERE word <> ''
AND NOT([StartPoint] = 1 AND [ENDPOINT] = 1)
)
,countWord AS( SELECT COUNT(*) c FROM result )
--------------------------------------------------------------
,mix (StartID,EndID,ID,[Count],word) AS
(
SELECT 1,0,ID,1,word
FROM Result

UNION ALL

SELECT CASE
WHEN EndID + 1 = (SELECT c FROM countWord) THEN startID + 1
ELSE startID
END AS StartID
,CASE
WHEN EndID + 1 = (SELECT c FROM countWord ) THEN 1
ELSE EndID + 1
END AS [EndID]
,ID
,(endid + 1) -(startid) + 1 AS [count]
,CAST(
RTRIM((SELECT (word)+' ' FROM Result
WHERE id BETWEEN startID AND EndID + 1
FOR XML PATH(''))
)AS VARCHAR(500)) AS word
FROM mix
WHERE startID <= ( SELECT c FROM countWord ) AND (id = 1)
)

,mixAndID (word,id,[count],[len]) AS
(
SELECT DISTINCT word
,ROW_NUMBER() OVER(ORDER BY [count] DESC, StartID) ID
,[count]
,LEN(word)
FROM mix
WHERE NOT(word IS NULL) AND NOT(StartID = 1 AND endid = 0)
)
,CountMix AS( SELECT COUNT(ID) c FROM mixAndID)
--------------------------------------------------------------
,Compare (id,Countour,total,temp,MyWord,c)AS
(
SELECT TOP(@top) id,
(SELECT min(id) FROM mixAndID
WHERE [len]<=LEN(ltrim(rtrim(mv.Title)))
AND
[Count]<=LEN(LTRIM(rtrim(mv.Title)))-LEN(REPLACE(LTRIM(rtrim(mv.Title)),' ',''))+1
) AS countour

,CAST(' ' + ltrim(rtrim(Title)) + ' ' AS VARCHAR(500)) AS Total
,CAST(' ' + LTRIM(RTRIM(Title)) + ' ' AS VARCHAR(500)) AS temp
,CAST(' ' AS VARCHAR(500)) AS MyWord
,0 AS c
FROM MyValue AS mv

UNION ALL

SELECT id,
Countour + 1 AS Countour,
Total,
CAST(
(CASE when (DATALENGTH(temp)>=DATALENGTH(myword) ) then
REPLACE(
REPLACE(temp,(SELECT (' ' +word + ' ')FROM mixAndId WHERE Id = Countour + 1 ),' ')
,(SELECT (' ' +word + ' ')FROM mixAndId WHERE Id = Countour + 1 )
,' ')
ELSE temp
END

)AS VARCHAR(500)) as temp
,(
SELECT word
FROM mixAndId
WHERE id = Countour + 1
) AS MyWord
,
CASE when (DATALENGTH(temp)>=DATALENGTH(myword) ) then
(DATALENGTH(temp) - DATALENGTH
(replace(
REPLACE(Temp,(SELECT ' ' + word+' ' FROM mixAndId WHERE id = Countour + 1),' ')
,(SELECT ' ' + word+' ' FROM mixAndId WHERE id = Countour + 1)
,' ')))
ELSE 0
end AS c

FROM compare
WHERE Countour < ( SELECT c FROM CountMix )
)

,final(id,title,result) AS (
SELECT TOP(@top) a.id,a.title
,STUFF(
(SELECT +','+myword+' ('+CAST(c/LEN(myword) AS VARCHAR(5))+') ' FROM compare AS b
WHERE c > 0 and a.id=b.id
FOR XML PATH('')),1,1,''
)
FROM myvalue AS a
GROUP BY a.id,a.title
)

SELECT id,title,result FROM final
WHERE not(result IS NULL)

OPTION(MAXRECURSION 0)

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

گویا قسمت mixAndID هم تغییر کرده. مثلا ستونlen جدیدا اضافه شده؟ میشه در پست قبلی کوئری کامل رو قرار بدین(لطفا همون پست قبلیتون رو ویرایش کنید).

[cherchil_hra]

گویا قسمت mixAndID هم تغییر کرده. مثلا ستونlen جدیدا اضافه شده؟ میشه در پست قبلی کوئری کامل رو قرار بدین(لطفا همون پست قبلیتون رو ویرایش کنید).

پست #5 (http://barnamenevis.org/showthread.php?375939-%D9%86%D9%87%D9%85%DB%8C%D9%86-%DA%86%D8%A7%D9%84%D8%B4-SQL-(%D8%AC%D8%B3%D8%AA%D8%AC%D9%88%DB%8C-%D8%A7%D9%84%DA%AF%D9%88-%D8%AA%D8%B1%DA%A9%DB%8C%D8%A8%D8%A7%D8%AA)&p=1677177&viewfull=1#post1677177) ویرایش شد و تمام کوئری قرار داده شد.

فقط همون len اضافه شده!

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

از کدهاتون کاملا مشخص هست که برای حل مساله زیاد انرژی صرف میکیند خوب ولی بهتر هست راه های ساده تر هم یادبگیرین. سعی کنید تا اونجایی که میشه از پیچیدگی مساله بکاهین و به سادگیش بیفزایین. در ادامه چنتا پیشنهاد دارم که راه حلتون رو ساده تر و خواناتر میکنه.
پیشنهادات:
فرض میگیریم که Pattern ما عبارت I go to school هست. و انتهای رشته یک space نیز وجود دارد.
یک: MyWords میتونه ساده تر، بهینه تر و خواناتر بشه. نیازی هم Result نخواهد بود؛
چون طول یک کلمه هیچ وقت از 20-25 کاراکتر تجاوز نمیکنه هنگام cast کردن یک کلمه طولش رو عدد پایینی در نظر میگیریم.
البته از این روش در راه حلهای مناسب چالش شماره 8 هم استفاده شده، کوئری زیر زمانی نتیجه کاملا صحیح میده که کاراکتر space در ابتدا متغیر نباشه و بین کلمات هم بیش از یک فاصله وجود نداشته باشد.
;WITH MyWords(ID, word, string) AS(

SELECT 1,
CAST(SUBSTRING(@a, 1, CHARINDEX(' ', @a) - 1) AS VARCHAR(8)),
STUFF(@a, 1, CHARINDEX(' ', @a), '')

UNION ALL

SELECT ID + 1,
CAST(SUBSTRING(string, 1, CHARINDEX(' ', string) - 1) AS VARCHAR(8)),
STUFF(string, 1, CHARINDEX(' ', string), '')
FROM MyWords
WHERE CHARINDEX(' ', string) > 0
)
و خروجی MyWords:

ID word string
----------- -------- -----------------
1 I go to school
2 go to school
3 to school
4 school

(4 row(s) affected)

دو: نیازی به عبارت جدولی countWord نیست؛ من در یکی از پست های پیشین توضیح دادم که برای بدست آوردن تعداد کلمات موجود در یک رشته یک تکنیک خیلی ساده وجود داره؛ یعنی به جای countWord شما اینکار رو انجام بدین، البته با توجه به این نکته که نباید بین کلمات بیش از یک کاراکتر وجود داشته باشد (اگر هم داشت باید حذف بشن)
و برای اینکه احیانا این مقدار چندبار مورد نیاز واقع باشه در همون اول مقدار را به یک متغیر انتساب میدیم یعنی:





Declaer @wrds int
select @wrds = len(a) - len(replace(a,' ','')) + 1

from (select ltrim(rtrim(@a)))d(a)


سه: عبارت mix اتون بسیار پیچیده هست، در حالی که میشه خیلی خیلی ساده ترش کرد به این شکل:
, mix(id, lastid, word, cnt) AS(
SELECT id, id, CAST(word AS VARCHAR(20)), 1
FROM MyWords

UNION ALL

SELECT c.id,
m.id,
CAST(c.word + ' ' + m.word AS VARCHAR(20)),
c.cnt + 1
FROM mix c
JOIN Mywords m
ON c.lastid = m.id - 1
),
که خروجیش برابر هست با:
id lastid word cnt
----------- ----------- -------------------- -----------
1 1 I 1
2 2 go 1
3 3 to 1
4 4 school 1
3 4 to school 2
2 3 go to 2
2 4 go to school 3
1 2 I go 2
1 3 I go to 3
1 4 I go to school 4

(10 row(s) affected)


چهار: نیازی به countMix نیست. همانطور که قبلا در یکی از پست های پیشینم اشاره شد تعداد ترکیبات رو میشه از طریف فرمول زیر بدست آورد(a متغیر حاوی الگوی ماست)، از اونجایی که ممکن به این مقدار در طول راه حل بازم اشاره بشه اون را به یک متغیر انتساب میدیم تا از نوشتن این کد بطور مکرر اجتناب کرده باشیم:



(LEN(@a) - LEN(REPLACE(@a,' ','')) + 1 + 1)*(LEN(@a) - LEN(REPLACE(@a,' ','')) + 1)/2


پس تا اینجا کوئریتون به این شکل در میاد:
DECLARE @a VARCHAR(500) ='I go to school ',
@wrds int, @mix int;

SELECT @mix = (LEN(@a) - LEN(REPLACE(@a,' ','')) + 1 + 1)*(LEN(@a) - LEN(REPLACE(@a,' ','')) + 1)/2

SELECT @wrds = len(a) - len(replace(a,' ','')) + 1
FROM (SELECT ltrim(rtrim(@a)))d(a);

;WITH MyWords(ID, word, string) AS(

SELECT 1,
CAST(SUBSTRING(@a, 1, CHARINDEX(' ', @a) - 1) AS VARCHAR(8)),
STUFF(@a, 1, CHARINDEX(' ', @a), '')

UNION ALL

SELECT ID + 1,
CAST(SUBSTRING(string, 1, CHARINDEX(' ', string) - 1) AS VARCHAR(8)),
STUFF(string, 1, CHARINDEX(' ', string), '')
FROM MyWords
WHERE CHARINDEX(' ', string) > 0
)
, mix(id, lastid, word, cnt) AS(
SELECT id, id, CAST(word AS VARCHAR(20)), 1
FROM MyWords

UNION ALL

SELECT c.id,
m.id,
CAST(c.word + ' ' + m.word AS VARCHAR(20)),
c.cnt + 1
FROM mix c
JOIN Mywords m
ON c.lastid = m.id - 1
),
mixAndID AS (
SELECT word,
ROW_NUMBER() OVER(ORDER BY cnt DESC, id) AS id,
cnt as [count],
LEN(word) AS [len]
FROM mix
)
------------------------------------------------------------

پنج: چرا در قسمت دوم compare جدول را با mixAndID جوین نمیکنید که دیگه نیاز نباشه دائما از subquery استفاده بشه اونم 5 تا؟!:لبخندساده:
یعنی:

FROM compare as c
JOIN mixAndId as m
ON m.id = Countour + 1
حالا در کوئرتون هرکجا که سابکوئری زیر آمد مقدار m.word رو قرار بدین:

(SELECT ' ' + word+' ' FROM mixAndId WHERE id = Countour + 1)

زیاد به compare و بعد از اون نپرداختم. شاید اگه ساده تر بشه بتونم بررسیش کنم.

[cherchil_hra]

ببخشید دیر شد، سرم یه ریزه شلوغ بود.

بله روش استفاده شده در چالش 8 رو یادم بود و اتفاقا می خواستم بذارمش، اما خوب دیدم این هم یه راه حل هستش(هرچند ناقص) پس گذاشتم بمونه. تعداد کاراکتر انتخابی 500 یا 25 تاثیری (چشمگیر) در کوئری نداره ولی دیگه 8 هم که شما گذاشتی خیلی کمه، البته منظورتون همون20 تا 25 بوده که گفتید.

قسمت mix که اصلاح کردید تاثیر بسیار خوبی در کوئری داشت و همینطور استفاده از متغیر @mix. طول رشته که 20 در نظر گرفتید بنابر طول رشته اصلی که ممکنه بیشتر از 20 باشه(500 کاراکتر)، برای ترکیبات بیش از 20 کاراکتر رشته ناقص برمی گردونه.

با کوئری که شما گفتید نتایج ذیل روی 2000 رکورد مشابه و رشته یکسان حاصل شد:
تعداد رکورد برگردانده شده : 881
با اصلاح کوئری : 00:54 ثانیه
کوئری قدیم : 01:07 ثانیه

از متغیر @mix و @wrds در کوئری اصلاح نشده قبلی استفاده کردم و مدت زمان به 01:00 کاهش یافت.

من نتونستم از join در قسمت compare استفاده کنم. به این خاطر که در این قسمت از حالت بازگشتی استفاده میشه هر الگو روی همون سطر اجرا میشه و temp معنی پیدا نمی کنه.

قسمت case هم که در compare بود به علت ایجاد اشتباه در نتایج برگشتی حذف شد.

کوئری با توجه به نکات شما اینگونه شد:


DECLARE @t AS TABLE (word VARCHAR(500),id INT,[count] int,[len] int)

DECLARE @a VARCHAR(500) ='I am a master of science student in Sharif University ',
@mix INT,@Top INT=2000;

set @mix = (LEN(@a) - LEN(REPLACE(@a,' ','')) + 1 + 1)*(LEN(@a) - LEN(REPLACE(@a,' ','')) + 1)/2

;WITH MyWords(ID, word, string) AS(

SELECT 1,
CAST(SUBSTRING(@a, 1, CHARINDEX(' ', @a) - 1) AS VARCHAR(500)),
STUFF(@a, 1, CHARINDEX(' ', @a), '')
UNION ALL

SELECT ID + 1,
CAST(SUBSTRING(string, 1, CHARINDEX(' ', string) - 1) AS VARCHAR(500)),
STUFF(string, 1, CHARINDEX(' ', string), '')
FROM MyWords
WHERE CHARINDEX(' ', string) > 0
)
, mix(id, lastid, word, cnt) AS(
SELECT id, id, CAST(word AS VARCHAR(500)), 1 FROM MyWords

UNION ALL

SELECT c.id,
m.id,
CAST(c.word + ' ' + m.word AS VARCHAR(500)),
c.cnt + 1
FROM mix c
JOIN Mywords m ON c.lastid = m.id - 1
),
mixAndID AS (
SELECT word,
ROW_NUMBER() OVER(ORDER BY cnt DESC, id) AS id,
cnt as [count],
LEN(word) AS [len]
FROM mix
)

------------------------------------------------------------
,Compare (id,Countour,total,temp,MyWord,c)AS
(
SELECT TOP(@top)
id,
(SELECT min(id) FROM mixAndID
WHERE [len]<=LEN(ltrim(rtrim(mv.Title)))
AND
[Count]<=LEN(LTRIM(rtrim(mv.Title)))-LEN(REPLACE(LTRIM(rtrim(mv.Title)),' ',''))+1
) AS countour
,CAST(' ' + ltrim(rtrim(Title)) + ' ' AS VARCHAR(500)) AS Total
,CAST(' ' + LTRIM(RTRIM(Title)) + ' ' AS VARCHAR(500)) AS temp
,CAST(' ' AS VARCHAR(500)) AS MyWord
,0 AS c
FROM MyValue AS mv

UNION ALL

SELECT id,
Countour + 1 AS Countour,
Total,
CAST(
REPLACE(
REPLACE(temp,(SELECT (' ' +word + ' ')FROM mixAndID WHERE Id = Countour + 1 ),' ')
,(SELECT (' ' +word + ' ')FROM mixAndID WHERE Id = Countour + 1 )
,' ')
AS VARCHAR(500)) as temp
,CAST ((
SELECT word
FROM mixAndID
WHERE id = Countour + 1
)AS VARCHAR(500)) AS MyWord
,
(DATALENGTH(temp) - DATALENGTH
(replace(
REPLACE(Temp,(SELECT ' ' + word+' ' FROM mixAndID WHERE id = Countour + 1),' ')
,(SELECT ' ' + word+' ' FROM mixAndID WHERE id = Countour + 1)
,' ')))

FROM compare
WHERE Countour < @mix
)

,final(id,title,result) AS (
SELECT TOP(@top) a.id,a.title
,STUFF(
(SELECT +','+myword+' ('+CAST(c/LEN(myword) AS VARCHAR(5))+') ' FROM compare AS b
WHERE c > 0 and a.id=b.id
FOR XML PATH('')),1,1,''
)
FROM myvalue AS a
GROUP BY a.id,a.title
)

SELECT id,title,result FROM final
WHERE not(result IS NULL)


OPTION (MAXRECURSION 0)


اما چیزی که به ذهنم رسید و در پست بعدی ازش استفاده میشه، این بود که قسمت MixAndID رو در یک جدول موقت قرار بدیم و بعد ازش استفاده بشه:

نتیجه روی همان مقادیر:
1.کوئری اصلاح شده : 52 ثانیه (2ثانیه کاهش)
2.کوئری قبل با استفاده از متغیرهای گفته شده:53 ثانیه (7 ثانیه کاهش)
باتوجه به اینکه،قسمت MixAndID کوئری اصلاح شده روی الگو تست شده بلافاصله ایجاد می شود ولی در کوئری قدیم یک ثانیه زمان می برد.

قسمت Mywords رو میشه اینجوری هم نوشت:

MyWords([StartPoint],[EndPoint],word) AS(
SELECT 1,1,@a
UNION ALL

SELECT
[EndPoint],
CHARINDEX(' ',@a,[EndPoint])+1,
LTRIM(RTRIM(SUBSTRING(@a, [StartPoint], [EndPoint] -[StartPoint])))
FROM MyWords
WHERE [EndPoint] >=StartPoint
)

در نتیجه زمان هر دو کوئری یکسان خواهد شد (اختلاف در هر حد میلی ثانیه)

در پست بعدی از جدول موقت استفاده می کنم و قسمت myWords رو هم به صورت کامل حذف می کنم

[cherchil_hra]

قبلش بگم که یادم بود باید همه چیز در قالب یک کوئری باشه (البته قسمت اصلی هست)، ولی خوب نتیجه جالب بود:

همانطور که در پست قبلی گفتم، در کوئری جدید، من قسمت MyWords رو حذف کردم چون کار اضافه بود و از جدول موقت برای نگهداری از ترکیبات استفاده می کنم.
در قسمت Mix از اول تا آخر رشته بررسی میشه و ترکیب ها ساخته میشه. وقتی به آخر رشته می رسیم کلمه اول از الگو حذف میشه و ترکیبات ساخته میشه تا آخر.
در قسمت MixAndID قسمت ها [Count] و [Len] حذف شدند.


;WITH mix(id,[EndPoint],String,word) AS(
SELECT 1,1,@a ,@a
UNION ALL

SELECT
id+1,
CHARINDEX(' ',(string),[EndPoint])+1,
CASE
WHEN CHARINDEX(' ',(string),[EndPoint])+1 = 1 THEN
cast((STUFF(string,1,CHARINDEX(' ',string,1),'')) AS VARCHAR(500))
ELSE string
END
,LTRIM(RTRIM(SUBSTRING(string, 1, [EndPoint]-1 )))
FROM mix
WHERE string<>''
)

, MixAndID AS (
SELECT ROW_NUMBER() OVER(ORDER BY LEN(word)-LEN(REPLACE(word,' ',''))+1 desc, ID asc) ID
,word
from mix
WHERE word<>'' AND id<>1
GROUP BY word,id
)


خوب حالا تمام ترکیبات رو با جدول اصلی مقایسه می کنیم و فقط اون ترکیباتی که موجود هستند انتخاب و بقیه حذف میشه:

,joint (row,id,title,word) AS (
SELECT
ROW_NUMBER() OVER(partition by val.id ORDER BY val.id,mix.id)
,val.id
,title
,' '+word+' '
FROM myvalue val
INNER JOIN mixAndID mix
ON val.id<@top AND ' '+title+' ' LIKE '% '+word+' %'
)


به ظاهر همه چیز خوبه و باید سرعت بالا بره ولی اگه از جدول موقت استفاده نشه چی میشه؟
روی 2000 رکورد امتحان کردم ولی با گذشت 20 دقیقه (تا همین الان که دارم می نویسم) نتیجه ای حاصل نشده، روی 200 رکورد 7 ثانیه!!!!
ولی با استفاده از جدول موقت روی 13000 رکورد: 16 ثانیه


DECLARE @a VARCHAR(500) =ltrim(rtrim('I am a master of science student in Sharif University '))
, @Top INT =13000

SET @a=REPLACE(REPLACE(REPLACE(@a, ' ', ' ▄'), '▄ ', ''), '▄', '')+ ' '

DECLARE @t AS TABLE (row INT,id INT,title VARCHAR(500),word VARCHAR(500))

;WITH mix(id,[EndPoint],String,word) AS(
SELECT 1,1,@a ,@a
UNION ALL

SELECT
id+1,
CHARINDEX(' ',(string),[EndPoint])+1,
CASE
WHEN CHARINDEX(' ',(string),[EndPoint])+1 = 1 THEN
cast((STUFF(string,1,CHARINDEX(' ',string,1),'')) AS VARCHAR(500))
ELSE string
END
,LTRIM(RTRIM(SUBSTRING(string, 1, [EndPoint]-1 )))
FROM mix
WHERE string<>''
)

, MixAndID AS (
SELECT ROW_NUMBER() OVER(ORDER BY LEN(word)-LEN(REPLACE(word,' ',''))+1 desc, ID asc) ID
,word
from mix
WHERE word<>'' AND id<>1
GROUP BY word,id
)
-------------------------------------------------------------- گزینش ترکیبات موجود با جدول اصلی
,joint (row,id,title,word) AS (
SELECT
ROW_NUMBER() OVER(partition by val.id ORDER BY val.id,mix.id)
,val.id
,title
,' '+word+' '
FROM myvalue val
INNER JOIN mixAndID mix
ON val.id<@top AND ' '+title+' ' LIKE '% '+word+' %'
)

INSERT INTO @T SELECT * FROM joint

OPTION (MAXRECURSION 0)

-------------------------------------------------------------- مقایسه ترکيبات
;with
Compare (id,Countour,total,temp,MyWord,c,maxRow)AS
(
SELECT j.id,
0 AS countour
,CAST(' ' + ltrim(rtrim(j.Title)) + ' ' AS VARCHAR(500)) AS Total
,CAST(' ' + LTRIM(RTRIM(j.Title)) + ' ' AS VARCHAR(500)) AS temp
,CAST('' AS VARCHAR(500)) AS MyWord
,0 AS c
,(SELECT max(row) FROM @t WHERE id=j.id)
FROM @t j
GROUP BY j.id,j.title

UNION ALL

SELECT com.id,
Countour + 1 AS Countour,
Total,
CAST(REPLACE(
REPLACE(temp,(SELECT word FROM @t WHERE id=com.id AND Row=com.Countour+1 ),' ')
,(SELECT word FROM @t WHERE id=com.id AND Row=com.Countour+1 )
,' ')
AS VARCHAR(500)) as temp
,cast((SELECT word FROM @t WHERE id=com.id AND row=com.Countour +1) AS VARCHAR(500))
,DATALENGTH(temp) - DATALENGTH(REPLACE
(REPLACE(Temp,(SELECT word FROM @t WHERE id=com.id AND Row=com.Countour+1 ) ,' ')
, (SELECT word FROM @t WHERE id=com.id AND Row=com.Countour+1 ) ,' '))
,maxRow
FROM compare com

WHERE maxRow>=com.Countour +1
)

,final(id,title,result) AS (
SELECT a.id,a.title
,STUFF(
(SELECT ','+myword+'('+CAST(c/LEN(LTRIM(myword)) AS VARCHAR(5))+')' FROM compare AS b
WHERE c > 0 and a.id=b.id
FOR XML PATH('')),1,1,''
)
FROM myvalue AS a
WHERE id<@top
GROUP BY a.id,a.title
)

SELECT id,title,result FROM final
WHERE not(result IS NULL)


OPTION (MAXRECURSION 0)

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

با جایگزین کردین جدول اعداد و تنظیم ستون آن به کلید اولیه و دو تیکه کردن راه حل (استفاده از متغیر جدولی) تونستم سرعت query را بسیار افزایش دهم.

نتیجه اجرای query آخر شما و query این پست روی حدود 2500 سطر (با داده های تکراری نه متنوع):
مال شما 54 ثانیه
مال من 27 ثانیه

در مورد جدول اعداد بگم (number_table) یه ستون داره که set as primary key باید بشه تا شاخص گذاری شود.

DECLARE @P VARCHAR(500) = 'I go to school';

DECLARE @t TABLE (combs VARCHAR(500), combs_rank INT);

WITH
--▄▄▄▄▄▄▄▄▄ FIRST PHASE: Generating Combinations by Cartesian Product ▄▄▄▄▄▄▄▄▄▄
GeneratingTable AS
(
SELECT N1.n AS words_cnt,
N2.n AS start_point,
N1.n + N2.n - 1 AS end_point
FROM (
SELECT LEN(@p) - LEN(REPLACE(@p,' ','')) + 1 AS words_nbr
) AS D
INNER JOIN number_table N1
ON N1.n <= D.words_nbr
INNER JOIN number_table N2
ON N2.n <= D.words_nbr - N1.n + 1
),
WordsStartPoint AS
(
SELECT word_nbr,
MIN (n) as start_point,
CHARINDEX(' ', @p + ' ', MIN(n) + 1) - MIN(n) AS word_length,
CHARINDEX(' ', @p + ' ', MIN(n) + 1) - 1 AS end_point
FROM (
SELECT n - LEN(REPLACE(LEFT(@p, n), ' ', '')) + 1 as word_nbr,
n
FROM number_table
WHERE n <= LEN(@p)
)D
WHERE RIGHT(LEFT(@p, n), 1) <> ' '
GROUP BY word_nbr
),
Combinations AS
(
SELECT SUBSTRING(@p , W1.start_point,
W2.start_point + W2.word_length - W1.start_point) AS combs,
ROW_NUMBER() OVER(ORDER BY GT.words_cnt DESC, GT.end_point) AS combs_rank
FROM GeneratingTable AS GT
INNER JOIN WordsStartPoint AS W1
ON GT.start_point = W1.word_nbr
INNER JOIN WordsStartPoint AS W2
ON GT.end_point = W2.word_nbr
)
INSERT INTO @t SELECT combs, combs_rank FROM Combinations;

WITH
--▄▄▄▄▄▄▄▄▄▄ SECOND PHASE: Pattern Matching by Recursion Algorithms ▄▄▄▄▄▄▄▄▄

Recursion (ID, strg, [str], cnt, cm) AS
(
SELECT RowID,
CAST(title AS VARCHAR(500)),
CAST(title AS VARCHAR(500)),
1,
CAST('' AS VARCHAR(500))
FROM (SELECT ID, ' ' + title + ' ' FROM myvalue) AS S(RowID, title)
JOIN @t c
ON s.title LIKE '% ' + Combs + ' %'
GROUP BY S.rowID, title


UNION ALL

SELECT ID,
CAST(CASE WHEN CHARINDEX(' ' + combs + ' ', strg) > 0
THEN STUFF(strg, CHARINDEX(' ' + combs + ' ', strg), LEN(combs) + 1, '')
ELSE strg
END AS VARCHAR(500)),
strg,
CASE WHEN CHARINDEX(' ' + combs + ' ',
STUFF(strg, CHARINDEX(' ' + combs + ' ', strg), LEN(combs) + 1, ' ')
) > 0
THEN cnt
ELSE cnt + 1
END,
combs
FROM Recursion AS R
INNER JOIN
@t AS PR
ON R.cnt = PR.combs_rank
AND ID = ID
WHERE combs_rank <=
(LEN(@p) - LEN(REPLACE(@p,' ','')) + 1 + 1)*(LEN(@p) - LEN(REPLACE(@p,' ','')) + 1)/2
)
--▄▄▄▄▄▄▄▄▄▄ THIRD PHASE: Concatenating Results ▄▄▄▄▄▄▄▄▄
SELECT S.*, STUFF(D.list, 1, 1, '') AS List
FROM MyValue S
CROSS APPLY (
SELECT '-' + cm + '(' + CAST(SUM(CASE WHEN LEN(strg) < LEN([str])
THEN 1 ELSE 0
END) AS VARCHAR(50)) + ')'
FROM Recursion R
GROUP BY id, cm
HAVING SUM(CASE WHEN LEN(strg) < LEN([str]) THEN 1 ELSE 0 END) > 0
AND id = S.id
ORDER BY id, cm
FOR XML PATH('')
)D(list)
WHERE List IS NOT NULL;

[cherchil_hra]

برای اینکه مشکل با مقادیر تکراری حل بشه، کد اصلاح شد، البته میشه قسمت joint رو هم حذف کرد که روی داده های تکراری سرعتش بسیار خوب میشه اما برای رکوردهای غیر تکراری زمانش از این کوئری چند ثانیه بیشتر میشه.

الگو: i go to school
رکوردها (2500 عدد): I I I I I I I I go go go to to
10 ثانیه زمان این کوئری، نسبت به کوئری شما کمتر شد.


DECLARE @a VARCHAR(500) =ltrim(rtrim('i go to school'))
, @Top INT =2500

SET @a=REPLACE(REPLACE(REPLACE(@a, ' ', ' ▄'), '▄ ', ''), '▄', '')+ ' '

DECLARE @t AS TABLE (row INT,id INT,title VARCHAR(500),word VARCHAR(500))

;WITH mix(id,[EndPoint],String,word) AS(
SELECT 1,1,@a ,@a

UNION ALL

SELECT
id+1,
CHARINDEX(' ',(string),[EndPoint])+1,
CASE
WHEN CHARINDEX(' ',(string),[EndPoint])+1 = 1 THEN
cast((STUFF(string,1,CHARINDEX(' ',string,1),'')) AS VARCHAR(500))
ELSE string
END
,LTRIM(RTRIM(SUBSTRING(string, 1, [EndPoint]-1 )))
FROM mix
WHERE string<>''
)

, MixAndID AS (
SELECT ROW_NUMBER() OVER(ORDER BY LEN(word)-LEN(REPLACE(word,' ',''))+1 desc, ID asc) ID
,word
from mix
WHERE word<>'' AND id<>1
GROUP BY word,id
)

--------------------------------------------------------------
,joint (row,id,title,word) AS (
SELECT
ROW_NUMBER() OVER(partition by val.id ORDER BY val.id,mix.id)
,val.id
,title
,' '+word+' '
FROM myvalue val
INNER JOIN mixAndID mix
ON val.id<@top AND ' '+title+' ' LIKE '% '+word+' %'
)

INSERT INTO @T SELECT * FROM joint

OPTION (MAXRECURSION 0)

-------------------------------------------------------------- بررسي ترکيبات
;with
Compare (id,Countour,total,temp,MyWord,c,maxRow)AS
(
SELECT j.id,
1 AS countour
,CAST(' ' + ltrim(rtrim(j.Title)) + ' ' AS VARCHAR(500)) AS Total
,CAST(' ' + LTRIM(RTRIM(j.Title)) + ' ' AS VARCHAR(500)) AS temp
,CAST('' AS VARCHAR(500)) AS MyWord
,0 AS c
,(SELECT max(row) FROM @t WHERE id=j.id)
FROM myvalue j

UNION ALL

SELECT com.id
,Countour + 1 AS Countour
,Total
,CAST(REPLACE(REPLACE(temp,t.word,' '),t.word,' ') AS VARCHAR(500)) as temp
,t.word
,(DATALENGTH(temp) - DATALENGTH(REPLACE(REPLACE(Temp,t.word ,' ') , t.word ,' ')))/LEN(LTRIM(t.word))
,maxRow
FROM compare com
INNER JOIN @T t
ON t.id=com.id and t.row=com.Countour
WHERE t.row>=com.Countour
)

,final(id,title,result) AS (
SELECT a.id,a.title
,STUFF(
(SELECT ','+myword+'('+CAST(c AS VARCHAR(5))+')' FROM compare AS b
WHERE c > 0 and a.id=b.id
FOR XML PATH('')),1,1,''
)
FROM myvalue AS a
WHERE id<@top
)

SELECT id,title,result FROM final
WHERE not(result IS NULL)

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

بطور وحشتناکی سریع. مخصوص title های تکراری.
اجرای روی 4 هزار سطر در کمتر از 1 ثانیه.
DECLARE @P VARCHAR(500) = 'I go to school';

DECLARE @t TABLE (combs VARCHAR(500), combs_rank INT);

WITH
--▄▄▄▄▄▄▄▄▄ FIRST PHASE: Generating Combinations by Cartesian Product ▄▄▄▄▄▄▄▄▄▄
GeneratingTable AS
(
SELECT N1.n AS words_cnt,
N2.n AS start_point,
N1.n + N2.n - 1 AS end_point
FROM (
SELECT LEN(@p) - LEN(REPLACE(@p,' ','')) + 1 AS words_nbr
) AS D
INNER JOIN number_table N1
ON N1.n <= D.words_nbr
INNER JOIN number_table N2
ON N2.n <= D.words_nbr - N1.n + 1
),
WordsStartPoint AS
(
SELECT word_nbr,
MIN (n) as start_point,
CHARINDEX(' ', @p + ' ', MIN(n) + 1) - MIN(n) AS word_length,
CHARINDEX(' ', @p + ' ', MIN(n) + 1) - 1 AS end_point
FROM (
SELECT n - LEN(REPLACE(LEFT(@p, n), ' ', '')) + 1 as word_nbr,
n
FROM number_table
WHERE n <= LEN(@p)
)D
WHERE RIGHT(LEFT(@p, n), 1) <> ' '
GROUP BY word_nbr
),
Combinations AS
(
SELECT SUBSTRING(@p , W1.start_point,
W2.start_point + W2.word_length - W1.start_point) AS combs,
ROW_NUMBER() OVER(ORDER BY GT.words_cnt DESC, GT.end_point) AS combs_rank
FROM GeneratingTable AS GT
INNER JOIN WordsStartPoint AS W1
ON GT.start_point = W1.word_nbr
INNER JOIN WordsStartPoint AS W2
ON GT.end_point = W2.word_nbr
)
INSERT INTO @t SELECT combs, combs_rank FROM Combinations;

WITH
--▄▄▄▄▄▄▄▄▄▄ SECOND PHASE: Pattern Matching by Recursion Algorithms ▄▄▄▄▄▄▄▄▄

Recursion (ID, strg, [str], cnt, cm) AS
(
SELECT RowID,
CAST(title AS VARCHAR(500)),
CAST(title AS VARCHAR(500)),
1,
CAST('' AS VARCHAR(500))
FROM (SELECT MIN(ID), ' ' + title + ' '
FROM myvalue
GROUP BY title) AS S(RowID, title)
JOIN @t c
ON s.title LIKE '% ' + Combs + ' %'
GROUP BY S.rowID, title


UNION ALL

SELECT ID,
CAST(CASE WHEN CHARINDEX(' ' + combs + ' ', strg) > 0
THEN STUFF(strg, CHARINDEX(' ' + combs + ' ', strg), LEN(combs) + 1, '')
ELSE strg
END AS VARCHAR(500)),
strg,
CASE WHEN CHARINDEX(' ' + combs + ' ',
STUFF(strg, CHARINDEX(' ' + combs + ' ', strg), LEN(combs) + 1, ' ')
) > 0
THEN cnt
ELSE cnt + 1
END,
combs
FROM Recursion AS R
INNER JOIN
@t AS PR
ON R.cnt = PR.combs_rank
AND ID = ID
WHERE combs_rank <=
(LEN(@p) - LEN(REPLACE(@p,' ','')) + 1 + 1)*(LEN(@p) - LEN(REPLACE(@p,' ','')) + 1)/2
)
--▄▄▄▄▄▄▄▄▄▄ THIRD PHASE: Concatenating Results ▄▄▄▄▄▄▄▄▄
SELECT Mv.*, STUFF(D.list, 1, 1, '') AS List
FROM MyValue S
CROSS APPLY (
SELECT '-' + cm + '(' + CAST(SUM(CASE WHEN LEN(strg) < LEN([str])
THEN 1 ELSE 0
END) AS VARCHAR(50)) + ')'
FROM Recursion R
GROUP BY id, cm
HAVING SUM(CASE WHEN LEN(strg) < LEN([str]) THEN 1 ELSE 0 END) > 0
AND id = S.id
ORDER BY id, cm
FOR XML PATH('')
)D(list)
INNER JOIN MyValue Mv
ON S.title = Mv.title
WHERE List IS NOT NULL;