Použití funkce IndexCol

Ve službě Oracle Analytics se příkazy CASE často používají, když se výpočet větví na základě hodnoty proměnné. Pokud je v příkazu CASE odkazováno na proměnnou, je vhodnější použít místo ní funkci IndexCol, aby se zvýšila efektivita generovaného kódu SQL. Toto téma popisuje funkci IndexCol a kdy ji použít.

O funkci IndexCol

Funkci IndexCol použijete, pokud se sloupce nebo hodnoty ve výpočtu mění v závislosti na hodnotě proměnné relace, úložiště nebo prezentace.

Syntaxe funkce IndexCol je následující:

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

Kde první argument je celé číslo a položky, které tvoří <<expr_list>> odpovídají počtu možných hodnot prvního argumentu. Jedna z těchto položek se pak použije v příkazu SQL na základě hodnoty prvního argumentu.

Pokud má například argument <<integer_literal>> tři možné hodnoty, pak musí být v argumentu <<expr_list>> tři argumenty, jeden pro každou možnou hodnotu <<integer_literal>>.

První argument je často založen na hodnotě proměnné relace nebo příkazu CASE v odkazu na proměnné. Funkci IndexCol můžete modelovat v souboru úložiště (RPD) nebo přímo ve sloupci sestavy. Do jednoho příkazu můžete vnořit více funkcí IndexCol.

Výhody funkce IndexCol

Výpočet pomocí příkazu <<case when>> se do fyzického kódu SQL přesune celý. Oproti tomu funkce IndexCol přenese do databáze pouze požadovaný sloupec nebo výraz. Je to proto, že funkce IndexCol je vyhodnocena před generováním fyzického kódu SQL.

V kombinaci s výzvami proměnných, které umožňují výběr v seznamu hodnot, můžete výrazně upravit strukturu sestavy bez zvýšených nákladů na výkon.

Jednou z nevýhod funkce IndexCol je, že ji nelze použít s výrazem like v celočíselných výpočtech, ačkoli v seznamu výrazů like použít lze. Pokud celočíselný výpočet vyžaduje výraz like, musíte místo této funkce použít příkaz CASE.

Příklad

Předpokládejme proměnnou relace s názvem PREFERRED_CURRENCY, která pro uživatele nastavuje preferovanou měnu. Poté se na základě hodnoty proměnné relace zobrazí výnos v měně zadané uživatelem.

Byly vytvořeny dva výpočty, které vracejí správnou měnu na základě hodnoty proměnné relace.

První používá příkaz CASE tímto způsobem:

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žívá funkci IndexCol tímto způsobem:

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

Protože první argument funkce IndexCol musí být celé číslo, používá se pro rozlišení příkaz CASE.

Při spuštění dotazu pomocí výpočtu příkazu CASE se celý příkaz CASE přenese do databáze, protože příkaz CASE se vyhodnocuje za běhu. V některých případech to způsobuje problémy s optimalizátorem.

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

Sešity a IndexCol

Funkci IndexCol můžete používat v sešitech.

V tomto příkladu se funkce IndexCol používá ke změně granularity období ve vizualizaci:

Vytvořte parametr, který se použije jako selektor sloupce pro výběr míry podrobnosti období. V tomto případě buď „Month“, nebo „Quarter“.

.jpg''

Vytvořte přizpůsobený výpočet, který provede funkci IndexCol. Zde je výpočet následující:

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 následuje
.jpg''

Přidejte parametr do panelu filtru sešitu. Uživatelé potom mohou změnit granularitu sestavy výběrem položky Month nebo Quarter ve filtru pro výběr sloupce.

.jpg''