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

26/07/2011

האופרטור Values

Filed under: Uncategorized — גרי רשף @ 21:57

איציק בן-גן מציג מדי פעם כל מיני טריקים שגורמים לי להיאנח- למה אני לא המצאתי את זה בעצמי?

כשאגדל אני רוצה להיות אב"ג!

הנה דוגמה לטריק שכזה, בעיבוד שלי- נתונה טבלת מכירות של סוכנים לפי שנים:

Use tempdb;
Go

If Object_Id('T_Mehirot','U') Is Not Null Drop Table T_Mehirot
Go

Create Table T_Mehirot(Moher Varchar(20),
                       S2007 Int,
                       S2008 Int,
                       S2009 Int,
                       S2010 Int);
Go

Insert
Into T_Mehirot
Select 'Anat', 56,84,59,52 Union All
Select 'Banay', 63,58,97,12 Union All
Select 'Carmel',51,53,59,57 Union All
Select 'Golan', 39,38,37,31;
Go

Select *
From T_Mehirot;
Go


clip_image002

אנחנו רוצים למצוא את המכירה השנתית הגדולה ביותר לכל מוכר, למשל – 84 עבור המוכרת Anat.

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

הפתרון האלגנטי-

Select *,
       (Select Max(S) From (Values(S2007),(S2008),(S2009),(S2010)) AS D(S)) Mx
From   T_Mehirot;

clip_image004

השימוש ב-Values שלרוב משמש אותנו בפקודות Insert מפורשות, מאפשר להפוך את הערכים מארבע העמודות לסט ולחשב להן מקסימום.

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

Select *,
       (Select Sum(S) From (Values(S2007),(S2008),(S2009),(S2010)) AS D(S)) Sm
From   T_Mehirot;

clip_image006

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

1. בפעולת חיבור יש להישמר מ-Nulls ולהפוך אותם לאפס על ידי IsNull.

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

מה קורה אם בהמשך לדוגמה עם המקסימום, נרצה למצוא את השנה בה הייתה המכירה המקסימלית (ולא רק את גודל המכירה)?

Select *,
       (Select Max(S) From (Values(S2007),(S2008),(S2009),(S2010)) AS D(S)) Mx,
       (Select Max(Case When S2=T3.Mx1 Then S1 Else '' End) From (Values('2007',S2007),('2008',S2008),('2009',S2009),('2010',S2010)) AS D(S1,S2) Cross Join (Select Max(S) Mx1 From (Values(S2007),(S2008),(S2009),(S2010)) AS D(S)) T3) SMx
From   T_Mehirot;

clip_image008

ביצעתי Cross Join בין סט ערכי המכירה לסט ערכי המכירה והשנים, וחיפשתי את המקסימום מהראשון בשני.

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

כיצד מוצאים את המכירה השנייה בגודלה?

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

Select *,
      (Select Max(S) From (Values(S2007),(S2008),(S2009),(S2010)) AS D(S)) Mx,
      (Select Top 1 * From (Select Top 2 * From (Values(S2007),(S2008),(S2009),(S2010)) As D(S) Order By S Desc) T Order By S Asc) Mx2
From  T_Mehirot;

clip_image010

באופן דומה נוכל למצוא גם את המכירה השלישית והרביעית בגודלה, וכך למיין את הנתונים בסדר יורד (משמאל לימין):

Select *,
       (Select Top 1 S From (Select Top 1 * From (Values(S2007),(S2008),(S2009),(S2010)) As D(S) Order By S Desc) T Order By S Asc) Mx1,
       (Select Top 1 S From (Select Top 2 * From (Values(S2007),(S2008),(S2009),(S2010)) As D(S) Order By S Desc) T Order By S Asc) Mx2,
       (Select Top 1 S From (Select Top 3 * From (Values(S2007),(S2008),(S2009),(S2010)) As D(S) Order By S Desc) T Order By S Asc) Mx3,
       (Select Top 1 S From (Select Top 4 * From (Values(S2007),(S2008),(S2009),(S2010)) As D(S) Order By S Desc) T Order By S Asc) Mx4
From   T_Mehirot;

clip_image012

בכיוון אחר- ניתן להשתמש ב-Values לביצוע UnPivot לטבלה:

Select M.Moher,
       T.Y,
       T.S
From   T_Mehirot M
Cross Apply (Select * From (Values('2007',S2007),('2008',S2008),('2009',S2009),('2010',S2010)) As D(Y,S)) T;

clip_image014

לסיכום- ניתן להיעזר באופרטור Values ליצירת סט, לא רק לפקודות Insert Into:

Select * From (Values(52),(16),(71),(60)) As D(S);

clip_image016

וכעת משיש לנו סט ניתן להוסיף או להחליף את ה-Select * ב-Distinct או בפונקציות אגרגטיביות, לבצע סינון ופילטור וכו';

או אפילו לאלתר טבלת מספרים קטנה On the fly לשימוש מקומי, במקרה זה ליצירת רשימת תאריכים של שבעת הימים הבאים:

Select DateAdd(Day,S,Cast(GetDate() As Date)) Taarih
From   (Values(1),(2),(3),(4),(5),(6),(7)) As D(S);

clip_image018

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

2 תגובות »

  1. […] חלופי הוא להשתמש באופרטור Values: Select ID, T1.[Date]From T_SCD TCross Apply (Select * From (Values(T.StartDate),(T.EndDate)) AS […]

    פינגבאק של יעול שאילתת Union עצמי ודוגמה ליישום בבדיקת טבלת Slowly Changing Dimension - גרי רשף — 09/01/2012 @ 20:13

  2. […] פתרון חלופי הוא להשתמש באופרטור Values: […]

    פינגבאק של יעול שאילתת Union עצמי ודוגמה ליישום בבדיקת טבלת Slowly Changing Dimension « הבלוג של גרי רשף — 08/01/2012 @ 20:18


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

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

תמונת Twitter

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

תמונת Facebook

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

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

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

מתחבר ל-%s

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

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