Найти среднее значение oracle
На этой странице мы собираемся обсудить использование SQL ROUND () вместе с функцией SQL AVG (). SQL ROUND () используется для округления значения до определенного десятичного знака.
Предложение GROUP BY с агрегатной функцией создает результат в группе.
Примеры:
Образец таблицы: клиент
Чтобы получить среднее значение параметра «holy_amount» как целое число, означающее округление до нуля из таблицы «customer», можно использовать следующий оператор SQL:
SQL AVG () с SUM ()
Чтобы получить сумму 'advance_amount' и среднего значения 'advance_amount' из таблицы 'orders', можно использовать следующий оператор SQL:
Пример таблицы: заказы
Примечание. Выводы указанного оператора SQL, показанного здесь, взяты с использованием Oracle Database 10g Express Edition.
Вот слайд-презентация всех агрегатных функций.
Упражнения по SQL
- Упражнения по SQL, практика, решение
- SQL Получить данные из таблиц [33 Упражнения]
- Булевы и реляционные операторы SQL [12 упражнений]
- Подстановочные знаки SQL и специальные операторы [22 упражнения]
- Агрегатные функции SQL [25 упражнений]
- Вывод запроса форматирования SQL [10 упражнений]
- SQL-запросы к нескольким таблицам [7 упражнений]
- ФИЛЬТРАЦИЯ И СОРТИРОВКА в базе данных персонала [38 упражнений]
- SQL СОЕДИНЯЕТ
- SQL СОЕДИНЯЕТСЯ [29 упражнений]
- SQL присоединяется к базе данных HR [27 упражнений]
- ПОДПИСИ SQL [39 упражнений]
- SQL ПОДПИСИ по базе данных HR [55 упражнений]
- ОСНОВНЫЕ запросы к базе данных фильмов [10 упражнений]
- ПОДПИСКИ на фильм База данных [16 упражнений]
- ПРИСОЕДИНЯЕТСЯ к базе данных фильма [24 упражнения]
- Вступление
- ОСНОВНЫЕ запросы по футболу базы данных [29 упражнений]
- ПОДПИСКИ по футбольной базе данных [33 упражнения]
- ПРИСОЕДИНЯЕТСЯ к запросам по футбольной базе данных [61 упражнений]
- Вступление
- ОСНОВНЫЕ, ПОДПИСИ И СОЕДИНЕНИЯ [39 упражнений]
- ОСНОВНЫЕ запросы к базе данных сотрудников [115 упражнений]
- БРОНИРОВАНИЕ на сотрудника База данных [77 Упражнения]
Хотите улучшить вышеуказанную статью? Вносите свои заметки / комментарии / примеры через Disqus.
Математические функции
Описанные в этом разделе математические функции выполняют математические операции.
Вычисление абсолютного значения числового выражения.
Вычисление арккосинуса числового выражения.
Вычисление арксинуса числового выражения.
Вычисления арктангенса числового выражения.
Вычисляет арктангенс y / x , где y – первое числовое выражение, а x – второе числовое выражение.
Округление нецелого числового выражения до ближайшего целого числа в сторону увеличения. Если результатом числового выражения является целое число, функция CEILING возвращает это целое число.
Вычисление косинуса числового выражения.
Вычисление котангенса числового выражения.
Служит для преобразования выражения из радиан в градусы
Служит для возведения значения в указанную степень Рассчитывает e в n-й степени, где e – основание натурального логарифма.
Int ExtractBit(1, 5)
Извлекает бит в конкретной позиции целого числа. Возвращает целое число, 0 или 1, соответствующее позиции бита.
Округление нецелого числового выражения до ближайшего целого числа в сторону уменьшения. Если результатом числового выражения является целое число, функция FLOOR возвращает это целое число.
Служит для вычисления натурального логарифма выражения
Служит для вычисления десятичного логарифма выражения
Делит первое числовое выражение на второе числовое выражение и возвращает остаток от деления.
Возвращает значение числа пи.
Возводит первое численное выражение в степень, которая равна второму численному выражению.
Служит для преобразования выражения из градусов в радианы.
Служит для получения псевдослучайного числа между 0 и 1.
Служит для получения псевдослучайного числа, вычисление которого базируется на начальном значении. Для одинаковых начальных значений вырабатываются одинаковые наборы псевдослучайных чисел.
Округляет числовое выражение с точностью до n десятичных знаков.
Эта функция возвращает:
1, если результат вычисления числового выражения — положительное число;
-1, если результат вычисления числового выражения — отрицательное число;
0, если результат вычисления числового выражения — ноль.
Вычисление синуса числового выражения.
Вычисление квадратного корня аргумента числового выражения. Результатом вычисления числового выражения аргумента должно быть неотрицательное число.
Вычисление тангенса числового выражения.
Усекает десятичное число и возвращает число с заданным количеством десятичных знаков.
Функции временного ряда
Функции временного ряда — это функции агрегации, которые применяются к измерениям времени.
Элементы измерения времени должны находиться на уровне такой функции или ниже. Поэтому в запрос необходимо спроецировать один или несколько столбцов, которые однозначно идентифицируют элемент на заданном уровне.
SELECT Year_ID, Ago(sales, year, 1)
Вычисляет значение показателя, агрегированное от текущего момента времени до указанного периода времени в прошлом. Например, функция AGO может выдать результаты по продажам за каждый месяц текущего квартала и соответствующим продажам за квартал назад.
SELECT Month_ID, Periodrolling (monthly_sales, -1, 1)
Вычисляет агрегацию показателя за период, начало и конец которого отстоят от текущего момента времени соответственно на x и y единиц времени. Например, функция PERIODROLLING может вычислить продажи за период, который начинается за квартал до текущего квартала и заканчивается через квартал после текущего квартала.
SELECT Year_ID, Month_ID, ToDate (sales, year)
Агрегирует показатель от начала указанного периода времени до текущей отображаемой даты. Например, эта функция позволяет вычислять продажи с начала года.
FORECAST(numeric_expr, ([series]), output_column_name, options, [runtime_binded_options])
Эта функция создает модель временного ряда указанного показателя над рядом, используя либо экспоненциальное сглаживание, либо ARMIA, и выводит прогноз для набора периодов, указанных в значении numPeriods.
ваша группа-это то, что агрегирует ваше среднее значение, и оно группируется по всей таблице (я предполагаю, что вы сделали это, чтобы разрешить выбор для всего), просто переместите свой avg в другой подзапрос, удалите всеобъемлющую группу, и это должно ее решить.
при запуске basic SELECT AVG(cost) заявление это, естественно, группировка по указанному столбцу (стоимость в этом случае), поскольку это то, что вы запрашиваете. Я бы предложил прочитать больше на ГРУППЫ ПО и Сростки чтобы лучше понять концепцию. Это должно помочь вам более простое решение.
обновление:
ответ ниже на самом деле из ответа Дэвида. Он использует аналитические функции. В принципе, происходит то, что при каждом вызове AVG вы говорите движку, что использовать для функции (в этом случае ничего). Достойную запись по аналитическим функциям можно найти здесь и здесь и больше с google по этому вопросу.
однако, если ваш SQL engine допускает переменные, вы можете так же легко сделать следующий ответ. Я действительно предпочитаю это для будущей ремонтопригодности / читаемости. Причина в том, что переменная с хорошим именем может быть очень описательной для будущих читателей кода по сравнению с аналитической функцией, которая требует немного больше работы для чтения (особенно если вы не понимаете функцию over).
кроме того, это решение дублирует один и тот же запрос дважды, поэтому, возможно, стоит сохранить среднее значение в переменной SQL. Затем вы можете изменить свое утверждение, чтобы просто использовать это глобальное среднее
это переменные в SQL-Server (вам придется адаптировать его для собственного экземпляра SQL)
это решение будет читать намного чище для будущих читателей вашего SQL тоже
поскольку вы используете Oracle, вы должны иметь возможность использовать AVG () в качестве аналитической (оконной) функции:
Не нужны подзапросы или группы.
самое простое изменение-изменить avg(cost) as Average to (select avg(cost) from mobile) as Average . Это также означает, что вам не понадобится GROUP BY пункт больше (так как он не делает то, что вы на самом деле хотели):
если ваш запрос слишком дорог, таким образом, напишите мне рекомендацию, тогда я улучшу ее.
Чтобы получить данные среднего значения 'advance_amount' из таблицы 'orders', можно использовать следующий оператор SQL:
Пример таблицы: заказы
Иллюстрированная презентация:
SQL AVG () с ROUND (), округленным до ближайшего отрицательного значения
Далее мы обсудим, как можно использовать SQL ROUND () вместе с функцией SQL AVG () для округления значения до определенного десятичного знака. Округление может быть выполнено до ближайших отрицательных или положительных знаков после запятой (.).
Образец таблицы: клиент
Чтобы получить среднее значение «(879.254 / 2)» с заголовком «My Avg» и среднее значение «(879.254 / 2)», округленное до 1 десятичного знака, отрицательное от десятичной точки с заголовком «Rounded -1 Decimal» Place 'from' customer ', можно использовать следующий оператор SQL:
Функции календаря
Функции календаря позволяют работать с различными типами данных DATE и DATETIME в рамках календарного года.
Возвращает текущую дату.
Возвращает текущее время с указанным числом разрядов точности, например: HH:MM:SS.SSS
Если аргумент не указан, функция возвращает стандартную точность.
Возвращает текущую метку даты/времени с указанным числом разрядов точности.
Возвращает название дня недели для указанного выражения даты.
Возвращает число, соответствующее дню месяца для указанного выражения даты.
Возвращает число от 1 до 7, соответствующее дню недели для указанного выражения даты. Пример: 1 всегда соответствует воскресенью, 2 — понедельнику и т. д. до субботы, соответствующей числу 7.
Возвращает число (от 1 до 366), соответствующее дню года для указанного выражения даты.
Возвращает число (от 1 до 92), соответствующее дню квартала для указанного выражения даты.
Возвращает число (от 0 до 23), соответствующее часу для указанного выражения даты. Пример: 0 соответствует 00:00, а 23 — 23:00.
Возвращает число (от 0 до 59), соответствующее минуте для указанного выражения даты.
Возвращает число (от 1 до 12), соответствующее месяцу для указанного выражения даты.
Возвращает название месяца для указанного выражения даты.
Возвращает число (от 1 до 3), соответствующее месяцу квартала для указанного выражения даты.
Возвращает текущую метку времени. Функция Now эквивалентна функции Current_Timestamp .
Возвращает число (от 1 до 4), соответствующее кварталу года для указанного выражения даты.
Возвращает число (от 0 до 59), соответствующее секундам для указанного выражения даты.
TimeStampAdd(SQL_TSI_MONTH, 12,Time."Order Date")
Добавляет указанное число интервалов к метке времени и возвращает одну метку времени.
Варианты интервалов: SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR
TimeStampDiff(SQL_TSI_MONTH, Time."Order Date",CURRENT_DATE)
Возвращает общее число указанных интервалов между двумя метками времени.
Используются те же интервалы, что и для функции TimeStampAdd.
Возвращает число (от 1 до 13), соответствующее неделе квартала для указанного выражения даты.
Возвращает число (от 1 до 53), соответствующее неделе года для указанного выражения даты.
Возвращает год для указанного выражения даты.
SQL AVG () с где
Чтобы получить среднее значение 'advance_amount' из таблицы 'orders' при следующих условиях -
1. «ord_amount» должно быть больше 1000,
2. и значение ord_amount должно быть до 4500,
можно использовать следующий оператор SQL:
Пример таблицы: заказы
Иллюстрированная презентация:
SQL AVG () с ROUND () и группировать по
Образец таблицы: клиент
Чтобы получить данные для «agent_code», нужно указать сумму «Opening_amt» с заголовком «Op. Amt »и среднее значение« имя_оценки »как целого числа, то есть округленный ноль (0) с заголовком« Rou. Avg. для каждого агента из таблицы «клиент» со следующим условием -
1. каждый агент должен прийти в группу,
можно использовать следующий оператор SQL:
SQL AVG () с нулевым
Чтобы получить данные «agent_code», сумму «creation_amt», количество клиентов для каждого агента и «receive_amt» из таблицы «customer», можно использовать следующую инструкцию SQL:
Образец таблицы: клиент
Строковые функции
Строковые функции служат для выполнения различных операций с символами. Они применяются к символьным строкам.
Преобразование строки, состоящей из одного символа, в соответствующий код ASCII (число от 0 до 255). Если символьное выражение содержит несколько символов, возвращается ASCII-код первого символа в выражении.
Возвращает длину указанной строки в битах. Длина каждого символа Unicode — 2 байта (16 битов).
Преобразует числовое значение от 0 до 255 в символьное значение, ASCII-код которого соответствует этому значению.
Возвращает длину указанной строки, выраженную количеством символов. Начальные и конечные пробелы не учитываются в длине строки.
SELECT DISTINCT Concat ('abc', 'def') FROM employee
Объединяет две строки символов.
SELECT Insert('123456', 2, 3, 'abcd') FROM table
Вставка заданной строки символов в указанное местоположение в другой строке символов.
SELECT Left('123456', 3) FROM table
Возвращает заданное количество символов в начале строки.
Возвращает длину указанной строки, выраженную количеством символов. Символы пробела в конце строки не учитываются.
Возвращает номер позиции строки символов в другой строке символов. Если строка символов не найдена в выбранной строке, функция возвращает значение 0.
Locate('d' 'abcdef', 3)
Подобно функции Locate возвращает номер позиции строки символов в другой строке символов. LocateN включает целочисленный аргумент, который позволяет указать позицию начала поиска.
Преобразование строки символов в нижний регистр.
Возвращает число байтов указанной строки.
Возвращает номер позиции элемента strExpr1 в символьном выражении. Если элемент strExpr1 не найден, функция возвращает значение 0.
Повторяет указанное выражение n раз.
Replace('abcd1234', '123', 'zz')
Замена одного или нескольких символов в указанном символьном выражении другим символом или несколькими другими символами.
SELECT Right('123456', 3) FROM table
Возвращает заданное количество символов в конце строки.
Выполняет вставку пробелов.
Substring('abcdef' FROM 2)
Создание новой строки, начинающейся после фиксированного количества символов в исходной строке.
Substring('abcdef' FROM 2 FOR 3)
Подобно функции Substring создает новую строку, начинающуюся с фиксированного числа символов исходной строки.
SubstringN включает целочисленный аргумент, который позволяет указать длину новой строки в количестве символов.
Trim(BOTH '_' FROM '_abcdef_')
Удаление указанных символов в начале и в конце строки.
Trim(LEADING '_' FROM '_abcdef')
Удаление указанных символов в начале строки.
Trim(TRAILING '_' FROM 'abcdef_')
Удаление указанных символов в конце строки.
Преобразование строки символов в верхний регистр.
Оценочные функции
Оценочные функции — это функции базы данных, которые могут передаваться в выражения для более сложных вычислений.
Для функций встроенной базы данных может потребоваться один или несколько столбцов. В функции на эти столбцы ссылается элемент %1 . %N. Фактические столбцы должны быть перечислены после функции.
SELECT EVALUATE('instr(%1, %2)', address, 'Foster City') FROM employees
Передает указанную функцию базы данных с дополнительными ссылочными столбцами в качестве параметров на оценку в базу данных.
EVALUATE_AGGR('REGR_SLOPE(%1, %2)', sales.quantity, market.marketkey)
Передает указанную функцию базы данных с дополнительными ссылочными столбцами в качестве параметров на оценку в базу данных. Эта функция предназначена для функций агрегирования с фразой GROUP BY .
Функции преобразования
Функции преобразования преобразуют значение из одной формы в другую.
Cast(hiredate AS CHAR(40)) FROM employee
Меняет тип данных выражения или нулевого литерала на другой тип данных. Например, можно привести к другому типу данных элемент customer_name (с типом данных Char или Varchar) или дату рождения (литерал datetime).
Используйте функцию Cast для изменения типа данных на Date.
Не используйте ToDate .
Проверяет, равняется ли значение выражения неопределенному значению, и, если равняется, присваивает выражению указанное значение.
SELECT IndexCol(VALUEOF (NQ_SESSION.GEOGRAPHY_LEVEL), Country, State, City), Revenue FROM Sales
Использует внешние данные для возврата соответствующего столбца подключенному к системе пользователю.
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 в качестве первого выражения нельзя.
SELECT To_DateTime ('2009-03-0301:01:00', 'yyyy-mm-dd hh:mi:ss') FROM sales
Преобразует строковые литералы формата dateTime в тип данных DateTime.
Функции агрегирования
Функции агрегирования выполняют операции над несколькими значениями для получения сводного результата.
Вычисляет среднее по набору числовых значений.
Bin(UnitPrice BY ProductName)
Выбирает числовой атрибут из измерения, таблицы файлов или показателя, содержащего значения данных и помещает его в дискретном количестве контейнеров. Эта функция обрабатывается как новый атрибут измерения для таких целей, как агрегирование, фильтрование и детализация.
Определяет количество элементов с ненулевым значением.
Выбирает первое ненулевое возвращенное значение аргумента выражения. Функция First работает на уровне наибольшей детализации, указанном в явно определенном измерении.
Выбирает последнее ненулевое возвращенное значение выражения.
Служит для вычисления максимального значения (наибольшего числового значения) для строк, удовлетворяющих аргументу числового выражения.
Служит для вычисления медианы строк, удовлетворяющих аргументу числового выражения. В случае четного числа строк медиана — это среднее двух центральных строк. Эта функция всегда возвращает значение типа double.
Служит для вычисления минимального значения (наименьшего числового значения) для строк, удовлетворяющих аргументу числового выражения.
StdDev(Sales) StdDev(DISTINCT Sales)
Возвращает среднеквадратическое отклонение для набора значений. Всегда возвращается значение типа double.
StdDev_Pop(Sales) StdDev_Pop(DISTINCT Sales)
Возвращает стандартное отклонение для набора значений, используя вычислительную формулу для расчета дисперсии и среднеквадратического отклонения.
Служит для вычисления суммы всех значений, удовлетворяющих аргументу числового выражения.
SQL AVG () с ROUND (), округленным до ближайшего положительного значения
Образец таблицы: клиент
Чтобы получить среднее значение «(879.254 / 2)» с заголовком «My Avg» и среднее значение «(879.254 / 2)», округленное до 2 десятичных разрядов, положительное от десятичной точки с заголовком «Rounded 2 Decimal Place» из таблицы customer можно использовать следующий оператор SQL:
Функции отображения
Функции отображения применяются к набору результатов запроса.
Возвращает n наименьших значений выражения в порядке от наименьшего к наибольшему.
Filter(Sales USING Product = 'widget')
Служит для вычисления выражения с помощью заданного фильтра предварительной агрегации.
Служит для вычисления скользящего среднего (усредненного значения) для последних n строк данных набора результатов, включая текущую строку.
SELECT Month, Revenue, Msum(Revenue, 3) as 3_MO_SUM FROM Sales
Служит для вычисления скользящей суммы для последних n строк данных, включая текущую строку.
Сумма для первой строки равна значению числового выражения первой строки. Сумма второй строки вычисляется как сумма первых двух строк данных и т. д. После достижения n -й строки данных вычисляется сумма последних n строк данных.
Определяет ранг значения из заданного пользователям диапазона. Возвращает целые значения, представляющие произвольный диапазон рангов. В примере показан диапазон значений от 1 до 100, где наименьший объем продаж = 1, а наибольший объем продаж = 100.
Служит для вычисления ранга процентиля для каждого значения, удовлетворяющего аргументу числового выражения. Ранги процентиля находятся в пределах от 0 (первый процентиль) до 1 (сотый процентиль) включительно.
Служит для вычисления ранга каждого значения, удовлетворяющего аргументу числового выражения. Наибольшему значению присваивается ранг 1, а каждому следующему по порядку рангу присваивается следующее по порядку целое число (2, 3, 4. ). Если имеется несколько равных значений, им присваивается одинаковый ранг (например, 1, 1, 1, 4, 5, 5, 7. ).
SELECT month, profit, Rcount(profit) FROM sales WHERE profit > 200
Принимает в качестве входных данных набор записей и подсчитывает число записей, обнаруженных к текущему моменту.
SELECT month, profit, Rmax(profit) FROM sales
Принимает в качестве входных данных набор записей и отображает максимальное значение записей, обнаруженных к текущему моменту. Указанный тип данных должен поддерживать упорядочение.
SELECT month, profit, Rmin(profit) FROM sales
Принимает в качестве входных данных набор записей и отображает минимальное значение записей, обнаруженных к текущему моменту. Указанный тип данных должен поддерживать упорядочение.
SELECT month, revenue, Rsum(revenue) as RUNNING_SUM FROM sales
Служит для вычисления скользящей суммы для всех обнаруженных к текущему моменту записей.
Сумма для первой строки равна значению числового выражения первой строки. Сумма второй строки вычисляется как сумма первых двух строк данных и т. д.
Возвращает n наибольших значений выражения в порядке от наибольшего к наименьшему.
SQL AVG () с группировкой по
Пример таблицы: заказы
Чтобы получить данные «agent_code», используйте среднее значение «advance_amount» для каждого агента из таблицы «orders» со следующим условием:
1. каждый код агента должен быть в группе,
можно использовать следующий оператор SQL:
SQL AVG () с ROUND () как целое число
Образец таблицы: клиент
Чтобы получить среднее значение '(879.254 / 2)' и среднее значение '(879.254 / 2)', округленное до 0-го знака после запятой с заголовком 'Rounded' из таблицы 'customer', следующий оператор SQL может быть используемый :
Примечание. Выводы указанного оператора SQL, показанного здесь, взяты с использованием Oracle Database 10g Express Edition.
Вот слайд-презентация всех агрегатных функций.
Упражнения по SQL
- Упражнения по SQL, практика, решение
- SQL Получить данные из таблиц [33 Упражнения]
- Булевы и реляционные операторы SQL [12 упражнений]
- Подстановочные знаки SQL и специальные операторы [22 упражнения]
- Агрегатные функции SQL [25 упражнений]
- Вывод запроса форматирования SQL [10 упражнений]
- SQL-запросы к нескольким таблицам [7 упражнений]
- ФИЛЬТРАЦИЯ И СОРТИРОВКА в базе данных персонала [38 упражнений]
- SQL СОЕДИНЯЕТ
- SQL СОЕДИНЯЕТСЯ [29 упражнений]
- SQL присоединяется к базе данных HR [27 упражнений]
- ПОДПИСИ SQL [39 упражнений]
- SQL ПОДПИСИ по базе данных HR [55 упражнений]
- ОСНОВНЫЕ запросы к базе данных фильмов [10 упражнений]
- ПОДПИСКИ на фильм База данных [16 упражнений]
- ПРИСОЕДИНЯЕТСЯ к базе данных фильма [24 упражнения]
- Вступление
- ОСНОВНЫЕ запросы по футболу базы данных [29 упражнений]
- ПОДПИСКИ по футбольной базе данных [33 упражнения]
- ПРИСОЕДИНЯЕТСЯ к запросам по футбольной базе данных [61 упражнений]
- Вступление
- ОСНОВНЫЕ, ПОДПИСИ И СОЕДИНЕНИЯ [39 упражнений]
- ОСНОВНЫЕ запросы к базе данных сотрудников [115 упражнений]
- БРОНИРОВАНИЕ на сотрудника База данных [77 Упражнения]
Хотите улучшить вышеуказанную статью? Вносите свои заметки / комментарии / примеры через Disqus.
The semantics of this syntax are discussed in the sections that follow.
Specify the name of an analytic function (see the listing of analytic functions following this discussion of semantics).
Analytic functions take 0 to 3 arguments. The arguments can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. Oracle determines the argument with the highest numeric precedence and implicitly converts the remaining arguments to that data type. The return type is also that data type, unless otherwise noted for an individual function.
Numeric Precedence for information on numeric precedence and Table 2-8 for more information on implicit conversion
Use OVER analytic_clause to indicate that the function operates on a query result set. This clause is computed after the FROM , WHERE , GROUP BY , and HAVING clauses. You can specify analytic functions with this clause in the select list or ORDER BY clause. To filter the results of a query based on an analytic function, nest these functions within the parent query, and then filter the results of the nested subquery.
Notes on the analytic_clause :
The following notes apply to the analytic_clause :
You cannot nest analytic functions by specifying any analytic function in any part of the analytic_clause . However, you can specify an analytic function in a subquery and compute another analytic function over it.
You can specify OVER analytic_clause with user-defined analytic functions as well as built-in analytic functions. See CREATE FUNCTION.
The PARTITION BY and ORDER BY clauses in the analytic_clause are collation-sensitive.
Appendix C in Oracle Database Globalization Support Guide for the collation determination rules for the OVER (PARTITION BY . ORDER BY . ) clause of an analytic function
window_clause in the SELECT statement
Use the PARTITION BY clause to partition the query result set into groups based on one or more value_expr . If you omit this clause, then the function treats all rows of the query result set as a single group.
To use the query_partition_clause in an analytic function, use the upper branch of the syntax (without parentheses). To use this clause in a model query (in the model_column_clauses ) or a partitioned outer join (in the outer_join_clause ), use the lower branch of the syntax (with parentheses).
You can specify multiple analytic functions in the same query, each with the same or different PARTITION BY keys.
If the objects being queried have the parallel attribute, and if you specify an analytic function with the query_partition_clause , then the function computations are parallelized as well.
Valid values of value_expr are constants, columns, nonanalytic functions, function expressions, or expressions involving any of these.
Use the order_by_clause to specify how data is ordered within a partition. For all analytic functions you can order the values in a partition on multiple keys, each defined by a value_expr and each qualified by an ordering sequence.
Within each function, you can specify multiple ordering expressions. Doing so is especially useful when using functions that rank values, because the second expression can resolve ties between identical values for the first expression.
Whenever the order_by_clause results in identical values for multiple rows, the function behaves as follows:
CUME_DIST , DENSE_RANK , NTILE , PERCENT_RANK , and RANK return the same result for each of the rows.
ROW_NUMBER assigns each row a distinct value even if there is a tie based on the order_by_clause . The value is based on the order in which the row is processed, which may be nondeterministic if the ORDER BY does not guarantee a total ordering.
For all other analytic functions, the result depends on the window specification. If you specify a logical window with the RANGE keyword, then the function returns the same result for each of the rows. If you specify a physical window with the ROWS keyword, then the result is nondeterministic.
Restrictions on the ORDER BY Clause
The following restrictions apply to the ORDER BY clause:
When used in an analytic function, the order_by_clause must take an expression ( expr ). The SIBLINGS keyword is not valid (it is relevant only in hierarchical queries). Position ( position ) and column aliases ( c_alias ) are also invalid. Otherwise this order_by_clause is the same as that used to order the overall query or subquery.
An analytic function that uses the RANGE keyword can use multiple sort keys in its ORDER BY clause if it specifies any of the following windows:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW . The short form of this is RANGE UNBOUNDED PRECEDING .
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
RANGE BETWEEN CURRENT ROW AND CURRENT ROW
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Window boundaries other than these four can have only one sort key in the ORDER BY clause of the analytic function. This restriction does not apply to window boundaries specified by the ROW keyword.
Specify the ordering sequence (ascending or descending). ASC is the default.
NULLS FIRST | NULLS LAST
Specify whether returned rows containing nulls should appear first or last in the ordering sequence.
NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.
Analytic functions always operate on rows in the order specified in the order_by_clause of the function. However, the order_by_clause of the function does not guarantee the order of the result. Use the order_by_clause of the query to guarantee the final result ordering.
order_by_clause of SELECT for more information on this clause
Some analytic functions allow the windowing_clause . In the listing of analytic functions at the end of this section, the functions that allow the windowing_clause are followed by an asterisk (*).
ROWS | RANGE | GROUPS
The keywords ROWS , RANGE , and GROUPS are options to define a window frame unit used for calculating the function result. The function is then applied to all the rows in the window. The window moves through the query result set or partition from top to bottom.
Use ROWS to specify the window frame extent by counting rows forward or backward from the current row. ROWS allows any number of sort keys, of any ordered data types.
Use RANGE to specify the window frame extent as a logical offset. RANGE allows only one sort key, and its declared data type must allow addition and subtraction operations, for example they must be numeric, datetime, or interval data types.
Use GROUPS to specifiy the window frame extent with both ROWS and RANGE characteristics. Like ROWS a GROUPS window can have any number of sort keys, or any ordered types. Like RANGE , a GROUPS window does not make cutoffs between adjacent rows with the same values in the sort keys.
You cannot specify this clause unless you have specified the order_by_clause . Some window boundaries defined by the RANGE clause let you specify only one expression in the order_by_clause . Refer to Restrictions on the ORDER BY Clause.
The value returned by an analytic function with a logical offset is always deterministic. However, the value returned by an analytic function with a physical offset may produce nondeterministic results unless the ordering expression results in a unique ordering. You may have to specify multiple columns in the order_by_clause to achieve this unique ordering.
Use the BETWEEN . AND clause to specify a start point and end point for the window. The first expression (before AND ) defines the start point and the second expression (after AND ) defines the end point.
If you omit BETWEEN and specify only one end point, then Oracle considers it the start point, and the end point defaults to the current row.
Specify UNBOUNDED PRECEDING to indicate that the window starts at the first row of the partition. This is the start point specification and cannot be used as an end point specification.
Specify UNBOUNDED FOLLOWING to indicate that the window ends at the last row of the partition. This is the end point specification and cannot be used as a start point specification.
As a start point, CURRENT ROW specifies that the window begins at the current row or value (depending on whether you have specified ROW or RANGE , respectively). In this case the end point cannot be value_expr PRECEDING .
As an end point, CURRENT ROW specifies that the window ends at the current row or value (depending on whether you have specified ROW or RANGE , respectively). In this case the start point cannot be value_expr FOLLOWING .
value_expr PRECEDING or value_expr FOLLOWING
For RANGE or ROW :
If value_expr FOLLOWING is the start point, then the end point must be value_expr FOLLOWING .
If value_expr PRECEDING is the end point, then the start point must be value_expr PRECEDING .
If you are defining a logical window defined by an interval of time in numeric format, then you may need to use conversion functions.
NUMTOYMINTERVAL and NUMTODSINTERVAL for information on converting numeric times into intervals
If you specified ROWS :
value_expr is a physical offset. It must be a constant or expression and must evaluate to a positive numeric value.
If value_expr is part of the start point, then it must evaluate to a row before the end point.
If you specified RANGE :
value_expr is a logical offset. It must be a constant or expression that evaluates to a positive numeric value or an interval literal. Refer to Literals for information on interval literals.
You can specify only one expression in the order_by_clause .
If value_expr evaluates to a numeric value, then the ORDER BY expr must be a numeric or DATE data type.
If value_expr evaluates to an interval value, then the ORDER BY expr must be a DATE data type.
If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW .
You can remove rows, groups, and ties from the window frame with the EXCLUDE options:
If you specify EXCLUDE CURRENT ROW , and the current row in in the window frame, then the current row is removed from the window frame.
If you specify EXCLUDE GROUP , then the current row and any peers of the current row are removed from the window frame.
If you specify EXCLUDE TIES , then the peers of the current row are removed from the window frame. The current row is retained. Note, that if the current row is previously removed from the window frame, it remains removed.
If you specify EXCLUDE NO OTHERS , then no additional rows are removed from the window frame. This is the default option.
Analytic functions are commonly used in data warehousing environments. In the list of analytic functions that follows, functions followed by an asterisk (*) allow the full syntax, including the windowing_clause .
Oracle Database Data Warehousing Guide for more information on these functions and for scenarios illustrating their use
В выражениях можно использовать различные типы функций.
Аналитические функции
Аналитические функции позволяют исследовать данные с помощью моделей, например линий тренда и кластеров.
TRENDLINE(revenue, (calendar_year, calendar_quarter, calendar_month) BY (product), 'LINEAR', 'VALUE')
Эта функция выполняет аппроксимацию по линейной или экспоненциальной модели и возвращает подобранные значения или модель. Значение numeric_expr представляет значение Y для тренда, а ряд (столбцы времени) представляет значение X.
CLUSTER((product, company), (billed_quantity, revenue), 'clusterName', 'algorithm=k-means;numClusters=%1;maxIter=%2;useRandomSeed=FALSE;enablePartitioning=TRUE', 5, 10)
Эта функция группирует набор записей по одному или нескольким входным выражениям, используя метод k-средних или иерархическую кластеризацию.
OUTLIER((product, company), (billed_quantity, revenue), 'isOutlier', 'algorithm=mvoutlier')
Эта функция классифицирует запись как отклонение по одному или нескольким входным выражениям, используя метод k-средних, иерархическую кластеризацию или алгоритмы обнаружения отклонений в многомерных данных.
REGR(revenue, (discount_amount), (product_type, brand), 'fitted', '')
Эта функция выполняет аппроксимацию по линейной модели и возвращает подобранные значения или модель. Эту функцию можно использовать для аппроксимации линейной кривой по двум показателям.
EVALUATE_SCRIPT('filerepo://obiee.Outliers.xml', 'isOutlier', 'algorithm=mvoutlier;id=%1;arg1=%2;arg2=%3;useRandomSeed=False;', customer_number, expected_revenue, customer_age)
Эта функция выполняет сценарий R, указанный в значении script_file_path, передавая в качестве входного значения один или несколько столбцов или литеральных выражений. Вывод функции определяется значением output_column_name.
Системные функции
Системная функция USER возвращает значения, относящиеся к сеансу.
Она возвращает имя пользователя, с которым был выполнен вход в систему.
Читайте также: