גרי רשף

חישוב מכפלת ערכים בשליפת Group By

ב-TSQL יש פונקציית Sum לסיכום ערכים, אך אין פונקציית Product לכפילתם. כיצד נחשב איפוא מכפלה של ערכים בשליפת Group By?
הזכרתי בעבר את הפתרון שלמדתי מאב"ג ושהוא עצמו למד מאחרים, ומדי פעם אני נאנח בצער על שלא אני המצאתי את הפטנט הזה: כשלמדתי בתיכון לפני קרוב ל-40 שנה מחשבוני הכיס רק הופיעו, אנחנו היינו כחולמים כשראינו שהם מסוגלים לחבר 1 ועוד 1 ולהחזיר את התוצאה 2, והמשוכללים שבהם כללו פונקציות כמו שורש והראו שהשורש הריבועי של 9 הוא 2.9999999 (בערך)..
לשיעורי חשבון הגענו מצויידים בלוח לוגריתמים- חוברת שכלל טבלאות לוגריתמים של מספרים, וטבלאות סינוס/קוסינוס/טנגנס של זוויות; ובעזרתם פתרנו בעיות חישוביות. הלוגריתמים סייעו לבצע מכפלות מסובכות או חישובי חזקות מעוררי חלחלה בכך שבמקום לכפול שני מספרים היינו מחברים את הלוגריתמים שלהם, ומוצאים את האנטילוג של התוצאה.
זה בערך הרעיון גם בפטנט לחישוב מכפלה:

Select  ID,

        Exp(Sum(Log(MyFld)))

From    MyTbl

Group By ID;

פונקציית Log ב-SQL Server היא לפי הבסיס הטבעי e, והפונקציה Exp מחזירה את e בחזקת הפרמטר שזה בעצם האנטילוג.
יש לפתרון הזה מגבלות, ובראש ובראשונה העובדה שאין Log לאפס ולמספרים שליליים, ולכן גם לבעייה זו נאלתר פתרון.
קודם כל- ניצור בעייה שיש לפתור:

Create Table #T(ID Int, I Int);

Go

Insert

Into #T(ID,I)

Select 1,2  Union All --ID=1 is OK

Select 1,8  Union All

Select 1,5  Union All

Select 2,0  Union All --ID=2 has 1 zero

Select 2,2  Union All

Select 2,8  Union All

Select 2,5  Union All

Select 3,-2 Union All --ID=3 has 2 negatives

Select 3,8  Union All

Select 3,-5 Union All

Select 4,2  Union All --ID=4 Has 1 negative

Select 4,-8 Union All

Select 4,5  Union All

Select 5,0  Union All --ID=5 Has 2 zeros and 3 negatives

Select 5,-2 Union All

Select 5,-8 Union All

Select 5,-5 Union All

Select 5,0;

Go

Select *

From   #T

Order By ID;

וחישוב המכפלה של I לכל ID:

Select  ID,

        Case When Min(Abs(I))=0 Then 0

             Else Exp(Sum(Log(Case When I=0 Then 1 Else Abs(I) End)))*Power(-1,Count(Case When I<0 Then 1 End))

             End

From    #T

Group By ID;

התיבה האדומה: אם יש 0 (אפס) בדרך המכפלה גם 0.
התיבה השחורה: חישוב ה-Log מתבצע על הערך המוחלט, ובנפרד אני מחשב כמה שליליים יש (במקרה של 0 לא משנה מה נבחר).
התיבה התכולה: התוצאה מוכפלת ב-(-1) בחזקת מספר השליליים (אם זוגי אזי 1 ואם שלילי -1).

עד כמה התוצאה מדוייקת? הרי הלוגריתמים אינם מספרים שלמים ובחישובים על הרבה ערכים עלולה להיווצר סטייה ונקבל תוצאות לא מדוייקות.. ביצעתי מספר בדיקות- הצלחתי לחשב עצרת עד 15! (יותר מטריליון) ללא כל סטיה, וחזקות של 2 עד 2²⁴ (יותר מ-16 מיליון) ללא סטיה. מעבר לכך נוצרות סטיות שהן זניחות בהתחשב בגודל המספרים, והן בעייתיות בעיקר מבחינה אסתטית (מכפלה של שלמים מחזירה מספר לא שלם).
יחד עם זאת- גם הבעייה האסתטית לרוב אינה בעייה כלל: מתי נצטרך לחשב מכפלות בצורה כזו? הרי אף אחד אינו נדרש לחשב מכפלות אסטרונומיות של מספרים שלמים; ולרוב נצטרך לחשב מכפלות של אחוזים (למשל- חישוב ריבית דריבית להלוואות) או מכפלות של סיכויים, ואז מדובר מראש בשברים שמכפלתם אף היא שבר.