Аналитическая функция AVG() вычисляет среднее значение для заданных аргументом данных. Тип данных аргумента есть NUMBER либо любой другой тип данных, который можно преобразовать NUMBER.
Синтаксис:
AVG ([{DISTINCT/ALL}] expr ) OVER ( [ < partition_by_clause > ] [< order_by_clause > [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}] ] [<window_clause>])
Фраза <partition_by_clause> = ([PARTITION BY <value expression1> [, ...]]) делит результирующее множество, полученное с помощью предложения FROM, на секции, к которым применяется функция RANK.
Фраза <order_by_clause> = (ORDER BY <value expression2> [{NULLS FIRST/NULLS LAST}] [ASC/DESC] ) определяет порядок, в котором значения CUME_DIST применяются к строкам в секции. Нельзя использовать порядковый номер колонки в строке результирующего множества в ORDER BY, если используется ранжирующая функция.
Фраза <window_clause> задает определение окна (см. раздел 4.3).
Далее при определении аналитических агрегатных и статистических функции мы не будем приводить расшифровку вышеприведенных фраз, поскольку она будет такой же.
Если указана опция DISTINCT, то можно указать только < partition_by_clause >. Если предложение OVER отсутствует, то функция AVG будет агрегирующей.
Примером использования AVG в качестве агрегирующей функции может быть запрос к таблице EMPLOYEES схемы HR, с помощью которого вычисляется средняя зарплата (6461.83 у.е.) служащих организации:
SELECT AVG(salary) FROM EMPLOYEES;
Приведем пример использования AVG в качестве аналитической функции. Пусть требуется узнать средний оклад сотрудников для каждого руководителя в организации. Для этого составим следующий запрос к таблице EMPLOYEES схемы HR:
SELECT DISTINCT manager_id, AVG(salary) OVER (PARTITION BY manager_id ) AS asal FROM EMPLOYEES ORDER BY manager_id ;
Результат выполнения запроса (фрагмент):
MANAGER_ID ASAL 100 11100 101 8983,2 102 9000 103 4950 …… 205 8300 NULL 24000
Точно такой же результат можно получить с помощью запроса:
SELECT DISTINCT manager_id, AVG(salary) FROM EMPLOYEES GROUP BY manager_id ORDER BY manager_id ;
Пусть необходимо составить отчет, включающий оклад каждого сотрудника и среднюю зарплату всех принятых на работу в течение 90 предыдущих дней, а также среднюю зарплату всех принятых на работу в течение 90 следующих дней. Для этого составим следующий запрос к таблице EMPLOYEES схемы HR:
SELECT last_name, hire_date, salary, AVG(salary) OVER (ORDER BY hire_date ASC RANGE 90 PRECEDING) asal_90_before, AVG(salary) OVER(ORDER BY hire_date DESC RANGE 90 PRECEDING) asal_90_after FROM EMPLOYEES;
Результат выполнения запроса (фрагмент):
LAST_NAME HIRE_DATE SALARY ASAL_90_BEFORE ASAL_90_AFTER Banda 21.04.08 6200 5600 6150 Kumar 21.04.08 6100 5600 6150 Ande 24.03.08 6400 5211,11 6233,33 Markle 08.03.08 2200 4540 5225 Lee 23.02.08 6800 5010 5540 Philtanker 06.02.08 2200 5100 4983,33 Geoni 03.02.08 2800 5390 4671,49 …..
Для первого использования функции AVG сгенерированное перемещающееся окно включает предыдущие строки группы (в данном случае вся таблица), отстоящие от текущей строки не более чем на 90 дней, а во втором случае последующие, отстоящие не более чем на 90 дней.
Для сотрудника Markle в первое окно попадают сотрудники Banda, Kumar, Ande и Markle с окладами 6200, 6100, 6400 и 2200 у.е., т.е. среднее по окну равно 5225 у.е.
Аналитическая функция COUNT() вычисляет количество строк в результирующем множестве.
Синтаксис:
COUNT ([{DISTINCT/ALL}] expr ) OVER ( [ < partition_by_clause > ] [< order_by_clause > [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}] ] [<window_clause>])
Если указана опция DISTINCT, то можно указать только < partition_by_clause >. Если в качестве аргумента указана «*», функция обрабатывает все строки, включая дубликаты и строки с NULL-значениями в колонках аргумента.
Если предложение OVER отсутствует, то функция COUNT будет агрегирующей. Так можно вычислить количество сотрудников (35), получающих комиссионные, с помощью запроса к таблице EMPLOYEES схемы HR:
SELECT COUNT(*) C_Emp FROM EMPLOYEES WHERE commission_pct > 0;
Приведем пример использования COUNT в качестве аналитической функции. Пусть нужно вычислить для каждого сотрудника количество сотрудников, имеющих оклад в диапазоне меньше 50 и больше 150 у.е. Следующий запрос к таблице EMPLOYEES схемы HR даст ответ на поставленную задачу:
SELECT last_name, salary,
COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND
150 FOLLOWING) AS mov_count
FROM employees
ORDER BY salary, last_name;
Результат выполнения запроса (фрагмент):
LAST_NAME SALARY MOV_COUNT Olson 2100 3 Markle 2200 2 Philtanker 2200 2 Gee 2400 8 Landry 2400 8 Colmenares 2500 10 Marlow 2500 10 Patel 2500 10 Perkins 2500 10 Sullivan 2500 10 Vargas 2500 10 Grant 2600 6 Himuro 2600 6 Matos 2600 6 OConnell 2600 6 Mikkilineni 2700 6 Seo 2700 6 Atkinson 2800 7 Geoni 2800 7 ….
Чтобы понять, как было выполнено вычисление, следует рассмотреть формирование перемещающегося окна. 50 PRECEDING определяет, что окно включает предыдущие строки группы (в данном случае вся таблица), отстоящие от текущей строки не более чем на 50 у.е. в окладе. 150 FOLLOWING определяет, что окно включает последующие строки, отстоящие от текущей строки не менее чем на 150 у.е. в окладе.
Сотрудник Olson имеет минимальный оклад. В окно для него попадает он сам и сотрудники Markle и Philtanker, т.е. количество равно 3. В окно для сотрудника Markle попадет он сам и сотрудник и Philtanker и т.д.
Аналитическая функция MAX() вычисляет максимальное значение аргумента в результирующем множестве.
Синтаксис:
MAX ([{DISTINCT/ALL}] expr ) OVER ( [ < partition_by_clause > ] [< order_by_clause > [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}] ] [<window_clause>])
Если предложение OVER отсутствует, то функция MAX будет агрегирующей. Так можно вычислить максимальный оклад сотрудников (24000 у.е.) в организации с помощью запроса к таблице EMPLOYEES схемы HR:
SELECT MAX(salary) FROM EMPLOYEES;
Приведем пример использования MAX в качестве аналитической функции. Пусть нужно узнать для каждого сотрудника размер самого высокого оклада среди сотрудников, подчиняющихся руководителям, как сотрудникам. Следующий запрос к таблице EMPLOYEES схемы HR даст ответ на поставленную задачу:
SELECT manager_id, last_name, salary,
MAX(salary) OVER (PARTITION BY manager_id) AS mgr_max
FROM EMPLOYEES
ORDER BY manager_id, last_name, salary;
Результат выполнения запроса (фрагмент):
MANAGER_ID LAST_NAME SALARY MGR_MAX 100 Cambrault 11000 17000 100 De Haan 17000 17000 100 Errazuriz 12000 17000 100 Fripp 8200 17000 100 Hartstein 13000 17000 100 Kaufling 7900 17000 100 Kochhar 17000 17000 100 Mourgos 5800 17000 100 Partners 13500 17000 100 Raphaely 11000 17000 100 Russell 14000 17000 100 Vollman 6500 17000 100 Weiss 8000 17000 100 Zlotkey 10500 17000 101 Baer 10000 12008 101 Greenberg 12008 12008 …..
Если использовать этот запрос в качестве подзапроса, то можно определить сотрудника, имеющего самый высокий оклад в отделе:
SELECT manager_id, last_name, salary
FROM (SELECT manager_id, last_name, salary,
MAX(salary) OVER (PARTITION BY manager_id) AS rmax_sal
FROM EMPLOYEES)
WHERE salary = rmax_sal
ORDER BY manager_id, last_name, salary;
Результат выполнения запроса (фрагмент):
MANAGER_ID LAST_NAME SALARY 100 De Haan 17000 100 Kochhar 17000 101 Greenberg 12008 101 Higgins 12008 102 Hunold 9000 103 Ernst 6000 108 Faviet 9000 114 Khoo 3100 ….
Аналитическая функция MIN() вычисляет минимальное значение аргумента в результирующем множестве.
Синтаксис:
MIN ([{DISTINCT/ALL}] expr ) OVER ( [ < partition_by_clause > ] [< order_by_clause > [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}] ] [<window_clause>])
Если предложение OVER отсутствует, то функция MIN будет агрегирующей. Так можно узнать дату самого раннего устройства на работу в организацию (13.01.01 г.) с помощью запроса к таблице EMPLOYEES схемы HR:
SELECT MIN(hire_date) FROM EMPLOYEES;
Приведем пример использования MIN в качестве аналитической функции. Пусть нужно узнать для каждого сотрудника размер самого низкго оклада среди сотрудников, подчиняющихся руководителям, как сотрудникам. Следующий запрос к таблице EMPLOYEES схемы HR даст ответ на поставленную задачу:
SELECT manager_id, last_name, salary,
MIN(salary) OVER(PARTITION BY manager_id) AS p_cmin
FROM employees
ORDER BY manager_id, last_name, salary;
Результатом запроса будет (фрагмент):
MANAGER_ID LAST_NAME SALARY P_CMIN 100 Cambrault 11000 5800 100 De Haan 17000 5800 100 Errazuriz 12000 5800 100 Fripp 8200 5800 100 Hartstein 13000 5800 100 Kaufling 7900 5800 100 Kochhar 17000 5800 100 Mourgos 5800 5800 100 Partners 13500 5800 100 Raphaely 11000 5800 100 Russell 14000 5800 100 Vollman 6500 5800 100 Weiss 8000 5800 100 Zlotkey 10500 5800 101 Baer 10000 4400 101 Greenberg 12008 4400 …..
Аналитическая функция SUM() вычисляет сумму по значению аргумента в результирующем множестве.
Синтаксис:
SUM ([{DISTINCT/ALL}] expr ) OVER ( [ < partition_by_clause > ] [< order_by_clause > [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}] ] [<window_clause>])
Если предложение OVER отсутствует, то функция SUM будет агрегирующей. Так можно вычислить сумму всех окладов сотрудников (691400 у.е.) в организации с помощью запроса к таблице EMPLOYEES схемы HR:
SELECT SUM(salary) FROM EMPLOYEES;
Приведем пример использования SUM в качестве аналитической функции. Пусть нужно для каждого руководителя вычислить сумму окладов сотрудников, которые подчиняются этому руководителю и их оклад меньше или равен текущему значению в строке. Следующий запрос к таблице EMPLOYEES схемы HR даст ответ на поставленную задачу:
SELECT manager_id, last_name, salary, SUM(salary) OVER (PARTITION BY manager_id ORDER BY salary RANGE UNBOUNDED PRECEDING) l_csum FROM employees ORDER BY manager_id, last_name, salary, l_csum;
Результат выполнения запроса (фрагмент):
MANAGER_ID LAST_NAME SALARY L_CSUM 100 Cambrault 11000 68900 100 De Haan 17000 155400 100 Errazuriz 12000 80900 100 Fripp 8200 36400 100 Hartstein 13000 93900 100 Kaufling 7900 20200 100 Kochhar 17000 155400 100 Mourgos 5800 5800 100 Partners 13500 107400 100 Raphaely 11000 68900 100 Russell 14000 121400 100 Vollman 6500 12300 100 Weiss 8000 28200 100 Zlotkey 10500 46900 101 Baer 10000 20900 101 Greenberg 12008 44916 101 Higgins 12008 44916 ….
Сгенерированное в запросе перемещающее окно начинает с первой строки секции и заканчивается текущей строкой.
Видно, что сотрудники that Raphaely и Cambrault имеют одинаковую вычисленную сумму, поскольку имеют одинаковый оклад.
На этом мы заканчиваем обзор агрегатных аналитических функций диалекта SQL Oracle 11g.
Помимо агрегатных аналитических функций, которые упоминались выше, в диалекте SQL Oracle 11g есть статистические функции, непосредственно предназначенные для финансовых и статистических вычислений.
Статистические функции выполняют вычисление на наборе значений и возвращают одно значение. Статистические функции часто используются в предложении GROUP BY команды SELECT.
Агрегатные и статистические функции
Указание ALL применяет функцию ко всем значениям. ALL является аргументом по умолчанию, а DISTINCT указывает, что рассматривается каждое уникальное значение.
Рассмотрим пример, в котором применяются встроенные агрегатные функции для вычисления основных статистических параметров.
Аналитическая функция CORR() вычисляет коэффициент корреляции для пары выражений, которые можно преобразовать к числовому типу данных.
Синтаксис:
CORR ([{DISTINCT/ALL}] expr1, expr2 ) OVER ( [ < partition_by_clause > ] [< order_by_clause > [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}] ] [<window_clause>])
Функция CORR применяется к паре аргументов-выражений, если они не имеют значения NULL (хотя бы одно или оба). Если функция применяется к пустому результирующему множеству, она возвращает NULL-значение. Тип данных возвращаемого значения NUMBER. Если предложение OVER не используется, то функция становится агрегирующей.
Вычисления выполняются по формуле (см. раздел 4.2):
Приведем пример использования CORR() в качестве аналитической функции. Вычислим корреляцию между продолжительностью работы в организации и размером оклада для подразделений с номерами 50 и 80. Для этого составим следующий запрос к таблице EMPLOYEES схемы HR:
SELECT employee_id, job_id,
TO_CHAR((SYSDATE - hire_date) YEAR TO MONTH ) "Г-М", salary,
CORR(SYSDATE-hire_date, salary)
OVER(PARTITION BY job_id) AS "Корреляция"
FROM EMPLOYEES
WHERE department_id in (50, 80)
ORDER BY job_id, employee_id;
Результат выполнения запроса (фрагмент):
EMPLOEEY_ID JOB_ID Г-М SALARY КОРРЕЛЯЦИЯ 145 SA_MAN +14-10 14000 0,912 146 SA_MAN +14-07 13500 0,912 147 SA_MAN +14-04 12000 0,912 148 SA_MAN +11-09 11000 0,912 149 SA_MAN +11-06 10500 0,912 150 SA_REP +14-06 10000 0,804 151 SA_REP +14-04 9500 0,804 152 SA_REP +13-11 9000 0,804 153 SA_REP +13-04 8000 0,804 ….
По результатам запроса видно, что можно говорить о положительной линейной корреляции между продолжительностью работы сотрудника в организации и его окладом, т.е. о линейной функциональной зависимости между этими величинами в указанных отделах.
Аналитическая функция COVAR_POP() вычисляет ковариацию генеральной совокупности для пары выражений, которые можно преобразовать к числовому типу данных.
Аналитическая функция COVAR_SAMP() вычисляет ковариацию выборочной совокупности для пар выражений, которые можно преобразовать к числовому типу данных.
Синтаксис:
COVAR_POP ([{DISTINCT/ALL}] expr1, expr2 ) OVER ( [ < partition_by_clause > ] [< order_by_clause > [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}] ] [<window_clause>])
COVAR_SAMP ([{DISTINCT/ALL}] expr1, expr2 ) OVER ( [ < partition_by_clause > ] [< order_by_clause > [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}] ] [<window_clause>])
Функция COVAR_POP (population covariance) и функция COVAR_SAMP (sample covariance) применяются к паре аргументов-выражений, если они не имеют значения NULL (хотя бы одно или оба). Если функция применяется к пустому результирующему множеству, он возвращает NULL-значение. Тип данных возвращаемого значения NUMBER. Если предложение over не используется, то функция становится агрегирующей.
Вычисления выполняются по формулам:
Приведем пример использования этих функций как агрегатных. Вычислим ковариацию генеральной совокупности и выборочную ковариацию между окладом сотрудников и продолжительностью их работы в организации (от текущей даты). Для этого составим следующий запрос к таблице EMPLOYEES схемы HR:
SELECT job_id,
COVAR_POP(SYSDATE-hire_date, salary) AS covar_pop,
COVAR_SAMP(SYSDATE-hire_date, salary) AS covar_samp
FROM EMPLOYEES
WHERE department_id in (50, 80)
GROUP BY job_id
ORDER BY job_id, covar_pop, covar_samp;
Результат выполнения запроса:
JOB_ID COVAR_POP COVAR_SAMP SA_MAN 660700,00 825875,00 SA_REP 579988,47 600702,34 SH_CLERK 212432,50 223613,16 ST_CLERK 176577,25 185870,79 ST_MAN 436092 545115
Приведем пример использования аналитических функций COVAR_POP и COVAR_SAMP. Вычислим накопительные (кумулятивные) ковариацию генеральной совокупности и выборочную ковариацию между окладом сотрудников и продолжительностью их работы в организации (от текущей даты). Для этого составим следующий запрос к таблице EMPLOYEES схемы HR:
SELECT job_id,
COVAR_POP(SYSDATE-hire_date, salary) OVER BY (ORDER BY job_id) AS covar_pop,
COVAR_SAMP(SYSDATE-hire_date, salary) OVER BY (ORDER BY job_id) AS covar_samp
FROM EMPLOYEES
WHERE department_id in (50, 80)
ORDER BY job_id, covar_pop, covar_samp;
Результат выполнения запроса (фрагмент):
JOB_ID COVAR_POP COVAR_SAMP SA_MAN 660700,00 825875,00 SA_MAN 660700,00 825875,00 SA_MAN 660700,00 825875,00 SA_MAN 660700,00 825875,00 SA_MAN 660700,00 825875,00 SA_REP 623032,27 641912,03 SA_REP 623032,27 641912,03 ……
Аналитическая функция MEDIAN() вычисляет медиану выборки для выражения, которое можно преобразовать к числовому типу данных.
Синтаксис:
MEDIAN ([{DISTINCT/ALL}] expr1, expr2 ) OVER ( [ < partition_by_clause > ]
Функция MEDIAN применяется к выражению с числовым или временным типом данных. NULL-значения игнорируются. Тип данных возвращаемого значения NUMBER или DATETIME. Если предложение over не используется, то функция становится агрегирующей.
Если в выборке нечетное количество записей, значение медианы равно значению записи, расположенной точно в середине выборки. Выше и ниже этого значения существует одинаковое количество элементов. Если в выборке четное количество значений, значение медианы равно, либо среднему двух центральных значений (в случае финансовых медиан), либо меньшему из них (в случае статистических медиан).
В SQL вычисления выполняются следующим образом. Сначала строки сортируются. N – количество строк в группе или секции. Вычисляется номер строки RN как 1+0.5*(N-1). Далее вычисляются числа CRN = CEILING(RN) и FRN = FLOOR(RN). Окончательный результат: если CRN=NR=FNR, то за медиану принимается значение выражения в строке NR, в противном случае за медиану принимается значение равное (CRN-RN)*(значение выражения в строке FNR)+(RN-FRN)*(значение выражения в строке CNR).
Следующий запрос к таблице EMPLOYEES схемы HR вычисляет медиану окладов сотрудников для каждого подразделения организации:
SELECT department_id, MEDIAN(salary) FROM EMPLOYEES GROUP BY department_id ORDER BY department_id;
Результат запроса:
DEPARTMENT_ID MEDIAN 10 4400 20 9500 30 2850 40 6500 50 3100 60 4800 70 10000 80 8900 90 17000 100 8000 110 10154 NULL 7000
Приведем пример использования аналитической функции MEDIAN. Вычислим медиану окладов сотрудников для каждого руководителя из некоторого подмножества подразделений организации. Для этого составим запрос к таблице EMPLOYEES схемы HR:
SELECT manager_id, employee_id, salary,
MEDIAN(salary) OVER (PARTITION BY manager_id) "Median"
FROM EMPLOYEES
WHERE department_id > 60
ORDER BY manager_id, employee_id;
Результатом выполнения запроса будет (фрагмент):
MANAGER_ID EMPLOEEY_IS SALARY MEDIAN 100 101 17000 13500 100 102 17000 13500 100 145 4000 13500 100 146 13500 13500 100 147 12000 13500 100 148 11000 13500 100 149 10500 13500 101 108 12008 12008 101 204 10000 12008 101 205 12008 12008 108 109 9000 7800 108 110 8200 7800 108 111 7700 7800 108 112 7800 7800
Аналитическая функция STDDEV() вычисляет стандартное (среднеквадратичное) отклонение выражения, которое можно преобразовать к числовому типу данных для группы строк.
Аналитическая функция STDDEV_POP() вычисляет стандартное отклонение генеральной совокупности (population standard deviation) выражения, которое можно преобразовать к числовому типу данных для группы строк.
Аналитическая функция STDDEV_SAMP() вычисляет выборочное стандартное (среднеквадратичное) отклонение (sample standard deviation) выражения, которое можно преобразовать к числовому типу данных для группы строк.
Синтаксис:
STDDEV ([{DISTINCT/ALL}] expr1, expr2 ) OVER ( [ < partition_by_clause > ] [< order_by_clause > [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}] ] [<window_clause>])
STDDEV_POP ([{DISTINCT/ALL}] expr1, expr2 ) OVER ( [ < partition_by_clause > ] [< order_by_clause > [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}] ] [<window_clause>])
STDDEV_SAMP ([{DISTINCT/ALL}] expr1, expr2 ) OVER ( [ < partition_by_clause > ] [< order_by_clause > [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}] ] [<window_clause>])
Значение этих функций вычисляется как квадратный корень из значений функций VARIANCE, VAR_POP, VAR_SAMP, соответственно. Функция STDDEV отличается от STDDEV_SAMP в том, что STDDEV возвращает нуль, когда в результирующем множестве только одна строка, тогда как STDDEV_SAMP возвращает NULL-значение.
Если предложение OVER не указано, то эти функции становятся агрегирующими. Если используется квалификатор DISTINCT при вызове аналитической функции STDDEV, то в предложении OVER можно использовать только предложение PARTITION BY.
Приведем примеры использования функции вычисления стандартных отклонений.
В случае использования этих функций как агрегатных следующий запрос к таблице EMPLOYEES схемы HR
SELECT STDDEV(salary) AS S, STDDEV_POP(salary) As S_POP, STDDEV_SAMP(salary) As S_SAMP FROM EMPLOYEES;
рассчитает средние отклонения по окладам сотрудников в организации (3909.58, 3891.27, 3909.58 у.е., соответственно).
Для случая использования этих функций как аналитических вычислим стандартные отклонения окладов по сотрудникам в подразделениях. Можно составить следующий запрос к таблице EMPLOYEES схемы HR:
SELECT department_id, last_name, salary, STDDEV(salary) OVER (PARTITION BY department_id) AS std, STDDEV_POP(salary) OVER (PARTITION BY department_id) AS pop_std, STDDEV_SAMP(salary) OVER (PARTITION BY department_id) AS pop_std FROM EMPLOYEES ORDER BY department_id, last_name, salary;
Результат выполнения запроса (фрагмент):
DEPARTMENT_ID LAST_NAME SALARY STD POP_STD SAMP_STD 10 Whalen 4400 0 0 NULL 20 Fay 6000 4949,75 3500 4949,75 20 Hartstein 13000 4949,75 3500 4949,75 30 Baida 2900 3362,59 3069,61 3362,59 ….
Аналитическая функция VARIANCE() вычисляет дисперсию выражения, которое можно преобразовать к числовому типу данных для группы строк.
Аналитическая функция VAR_POP() вычисляет дисперсию генеральной совокупности выражения, которое можно преобразовать к числовому типу данных для группы строк.
Аналитическая функция VAR_SAMP() вычисляет выборочную дисперсию выражения, которое можно преобразовать к числовому типу данных для группы строк.
Синтаксис:
VARIANCE ([{DISTINCT/ALL}] expr1, expr2 ) OVER ( [ < partition_by_clause > ] [< order_by_clause > [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}] ] [<window_clause>])
VAR_POP ([{DISTINCT/ALL}] expr1, expr2 ) OVER ( [ < partition_by_clause > ] [< order_by_clause > [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}] ] [<window_clause>])
VAR_SAMP ([{DISTINCT/ALL}] expr1, expr2 ) OVER ( [ < partition_by_clause > ] [< order_by_clause > [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}] ] [<window_clause>])
Функция VARIANCE вычисляет дисперсию как 0, если количество строк в группе равно 1, VAR_SAMP в противном случае.
Функция VAR_POP вычисляет дисперсию генеральной совокупности по формуле:
Функция VAR_SAMP вычисляет дисперсию по формуле:
Функция VARIANCE отличается от VAR_SAMP в том, что VARIANCE возвращает нуль, когда в результирующем множестве только одна строка, тогда как VAR_SAMP возвращает NULL-значение.
Если предложение OVER не указано, то эти функции становятся агрегирующими. Если используется квалификатор DISTINCT при вызове аналитической функции VARIANCE, то в предложении OVER можно использовать только предложение PARTITION BY.
Приведем примеры использования функции вычисления стандартных отклонений.
В случае использования этих функций как агрегатных, следующий запрос к таблице EMPLOYEES схемы HR
SELECT VARIANCE(salary) AS Var, VAR_POP(salary) AS P_V, VAR_SAMP(salary) AS S_V FROM EMPLOYEES;
вычислит дисперсии окладов сотрудников организации.
Для случая использования этих функций как аналитических вычислим дисперсии окладов сотрудников в подразделении 30. Можно составить следующий запрос к таблице EMPLOYEES схемы HR:
SELECT last_name, salary, VARIANCE(salary)
OVER (ORDER BY hire_date) "V",
VAR_POP(salary)
OVER (ORDER BY hire_date) as Var_P,
VAR_SAMP(salary)
OVER (ORDER BY hire_date) As Var_S
FROM EMPLOYEES
WHERE department_id = 30
ORDER BY last_name, salary;
Результат выполнения запроса:
LAST_NAME SALARY V VAR_P VAR_S Baida 2900 16283333,33 12212500 16283333,33 Colmenares 2500 11307000 9422500 11307000 Himuro 2600 13317000 10653600 13317000 Khoo 3100 31205000 15602500 31205000 Raphaely 11000 0 0 NULL Tobias 2800 21623333,33 14415555,56 21623333,33
На этом мы заканчиваем обзор статистических аналитических функций диалекта SQL Oracle 11g.
Для получения официальных документов о завершении программы дополнительного профессионального образования (удостоверения о повышении квалификации, дипломов о профессиональной переподготовке и MBA) необходимо предоставить:
Внимание! Вы можете не заказывать доставку бумажной версии официального документы, а скачать его в электронном виде и распечатать самостоятельно. Информация о выданном документе в течение 1 месяца загружается в Федеральную информационную систему «Федеральный реестр сведений о документах об образовании и (или) о квалификации, документах об обучении» - ФИС ФРДО.
Доступ на новый сайт осуществляется с использованием адреса электронной почты, который был указан вами при регистрации на "старом". Мы постарались перенести все ваши данные с прежнего ресурса, однако не исключена вероятность потери части информации.
При возникновении проблемы со входом, воспользуйтесь функцией сброса пароля
Если вы обнаружите несоответствия, пожалуйста, сообщите нам.