ממוצעים נעים

11/08/2012

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

ליצירת הנתונים להדגמה אני בדרך כלל משתמש בטבלת sys.messages להדגמות מכיוון שהיא מגיעה עם 100-250 אלף שורות (גרסאות 2008 ו-2012 בהתאמה) ולא צריך לכתוב סקריפטים מסובכים ליצירת נתונים. דא עקא- אין בטבלה נתונים עתיים (=לאורך זמן) אבל עם כמה אילתורים נקבל טבלת שערי מניות (לכאורה):

Use tempdb;

Go


If Object_ID('T_Rates','U') Is Not Null Drop Table T_Rates;

Go


Select  language_id Share,

        Cast(message_id As DateTime) [Date],

        severity Rate

Into    T_Rates

From    sys.messages;


Alter Table T_Rates Alter Column Share Int not Null;

Go


Alter Table T_Rates Alter Column [Date] DateTime not Null;

Go


Alter Table T_Rates Add Constraint PK_T_Rates Primary Key Clustered (Share,[Date]);

Go


Select * From T_Rates;

image

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

בגרסאות הקודמות ל-2012 (2005 – R2 2008) עבור N=3 נחשב כך:

Declare @I Int;


Set @I=3;

With T As

(Select Row_Number() Over(Partition By Share Order By [Date]) N,

        *

From    T_Rates)

Select  (Select Avg(Cast(Rate As Float)) From T T2 Where T2.Share=T1.Share And T1.N-T2.N Between 0 And 2) Avrg,

        *

From    T T1

Order By Share,

        N;

image

בגרסת 2012 ניתן לבצע זאת בדרך אחרת הודות ליכולות החדשות של פונקציות החלון, אינני משווה כאן ביצועים אבל יש להדגיש שב-2012 מדובר ב-Table Scan בודד לעומת אחד ראשי ומשני שמופעל מספר רב של פעמים:

Select  *,

        Avg(Cast(Rate As Float)) Over(Partition By Share

            Order By [Date]

            Rows Between 2 Preceding And Current Row) RnAvg

From    T_Rates

Order By Share,

        [Date];

image

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

ממוצע נע משוקלל משקלל את הערכים ב-N התקופות הרלוונטיות תוך מתן משקל גבוה יותר לערכים האחרונים. למשל, עבור N=3, הערך האחרון (והמשמעותי) יוכפל ב-3, זה שלפניו ב-2, והראשון (והפחות משמעותי) ב-1; וסכום המכפלות יחולק ב-1+2+3=6. המשקל במקרה זה יורד בקו ישר מ-N לתקופה האחרונה ועד ל-1 לתקופה הראשונה.

בגרסאות קודמות ל-2012 עבור N=3 נחשב כך:

Declare @I Int;

Set     @I=3;

With T As

(Select Row_Number() Over(Partition By Share Order By [Date]) N,

        *

From    T_Rates)

Select  T1.Share,

        T1.N,

        Max(T1.Rate) T_Rates,

        Sum((Case When @I>T1.N Then T1.N Else @I End-(T1.N-T2.N))*Cast(T2.Rate As Float))/(Case When @I>T1.N Then T1.N Else @I End*(Case When @I>T1.N Then T1.N Else @I End+1)/2) Avrg

From    T T1

Inner Join T T2

        On T1.Share=T2.Share

        And T1.N-T2.N Between 0 And @I-1

Group By T1.Share,

        T1.N

Order By T1.Share,

        T1.N;

image

הפעם זה קצת יותר מסובך, בעיקר בגלל מקרי הקצה (השורה עם ה-Sum): ביצעתי Join עצמי בו לכל שורה מהטבלה הותאמו 3 השורות האחרונות שלה כולל הנוכחית,

על כל 3 שורות כאלו התבצע Sum תוך מכפלה מתאימה של כל שורה באינדקס המחושב שלה (1,2,3),

וחלוקה ב-6 (1+2+3 שהם 3*(3+1)/2).

את הימים הראשונים כשיש פחות משלושה ימים יש לחשב אחרת, ולכן תנאי ה-Case המסורבלים בחישוב.

ב-SQL Server 2012 הגישה שונה לחלוטין:

With T As

(Select Row_Number() Over(Partition By Share Order By [Date]) N,

        *

From    T_Rates),

T1 As

(Select *,

        Sum(N*Cast(Rate As Float)) Over(Partition By Share

                    Order By [Date]

                    Rows Between 2 Preceding And Current Row) Sum1,

        Sum(Cast(Rate As Float)) Over(Partition By Share

                    Order By [Date]

                    Rows Between 2 Preceding And Current Row) Sum2,

        Count(1) Over(Partition By Share

                    Order By [Date]

                    Rows Between 2 Preceding And Current Row) Cnt

From    T)

Select  *,

        (Sum1-(N-3)*Sum2)/(Cnt*(2*3+(Cnt-1)*(-1))/2)

From    T1

Order By Share,

        [Date];

image

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

את הרעיון מאחורי הקוד אדגים בעזרת האיור הבא המתייחס לממוצע משוקלל על פני 6 תקופות (ולא 3) ולחישוב של תקופה 11 המשקללת את 6 התקופות 6-11:

ממוצע נע משוקלל

את שורה 11 נכפיל ב-11, את שורה 10 ב-10 וכו’ (המונה “זמין” בחישובי פונקציית החלון בכל שורה)

ובסה”כ נקבל את כל ה”טרפז” שגובהו 6, בסיסו העליון 6, ובסיסו התחתון 11.

מהטרפז יש להחסיר את כל המלבן השמאלי שבסיסו 5 וגובהו 6, שהוא סכום 6 האחרונים כפול (11 פחות 6) כדי לקבל את המשולש הימני שהוא הוא המכפלה שיש לחלק ב-6+5+4+3+2+1=21 כדי לקבל את הממוצע המשוקלל.

Sum1 הוא הטרפז כולו,

Sum2 הגובה של המלבן (סכום 6 הערכים) שיש להכפיל בבסיסו,

Cnt הגובה של המלבן (6 במקרה זה);

וכמובן- יש להתחשב במקרי הקצה כשיש פחות מ-6 איברים.

ממוצע נע אקספוננציאלי מתייחס לכאורה גם הוא ל-N התקופות האחרונות: מחושב מקדם שקלול 2/(N+1)שמשמעותו היא שעבור N=6 המקדם הוא 2/7 ואזי יש לשקלל את הערך האחרון ב-2/7 ואת הממוצע הנע האקספוננציאלי הקודם ב-5/7 (בתקופה הראשונה הממוצע הנע האקספוננציאלי שווה לערך המניה או המכירות של אותה תקופה). כתבתי "לכאורה" מכיוון שבפועל כל ערך משפיע על כל הממוצעים העתידיים, למעט מקרה הקיצון של N=1.

במקרה של N=3 הערך השוטף משוקלל ב-1/2 והממוצע הקודם ב-1/2.

במיוחד בשביל זה המציאו את ה-CTE הרקורסיבי:

Declare @I Int;

Set @I=3;

With T As

(Select Row_Number() Over(Partition By Share Order By [Date]) N,

        *

From    T_Rates),

T1 As

(Select *,

        Cast(Rate As Float) EMA

From    T

Where   N=1

Union   All

Select  T.N,

        T1.Share,

        T.Date,

        T.Rate,

        2./(@I+1)*T.Rate+(@I-1.)/(@I+1.)*T1.EMA EMA

From    T1

Inner Join T

        On T1.Share=T.Share

        And T1.N=T.N-1)

Select  *

From    T1

--Order By Share,

-- N 

option  (MaxRecursion 0);

image

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

זה אחד המקרים בהם יש להשתמש ב-Cursor: הוא יעבור חיש על כל השורות, בכל שורה יזכור את ערך ה-EMA הקודם ויחשב בעזרתו את הנוכחי, ויעבור לשורה הבאה. ב-CTE רקורסיבי יש לשלוף בכל פעם את השורה הנוכחית מהטבלה, וזו כמובן איוולת. דוגמה:

Alter Table T_Rates Add MyEMA Float;


Declare @Share Int,

        @PrevShare Int,

        @Date DateTime,

        @Rate Int,

        @EMA Float,

        @I Int;

Select  @I=3;

Declare Cr Cursor For Select Share, [Date], Rate From T_Rates Order By Share, Date For Update Of MyEMA;

Open    Cr;

Fetch   Cr Into @Share, @Date, @Rate;

While   @@Fetch_Status = 0

        Begin

        If @Share<>IsNull(@PrevShare,0)

        Select @EMA=Null,

               @PrevShare=@Share;

        Select @EMA=2./(@I+1.)*@Rate+(@I-1.)/(@I+1.)*IsNull(@EMA,@Rate);

        Update T_Rates

        Set    MyEMA=@EMA

        Where  Current Of Cr;

Fetch   Cr Into @Share, @Date, @Rate;

End

Close   Cr;

Deallocate Cr;

Go


Select * From T_Rates Order By Share, Date;

image

אצלי הריצה נמשכה כ-37 שניות על כ-250,000 שורות.

מה עם SQL 2012? הרי יש שם פונקציית Lag שיכולה לפנות לכאורה לשורה הקודמת, לקחת את ה-EMA משם ולחשב את הנוכחי.. רק לכאורה: EMA אינו ערך בשורה הקודמת אלא חישוב, ו-Lag אינה יכולה לפנות לחישוב הקודם שהיא עצמה ביצעה.. כלומר- אם בטבלה יש עמודות A,B,C ופונקציית Lag מחשבת את עמודה D; היא אינה יכולה לפנות לעמודה D בשורה הקודמת אלא רק ל-A,B,C שהן עמודות בטבלה; ובאופן דומה לא יעבדו עמודה D מחושבת הכוללת שימוש ב-Lag או View הכולל את עמודה D..

פתרון שמצאתי מאוחר יותר, יעיל אך מוגבל, בעזרת פונקציות חלון, ניתן לראות כאן.

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *

תגובה אחת

  1. Pingback: מגבלות של פונקציות חלון | גרי רשף