חישוב שינויים וסיכומים בעזרת פונקציות החלון החדשות של SQL Server 2012 Denali

מד"ד התלוצץ פעם במידה רבה של צדק שבעזרת הפונקציות החדשות של גרסת Denali כל אחד יוכל להיות אב"ג, ובפוסט הזה אראה כיצד ניתן לחשב כעת בקלות הפרשים (מוחלטים ויחסיים) בסדרות תקופתיות, וכיצד ניתן מנגד לסכום את ההפרשים במקרה הצורך ולקבל את הסכומים התקופתיים.
לא אראה – לצורך ההשוואה – כיצד זה נעשה בעבר, ואציין רק שזה "קצת" מסובך ו"קצת" כבד; אלא אם כן מד"ד מארגן לי את ה-FastTrack שהגיעה לאחרונה לארץ + צוות חילוץ של פיקוד העורף.

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

Use tempdb;

Go

If Object_Id('T_Toshavim','U') Is Not Null Drop Table T_Toshavim;

Go

Create table T_Toshavim(Shana SmallInt Primary Key,

                        Toshavim Decimal(5,1),

                        Shinuy Decimal(5,1));

Go

Insert

Into T_Toshavim(Shana,Toshavim,Shinuy)

Values  (1948,872.7,NULL),

        (1949,1173.9,301.2),

        (1950,1370.1,196.2),

        (1951,1577.8,207.7),

        (1952,1629.5,51.7),

        (1953,1669.4,39.9),

        (1954,1717.8,48.4),

        (1955,1789.1,71.3),

        (1956,1872.4,83.3),

        (1957,1976.0,103.6),

        (1958,2031.7,55.7),

        (1959,2088.7,57.0),

        (1960,2150.4,61.7),

        (1961,2234.2,83.8),

        (1962,2331.8,97.6),

        (1963,2430.1,98.3),

        (1964,2525.6,95.5),

        (1965,2598.4,72.8),

        (1966,2657.4,59.0),

        (1967,2776.3,118.9),

        (1968,2841.1,64.8),

        (1969,2929.5,88.4),

        (1970,3022.1,92.6),

        (1971,3120.7,98.6),

        (1972,3225.0,104.3),

        (1973,3338.2,113.2),

        (1974,3421.6,83.4),

        (1975,3493.2,71.6),

        (1976,3575.4,82.2),

        (1977,3653.2,77.8),

        (1978,3737.6,84.4),

        (1979,3836.2,98.6),

        (1980,3921.7,85.5),

        (1981,3977.7,56.0),

        (1982,4063.6,85.9),

        (1983,4148.5,84.9),

        (1984,4199.7,51.2),

        (1985,4266.2,66.5),

        (1986,4331.3,65.1),

        (1987,4406.5,75.2),

        (1988,4476.8,70.3),

        (1989,4559.6,82.8),

        (1990,4821.7,262.1),

        (1991,5058.8,237.1),

        (1992,5195.9,137.1),

        (1993,5327.6,131.7),

        (1994,5471.5,143.9),

        (1995,5612.3,140.8),

        (1996,5757.9,145.6),

        (1997,5900.0,142.1),

        (1998,6041.4,141.4),

        (1999,6209.1,167.7),

        (2000,6369.3,160.2),

        (2001,6508.8,139.5),

        (2002,6631.1,122.3),

        (2003,6748.4,117.3),

        (2004,6869.5,121.1),

        (2005,6990.7,121.2),

        (2006,7116.7,126.0),

        (2007,7243.6,126.9),

        (2008,7419.1,175.5),

        (2009,7552.0,132.9),

        (2010,7695.1,143.1);

Go

Select  *

From    T_Toshavim

Order By Shana;

Go

כפי שאפשר לראות- בטבלה יש יתירות, ומופיעים שם גם מספר התושבים בכל שנה וגם השינוי השנתי.

אנחנו נניח בשלב הראשון שרק מספר התושבים מופיע ושיש לחשב את השינוי השנתי,

ובשלב השני נניח שמופיעים השינויים וצריך לסכום אותם ולחבר למספר התושבים ההתחלתי כדי לדעת את מספרם בכל שנה:

Select  *,

        Toshavim-Lag(Toshavim,1) Over(Order By Shana) Shinuy1

From    T_Toshavim

Order By Shana;

פונקציית Lag מאפשרת למצוא את מספר התושבים בשנה הקודמת, ואם מפחיתים את זה ממספר התושבים הנוכחי – מקבלים את השינוי.

סכימת השינויים:

Select  *,

        First_Value(Toshavim) Over(Order By Shana)+

        Sum(Shinuy) Over(Order By Shana Rows Between Unbounded Preceding And Current Row) Toshavim1

From T_Toshavim

Order By Shana;

פונקציית החלון First_Value מאפשרת למצוא את מספר התושבים ההתחלתי (יש כאן כשל מתודי מכיוון שאני מניח שמספר התושבים נתון אבל זו הזדמנות להציג גם פונקציה זו),

ולפונקציית החלון Sum התווסף האופרטור Rows שמאפשר להגדיר מסגרת דינאמית (Frame) שאותו יש לסכום.

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

If Object_Id('T_Madad','U') Is Not Null Drop Table T_Madad;

Go

Create Table T_Madad(Shana SmallInt,

                    Hodesh TinyInt,

                    Madad Decimal(10,7),

                    Shinuy Decimal(19,18),

                    Constraint PK_T_Madad Primary Key Clustered(Shana, Hodesh));

Go

Insert

Into    T_Madad

Values  (2010, 12, 101.7907634, 0.003717471847701109),

        (2010, 11, 101.4137606, 0.000930232490198810),

        (2010, 10, 101.3195099,0.002798507257722001 ),

        (2010, 9, 101.0367578, 0.002806360879009696),

        (2010, 8, 100.7540057, 0.004699248775403322),

        (2010, 7, 100.2827521, 0.004721434971020463),

        (2010, 6, 99.8114986, 0.002840908882540784),

        (2010, 5, 99.5287465, 0.003802282101270077),

        (2010, 4, 99.1517436, 0.008628954305672938),

        (2010, 3, 98.3034873, 0.000959692827914721),

        (2010, 2, 98.2092366, -0.002870813187265860),

        (2010, 1, 98.4919887, Null);

Go

Select  *

From    T_Madad

Order By Shana,

        Hodesh;

Go

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

Select  *,

        Madad/Lag(Madad,1) Over(Order By Shana,Hodesh)-1 Shinuy1

From    T_Madad

Order By Shana,

        Hodesh;

חילקנו את המדד הנוכחי בקודם לו והחסרנו 1.

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

Select  *,

        First_Value(Madad) Over(Order By Shana,Hodesh)*Exp(Sum(Log(1+IsNull(Shinuy,0)))

                                                                Over(Order By Shana,Hodesh

                                                                Rows Between Unbounded Preceding And Current Row)) Madad1

From    T_Madad

Order By Shana,

        Hodesh;

המדד הראשון (98.4919887) המתקבל על ידי First_Value

מוכפל במכפלה של השינויים במדד (ליתר דיוק: השינוי במדד + 1) המתקבלת מהפעלה של פונקציית Log (לפי הבסיס הטבעי) עליה, סכימה מצטברת שלהם על ידי Sum, וביצוע אנטי-לוג בעזרת הפונקציה Exp המחזירה e^x כאשר X הוא הפרמטר (סכום הלוגריתמים) המועבר לפונקציה.