Функции

В выражениях можно использовать различные типы функций.

Разделы:

Аналитические функции

Аналитические функции позволяют исследовать данные с помощью моделей, например прогнозов, линий трендов и кластеров. Можно также перетащить аналитические функции в редактор рабочей книги.

Можно также добавить в рабочую книгу прогнозы, линии трендов и кластеры, выбрав их на вкладке "Аналитика" в панели данных рабочей книги. См. раздел Добавление функций статического анализа в визуализации.

Функция Пример Описание Синтаксис

CLUSTER

CLUSTER((product, company), (billed_quantity, revenue), 'clusterName', 'algorithm=k-means;numClusters=%1;maxIter=%2;useRandomSeed=FALSE;enablePartitioning=TRUE', 5, 10)

Эта функция группирует набор записей по одному или нескольким входным выражениям, используя метод k-средних или иерархическую кластеризацию.

CLUSTER((dimension_expr1, ... dimension_exprN), (expr1, ... exprN), output_column_name, options, [runtime_binded_options])

FORECAST

Пример прогноза доходов по дням

В этом примере выбирается прогноз доходов по дням.

FORECAST("A - Sample Sales"."Base Facts"."1- Revenue" Target,
("A - Sample Sales"."Time"."T00 Calendar Date"),'forecast', 'numPeriods=30;predictionInterval=70;') ForecastedRevenue

Пример прогноза доходов по году и кварталу

В этом примере выбирается прогноз доходов по году и кварталу.

FORECAST("A - Sample Sales"."Base Facts"."1- Revenue",
("A - Sample Sales"."Time"."T01 Year" timeYear, "A - Sample Sales"."Time"."T02 Quarter" TimeQuarter),'forecast', 'numPeriods=30;predictionInterval=70;') ForecastedRevenue

Создает модель временного ряда указанного показателя над серией данных, используя экспоненциальную аппроксимацию (ETS), Seasonal ARIMA, ARIMA или Prophet. Эта функция выводит прогноз для набора периодов, как указано в аргументе numPeriods.

См. также ниже дополнительные параметры функции FORECAST.

FORECAST(measure, ([series]), output_column_name, options,[runtime_binded_options])])

Где:

  • measure представляет собой показатель для прогнозирования, например данные о доходах.

  • series представляет собой временной интервал, используемый для построения модели прогнозирования. Элемент series — список из одного или нескольких столбцов измерения времени. Если значение series не указано, временной интервал определяется из запроса.

  • output_column_name представляет собой допустимые имена столбцов для forecast, low, high и predictionInterval.

  • options представляет собой список строк в виде пар "имя/значение", разделенных точкой с запятой (;). Значение может включать выражение %1 ... %N, указанное в runtime_binded_options.

  • runtime_binded_options представляет собой список столбцов и параметров, разделенных запятыми. Значения этих столбцов и параметров оцениваются и решаются во время выполнения отдельного запроса.

См. также ниже дополнительные параметры функции FORECAST.

OUTLIER

OUTLIER((product, company), (billed_quantity, revenue), 'isOutlier', 'algorithm=kmeans')

Эта функция классифицирует запись как выброс по одному или нескольким входным выражениям, используя метод k-средних, иерархическую кластеризацию или алгоритмы обнаружения выбросов в многомерных данных.

OUTLIER((dimension_expr1, ... dimension_exprN), (expr1, ... exprN), output_column_name, options, [runtime_binded_options])

REGR

REGR(revenue, (discount_amount), (product_type, brand), 'fitted', '')

Эта функция выполняет аппроксимацию по линейной модели и возвращает подобранные значения или модель. Эту функцию можно использовать для аппроксимации линейной кривой по двум показателям.

REGR(y_axis_measure_expr, (x_axis_expr), (category_expr1, ..., category_exprN), output_column_name, options, [runtime_binded_options])

TRENDLINE

TRENDLINE(revenue, (calendar_year, calendar_quarter, calendar_month) BY (product), 'LINEAR', 'VALUE')

Oracle рекомендует при просмотре визуализации применить линию тренда с помощью свойства Добавить статистику. См. раздел "Настройка свойств визуализации".

Эта функция выполняет аппроксимацию по линейной, полиномиальной или экспоненциальной модели и возвращает подобранные значения или модель. Значение numeric_expr представляет значение Y для тренда, а series (столбцы времени) представляет значение X.

TRENDLINE(numeric_expr, ([series]) BY ([partitionBy]), model_type, result_type)

Параметры функции FORECAST В таблице ниже перечислены параметры, которые можно использовать с функцией FORECAST.

Имя параметра Значения Описание
numPeriods Целое число Количество периодов для прогнозирования.
predictionInterval От 0 до 100, где более высокие значения указывают более высокую степень достоверности Уровень достоверности прогнозирования.
modelType

ETS (экспоненциальная аппроксимация)

SeasonalArima

ARIMA

Prophet

Модель, используемая для прогнозирования.
useBoxCox

TRUE

FALSE

Если TRUE, используйте преобразование Бокса-Кокса.
lambdaValue Неприменимо

Параметр преобразования Бокса-Кокса.

Игнорируется, если NULL или useBoxCox имеет значение FALSE.

В противном случае данные преобразуются до оценки модели.

trendDamp

TRUE

FALSE

Используется только в модели экспоненциального сглаживания

Если TRUE, используйте затухающий тренд. Если FALSE или NULL, используйте незатухающий тренд.

errorType

Неприменимо

Используется только в модели экспоненциального сглаживания
trendType

N (нет)

A (аддитивный)

M (мультипликативный)

Z (автоматически выбранный)

Используется только в модели экспоненциального сглаживания
seasonType

N (нет)

A (аддитивный)

M (мультипликативный)

Z (автоматически выбранный)

Используется только в модели экспоненциального сглаживания
modelParamIC

ic_auto

ic_aicc

ic_bic

ic_auto (по умолчанию)

Информационный критерий (IC), используемый при выборе модели.

Функции преобразования

Функции преобразования преобразуют значение из одной формы в другую.

Функция Пример Описание Синтаксис

CAST

CAST(hiredate AS CHAR(40)) FROM employee

Меняет тип данных выражения или нулевого литерала на другой тип данных. Например, можно привести к другому типу данных элемент customer_name (с типом данных CHAR или VARCHAR) или birthdate (литерал datetime).

Функция CAST используется для изменения типа данных на тип Date (Дата).

Не используйте TODATE.

CAST(expr AS type)

IFNULL

IFNULL(Sales, 0)

Проверяет, равняется ли значение выражения неопределенному значению, и, если равняется, присваивает выражению указанное значение.

IFNULL(expr, value)

INDEXCOL

SELECT INDEXCOL(VALUEOF ("NQ_SESSION"."GEOGRAPHY_LEVEL"), Country, State, City), Revenue FROM Sales

Использует внешние данные для возврата соответствующего столбца подключенному к системе пользователю.

INDEXCOL([integer literal], [expr1] [, [expr2], ?-])

NULLIF

SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID" FROM employees e, job_history j WHERE e.employee_id = j.employee_id ORDER BY last_name, "Old Job ID";

Используется для сравнения двух выражений. Если они равны, функция возвращает неопределенное значение (NULL). Если они не равны, функция возвращает первое выражение. Указывать литерал NULL в качестве первого выражения нельзя.

NULLIF([expression], [expression])

To_DateTime

SELECT To_DateTime ('2009-03-0301:01:00', 'yyyy-mm-dd hh:mi:ss') FROM sales

Преобразует строковые литералы формата DateTime в тип данных DateTime.

To_DateTime([expression], [literal])

VALUEOF

SalesSubjectArea.Customer.Region = VALUEOF("Region Security"."REGION")

Ссылается на значение переменной семантической модели в фильтре.

Переменные expr используются как аргументы функции VALUEOF. Обращайтесь к статическим переменным семантической модели по имени.

VALUEOF(expr)

Функции извлечения даты

Эти функции рассчитывают или округляют значения меток времени до ближайшего заданного значения времени (например, часа, дня, недели, месяца или квартала).

Рассчитанные метки времени применяются для агрегирования данных с использованием другого типа детализации. Например, можно применить функцию EXTRACTDAY() к датам заказа на продажу, чтобы рассчитать метку времени на полночь в день оформления заказов и агрегировать данные по дням.

Функция Пример Описание Синтаксис

Извлечение дня

EXTRACTDAY("Order Date")
  • 2/22/1967 3:02:01 AM возвращает 2/22/1967 12:00:00 AM.
  • 9/2/2022 10:38:21 AM возвращает 9/2/2022 12:00:00 AM.

Возвращает метку времени на полночь (12 AM) в день, когда появляется входное значение. Например, если 22 февраля входной меткой времени является 3:02:01 AM, функция возвращает метку времени на 12:00:00 AM 22 февраля.

EXTRACTDAY(expr)

Извлечение часов

EXTRACTHOUR("Order Date")
  • 2/22/1967 3:02:01 AM возвращает 2/22/1967 3:00:00 AM.
  • 6/17/1999 11:18:30 PM возвращает 6/17/1999 11:00:00 PM.

Возвращает метку времени на начало часа, в течение которого появляется входное значение. Например, если входной меткой времени является 11:18:30 PM, функция возвращает метку времени на 11:00:00 PM.

EXTRACTHOUR (expr)

Извлечение часов дня

EXTRACTHOUROFDAY("Order Date")
  • 2014/09/24 10:58:00 возвращает 2000/01/01 10:00:00.
  • 2014/08/13 11:10:00 возвращает 2000/01/01 11:00:00

Возвращает метку времени, где час соответствует часу входного значения со значениями года, месяца, дня, минут и секунд по умолчанию.

EXTRACTHOUROFDAY(expr)

Извлечение миллисекунд

EXTRACTMILLISECOND("Order Date")
  • 1997/01/07 15:32:02.150 возвращает 1997/01/07 15:32:02.150.
  • 1997/01/07 18:42:01.265 возвращает 1997/01/07 18:42:01.265.
Возвращает метку времени с миллисекундами для входного значения. Например, если входной меткой времени является 15:32:02.150, функция возвращает метку времени на 15:32:02.150.

EXTRACTMILLISECOND(expr)

Извлечение минут

EXTRACTMINUTE("Order Date")
  • 6/17/1999 11:18:00 PM возвращает 6/17/1999 11:18:00 PM.
  • 9/2/2022 10:38:21 AM возвращает 9/2/2022 10:38:00 AM.

Возвращает метку времени на начало минуты, в течение которой появляется входное значение. Например, если входной меткой времени является 11:38:21 PM, функция возвращает метку времени на 11:38:00 PM.

EXTRACTMINUTE (expr)

Извлечение месяца

EXTRACTMONTH("Order Date")
  • 2/22/1967 3:02:01 AM возвращает 2/1/1967 12:00:00 AM.
  • 6/17/1999 11:18:00 PM возвращает 6/1/1999 12:00:00 AM.

Возвращает метку времени на первый день месяца, в котором появляется входное значение. Например, если входной меткой времени является 22 февраля, функция возвращает метку времени на 1 февраля.

EXTRACTMONTH(expr)

Извлечение квартала

EXTRACTQUARTER("Order Date")
  • 2/22/1967 3:02:01 AM возвращает 1/1/1967 12:00:00 AM, первый день первого финансового квартала.
  • 6/17/1999 11:18:00 PM возвращает 4/1/1999 12:00:00 AM, первый день второго финансового квартала.

  • 9/2/2022 10:38:21 AM возвращает 7/1/2022 12:00:00 AM, первый день третьего финансового квартала.

    Совет. Используйте QUARTER (expr) для расчета только порядкового номера квартала по возвращенной метке времени.

Возвращает метку времени на первый день квартала, в котором появляется входное значение. Например, если входная метка времени появляется в третьем финансовом квартале, функция возвращает метку времени на 1 июля.

EXTRACTQUARTER(expr)

Извлечение секунд

EXTRACTSECOND("Order Date")
  • 1997/01/07 15:32:02.150 возвращает 1997/01/07 15:32:02.
  • 1997/01/07 20:44:18.163 возвращает 1997/01/07 20:44:18.

Возвращает метку времени для входного значения. Например, если входной меткой времени является 15:32:02.150, функция возвращает метку времени на 15:32:02.

EXTRACTSECOND(expr)

Извлечение недели

EXTRACTWEEK("Order Date")
  • 2014/09/24 10:58:00 возвращает 2014/09/21.

  • 2014/08/13 11:10:00 возвращает 2014/08/10.

Возвращает дату первого дня недели (воскресенье), на которой появляется входное значение. Например, если входная метка времени появляется в среду, 24 сентября, функция возвращает метку времени на воскресенье, 21 сентября.

Примечание. Если первый день недели (т. е. воскресенье) приходится на предыдущий год и тем самым отрицательно влияет на агрегацию, функция возвращает седьмой день недели (субботу) текущим годом вместо первого дня недели, относящегося к предыдущему году. Например, 1/1/24, 1/2/24 и 1/3/24 агрегируются по субботе 1/6/24 вместо воскресенья 12/29/23.

EXTRACTWEEK(expr)

Извлечение года

EXTRACTYEAR("Order Date")
  • 1967/02/22 03:02:01 возвращает 1967/01/01 00:00:00.
  • 1999/06/17 23:18:00 возвращает 1999/01/01 00:00:00.

Возвращает метку времени на 1 января года, в котором появляется входное значение. Например, если входная метка времени появляется в 1967 г., функция возвращает метку времени на 1 января 1967 г.

EXTRACTYEAR (expr)

Советы по использованию размерностей дат в предметных областях

Предлагаем несколько советов, которые позволят получить наилучшие результаты при использовании дат предметной области в расчетах.

Добавление даты предметной области к расчету

Если перетащить дату из предметной области прямо в расчет и обработать ее как строку или целое число, произойдет ошибка. Это происходит из-за того, что в основе даты лежит метка времени.

Вместо этого для интерпретации даты нужно использовать Функции извлечения даты.

Например, у вас есть следующие даты предметной области.
Описание GUID-39C55B3A-A055-45D3-9904-948680247C08-default.png ниже
.png

Чтобы извлечь из них месяцы, используйте функцию ExtractMonthOfYear:

case when monthname(ExtractMonthOfYear("Date")) in ('Jan' ,'Feb', 'Mar') 
THEN 'Q1' 
ELSE 'Rest of the year' END

Функции отображения

Функции отображения применяются к набору результатов запроса.

Функция Пример Описание Синтаксис

BottomN

BottomN(Sales, 10)

Возвращает n наименьших значений выражения в порядке от наименьшего к наибольшему.

BottomN([NumericExpression], [integer])

FILTER

FILTER(Sales USING Product = 'widget')

Служит для вычисления выражения с помощью заданного фильтра предварительной агрегации.

FILTER(measure USING filter_expr)

MAVG

MAVG(Sales, 10)

Служит для вычисления скользящего среднего (усредненного значения) для последних n строк данных набора результатов, включая текущую строку.

MAVG([NumericExpression], [integer])

MSUM

SELECT Month, Revenue, MSUM(Revenue, 3) as 3_MO_SUM FROM Sales

Служит для вычисления скользящей суммы для последних n строк данных, включая текущую строку.

Сумма для первой строки равна значению числового выражения первой строки. Сумма второй строки вычисляется как сумма первых двух строк данных и т. д. После достижения n-й строки данных вычисляется сумма последних n строк данных.

MSUM([NumericExpression], [integer])

NTILE

NTILE(Sales, 100)

Определяет ранг значения из заданного пользователям диапазона. Возвращает целые значения, представляющие произвольный диапазон рангов. В примере показан диапазон значений от 1 до 100, где наименьший объем продаж = 1, а наибольший объем продаж = 100.

NTILE([NumericExpresssion], [integer])

PERCENTILE

PERCENTILE(Sales)

Служит для вычисления ранга процентиля для каждого значения, удовлетворяющего аргументу числового выражения. Ранги процентиля находятся в пределах от 0 (первый процентиль) до 1 (сотый процентиль) включительно.

PERCENTILE([NumericExpression])

RANK

RANK(Sales)

Служит для вычисления ранга каждого значения, удовлетворяющего аргументу числового выражения. Наибольшему значению присваивается ранг 1, а каждому следующему по порядку рангу присваивается следующее по порядку целое число (2, 3, 4,...). Если имеется несколько равных значений, им присваивается одинаковый ранг (например, 1, 1, 1, 4, 5, 5, 7...).

RANK([NumericExpression])

RCOUNT

SELECT month, profit, RCOUNT(profit) FROM sales WHERE profit > 200

Принимает в качестве входных данных набор записей и подсчитывает число записей, обнаруженных к текущему моменту.

RCOUNT([NumericExpression])

RMAX

SELECT month, profit, RMAX(profit) FROM sales

Принимает в качестве входных данных набор записей и отображает максимальное значение записей, обнаруженных к текущему моменту. Указанный тип данных должен поддерживать упорядочение.

RMAX([NumericExpression])

RMIN

SELECT month, profit, RMIN(profit) FROM sales

Принимает в качестве входных данных набор записей и отображает минимальное значение записей, обнаруженных к текущему моменту. Указанный тип данных должен поддерживать упорядочение.

RMIN([NumericExpression])

RSUM

SELECT month, revenue, RSUM(revenue) as RUNNING_SUM FROM sales

Служит для вычисления скользящей суммы для всех обнаруженных к текущему моменту записей.

Сумма для первой строки равна значению числового выражения первой строки. Сумма второй строки вычисляется как сумма первых двух строк данных и т. д.

RSUM([NumericExpression])

TOPN

TOPN(Sales, 10)

Возвращает n наибольших значений выражения в порядке от наибольшего к наименьшему.

TOPN([NumericExpression], [integer])

Советы по использованию функций отображения

  • FILTER – если вы создаете отчет, используя предметную область, воспользуйтесь иерархиями в этой области, вместо того чтобы фильтровать столбцы иерархии прямо в расчетах. Иными словами, если у предметной области есть иерархия для времени/финансового года/финансового квартала, старайтесь не использовать:

    filter (<measure> using fiscal_quarter = 'Q4')

    filter (<measure> using fiscal_quarter = 'Q3')

    filter (<measure> using fiscal_year = 'FY24')

Оценочные функции

Оценочные функции — это функции базы данных, которые могут передаваться в выражения для более сложных вычислений.

Для функций встроенной базы данных может потребоваться один или несколько столбцов. В функции на эти столбцы ссылается элемент %1 ... %N. Фактические столбцы должны быть перечислены после функции.

Функция Пример Описание Синтаксис

EVALUATE

SELECT EVALUATE('instr(%1, %2)', address, 'Foster City') FROM employees

Передает указанную функцию базы данных с дополнительными ссылочными столбцами в качестве параметров на оценку в базу данных.

EVALUATE([строковое выражение], [выражения, разделенные запятыми])

EVALUATE_AGGR

EVALUATE_AGGR('REGR_SLOPE(%1, %2)', sales.quantity, market.marketkey)

Передает указанную функцию базы данных с дополнительными ссылочными столбцами в качестве параметров на оценку в базу данных. Эта функция предназначена для функций агрегирования с фразой GROUP BY.

EVALUATE_AGGR('db_agg_function(%1...%N)' [AS datatype] [, column1, columnN])

Математические функции

Описанные в этом разделе математические функции выполняют математические операции.

Функция Пример Описание Синтаксис

ABS

ABS(Profit)

Вычисление абсолютного значения числового выражения.

expr — любое выражение, в результате вычисления которого получается числовое значение.

ABS(expr)

ACOS

ACOS(1)

Вычисление арккосинуса числового выражения.

expr — любое выражение, в результате вычисления которого получается числовое значение.

ACOS(expr)

ASIN

ASIN(1)

Вычисление арксинуса числового выражения.

expr — любое выражение, в результате вычисления которого получается числовое значение.

ASIN(expr)

ATAN

ATAN(1)

Вычисления арктангенса числового выражения.

expr — любое выражение, в результате вычисления которого получается числовое значение.

ATAN(expr)

ATAN2

ATAN2(1, 2)

Служит для вычисления арктангенса y/x, где y — первое числовое выражение, а x — второе числовое выражение.

ATAN2(expr1, expr2)

CEILING

CEILING(Profit)

Округление нецелого числового выражения до ближайшего целого числа в сторону увеличения. Если результатом числового выражения является целое число, функция CEILING возвращает это целое число.

CEILING(expr)

COS

COS(1)

Вычисление косинуса числового выражения.

expr — любое выражение, в результате вычисления которого получается числовое значение.

COS(expr)

COT

COT(1)

Вычисление котангенса числового выражения.

expr — любое выражение, в результате вычисления которого получается числовое значение.

COT(expr)

DEGREES

DEGREES(1)

Служит для преобразования выражения из радиан в градусы

expr — любое выражение, в результате вычисления которого получается числовое значение.

DEGREES(expr)

EXP

EXP(4)

Служит для возведения значения в указанную степень Вычисляет значение e в степени n, где e — основание натурального логарифма.

EXP(expr)

ExtractBit

Int ExtractBit(1, 5)

Извлекает бит в конкретной позиции целого числа. Возвращает целое число, 0 или 1, соответствующее позиции бита.

ExtractBit([Source Number], [Digits])

FLOOR

FLOOR(Profit)

Округление нецелого числового выражения до ближайшего целого числа в сторону уменьшения. Если результатом числового выражения является целое число, функция FLOOR возвращает это целое число.

FLOOR(expr)

LOG

LOG(1)

Служит для вычисления натурального логарифма выражения

expr — любое выражение, в результате вычисления которого получается числовое значение.

LOG(expr)

LOG10

LOG10(1)

Служит для вычисления десятичного логарифма выражения

expr — любое выражение, в результате вычисления которого получается числовое значение.

LOG10(expr)

MOD

MOD(10, 3)

Делит первое числовое выражение на второе числовое выражение и возвращает остаток от деления.

MOD(expr1, expr2)

PI

PI()

Возвращает значение числа пи.

PI()

POWER

POWER(Profit, 2)

Возводит первое численное выражение в степень, которая равна второму численному выражению.

POWER(expr1, expr2)

RADIANS

RADIANS(30)

Служит для преобразования выражения из градусов в радианы.

expr — любое выражение, в результате вычисления которого получается числовое значение.

RADIANS(expr)

RAND

RAND()

Служит для получения псевдослучайного числа между 0 и 1.

RAND()

RANDFromSeed

RAND(2)

Служит для получения псевдослучайного числа, вычисление которого базируется на начальном значении. Для одинаковых начальных значений вырабатываются одинаковые наборы псевдослучайных чисел.

RAND(expr)

ROUND

ROUND(2.166000, 2)

Округляет числовое выражение с точностью до n десятичных знаков.

expr — любое выражение, в результате вычисления которого получается числовое значение.

integer — любое положительное целое число, которое представляет число значащих разрядов

ROUND(expr, integer)

SIGN

SIGN(Profit)

Возвращает следующее:

  • 1, если результат вычисления числового выражения — положительное число;

  • -1, если результат вычисления числового выражения — отрицательное число;

  • 0, если результат вычисления числового выражения — ноль.

SIGN(expr)

SIN

SIN(1)

Вычисление синуса числового выражения.

SIN(expr)

SQRT

SQRT(7)

Вычисление квадратного корня аргумента числового выражения. Результатом вычисления числового выражения аргумента должно быть неотрицательное число.

SQRT(expr)

TAN

TAN(1)

Вычисление тангенса числового выражения.

expr — любое выражение, в результате вычисления которого получается числовое значение.

TAN(expr)

TRUNCATE

TRUNCATE(45.12345, 2)

Усекает десятичное число и возвращает число с заданным количеством десятичных знаков.

expr — любое выражение, в результате вычисления которого получается числовое значение.

integer — любое положительное целое число, представляющее количество символов справа от десятичной точки в возвращаемом числе.

TRUNCATE(expr, integer)

Выполнение функций агрегирования

Функции агрегирования выполняют операции над несколькими значениями для получения сводного результата.

Функция Пример Описание Синтаксис

MAVG

 

Служит для вычисления скользящего среднего (усредненного значения) для последних n строк данных набора результатов, включая текущую строку.

expr —любое выражение, в результате вычисления которого получается числовое значение. integer — любое положительное целое число. Представляет среднее для последних n строк данных.

MAVG(expr, integer)

MSUM

select month, revenue, MSUM(revenue, 3) as 3_MO_SUM from sales_subject_area

Служит для вычисления скользящей суммы для последних n строк данных, включая текущую строку.

expr —любое выражение, в результате вычисления которого получается числовое значение. integer — любое положительное целое число. Представляет сумму последних n строк данных.

MSUM(expr, integer)

RSUM

SELECT month, revenue, RSUM(revenue) as RUNNING_SUM from sales_subject_area

Служит для вычисления скользящей суммы для всех обнаруженных к текущему моменту записей.

expr — любое выражение, в результате вычисления которого получается числовое значение.

RSUM(expr)

RCOUNT

select month, profit, RCOUNT(profit) from sales_subject_area where profit > 200

Принимает в качестве входных данных набор записей и подсчитывает число записей, обнаруженных к текущему моменту.

expr — выражение произвольного типа данных.

RCOUNT(expr)

RMAX

SELECT month, profit,RMAX(profit) from sales_subject_area

Принимает в качестве входных данных набор записей и отображает максимальное значение записей, обнаруженных к текущему моменту.

expr — выражение произвольного типа данных.

RMAX(expr)

RMIN

select month, profit,RMIN(profit) from sales_subject_area

Принимает в качестве входных данных набор записей и отображает минимальное значение записей, обнаруженных к текущему моменту.

expr — выражение произвольного типа данных.

RMIN(expr)

Пространственные функции

Пространственные функции позволяют выполнять географический анализ при моделировании данных. Например, можно рассчитать расстояние между двумя географическими областями (называемыми фигурами или полигонами).

Примечание.:

Такие пространственные функции нельзя использовать в пользовательских расчетах для рабочих книг с визуализацией.
Функция Пример Описание Синтаксис
GeometryArea

GeometryArea(Shape)

Вычисление площади, занимаемой фигурой.

GeometryArea(Shape)

GeometryDistance

GeometryDistance(TRIP_START, TRIP_END)

Расчет расстояния между двумя фигурами.

GeometryDistance(Shape 1, Shape 2)

GeometryLength

GeometryLength(Shape)

Расчет окружности фигуры.

GeometryLength(Shape)

GeometryRelate

GeometryRelate(TRIP_START, TRIP_END)

Определяет, находится ли одна фигура внутри другой. Возвращает TRUE или FALSE в виде строки (varchar).

GeometryRelate(Shape 1, Shape 2)

GeometryWithinDistance

GeometryWithinDistance(TRIP_START, TRIP_END, 500)

Определяет, находятся ли две фигуры на определенном расстоянии друг от друга. Возвращает TRUE или FALSE в виде строки (varchar).

GeometryWithinDistance(Shape1, Shape2, DistanceInFloat)

Строковые функции

Строковые функции служат для выполнения различных операций с символами. Они применяются к символьным строкам.

Функция Пример Описание Синтаксис

ASCII

ASCII('a')

Преобразование строки, состоящей из одного символа, в соответствующий код ASCII (число от 0 до 255). Если символьное выражение содержит несколько символов, возвращается ASCII-код первого символа в выражении.

expr — любое выражение, в результате вычисления которого получается строка символов.

ASCII(expr)

BIT_LENGTH

BIT_LENGTH('abcdef')

Возвращает длину указанной строки в битах. Длина каждого символа Unicode — 2 байта (16 битов).

expr — любое выражение, в результате вычисления которого получается строка символов.

BIT_LENGTH(expr)

CHAR

CHAR(35)

Преобразует числовое значение от 0 до 255 в символьное значение, ASCII-код которого соответствует этому значению.

expr — любое выражение, в результате вычисления которого получается числовое значение от 0 до 255.

CHAR(expr)

CHAR_LENGTH

CHAR_LENGTH(Customer_Name)

Возвращает длину указанной строки, выраженную количеством символов. Начальные и конечные пробелы не учитываются в длине строки.

expr — любое выражение, в результате вычисления которого получается строка символов.

CHAR_LENGTH(expr)

CONCAT

SELECT DISTINCT CONCAT ('abc', 'def') FROM employee

Объединяет две строки символов.

expr — выражение, в результате вычисления которого получаются строки символов, разделенные запятыми.

Для функции CONCAT необходимо использовать необработанные, неформатированные данные.

CONCAT(expr1, expr2)

INSERT

SELECT INSERT('123456', 2, 3, 'abcd') FROM table

Вставка заданной строки символов в указанное местоположение в другой строке символов.

expr1 — любое выражение, в результате вычисления которого получается строка символов. Определяет целевую строку символов.

integer1 — любое положительное целое число, представляющее количество символов от начала целевой строки, после которых требуется вставить вторую сроку.

integer2 — любое положительное целое число, представляющее количество символов в целевой строке, которое требуется заменить второй строкой.

expr2 — любое выражение, в результате вычисления которого получается строка символов. Определяет строку символов, которую требуется вставить в целевую строку.

INSERT(expr1, integer1, integer2, expr2)

LEFT

SELECT LEFT('123456', 3) FROM table

Возвращает заданное количество символов в начале строки.

expr — любое выражение, в результате вычисления которого получается строка символов.

integer — любое положительное целое число, представляющее количество символов от левого края строки, которое возвращается в качестве результата.

LEFT(expr, integer)

LENGTH

LENGTH(Customer_Name)

Возвращает длину указанной строки, выраженную количеством символов. Символы пробела в конце строки не учитываются.

expr — любое выражение, в результате вычисления которого получается строка символов.

LENGTH(expr)

LOCATE

LOCATE('d' 'abcdef')

Возвращает номер позиции строки символов в другой строке символов. Если строка символов не найдена в выбранной строке, функция возвращает значение 0.

expr1 — любое выражение, в результате вычисления которого получается строка символов. Определяет строку, которую требуется найти.

expr2 — любое выражение, в результате вычисления которого получается строка символов.

Определяет строку символов, в которой выполняется поиск.

LOCATE(expr1, expr2)

LOCATEN

LOCATEN('d' 'abcdef', 3)

Подобно функции LOCATE, возвращает номер позиции строки символов в другой строке символов. LOCATEN включает целочисленный аргумент, который позволяет указать позицию начала поиска.

expr1 — любое выражение, в результате вычисления которого получается строка символов. Определяет строку, которую требуется найти.

expr2 — любое выражение, в результате вычисления которого получается строка символов. Определяет строку символов, в которой выполняется поиск.

integer — любое положительное (ненулевое) целое число, представляющее начальную позицию, с которой начинается поиск данной строки символов.

LOCATEN(expr1, expr2, integer)

LOWER

LOWER(Customer_Name)

Преобразование строки символов в нижний регистр.

expr — любое выражение, в результате вычисления которого получается строка символов.

LOWER(expr)

OCTET_LENGTH

OCTET_LENGTH('abcdef')

Возвращает число байтов указанной строки.

expr — любое выражение, в результате вычисления которого получается строка символов.

OCTET_LENGTH(expr)

POSITION

POSITION('d', 'abcdef')

Возвращает номер позиции элемента strExpr1 в символьном выражении. Если элемент strExpr1 не найден, функция возвращает значение 0.

expr1 — любое выражение, в результате вычисления которого получается строка символов. Определяет строку, поиск которой следует выполнить в целевой строке. Например, "d".

expr2 — любое выражение, в результате вычисления которого получается строка символов. Определяет целевую строку символов, в которой осуществляется поиск. Например, "abcdef".

POSITION(expr1, expr2)

REPEAT

REPEAT('abc', 4)

Повторяет указанное выражение n раз.

expr — любое выражение, в результате вычисления которого получается строка символов.

integer — любое положительное целое число, представляющее количество вхождений заданной строки символов.

REPEAT(expr, integer)

REPLACE

REPLACE('abcd1234', '123', 'zz')

Замена одного или нескольких символов в указанном символьном выражении другим символом или несколькими другими символами.

expr1 — любое выражение, в результате вычисления которого получается строка символов. Это строка символов, в которой требуется выполнить замену символов.

expr2 — любое выражение, в результате вычисления которого получается строка символов. Вторая строка определяет символы из первой строки, которые требуется заменить.

expr3 — любое выражение, в результате вычисления которого получается строка символов. Третья строка задает символы, которые требуется подставить в первую строку.

REPLACE(expr1, expr2, expr3)

RIGHT

SELECT RIGHT('123456', 3) FROM table

Возвращает заданное количество символов в конце строки.

expr — любое выражение, в результате вычисления которого получается строка символов.

integer — любое положительное целое число, представляющее количество символов от правого края строки, которое возвращается в качестве результата.

RIGHT(expr, integer)

SPACE

SPACE(2)

Выполняет вставку пробелов.

integer — положительное целое число, задающее количество пробелов, которое требуется вставить.

SPACE(expr)

SUBSTRING

SUBSTRING('abcdef' FROM 2)

Создание новой строки, начинающейся после фиксированного количества символов в исходной строке.

expr — любое выражение, в результате вычисления которого получается строка символов.

startPos — любое положительное целое число, представляющее количество символов от начала строки с левой стороны, с которого начинается результат.

SUBSTRING([SourceString] FROM [StartPostition])

SUBSTRINGN

SUBSTRING('abcdef' FROM 2 FOR 3)

Подобно функции SUBSTRING создает новую строку, начинающуюся с фиксированного числа символов исходной строки.

SUBSTRINGN включает целочисленный аргумент, который позволяет указать длину новой строки в символах.

expr — любое выражение, в результате вычисления которого получается строка символов.

startPos — любое положительное целое число, представляющее количество символов от начала строки с левой стороны, с которого начинается результат.

SUBSTRING(expr FROM startPos FOR length)

TrimBoth

Trim(BOTH '_' FROM '_abcdef_')

Удаление указанных символов в начале и в конце строки.

char — любой отдельный символ. Если не задан символ (и обязательные одинарные кавычки), по умолчанию используется пустой символ.

expr — любое выражение, в результате вычисления которого получается строка символов.

TRIM(BOTH char FROM expr)

TRIMLEADING

TRIM(LEADING '_' FROM '_abcdef')

Удаление указанных символов в начале строки.

char — любой отдельный символ. Если не задан символ (и обязательные одинарные кавычки), по умолчанию используется пустой символ.

expr — любое выражение, в результате вычисления которого получается строка символов.

TRIM(LEADING char FROM expr)

TRIMTRAILING

TRIM(TRAILING '_' FROM 'abcdef_')

Удаление указанных символов в конце строки.

char — любой отдельный символ. Если не задан символ (и обязательные одинарные кавычки), по умолчанию используется пустой символ.

expr — любое выражение, в результате вычисления которого получается строка символов.

TRIM(TRAILING char FROM expr)

UPPER

UPPER(Customer_Name)

Преобразование строки символов в верхний регистр.

expr — любое выражение, в результате вычисления которого получается строка символов.

UPPER(expr)

Советы по использованию LISTAGG в строковых функциях

Вот несколько советов по использованию функции LISTAGG для получения наилучших результатов при обработке строк в рабочих книгах. Например, может потребоваться создать строку, содержащую все города в столбце данных.

Объединение нескольких значений:

Для объединения нескольких значений в одну ячейку и одну строку используйте LISTAGG.

LISTAGG([DISTINCT] <column to concatenate> BY <grouping column>) ON OVERFLOW TRUNCATE
  • DISTINCT — если значения не уникальны, используйте этот аргумент для удаления дубликатов значений.
  • ON OVERFLOW TRUNCATE — если длина результата превышает максимально допустимую, используйте этот аргумент для усечения возвращаемой строки.
Пример данных
Number City State
12     New York    New York
14     New York    New York
30     Boston      Massachusetts
18     Salem    Massachusetts
12     Buffalo     New York
10     Buffalo     New York
20     Albany      New York

Примеры команд и вывод

Выходные данные LISTAGG(City, ', '): "New York, New York, Boston, Salem, Buffalo, Buffalo, Albany"

Выходные данные LISTAGG(DISTINCT City, ', '): "New York, Boston, Salem, Buffalo Albany"

Выходные данные LISTAGG(City, ', ' ON OVERFLOW TRUNCATE '...'): "New York, New York, Boston, Salem, Buffalo, ..."

Системные функции

Системная функция USER возвращает значения, относящиеся к сеансу. Например, имя пользователя, с которым был выполнен вход в систему.

Функция Пример Описание Синтаксис

DATABASE

 

Возвращает имя предметной области, в которую выполнен вход.

DATABASE()

USER

 

Возвращает имя пользователя для семантической модели, в которую выполнен вход.

USER()

Функции временного ряда

Функции временного ряда — это функции агрегации и прогнозирования данных на основании измерений времени. Например, можно использовать функцию AGO для расчета дохода за прошлый год.

Элементы измерения времени должны находиться на уровне такой функции или ниже. Поэтому в запрос необходимо спроецировать один или несколько столбцов, которые однозначно идентифицируют элемент на заданном уровне.

Функция Пример Описание Синтаксис

AGO

SELECT Year_ID, AGO(sales, year, 1)

Вычисляет агрегированное значение показателя за указанный период времени в прошлом. Например, для расчета месячного дохода один год назад используйте AGO(Revenue, Year, 1, SHIP_MONTH). Для расчета квартального дохода в прошлом квартале используйте AGO(Revenue, Quarter, 1).

AGO(MEASURE, TIME_LEVEL, OFFSET)

Где:

  • MEASURE представляет собой показатель для расчета, например дохода.
  • TIME_LEVEL представляет собой временной интервал, который может быть годом, кварталом, месяцем, неделей или днем.
  • OFFSET представляет собой количество временных интервалов, для которых выполняется расчет; например, "1" для одного года.

PERIODROLLING

SELECT Month_ID, PERIODROLLING (monthly_sales, -1, 1)

Вычисляет агрегацию показателя за период, начало и конец которого отстоят от текущего момента времени соответственно на x и y единиц времени. Например, функция PERIODROLLING может вычислить продажи за период, который начинается за квартал до текущего квартала и заканчивается через квартал после текущего квартала.

PERIODROLLING(measure, x [,y])

Где:

  • MEASURE представляет собой имя столбца показателя.
  • X – это целое число, которое представляет собой смещение относительно текущего момента времени.
  • Y – это целое число, которое представляет собой количество единиц времени, в течение которых вычисляется функция.
  • HIERARCHY – это необязательный аргумент, который задает имя иерархии во временном измерении, например YR, MON, DAY, и который требуется использовать для вычисления интервала времени.

TODATE

SELECT Year_ID, Month_ID, TODATE (sales, year)

Вычисляет агрегированное значение показателя от начала периода времени до последнего периода времени (например, расчет с начала года).

Например, для расчета продаж с начала года используйте TODATE(sales, year).

TODATE(MEASURE, TIME_LEVEL)

Где:

  • MEASURE представляет собой выражение, содержащее ссылку хотя бы на один столбец показателя, например продажи.
  • TIME_LEVEL представляет собой временной интервал, который может быть годом, кварталом, месяцем, неделей или днем.