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 případy jejího použití.
Funkce 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
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, že existuje proměnná relace s názvem PREFERRED_CURRENCY, která nastavuje preferovanou měnu pro uživatele, a pak se na základě hodnoty proměnné relace zobrazí výnosy 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í z nich používá příkaz Case, jako v tomto příkladu:
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 jako v tomto příkladu.
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 ) D1Sešity a INDEXCOL
S příchodem parametrů lze nyní funkci IndexCol 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 volič sloupce pro výběr míry podrobnosti období, v tomto případě buď „Měsíc“, nebo „Čtvrtletí“.
- Dále vytvořte vlastní 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") - Vložte parametr do panelu filtru sešitu. Uživatelé mohou změnit granularitu sestavy výběrem položky „Měsíc“ nebo „Čtvrtletí“ ve filtru pro výběr sloupce.
