Использование функции 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 if>> полностью переносится в физический код SQL. Для сравнения: функция IndexCol передает в базу данных только требуемый столбец или выражение. Это связано с тем, что функция IndexCol оценивалась до того, как физический код SQL был сгенерирован.
В сочетании с запросами переменных, которые позволяют выбирать значения из списка, можно существенно изменить структуру отчета без увеличения затрат на производительность.
Один из недостатков функции IndexCol – невозможность ее использования совместно с like в целочисленных расчетах. При этом можно использовать like в списке выражений. Если для целочисленного расчета требуется like, вместо этого необходимо использовать инструкцию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 оценивается во время выполнения. В некоторых случаях это вызывает проблемы с оптимизатором.
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") - Добавьте параметр на панель фильтров рабочей книги. Пользователи могут изменять степень детализации отчета, выбирая Месяц или Квартал из фильтра селектора столбца.
