Používanie funkcie IndexCol

V službe Oracle Analytics sa príkazy CASE často používajú, keď sa výpočet rozvetvuje na základe hodnoty premennej. Keď sa v príkaze CASE odkazuje na premennú, je lepšie použiť funkciu IndexCol, aby sa zlepšila efektivita vygenerovaného kódu SQL. Táto téma opisuje funkciu IndexCol a kedy ju použiť.

Funkcia IndexCol

Funkcia IndexCol sa používa, keď sa stĺpce alebo hodnoty vo výpočte líšia v závislosti od hodnoty premennej relácie, depozitára alebo prezentácie.

Syntax funkcie IndexCol je::

INDEXCOL(<<integer_literal>>, <<expr_list>>)

Výsledkom prvého argumentu je celé číslo a položky, z ktorých pozostáva argument <<expr_list>>, zodpovedajú počtu možných hodnôt prvého argumentu. Jedna z týchto položiek sa potom použije v príkaze SQL na základe hodnoty prvého argumentu.

Ak má napríklad argument <<integer_literal>> tri možné hodnoty, v argumente <<expr_list>> musia byť tri argumenty, jeden pre každú možnú hodnotu argumentu <<integer_literal>>.

Prvý argument je často založený na hodnote premennej relácie alebo príkazu CASE vo vzťahu k premenným. Funkciu IndexCol môžete modelovať v súbore depozitára (RPD) alebo priamo v stĺpci zostavy. Do jedného príkazu môžete vnoriť viacero funkcií IndexCol.

Výhody funkcie IndexCol

Výpočet používajúci príkaz <<case when>> sa do fyzického kódu SQL vloží ako celok. Na druhej strane, funkcia IndexCol odosiela do databázy iba požadovaný stĺpec alebo výraz. Je to preto, lebo funkcia IndexCol sa vyhodnocuje pred generovaním fyzického kódu SQL.

V kombinácii s výzvami premenných, ktoré umožňujú výber v zozname hodnôt, môžete výrazne upraviť štruktúru zostavy bez akýchkoľvek zvýšených nákladov na výkon.

Jednou nevýhodou funkcie IndexCol je, že ju nemožno používať s operátorom like v celočíselných výpočtoch, aj keď operátor like možno používať v zozname výrazov. Ak celočíselný výpočet vyžaduje operátor like, je potrebné namiesto neho použiť príkaz CASE.

Príklad

Prepokladajme, že existuje premenná relácie s názvom PREFERRED_CURRENCY, ktorá pre používateľa nastaví preferovanú menu. Výnos sa potom na základe hodnoty premennej relácie zobrazí v mene, ktorú zadal používateľ.

Vytvorili sa dva výpočty na vrátenie správnej meny na základe hodnoty premennej relácie.

Prvý používa príkaz CASE takto:

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

Druhý používa funkciu IndexCol takto:

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")

Keďže výsledkom prvého argumentu funkcie IndexCol musí byť celé číslo, na získanie tohto výsledku sa používa príkaz CASE.

Keď sa dopyt spustí pomocou výpočtu príkazu CASE, do databázy sa odošle celý príkaz CASE, pretože tento príkaz sa vyhodnocuje v čase behu. V niektorých prípadoch to spôsobuje problémy s optimalizátorom.

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

Zošity a funkcia IndexCol

Funkciu IndexCol môžete používať v zošitoch.

V tomto príklade sa funkcia IndexCol používa na zmenu granularity obdobia vo vizualizácii:

Vytvorte parameter, ktorý sa použije ako selektor stĺpca na výber granularity obdobia, v tomto prípade buď Mesiac, alebo Štvrťrok.

.jpg

Vytvorte vlastný výpočet na vykonanie funkcie IndexCol. Tento výpočet je tu:

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")

Popis GUID-1EBB2C95-3D23-48C6-9754-EF968AFAFE98-default.jpg je uvedený nižšie
.jpg

Pridajte parameter na panel s nástrojmi filtra v zošite. Používatelia potom môžu zmeniť granularitu zostavy výberom položky Mesiac alebo Štvrťrok z filtra výberu stĺpcov.

.jpg