4.3.2.1.3. Вычисления и групповые операции в запросах

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

Вычисляемые поля. В инструкции SELECTв списке отби­раемых полей добавляется выражение, по которому вычисля­ется новое поле, и посредством ключевого слова ASопределя­ется его имя в формируемом наборе данных. На рис. 4.18 при­веден запрос, формирующий ведомость начислений сотрудникам с вычисляемым полем «ИТОГО».

Рис. 4.17. Пример запроса на объединение

Групповые операции.В процессе отбора и обработки дан­ных важное значение имеют группирование данных по значе­ниям какого-либо поля и осуществление тех или иных опера­ций над сгруппированными записями. Групповые операции осу­ществляются на основе SQL-предложения GROUP BYв сочетании состатистическими функциями SQL.В большин­стве диалектов языка SQLв состав инструкции SELECT допус­кается включение статистических функций SQL,которые осу­ществляют те или иныегрупповые вычислительные операции над отбираемыми записями.

К числу статистических функций SQLотносятся:

SUM(выражение) — вычисляет сумму набора значений;

Рис. 4.18. Пример запроса на выборку с вычисляемым полем

AVG(выражение) — вычисляет среднее арифметическое на­бора чисел;

Min(выражение) — вычисляет минимальное значение из на­бора значений;

Мах(выражение) — вычисляет максимальное значение из набора значений;

StDev(выражение) — вычисляет среднеквадратичное от­клонение набора значений;

Count(выражение) — вычисляет количество записей, содер­жащихся в наборе;

Var(выражение) — вычисляет дисперсию по набору значе­ний.

К числу функций, используемых в групповых операциях, относятся также функции First(выражение) и Last(выpaжeниe), вычисляющие (возвращающие), соответственно, первое и пос­леднее значения поля в наборе данных. В выражениях в каче­стве аргумента допускается использование имен полей таблиц.

Собственно сами групповые вычисления задаются посред­ством включения в SQL-инструкцию SELECTвычисляемого поля на основе выражения со статистическими функциями, вы­полняемыми над наборами данных, формируемыми предложением GROUP BY.

Для примера на рис. 4.19 приведен запрос, формирующий итоговые данные по общей сумме премиальных каждого из пре­мированных сотрудников. Группирование данных производит­ся по полю «ФИО», т. е. все записи с одинаковыми значениями поля «ФИО», объединяются в одну и дополнительно формиру­ется вычисляемое поле «ИТОГО», рассчитываемое как сумма сгруппированных в одну записей по произведению полей «Ок­лад» и «Премия».

Рис. 4.19. Пример запроса с групповой функцией Sum

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

Запросы по поиску повторовприменяются для анализа на­личия повторяющихся групп значений по определенному полю и их количественных (статистических) данных. В качестве при­мера на рис. 4.20 приведен запрос по поиску повторов в табли­це сотрудники по полю «Должность», формирующий в итоге штатную расстановку по заполненным должностям.

Рис. 4.20. Пример запроса по поиску повторяющихся значений

Более сложные статистические задачи решают перекре­стные запросы.Название «перекрестный» отражает принцип формирования и представления результатов таких запросов.

На рис. 4.21 иллюстрируется принцип построения итого­вой (сводной) таблицы перекрестного запроса. В исходной (ба­зовой) таблице для перекрестного запроса выбираются два поля. По повторяющимся значениям одного поля формируются на­звания заголовков строк итоговой (сводной) таблицы — «боковик» сводной таблицы. По повторяющимся значениям другого поля образуются названия столбцов итоговой таблицы — «шап­ка» сводной таблицы. В ячейках сводной таблицы отражаются результаты статистических функций по группам данных в ка­ких-либо полях исходной таблицы.

Рис. 4.21. Принцип формирования результатов перекрестного запроса

Для примера на рис. 4.22 представлен запрос по формиро­ванию статистических данных о количестве исполненных раз­личными сотрудниками документов с разнесением по видам документов. Полем для формирования боковика определено поле «Фамилия», полем для формирования шапки определено поле «Вид документа». На рисунке представлен также вариант SQL-инструкции, реализующей данный запрос.*

* В диалекте SQL MS Access.

Рис. 4.22. Пример перекрестного запроса