Джон Адольф Палинский

Опции оператора Group By, функции группировки и статистики, прогнозирование будущих значений
(The Group By Clause Options, the Grouping and Statistic Functions, and Predicting Future Values,
by John Adolph Palinski)

Источник: Oracle Professional: March 2002,
http://www.pinnaclepublishing.com/OP/OPmag.nsf/
0/483603F7BF527ECF85256B6A0006EA9F!open&login

Эта статья – первая из трех статей, в которых Джон Адольф Палинский затрагивает некоторые из новых возможностей базы данных Oracle. В ней рассматриваются опции оператора Group By - Rollup и Cube, а также функции группировки и статистические функции. Следующая статья расскажет о новом предложении Case и функциях ранжирования. А третья - о функциях организации окон, которые используются для вычисления скользящего среднего.

В базу данных, начиная с Oracle 8.1.7, добавлены некоторые новые функции. Эти функции предназначены для того, чтобы позволить пользователю более легко создавать бизнес-логику. Аналитики бизнес-процессов могут подтвердить, насколько сложно вычисление скользящего среднего, значений опережения/запаздывания, статистических значений или будущих значений. Наконец, аналитикам часто требуется специальный пакет типа SAS или Cognos для вычисления этих значений. Появление таких функций в базе данных устраняет необходимость использования средств, разработанных третьими фирмами.

В этой части, которая является первой в серии, состоящей из трех частей, будут рассмотрены опции оператора Group By - Rollup и Cube, а также функции группировки и статистические функции. Кроме того, будут рассмотрены способы вычисления будущих значений, основанных на линии регрессии.

Опция Rollup

В Oracle добавлены две опции, которые могут быть включены в оператор Group By. Обычно оператор Group By используется для вычисления агрегированного значения, основанного на столбцах, перечисленных в этом операторе. А опции предназначены для того, чтобы увеличить способность Oracle анализировать данные большой размерности. Размерность – это категория, такая как регион, штат или департамент продаж. Новые опции называются Rollup и Cube. Они добавляют некоторые новые промежуточные итоги к агрегированному набору результатов. Предварительно рассмотрим Листинг 1, — на нем показан обычный запрос, в котором используется оператор Group By. Этот запрос вычисляет итоговую запись для каждого уникального набора, состоящего из департамента и штата.

Листинг 1. Предложение Select, которое вычисляет агрегированное значение при помощи оператора Group By.

SQL> select fk_department, state, sum(tool_cost)
from employee, emp_tools
where payroll_number = fk_payroll_number
group by fk_department, state
order by 1,2;
 
 
 
 FK_D ST SUM(TOOL_COST)
 ---- -- --------------
 INT ME 46.2
 INT NE 12
 INT NY 324
 INT TX 375
 INT VT 35
 POL CA 18.5
 POL NC 16.7
 POL NY 20
 POL VA  116.95
 WEL IA 24
 WEL IL 28.7
 WEL MA 88.85
 WEL NY 61.95
 WEL OH 23
 
 14 rows selected.
 
SQL>
 
End Listing
 
 
 

В оператор Group By можно добавить опцию Rollup. Эта опция приводит к созданию промежуточных итогов по столбцу назначения. Промежуточные итоги вычисляются от самого низкого уровня до общей суммы. При этом выполняется n+1 уровня вычислений, где n – это количество столбцов, входящих в опцию Rollup.

На Листинге 2 для иллюстрации показано модифицированное Select-предложение из Листинга 1. Столбцы fk_department и state находятся внутри функции Rollup. В результате будет получено три (два столбца назначения + один) новые записи. Эти записи представляют собой:

Обычное агрегированное число, которое будет вычислено, как будто столбцы назначения находятся только в операторе Group By. В этом случае оно будет означать стоимость средств штата в департаменте.

Общая стоимость средств каждого департамента независимо от штата.

Общая стоимость средств.

Листинг 2. Использование опции Rollup для вычисления стоимости средств департамента.

SQL> select fk_department, state, sum(tool_cost)
from employee, emp_tools
where payroll_number = fk_payroll_number
group by rollup(fk_department, state)
 order by 1,2;
 
 FK_D ST SUM(TOOL_COST)
 ---- -- -------------- 
 INT ME 46.2
 INT NE 12
 INT NY 324
 INT TX 375
 INT VT 35
 INT 792.2
 POL CA 18.5
 POL NC  16.7
 POL NY 20
 POL VA 116.95
 POL 172.15
 WEL IA 24
 WEL IL 28.7
 WEL MA 88.85
 WEL NY 61.95
 WEL OH 23
 WEL 226.5
 1190.85
 
 18 rows selected.
 
 SQL>
 
 End listing
 

В опцию Rollup не требуется включать все столбцы оператора Group By. Это видно на Листинге 3. Select-предложение из Листинга 2 изменено так, что столбец state переместился из опции Rollup. Теперь будут выполняться следующие вычисления:

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

Суммирование стоимости средств каждого штата по всем департаментам. Это вычисление производится благодаря опции Rollup.

Можно заметить, что общая сумма стоимости средств всего результирующего набора не вычисляется. Это значение сворачивается только тогда, когда все столбцы оператора Group By находятся в опции Rollup.

Листинг 3. Использование опции Rollup для вычисления стоимости средств штата.

SQL> select state, fk_department, sum(tool_cost)
from employee, emp_tools
where payroll_number = fk_payroll_number
group by rollup(fk_department), state
 order by 1, 2;
 
 ST FK_D SUM(TOOL_COST)
 -- ---- --------------
 CA POL  18.5
 CA 18.5
 IA WEL 24
 IA 24
 IL WEL 28.7
 IL 28.7
 MA WEL 88.85
 MA 88.85
 ME INT 46.2
 ME 46.2
 NC POL 16.7
 NC  16.7
 NE INT 12
 NE 12
 NY INT 324
 NY POL 20
 NY WEL 61.95
 NY 405.95
 OH WEL 23
 OH 23
 TX INT 375
 TX 375
 VA POL 116.95
 VA 116.95
 VT INT 35
 VT 35
 
 26 rows selected.

SQL>

End listing

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

Опция Cube

Опция Cube в общем похожа на опцию Rollup. Различие заключается в том, что опция Cube вычисляет все возможные комбинации промежуточных итогов. Листинг 4 представляет собой модификацию Листинга 2. Ключевое слово Rollup заменено на Cube. В результате появляется дополнительный итог. Листинг 4 содержит еще и общую стоимость по каждому штату. Этого итога нет в результирующем наборе Листинга 2.

Листинг 4. Использование опции Cube для вычисления всех комбинаций промежуточных итогов.

SQL> select fk_department, state, sum(tool_cost)
from employee, emp_tools
where payroll_number = fk_payroll_number
group by cube(fk_department, state)
order by 1,2;
 
 FK_D ST SUM(TOOL_COST)
 ---- -- --------------
 INT ME 46.2
 INT NE 12
 INT NY 324
 INT TX 375
 INT VT 35
 INT 792.2
 POL CA 18.5
 POL NC 16.7
 POL NY  2
 POL VA 116.9
 POL 172.15
 WEL IA 24
 WEL IL 28.7
 WEL MA 88.85
 WEL NY 61.95
 WEL OH 23
 WEL 226.5
 CA 18.5
 IA 24
 IL  28.7
 MA 88.85
 ME 46.2
 NC 16.7
 NE 12
 NY 405.95
 OH 23
 TX 375
 VA 116.95
 VT 35
 1190.85
 
 30 rows selected.
 
 
 End Listing

Опцию Cube следует применять аккуратно. Если максимальное число вычислений окажется очень большим, то производительность может значительно понизиться. Для сохранения производительности более эффективным было бы использование опции Rollup, а не Cube.

Функция Grouping

Существует два способа определить, вычислена ли строка опцией Rollup/Cube. Вычисленные строки содержат значения null в некоторых столбцах. Конечно, такой метод нахождения строки-итога, разработчик не может использовать в программе. Поэтому Oracle предоставляет функцию, которая позволяет идентифицировать такие строки. Эта функция - Grouping. Она может содержать только один столбец назначения и возвращает 1, если строка является итогом по соответствующему измерению. В противном случае возвращается значение 0.

На Листинге 5 показана эта функция. Первая функция Grouping определяет строки, которые являются промежуточными итогами измерения fk_department. Вторая функция Grouping показывает промежуточные итоги каждого измерения state. Строки, созданные второй функцией, являются промежуточными итогами по стоимости средств в каждом департаменте. Обе функции Grouping возвращают 1 для общего итога, так как эта строка фактически вычисляется ими по обоим измерениям.

Листинг 5. Использование функции Grouping для определения строк, вычисленных опцией Rollup.

SQL> select fk_department, state, sum(tool_cost),
grouping(fk_department) as dept_group_value,
grouping(state) as state_group_value
from employee, emp_tools
where payroll_number = fk_payroll_number
group by rollup(fk_department, state)
order by 1,2;
 
 FK_D ST SUM(TOOL_COST) DEPT_GROUP_VALUE STATE_GROUP_VALUE
 ---- -- -------------- ---------------- -----------------
 INT ME 46.2 0  0
 INT NE 12 0 0
 INT NY 324 0 0
 INT TX 375 0 0
 INT VT 35 0 0
 INT  792.2 0 1
 POL CA 18.5 0 0
 POL NC 16.7 0 0
 POL NY 20 0 0
 POL VA 116.95  0 0
 POL 172.15 0 1
 WEL IA 24 0 0
 WEL IL 28.7 0 0
 WEL MA 88.85 0 0
 WEL NY 61.95 0 0
 WEL OH 23 0 0
 WEL 226.5 0 1
 1190.85 1 1
 18 rows selected.
 
 SQL> 
 
 End Listing

Функция Grouping может, кроме того, использоваться совместно с функцией Decode для вывода описания в вычисленных строках. Как видно из Листинга 6, функция Decode содержит вложенную функцию Grouping. Эта функция возвращает значение 0 или 1, в зависимости от того, является ли строка итогом. Если полученное значение 1, то в столбец помещается текст с описанием. Если обнаруживается строка со значением 0 по столбцу назначения, то отображается значение столбца назначения.

Листинг 6. Использование Decode и Grouping для вывода текста с описанием в итоговых строках.

SQL> select decode(grouping(fk_department), 1, ' Total Wages',
fk_department) fk_department,
decode(grouping(state), 1, ' Total Per Dept', state) 
state, sum(tool_cost)
from employee, emp_tools
where payroll_number = fk_payroll_number
group by rollup(fk_department, state)
 order by 1;
 
 FK_DEPARTMEN STATE SUM(TOOL_COST)
 ------------ ---------------- --------------
 Total Wages Total Per Dept 1190.85
 INT  ME 46.2
 INT NE 12
 INT NY 324
 INT TX 375
 INT VT 35
 INT Total Per Dept  792.2
 POL CA 18.5
 POL NC 16.7
 POL NY 20
 POL VA 116.95
 POL Total Per Dept 172.15
 WEL  IA 24
 WEL IL 28.7
 WEL MA 88.85
 WEL NY 61.95
 WEL OH 23
 WEL Total Per Dept  226.5
 
 18 rows selected.
 
 SQL>
 End listing

Статистические и регрессивные функции

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

В Таблице 1 описаны различные статистические функции, а на Листинге 7 демонстрируется их использование. Функции Листинга 7 используются для анализа набора закупленных партий очков. Особый интерес представляет функция Corr. Она используется для определения наличия взаимосвязи между числом месяца закупки и стоимостью очков в этом месяце. Определить, связана ли стоимость партии очков со временем, достаточно сложно. Вычисленный коэффициент корреляции равен 0.138 и показывает, что эта взаимосвязь очень мала.

Таблица 1. Статистические функции Oracle.

Имя функции	Шаблон	Описание
Var_pop	Var_pop(var)	Вычисляет дисперсию совокупности значений. Для заданного выражения var дисперсия совокупности var определяется как: (SUM(var*var) – SUM(var)*SUM(var) / COUNT(var))/COUNT(var)
Var_samp	Var_samp(var)	Вычисляет дисперсию выборки значений. Эта функция похожа на функцию Variance. Для заданного выражения var дисперсия выборки var определяется как: (SUM(var*var) - SUM(var)*SUM(var) / COUNT(var))/(COUNT(var)-1)
Stddev_pop	Stddev_pop(var)	Вычисляет среднее отклонение совокупности значений.
Stddev_samp	Stddev_samp(var)	Вычисляет среднее отклонение выборки значений.
Covar_pop	Covar_pop(var1, var2)	Вычисляет ковариантность совокупности пар значений. Вычисляется следующим образом: (SUM(var1 * var2) - SUM(var2) * SUM(var1) / n) / n
Covar_samp	Covar_samp(var1, var2)	Вычисляет ковариантность выборки пар значений. Вычисляется следующим образом: (SUM(var1*var2)-SUM(var1)*SUM(var2)/n)/(n-1)
Corr	Corr(var1, var2)	Вычисляет коэффициент корреляции для набора пар значений. Вычисляется следующим образом: COVAR_POP(var1, var2)/(STDDEV_POP(var1)*STDDEV_POP(var2))

Листинг 7. Вычисление различной статистики по набору закупленных партий очков.

SQL> select var_pop(sum(cost)) var_pop, var_samp(sum(cost)) var_samp,
stddev_pop(sum(cost)) stddev_pop, stddev_samp(sum(cost))
stddev_samp, covar_pop(sum(cost),
to_number(to_char(purchase_date, 'MM'))) covar_pop,
covar_samp(sum(cost), 
to_number(to_char(purchase_date, 'MM'))) covar_samp,
corr(sum(cost), 
to_number(to_char(purchase_date, 'MM'))) corr
from glasses
group by to_number(to_char(purchase_date, 'MM'));
 
 VAR_POP VAR_SAMP STDDEV_POP STDDEV_SAMP COVAR_POP COVAR_SAMP CORR
 -------- -------- ---------- ----------- --------- ---------- ---------
 4608.975 5069.872 67.889434 71.203039 33.123967 36.436364 .13866822
 
 SQL>
 
 End listing

Кроме того, к аналитическим средствам Oracle добавлена группа функций линейной регрессии. Эти средства полезны для продолжения линии тренда. Линия тренда – это, как правило, закономерность, которой придерживается набор значений. Линия тренда полезна для прогнозирования будущих значений. Этот означает, что тренд будет продолжаться и в будущем. Для продолжения линии тренда необходимо знать угол наклона и точку пересечения с осью Y. Набор линейных функций включает функции для вычисления этих значений. В Таблице 2 описаны различные линейные функции.

Таблица 2.Функции линейной регрессии Oracle.

Имя фукции	Шаблон	Описание
Regr_count	Regr_count(y, x)	Вычисляет количество непустых пар, используемых для создания линии регрессии.
Regr_avgx	Regr_avgx(y, x)	Вычисляет среднее независимой переменной линии регрессии.
Regr_avgy	Regr_avgy(y, x)	Вычисляет среднее зависимой переменной линии регрессии.
Regr_slope	Regr_slope(y, x)	Вычисляет угол наклона линии регрессии.
Regr_intercept	Regr_intercept(y, x)	Вычисляет точку пересечения линии регрессии с осью Y.
Regr_r2	Regr_r2(y, x)	Вычисляет коэффициент детерминации линии регрессии.
Regr_sxxx	Regr_sxxx(y, x)	Диагностическая статистика, используемая для анализа регрессии. Вычисляется следующим образом: REGR_COUNT(y, x) * VAR_POP(x)
Regr_syyy	Regr_syyy(y, x)	Диагностическая статистика, используемая для анализа регрессии. Вычисляется следующим образом: REGR_COUNT(y, x) * VAR_POP(y)
Regr_sxy	Regr_sxy(y, x)	Диагностическая статистика, используемая для анализа регрессии. Вычисляется следующим образом: REGR_COUNT(y, x) * COVAR_POP(y, x)

На Листинге 8 показано использование различных функций линейной регрессии. Эти функции используются для анализа набора записей о закупках очков. Анализируются пары значений, которые представляют собой стоимость партии в месяце (y) и время в месяцах (x). Особый интерес представляет вычисление Regr_slope (угла) и Regr_intercept (точки пересечения с осью Y). Они требуются для вычисления регрессии или линии тренда.

Листинг 8. Вычисление различных значений линейной регресси по набору закупленных партий очков.

SQL> select regr_count(sum(cost),
to_number(to_char(purchase_date, 'MM'))) count,
regr_avgx(sum(cost),
to_number(to_char(purchase_date, 'MM'))) avgx,
regr_avgy(sum(cost), 
to_number(to_char(purchase_date, 'MM'))) avgy,
regr_slope(sum(cost), 
to_number(to_char(purchase_date, 'MM'))) slope,
regr_intercept(sum(cost),
to_number(to_char(purchase_date, 'MM'))) regr_intercept,
regr_r2(sum(cost), 
to_number(to_char(purchase_date, 'MM'))) regr_r2,
regr_sxx(sum(cost), 
to_number(to_char(purchase_date, 'MM'))) regr_sxx,
regr_sxy(sum(cost), 
to_number(to_char(purchase_date, 'MM')) regr_sxy
from glasses
group by to_number(to_char(purchase_date, 'MM'));
 
 COUNT AVGX AVGY SLOPE REGR_INTERCEPT REGR_R2 REGR_SXX REGR_SXY
 ----- ----- ------ ----- -------------- --------- --------- ---------
 11 6.727 239.45 2.675 221.45527 .01922887 136.18182 364.36364
 
 SQL>
 End listing 

Рассмотрим пример практического использования этих функций. Предположим, что необходимо составить бюджет ежемесячных закупок очков на 2003 год. Для этого необходимо проанализировать стоимость очков за предыдущий год (Листинг 7). Коэффициент корреляции (Corr) таков, что такую закономерность можно считать трендом, который будет продолжаться и в следующем году. Предполагается, что ежемесячная стоимость в 2003 году может колебаться в пределах линии регрессии, вычисленной по данным за прошедший период.

На Листинге 9 показано Select-предложение, которое определяет желаемые в 2003 году предполагаемые данные об очках.

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

SQL> select to_char(add_months(to_date(rownum, 'mm'),12),
'MON-YYYY') Month,
intercept + (slope*(rownum+12)) "Estimate Cost"
from dictionary,
(select regr_intercept(sum(cost), 
 to_number(to_char(purchase_date, 'MM'))) 
intercept,
regr_slope(sum(cost),
to_number(to_char(purchase_date, 'MM'))) slope
from glasses
group by to_number(to_char(purchase_date, 'MM')))
where rownum <= 12;
 
 MONTH Estimate Cost
 -------- -------------
 JAN-2003 256.23765
 FEB-2003 258.91322
 MAR-2003 261.58879
 APR-2003 264.26435
 MAY-2003 266.93992
 JUN-2003 269.61549
 JUL-2003 272.29105
 AUG-2003 274.96662
 SEP-2003 277.64219
 OCT-2003 280.31776
 NOV-2003 282.99332
 DEC-2003 285.66889
 
 12 rows selected
 
 SQL>
 End listing 

Для создания этого Select-предложения были выполнены следующие шаги:

1. Создано внутреннее представление, которое вычисляет угол наклона (Regr_slope) и точку пересечения линии регрессии с осью Y (Regr_intercept). Для этого представления должна использоваться выборка данных. В нашем примере это все записи таблицы Eyeglass. Записи группируются и суммируются по месяцам закупок.
2. Внутреннее представление будет возвращать одну запись. Это представление должно быть объединено с какой-нибудь таблицей, которая возвращает 12 записей (или значений rownum). Необходимо использовать декартово произведение (без условия соединения). Этот шаг предназначен для создания значений строк с 1 по 12. Каждое значение представляет собой временной период, отмеряемый по оси X. Значения с 1 по 12 - это месяцы с января 2003 г. по декабрь 2003 г., т.е. месяцы, которые необходимо оценить. При этом предполагается, что анализируемые данные представляют собой значения с января 2002 г. по декабрь 2002 г. На Листинге 9 это представление соединяется со словарем базы данных.
3. Первое выражение оператора Select вычисляет значение оцениваемого месяца и года. Январь соответствует строке 1. Вложенные функции To_date и Add_months конвертируют номер строки в дату следующего года (соответственно, январь 2003г.).
4. Второе выражение оператора Select вычисляет ежемесячную предполагаемую стоимость очков. Для этого используется формула y + bx, где y представляет собой точку пересечения с осью Y, а b - угол наклона. X - независимая переменная, обозначающая время, вместо которой подставляется значение номера строки.