הפונקצייה האגרגטיבית String_Agg משרשרת ערכים למחרוזת ארוכה בשליפות הכוללות Group By. בפוסט הזה אציג את הפונקצייה למי שבמקרה עדיין לא שמע עליה, ולמי שכן - מספר בעיות ופתרונן.
עד שהיא הצטרפה לארגז הכלים של TSQL, השירשור היה מתבצע בעזרת פונקציית Clr או שימוש טריקי ב-XML, אבל עם String_agg - אפילו החבר'ה מה-HR יכולים כיום לשרשר ערכים להנאתם... נתחיל מדוגמה אמיתית אך מפושטת לצורך ההדגמה: יש לנו טבלה של קווי אוטובוסים, כשבכל שורה מופיע מספר הקו, התחנה בה הקו עוצר, והעיר בה הוא עוצר (בפועל הסט מקורו במספר טבלאות ולכן אינו מנורמל):
Create Table #BusLines(Line Int Not Null, Stop Char(1) Not Null, City Varchar(10));
Alter Table #BusLines Add Constraint PK_#BusLines Primary Key Clustered(Line, Stop);
Insert
Into #BusLines
Values
(1,'A','Haifa'),
(1,'B','Haifa'),
(1,'C','Tel Aviv'),
(2,'B','Haifa'),
(2,'A','Haifa'),
(3,'C','Tel Aviv');
Select * From #BusLines;
כעת אני רוצה לקבל רשימה של הקווים, וליד כל אחד - שמות התחנות בהן הוא עוצר, משורשרים עם פסיקים בינהם:
Select Line,
String_Agg(Stop,',') Stops
From #BusLines
Group By Line;
כך הפונקציה String_Agg מאפשרת לשרשר את ערכי עמודת Stop לכל קו, כאשר יש לציין מה יהיה התו המפריד (פסיק, במקרה זה).
שמות התחנות במחרוזת המשורשרת מופיעות לפי סדר אלפבתי עולה, מכיוון שיש Clustered Index על שתי העמודות הרלוונטיות, ולכן צפוי שהן ישלפו על פי הסדר. אם לא, או אם רוצים סדר אחר (נניח סדר תחנות יורד) - יש להוסיף לפונקצייה פסוקית מתאימה:
Select Line,
String_Agg(Stop,',') Within Group (Order By Stop Desc) Stops
From #BusLines
Group By Line;
מי שיסתכל ב-Execution Plan יוכל לראות שהמערכת מיינה את הנתונים מהטבלה לשם כך, ולכן אם יש שתי פונקציות String_agg באותו Select - לא ניתן יהיה למיין כל אחת בדרך אחרת, אלא לחפש פתרון טריקי כמו זה שיוצג בהמשך בעקיפין בהתייחס לבעייה אחרת.
מה קורה אם נרצה לשרשר דווקא את שמות הערים בהן כל קו עובר?
Select Line,
String_Agg(City,',') Cities
From #BusLines
Group By Line;
כפי שאפשר לראות - יש ערכים כפולים במחרוזות. אם נרצה להיפטר מהכפילות נוכל בשאילתת משנה לבצע Distinct על שתי העמודות הרלוונטיות ולקבל בשאילתה הראשית את מבוקשנו:
Select Line,
String_Agg(City,',') Cities
From (Select Distinct Line,
City
From #BusLines) BL
Group By Line;
ומה נעשה אם נרצה "גם וגם" - גם לשרשר את שמות התחנות, וגם את שמות הערים כך שהערכים יהיו יחודיים? ברשימת התחנות לא תהיה בעייה מכיוון שכל קו עובר פעם אחת בכל תחנה (ולכן יש Primary Key על שתי העמודות האלו), אבל מה עם שמות הערים? השימוש בשאילת משנה עם Distinct לא יעבוד במקרה זה והכפילות של הערים תישאר, וגם לידידנו ChatGPT ולהקתו (שאר האפליקציות בגרסתן נכון ליום כתיבת הפוסט) אין פתרון.
בשלב הזה אשאיר את הטבלה הנ"ל בצד, ומכיוון שהפתרונות כרוכים בבעיות ביצועים - אצור טבלה קצת יותר שמנמנה שתטחן את משאבי המערכת, כשאני נוהג להשתמש במקרים כאלה בטבלת המערכת sys.messages שיש בה כ-300K שורות - כל הודעות השגיאה של SQL Server בשפות השונות הנתמכות:
--Drop Table If Exists #messages;
Select *
Into #messages
From sys.messages;
Create Unique Clustered Index Idx_#messages On #messages(language_id,message_id);
Create Index Idx_message_id_severity On #messages(language_id,severity) Include(message_id);
Select Top(10) * From #messages;
כפי שאפשר לראות, יצרתי אינדקס רגיל בנוסף ל-Clustered Index שאזדקק לו בהמשך לשיפור הביצועים.
המטרה: לקבל לכל שפה את מספרי השגיאות משורשרים, ואת ציוני ה-severity משורשרים גם הם, וללא כפילויות. שימו לב שזהו תרגיל טכני, מספרי השגיאות יהיו זהים בכל שפה, וכך גם ה-severity; ואנחנו נתמקד באתגר הטכני (שירשור ללא כפילויות) ובביצועים.
האתגר: Group By על עמודת language_id ושירשור הערכים הייחודיים בעמודת message_id (לא אמורות להיות כפילויות בשל ה-Unique Clustered Index), ושירשור הערכים שבעמודת severity תוך ביצוע Distinct (כן צפויות כפילויות).
הדרך "המלוכלכת" לעשות את זה היא לבצע כל משימה בנפרד - במקרה של message_id בדרך הרגילה, ובמקרה של severity בעזרת שאילתת משנה + Distinct; ואז לאחד את שני הסטים על ידי Join. זה מחייב סריקה כפולה של הטבלה (וליתר דיוק - של ה-Clustered Index עבור message_id ושל האינדקס הרגיל עבור severity), אבל אנחנו ננסה לחשוב על דרכים יעילות יותר:
ביצוע String_Agg על שתי העמודות, ואז - לבצע on the fly פירוק של השירשור של severity לסט על ידי String_Split, שליפה עם Distinct, ו-String_agg חדש (הפעם ללא כפילויות).
שאילתת משנה בה נבצע Group By על language_id & severity ו-שירשור של message_id, ועל זה Group By נוסף רק על language_id ו-String_Agg על שתי העמודות האחרות. אם נחזור לטבלת קווי האוטובוס, היינו מקבלים שם בשאילתת המשנה את הסט הזה:
אפשר לראות שכעת עבור קו 1 (למשל) כל עיר (חיפה & תל אביב) מופיעה פעם אחת, ואילו הערכים שעברו שירשור ראשוני בעמודת Stops ישתרשרו ל-A,B,C ללא כפילויות כי הם ייחודיים עבור כל קו (כלומר- לא יתכן שעבור קו1 נקבל A,B בשורה אחת ו-A,C בשורה השנייה ואז כשנחבר אותם נקבל A,B,AC עם כפילות).
למלאכה:
--String_agg & String_Split on the fly
With T As
(Select M1.language_id,
String_Agg(Cast(M1.message_id As Varchar(Max)),',') messages,
String_Agg(Cast(M1.severity As Varchar(Max)),',') severities
From #messages M1
Group By M1.language_id)
Select T.language_id,
T.messages,
M2.severities
From T
Outer Apply (Select String_Agg(M2.severitiy,',') severities
From (Select Distinct [value] severitiy
From String_Split(T.severities,',') M2) M2) M2;
--Two pahses of Group By
With T As
(Select M1.language_id,
M1.severity,
String_Agg(Cast(M1.language_id As Varchar(Max)),',') languages
From #messages M1
Group By M1.language_id,
M1.severity)
Select T.language_id,
String_Agg(Cast(T.languages As Varchar(Max)),',') languages,
String_Agg(Cast(T.severity As Varchar(Max)),',') severities
From T
Group By T.language_id;
בשני המקרים המערכת נעזרה באינדקס הרגיל, אבל במקרה השני ללא תוספות: Index Scan ופעמיים Stream Aggregate. זה הכי נמוך שאפשר לרדת מבחינת המחיר...
Comments