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

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

20/09/2011 | פורסם על ידי

הבעיה:

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

והתוצאות:

Maxilaku
19.5

לסיכום

דוגמת TSQL פשוטה זו הראתה בכמה שורות קוד כמה אספקטים חשובים של השפה שהם שימוש ב-CURSORS מסוג SCROLL, שימוש ב-TEMPRARY TABLE   וכן שימוש ב DYNAMIC SQL.

לפרטים נוספים על SCROLLABLE CURSOR:

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

אלי לייבה, DBA אפליקטיבי בכיר ומפתח אפליקציות בחברת החשמל,
בעל 19 שנות ותק בתחום מסדי נתונים ותכנות.
ניתן לפנות לאלי במייל: iecdba@hotmail.com

5 תגובות ל- “פרוצדורה למציאת החציון – MEDIAN מטבלה או View”

commenter

In oracle (since 2003, version 10.1), there is a built-in function – median…

אלי לייבה | 22/09/2011 בשעה 02:47
commenter

Perhaps MSS will have a built-in function. This implementation shows other applicative abilities of MSS

david itshak | 25/09/2011 בשעה 02:14
commenter

שלום רב
בהמשך למאמר על ה- cursors אני אעלה שאלה ואתן עליה תשובה בקרוב :
ב- SQL server 2008R2 איך אני מתוך SRS יעול לדפדף בסט רשומות תוך שימוש בפונקציות של MSSQL מתוך מטרה לחזור אחרה מספר צעדים ע"פ דרשיה ?
בdenali יש פתרוון
חלקי
דוד יצחק

David Itshak | 25/09/2011 בשעה 02:16
commenter

בהמשך למאמר על ה- cursors אני אעלה שאלה ואתן עליה תשובה בקרוב :
ב- SQL server 2008R2 איך אני מתוך SSRS יכול לדפדף בסט רשומות תוך שימוש בפונקציות של MSSQL מתוך מטרה לחזור אחרה מספר צעדים ע”פ דרישה ? פונקציה זו היא לא מובנית ב- SSRS .
ב- denali יש פתרון.
דוד יצחק

אלי לייבה | 25/09/2011 בשעה 03:20
commenter

נכון מאוד אך ב SQL סטנדרטי לא היתה פונקציה לכך ב MSS

השאר תגובה:

שם (חובה):
אימייל (לא יפורסם) (חובה):
תגובה (חובה):

*



מאמרים קשורים

הפונקציה COALESCE

כולם מכירים את הפונקציה NVL, ומשתמשים בה לא מעט. לעומת זאת, הפונקציה COALESCE, השייכת לאותה משפחה של פונקציות, פחות מוכרת, למרות שהיא קיימת כבר מגרסה 9i ועולה ביכולותיה על NVL הישנה והטובה. מצד שני, אף [...]

טיפ: איך לזכור ב-SQLPLUS שאתה בסביבת היצור

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

עבודה תחת סביבת FireWall

אורי לרנר בטיפ קצר ושימושי על עבודה בסביבת אורקל [...]
Indexes

Indexes in SQL Server 2

ערן שקד ממשיך בסדרת מאמרים על אינדקסים בSQL [...]
Copyright 2019 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: