השתמש בפונקציית IndexCol
ב-Oracle Analytics, לעתים קרובות נעשה שימוש במשפטי CASE כאשר חישוב מסתעף במבוסס על ערך משתנה. כאשר במשפט CASE יש הפניה למשתנה, עדיף להשתמש בפונקציה IndexCol מקום לשפר את היעילות של קוד SQL שהופק. נושא זה מתאר את הפונקציה IndexCol ומתי להשתמש בה.
אודות פונקציית IndexCol
משתמשים בפונקציית IndexCol כאשר העמודות או הערכים בחישוב משתנים על פי ערך המשתנה של מושב עבודה, מאגר נתונים או מצגת.
התחביר של פונקציית IndexCol הוא:
INDEXCOL(<<integer_literal>>, <<expr_list>>)
כאשר הארגומנט הראשון נפתר למספר חיובי שלם והפריטים שמרכיבים את <<expr_list>> מקבילים למספר הערכים האפשריים של הארגומנט הראשון. לאחר מכן נעשה שימוש באחד מן הערכים הללו במשפט ה-SQL במבוסס על ערך הארגומנט הראשון.
לדוגמה, אם לארגומנט <<integer_literal>> יש שלושה ערכים אפשריים, אז חייבים להיות שלושה ארגומנטים בארגומנט <<expr_list>> , אחד לכל ערך אפשרי של <<integer_literal>>.
הארגומנט הראשון, לעתים קרובות מבוסס על הערך של משתנה מושב עבודה או משפט CASE ביחס למשתנים. תוכל ליצור מודל לפונקציית IndexCol בקובץ מאגר הנתונים (RPD) או ישירות בעמודת דוח. תוכל לקנן מספר פונקציות IndexCol כדי ליצר משפט יחיד.
ההטבות של פונקציית IndexCol
חישוב שעושה שימוש במשפט <<case when>> נדחף אל קוד ה-SQL הפיזי במלואו. לשם השוואה, פונקציית IndexCol דוחפת למטה אל מסד הנתונים רק את העמודה או הביטוי הדרושים. זאת, כיוון שהערכת פונקציית IndexCol מתבצעת לפני הפקת קוד ה-SQL.
כאשר מבנה הדוח משולב עם מנחי משתנים שמאפשרים בחירה מתוך רשימת ערכים, תוכל לשנות אותו באופן משמעותי ללא כל עלות מוגדלת על ביצועים.
חיסרון אחד של פונקציית IndexCol הוא שלא תוכל להשתמש בה עם דומה בחישובי המספר השלם, למרות שתוכל להשתמש בדומה ברשימת הביטויים. אם חישוב מספר חיובי שלם דורש דומה, עליך להשתמש במשפט CASE במקום זאת.
דוגמה
נניח שיש משתנה מושב עבודה בשם PREFERRED_CURRENCY שמגדיר את המטבע המועדף בשביל משתמש. לאחר מכן, בהתבסס על הערך של משתנה מושב העבודה, ההכנסה מוצגת במטבע שצוין על-ידי המשתמש.
נוצרו שני חישובים כדי להחזיר את המטבע הנכון המבוסס על הערך של משתנה מושב העבודה.
הראשון משתמש במשפט CASE, כמו זה:
CASE
WHEN VALUEOF("NQ_SESSION"."PREFERRED_CURRENCY") = 'USD' THEN "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Usd"
WHEN VALUEOF("NQ_SESSION"."PREFERRED_CURRENCY") = 'EUR' THEN "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Eur"
WHEN VALUEOF("NQ_SESSION"."PREFERRED_CURRENCY") = 'AUD' THEN "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Aud"
ELSE NULL
ENDהשני עושה שימוש בפונקציית IndexCol כמו בדוגמה הזאת:
INDEXCOL(
CASE VALUEOF("NQ_SESSION"."PREFERRED_CURRENCY")
WHEN 'USD' THEN 0
WHEN 'EUR' THEN 1
WHEN 'AUD' THEN 2
END ,
"01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Usd", "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Eur", "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Aud")כיוון שהארגומנט הראשון של פונקציית IndexCol חייב להיפתר למספר חיובי שלם, נעשה שימוש במשפט CASE בשביל הפתרון.
כשמריצים שאילתא באמצעות משפט ה-CASE, כל מהשפט ה-CASE כולו נדחף למטה למסד הנתונים, כיוון שהערכת משפט ה-CASE מתבצעת בזמן ריצה. במקרים מסוימים, דבר זה גורם לבעיות ב-optimizer.
WITH
SAWITH0 AS (select sum(case when 'USD' = 'USD' then T42437.Revenue_Usd when 'EUR' = 'USD' then T42437.Revenue_Eur when 'AUD' = 'USD' then T42437.Revenue_Aud else NULL end ) as c1,
T42412.Office_Dsc as c2,
T42412.Office_Key as c3
from
BISAMPLE.SAMP_OFFICES_D T42412 /* D30 Offices */ ,
BISAMPLE.SAMP_REVENUE_CURR_F T42437 /* F19 Rev. (Converted) */
where ( T42412.Office_Key = T42437.Office_Key )
group by T42412.Office_Dsc, T42412.Office_Key),
SAWITH1 AS (select 0 as c1,
D1.c2 as c2,
D1.c1 as c3,
D1.c3 as c4
from
SAWITH0 D1)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from
SAWITH1 D1
order by c2 ) D1
The same query run using the IndexCol function pushes down only the expression needed to satisfy the query, because the IndexCol function is resolved prior to SQL generation. This helps avoid issues with the Optimizer.
WITH
SAWITH0 AS (select sum(T42437.Revenue_Usd) as c1,
T42412.Office_Dsc as c2,
T42412.Office_Key as c3
from
BISAMPLE.SAMP_OFFICES_D T42412 /* D30 Offices */ ,
BISAMPLE.SAMP_REVENUE_CURR_F T42437 /* F19 Rev. (Converted) */
where ( T42412.Office_Key = T42437.Office_Key )
group by T42412.Office_Dsc, T42412.Office_Key),
SAWITH1 AS (select 0 as c1,
D1.c2 as c2,
D1.c1 as c3,
D1.c3 as c4
from
SAWITH0 D1)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from
SAWITH1 D1
order by c2 ) D1חוברות עבודה ו-IndexCol
אתה יכול להשתמש בפונקציה IndexCol בחוברות עבודה.
בדוגמה זו, נעשה שימוש בפונקציית IndexCol כדי לשנות את רמת פירוט התקופה בהמחשה גרפית:
- צור פרמטר אשר ישמש כבורר עמודות על מנת לבחור את רמת פירוט התקופה, במקרה זה, 'חודש' או 'רבעון'.
- צור חישוב מותאם אישית כדי לבצע את פונקציית
IndexCol. כאן, החישוב הוא:indexcol(case when @parameter("Time Selector Value")('Month')='Month' then 0 else 1 end, "HCM - Workforce Core"."Time"."Month Name", "HCM - Workforce Core"."Time"."Quarter") - הוסף את הפרמטר בסרגל הסינון של חוברת עבודה. משתמשים יכולים לשנות את רמת הפירוט של דוח על-ידי בחירה בחודש או רבעון ממסנן בורר העמודות.
