פרוצדורה למציאת החציון – MEDIAN מטבלה או View

הבעיה:

לשפת 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:

נתראה ברשומה הבאה!

The following two tabs change content below.

• Bio
• Latest Posts

אלי לייבה

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