הבעיה:
לשפת SQL הסטנדרטית יש מספר פונקציות הקבצה כגון SUM,MIN,MAX,AVG וכד'.
מושג סטטיסטי מאוד נפוץ הינו החציון (median) – זהו אותו ערך המחלק את הקבוצה כך שלפניו חצי איברי הקבוצה שקטנים ממנו ואחריו כל מחצית האיברים שגדולים ממנו
לשפת SQL אין מימוש לחציון. מאמר זה יציג פרוצדורה המממשת חציון.
הפתרון המוצע במאמר זה משתמש בטבלאות זמניות. אלה הן טבלאות המסומנות עם # בתחילת שמן.
טבלאות אלו נוצרות ב-tempdb ונמחקות ביציאה מה-session.
בנוסף, אנו נשתמש כאן ב-SQL דינאמי (= בניית פקודה כמחרוזת תוך כדי ריצה ורק לאחר מכן הרצתה),
ולבסוף נשתמש ב-CURSOR שניתן "לגלילה" – SCROLLABLE CURSOR – סוג של סמן שניתן "לעלות ולרדת בתוצאות שלו)
SCROLLABLE CURSORS
כפי שניתן ללמוד לגבי cursors מ http://en.wikipedia.org/wiki/Cursor_(databases),
הסוג המיוחד של scrollable cursors הינו משתנה מסוג cursor שיש לו את האפשרות ל"שנות" את כיוון התקדמותו – קדימה או אחורה וכן יש באפשרותו לבצע דילוג לרשומה ספציפית – הנ"ל נקרא דילוג אבסולוטי או דילוג יחסית למיקומו הנוכחי – דילוג רלאטיבי (יחסי).
מגדירים Scrollable Cursor כך:
DECLARE cursor_name sensitivity SCROLL CURSOR FOR SELECT ... FROM ...
השימוש בכל הפקודות ה-cursor האחרות זהה, למעט פקודות ה-FETCH ששם הוכנסו שיפורים ושינויים שנעזרים ביכולת ה CURSOR לזוז לכל כיוון ולקפוץ על פני שורות התוצאה:
FETCH [ NEXT | PRIOR | FIRST | LAST ] FROM cursor_name FETCH ABSOLUTE FROM cursor_name – לדילוג למיקום אבסולוטי בתוצאה FETCH RELATIVE FROM cursor_name – לדילוג יחסי
NEXT – תזוזה רשומה קדימה
PRIOR – תזוזה רשומה אחורה
FIRST – תזוזה לרשומה ראשונה
LAST – תזוזה לרשומה אחרונה
המימוש
פרמטרי הפרוצדורה:
- שם הטבלה/view
- שם השדה עליו מחשבים חציון
הפרוצדורה מחזירה תשובה במשתנה מסוג SQL variant, שכן התשובה יכולה להיות מספר, מחרוזת , תאריך וכו' – אין הגבלה לסוג השדה.
(הסבר על סוג data type זה: לינק) .
הפרוצדורה בונה טבלה זמנית של ערכי השדה ממוינים ע"י הרצת Dynamic SQL – כלומר אנו בונים טבלה שמכילה שדה מסוג SQL variant :
Create Table #tempmedian (col sql_variant)
אח"כ, בונים פקודת Dynamic SQL שמכניסה לטבלה זו ערכים בצורה הבאה:
set @sqlstmt = 'Insert #tempmedian select '+@columnname+' FROM '+ @tablename + ' order by 1 asc '
מהפרמטרים שאנו מקבלים, בונים שאילתא ומכניסים את תוצאות השאילתא לטבלה הזמנית שיצרנו באמצעות פקודת EXEC.
העובדה שזהו שדה SQL variant מבטיחה שכל סוג שדה תאריכי/אלפאנומרי/מספרי יהיה קביל.
לאחר מכן נגדיר SCROLLABLE CURSOR על הטבלה הזמנית ואנו גוללים באמצעות פקודת FETCH ABSOLUTE ישירות לאמצעו ומחזירים את הערך המבוקש.
להלן הקוד:
Create proc sp_calc_median (@tablename varchar(50), @columnname varchar(50), @result sql_variant OUTPUT) as begin declare @sqlstmt varchar(200) declare @midCount int set noCount on set @sqlstmt = 'Insert #tempmedian select ' + @columnname + ' FROM ' + @tablename + ' order by 1 asc ' Create Table #tempmedian (col sql_variant) exec (@sqlstmt) declare c_med cursor SCROLL for select * from #tempmedian select @midCount = round ( count(*) * 0.5,0 ) from #tempmedian open c_med fetch absolute @midCount from c_med into @result close c_med deallocate c_med drop table #tempmedian end go
דוגמא להרצת הפרוצדורה:
למשל בתוך Northwind DB –
declare @res sql_variant -- declaring an sql_variant column exec sp_calc_median 'northwind..products','productname',@res OUTPUT -- exec proc print convert (nvarchar,@res) -- getting the result for a varch column exec sp_calc_median 'northwind..products','UnitPrice',@res OUTPUT print convert (real,@res) -- getting result for a numeric column
והתוצאות:
לסיכום
דוגמת TSQL פשוטה זו הראתה בכמה שורות קוד כמה אספקטים חשובים של השפה שהם שימוש ב-CURSORS מסוג SCROLL, שימוש ב-TEMPRARY TABLE וכן שימוש ב DYNAMIC SQL.
לפרטים נוספים על SCROLLABLE CURSOR:
http://www.databasejournal.com/features/mssql/article.php/1439731/Using-SQL-Server-Cursors.htm
נתראה ברשומה הבאה!
אלי לייבה
Latest posts by אלי לייבה (see all)
- Buffered Pool Extension – אפשרות חזקה ב SQL SERVER 2014 - 07/05/2014
- עמידות מעוכבת של טרנזקציות ב SQL Server 2014 - 05/02/2014
- פרוצדורה למציאת החציון – MEDIAN מטבלה או View - 20/09/2011
5 תגובות ל- “פרוצדורה למציאת החציון – MEDIAN מטבלה או View”
In oracle (since 2003, version 10.1), there is a built-in function – median…
Perhaps MSS will have a built-in function. This implementation shows other applicative abilities of MSS
שלום רב
בהמשך למאמר על ה- cursors אני אעלה שאלה ואתן עליה תשובה בקרוב :
ב- SQL server 2008R2 איך אני מתוך SRS יעול לדפדף בסט רשומות תוך שימוש בפונקציות של MSSQL מתוך מטרה לחזור אחרה מספר צעדים ע"פ דרשיה ?
בdenali יש פתרוון
חלקי
דוד יצחק
בהמשך למאמר על ה- cursors אני אעלה שאלה ואתן עליה תשובה בקרוב :
ב- SQL server 2008R2 איך אני מתוך SSRS יכול לדפדף בסט רשומות תוך שימוש בפונקציות של MSSQL מתוך מטרה לחזור אחרה מספר צעדים ע”פ דרישה ? פונקציה זו היא לא מובנית ב- SSRS .
ב- denali יש פתרון.
דוד יצחק
נכון מאוד אך ב SQL סטנדרטי לא היתה פונקציה לכך ב MSS
השאר תגובה: