האופרטור Values

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

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

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

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

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

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

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

Select *,

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

From   T_Mehirot;

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

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

Select *,

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

From   T_Mehirot;

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

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;

ביצעתי 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;

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

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;

בכיוון אחר- ניתן להשתמש ב-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;

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

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

וכעת משיש לנו סט ניתן להוסיף או להחליף את ה-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);