הבלוג של גרי רשף

24/08/2011

חיתוך בין רשימות

Filed under: Uncategorized — גרי רשף @ 19:28

פוסט של משה טייכר בשם שליפת מילים מתוך טקסט בTSQL שהתפרסם לאחרונה עודד אותי לפרסם תגובה עקיפה בפוסט הסרת תווים מיותרים בו הצעתי דרך טובה יותר (לטעמי) לבצע מטלה מקדימה שלו, ואילו הפוסט הזה הוא בעקבות פטנט יפה שלמדתי ממנו – ביצוע Split בעזרת XML, ואשר סייע לי לפתור באופן אלגנטי בעייה שנתקלתי בה בעבר.

נתחיל מבעייה פשוטה יותר שטיפלתי בה בעקיפין מספר פעמים בעבר (אופציית Multi-Value ב-Reporting Services, יחס של רבים לרבים ללא טבלת עזר) ואציג אותה כאן בצורה מסודרת: נניח שיש לנו טבלת מכירות של מוכרים (מי מכר מה וכמה) וטבלת עמלות שמגדירה לכל מוכר עבור אילו פריטים יקבל עמלה:

If Object_Id('tempdb..#T_Mehirot','U') Is Not Null Drop Table #T_Mehirot;
Go

Create Table #T_Mehirot(ID Int Identity,
                        Moher Varchar(10),
                        Prit Varchar(10),
                        Camut Int);
Go

Insert
Into   #T_Mehirot
Select 'Hila','Milk',10 Union All
Select 'Hila','Bread',5 Union All
Select 'Hila','Butter',2 Union All
Select 'Hila','Butter',1 Union All
Select 'Gal','Milk',6 Union All
Select 'Gal','Milk',1 Union All
Select 'Gal','Bread',3 Union All
Select 'Gal','Butter',2;
Go

Select  *
From    #T_Mehirot;
Go

If Object_Id('tempdb..#T_Amalot','U') Is Not Null Drop Table #T_Amalot;
Go

Create Table #T_Amalot(Moher Varchar(10),
                       Pritim Varchar(Max));
Go

Insert
Into   #T_Amalot
Select 'Hila','Milk,Butter,Bread' Union All
Select 'Gal','Bread';
Go

Select  *
From    #T_Amalot;
Go

clip_image002

כפי שאפשר להבחין- טבלת העמלות (השניה) כוללת רשימה בצורת מחרוזת מופרדת בפסיקים, ולכאורה יש לבצע לה Split כדי שיהיה ניתן ליצור Join בין טבלת המכירות (הראשונה) לבינה. למעשה ניתן לעשות זאת בצורה פשוטה ויעילה יותר כך-

Select  *
From    #T_Mehirot M
Inner Join #T_Amalot A
        On M.Moher=A.Moher
        And ','+A.Pritim+',' Like '%,'+M.Prit+',%';

clip_image004

הוספתי לרשימה פסיקים משני הצדדים, וחיפשתי התאמות לפריטים הנמכרים בעזרת Like (וגם כאן- פסיקים משני הצדדים).

הבעייה הקשה יותר היא מה לעשות כשיש שתי עמודות עם רשימות מופרדות בפסיקים, ואז לא מדובר בשאלה יש/אין התאמה, אלא מה מתאים; ובמילים פשוטות יותר- להכין רשימת חיתוך של שתי הרשימות עם האיברים המשותפים:

If Object_Id('tempdb..#Tbl','U') Is Not Null Drop Table #Tbl;
Go

Create Table #Tbl(ID Int Identity,
                  L1 Varchar(Max),
                  L2 Varchar(Max));
Go

Insert
Into   #Tbl
Select '1,3,5,7,9','1,2,3,4,5' Union All
Select 'Avi,Batya,Gal,Dan','Gal,Dan,Batya,Avi' Union All
Select '','abc,def,ghi' Union All
Select ' xxx,yyy','xxx, yyy';
Go

Select  *
From    #Tbl;
Go

clip_image006

במקרה זה מדובר בדוגמה טכנית ללא סיפור כיסוי מקצועי: בשורה הראשונה יש התאמה חלקית בין הרשימות, בשניה התאמה מלאה אך בסדר שונה, בשלישית רשימה ריקה ורשימה עם תוכן, וברביעית רשימות דומות אבל עם רווחים מובילים שאמורים למנוע התאמות.

במקרה זה אנחנו לא רק נאלצים לכאורה לבצע Split, אלא חייבים; אבל כדי לא להסתבך ביצירת אובייקטים כמו פונקציה המבצעת Split וכו' אשתמש בפטנט שלמדתי מהפוסט הנ"ל לביצוע Split בעזרת XML, אבצע Join בין הרשימות, ואהפוך את רשימת החיתוך חזרה למחרוזת – שוב בעזרת XML.

במילים פשוטות: שימוש כפול ב-XML לביצוע Split של רשימה לסט, וביצוע שרשור של סט לרשימה:

With T As
(Select *,
        Cast('<Root><S>'+Replace(L1,',','</S><S>')+'</S></Root>' As XML) XML1,
        Cast('<Root><S>'+Replace(L2,',','</S><S>')+'</S></Root>' As XML) XML2
From    #Tbl)
Select  *,
        Stuff((Select ','+L1 As [text()]
              From    (Select S1.value('.','Varchar(MAX)') L1
                      From    XML1.nodes('//Root/S') Records1(S1)
                      Inner Join XML2.nodes('//Root/S') Records2(S2)
                              On S1.value('.','Varchar(MAX)')=S2.value('.','Varchar(MAX)')) T2
              Order By T2.L1
              For XML Path('')),1,1,'') L
From    T
Order By ID;

clip_image008

ההמרה ל-XML נעשית בתוך ה-CTE,

ה-Join מתבצע בין שתי הרשימות שהומרו לסטים,

הסט המקבל הופך חזרה למחרוזת בעזרת For XML Path,

והתוצאה מוצגת בעמודה הימנית L.

מודעות פרסומת

3 תגובות »

  1. […] T_Primes; על ביצוע Split בעזרת XML כתבתי כאן. וכעת ניצור טבלה לדוגמה ונאכלס אותה […]

    פינגבאק של ישום היררכיה (עץ) בעזרת מספרים ראשוניים - גרי רשף — 19/01/2012 @ 18:42

  2. […] ביצוע Split בעזרת XML כתבתי כאן. וכעת ניצור טבלה לדוגמה ונאכלס אותה בנתונים: If […]

    פינגבאק של ישום היררכיה (עץ) בעזרת מספרים ראשוניים - גרי רשף — 17/01/2012 @ 21:24

  3. […] על ביצוע Split בעזרת XML כתבתי כאן. […]

    פינגבאק של ישום היררכיה (עץ) בעזרת מספרים ראשוניים « הבלוג של גרי רשף — 17/01/2012 @ 21:05


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

הזינו את פרטיכם בטופס, או לחצו על אחד מהאייקונים כדי להשתמש בחשבון קיים:

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

יצירה של אתר חינמי או בלוג ב־WordPress.com.

%d בלוגרים אהבו את זה: