С.Д. Кузнецов - Основы баз данных (1121716), страница 74
Текст из файла (страница 74)
352 Лекция 16 Средства формулировки аналитических и рекурсивных запросов В системах баз данных, специально спроектированных в расчете на аналитические приложения, проблему обычно решают за счет явного избыточного хранения агрегированных данных (т. е. результатов вызовов агрегатных функций). Конечно, для этого требуется динамическая корректировка хранимых агрегатных значений при изменении детальных данных, но для таких специализированных баз данных это не слишком обременительно, поскольку аналитические базы данных обновляются сравнительно редко. Однако далеко не каждое предприятие может позволить себе одновременно поддерживать оперативную базу данных для работы обычных приложений оперативной обработки транзакций (01.ТР), таких, как бухгалтерские, кадровые и другие приложения, и аналитическую базу данных для приложений оперативной аналитической обработки (ОЕАР).
Приходится выполнять аналитические приложения над детальными оперативными базами данных, и эти приложения обращаются к СУБД с многочисленными трудоемкими запросами с разделами ОВСОВ ВУ и вызовами агрегатных функций. Разработчики стандарта языка ЯП. старались одновременно решить две задачи: сократить число запросов, требуемых в аналитических приложениях, и добиться снижения стоимости запросов с разделом ОВСОВ ВУ, обеспечивающих требуемые суммарные данные. В этой лекции мы обсудим наиболее важные, с нашей точки зрения, конструкции языка Я.Н., облегчающие формулировку выполнение и использование результатов аналитических запросов: разделы ОВООР Ву ВО?,ВОР и ОЕООР Ву СОВЕ и новую агрегатную функцию ОВООРП~О, позволяющую правильно трактовать результаты аналитических запросов при наличии неопределенных значений. Традиционно язык БОЕ. никогда не обладал возможностью формулировки рекурсивных запросов, где под рекурсивным запросом (упрощенно говоря) мы понимаем запрос к таблице, которая сама каким-либо образом изменяется при выполнении этого запроса.
Напомню, что это заложено в базовую семантику оператора БОЕс до выполнения раздела киеве результат раздела Рвом должен быть полностью вычислен. Однако разработчикам приложений часто приходится решать задачи, для которых недостаточно традиционных средств формулировки запросов языка БОЕЛ например, нахождение маршрута движения между двумя заданными географическими точками, определения общего набора комплектующих для сбора некоторого агрегата и т.
д. Компании-производители БОЕ-ориентированных СУБД пытались удовлетворять такие потребности за счет частных решений, обладающих ограниченными рекурсивными свойствами, но до появления стандарта Я;Н.:1999 общие стандартизованные средства отсутствовали. 353 Основы баз данных Курс Следует отметить и некоторое давление на Б9(.-сообщество со стороны сообщества логических систем баз данных. На основе языка логического программирования Рго!ок был разработан язык реляционных баз данных ()ага!ок, обеспечивающий все необходимые средства для обычной работы с базами данных наряду с развитыми возможностями рекурсивных запросов. Требовался адекватный ответ со стороны разработчиков стандарта Б(.)1..
Компромиссное (не слишком красивое) решение для введения рекурсии в Б(.!). бьшо найдено на основе введения раздела иттн в выражение запроса. Только в этом разделе допускается как линейная, так и взаимная рекурсия между вводимыми порождаемыми таблицами. При этом только для линейной рекурсии обеспечиваются дополнительные возможности управления порядком вычисления рекурсивно определенной порождаемой таблицы и контроля отсутствия циклов. Следует заметить, что при чтении стандарта временами возникает впечатление, что его авторы сами не до конца еше осознали всех возможных последствий, к которым может привести использование введенных конструкций.
Я думаю, что в следующих версиях стандарта следует ожидать уточнений и/или ограничений использования названных конструкций. В связи с этим в данной лекции мы ограничиваемся общими определениями рекурсивных конструкций языка Ь0) и обсуждением простого случая рекурсивного запроса. Возможности формулирования аналитических запросов Аналитическими запросами к базе данных принято называть запросы, сводные (агрегатные) результаты которых вычисляются над детальными данными, хранящимися в таблицах базы данных.
В этом смысле любой запрос на языке В()!., результат которого основан на вычислении агрегатных функций, можно назвать аналитическим. Характерная особенность аналитических запросов состоит в том, что, как правило, они применяются к большим по объему базам данных, и выполнение таких запросов вызывает существенные накладные расходы СУБД. В этом курсе мы не будем подробно обсуждать возможности языка $! )1, предназначенные для поддержки оперативной аналитической обработки баз данных (оьдр — оп-! )пе апа!Убса! ргосезз)пя). Рассмотрим только самые основные средства, опираясь на простые примеры. Для этих примеров предположим, что таблица кмр содержит следующий набор строк (покажем содержимое только тех столбцов, которые потребуются в примерах, причем ддя простоты будем считать, что в столбце кмр плтк содержится не полная дата, а только год рождения служащего): 354 лекции 16 Средства формулировки аналитических и рекурсивных запросов ЕМР ОЕРТ ЫО ЕМР МО ЕМР ВЕЛТЕ ЕМР БАЬ 1950 16000.00 2441 2442 1960 14000.00 2443 1960 19000.00 17000.00 1950 1950 16000.00 2445 2446 14000.00 1960 1960 20000.00 18000.00 2447 2448 1950 1950 13000.00 2449 2450 1960 21000.00 2451 1960 22000.00 Представим себе, что для проведения анализа требуется узнать максимальный размер зарплаты на всем предприятии, максимальный размер зарплаты в каждом отделе и максимальный размер зарплаты сотрудников каждой возрастной категории каждого отдела.
Если пользоваться стандартными средствами языка б( )Е, обсуждавшимися ранее в предложенном курсе, то для получения этих данных потребуется три запроса: ЯЕЬЕСТ МАХ (ЕМР ЯАЬ) АЯ МАХ ЕМТ ЯАЕ РВОМ ЕМР; ЯЕсЕСТ РЕЕТ ИО, МРЛ (ЕМР БЛс) АБ МАХ СЕР БА( РВОМ ЕМР ОВООР ВУ РЕРТ МО; БЕ) ЕСТ ()ЕРТ ИО, ЕМР ВЕЛТЕ, МАХ (ЕМР ЯА( ) АБ МАХ ОЕР ВЕЛТЕ БАЛ РВОМ ЕМР ОВООР ЕУ ОЕРТ ИО, ЕМР ВОЛТЕ; При выполнении запросов будут получены следующие результирующие таблицы: 385 Основы баэ данных Курс Рэздюл оноцР Вт ноылР Эти же результаты можно получить при выполнении единственного запроса, если в его формулировке использовать специальный вид группировки Ноияр (пример 16.1): ЯВВВОт Оерт ЕО, емр ВОАте, мАх 1емР ЯАВ) АЯ мАх ЯАь РВОМ ЕМР ОНООР Вт' НОЫЛР 10ЕРТ ИО, ЕМР ВВАТЕ); Сначала покажем, как будет выглядеть результирующая таблица этого запроса, а потом приведем развернутое пояснение действия новой конструкции.
В результате выполнения запроса будет получена таблица, показанная на рис. 16.1. Как видно, в столбце мАХ Яи, первой строки* результирующей таблицы находится максимальное значение зарплаты служащих на всем предприятии. Столбцы Оерт КО и емр ВОАте в этой строке содержат неопределенное значение, поскольку значение мйх яАВ не привязано к каким-либо отделу и возрастной категории. В столбце МАХ ЯИ, следующих трех строк находятся максимальные значения зарплаты служащих отделов с номерами 1, 2 и 3 соответственно, что показывают значения ' Конечно, мы показали строки результирующей таблицы, расположенные в удобном для нас порядке только для упрощения объяснений.
В действительности, строки результируюгцей таблицы (как обычно) будут располо:кены в порядке, определяемом системой. Чтобы добиться в точности такого порядка расположения строк, как это показано на рис. 16.1, к формулировке запроса из примера )бз нужно добавить раздел окпеп вт перт но, екР Вейте. 356 Лекция 15 Средства формулировки аналитических и рекурсивных запросов столбца ВВРт Но. Столбец ВМР Видтб в этих строках содержит неопрелеленное значение, поскольку значение МЛХ ВЛЬ не привязано к какой-либо возрастной категории.
Наконец, в столбце м((х ВЛЬ в последних шести строках содержатся максимальные значения зарплаты служащих каждой возрастной категории каждого отдела, что показывают значения столбцов ВВРт но и Вмр вплтВ, которые теперь содержат соответствующий номер отдела и год рождения служащих. Рис. 16.1. Результат запроса с разделом сВСВР Ву ВСВВВР В общем случае пусть раздел группировки запроса имеет вид сносв Ву ВОВВВР (спала,, славе„..., слапе„й, где славе, (1 = 1, 2,..., и(— имя столбца таблицы-результата раздела Рном запроса.
Пусть в списке вы- бОрКИ ИСПОЛЬЗуЮтСя ВЫЗОВЫ аГрЕГатНЫХ фуНКцИй ЛССо ЛСС„..., ЛОВ, Над значениями столбцов, не входящих в список группировки, а также имена столбцов спала„спатвез,..., спатзе„, Тогда запрос выполняется следующим образом. Первая строка результата (первый набор строк результирующей таблицы) производится таким образом, как если бы в запросе вообще отСутетВОВаа раЗдЕЛ СВСВР Ву, т. Е. аГрЕГатНЫЕ фуНКцИИ ЛССо ЛСС„..., ЛСС„ вычисляются над значениями всех строк таблицы. Значением столбцов спапел спалел..., славе, в этой строке является Н((ВВ.
(1+1) -й набор строк результата формируется так, как если бы раздел группировки запроса имел вид СВО(1Р ВУ (славе„спатяе„..., слаие,) (1<1<с(. Во всех этих строках значением столбцов спатвеп,п,..., спала„ является н()ы.. Наконец, (ле1)-й набор строк результата формируется так, как если бы раздел ГРУППИРОВКИ ЗаПРОСа ИМЕЛ ВИД СВСВР ВУ (СлатЯЕн СлаВЕл..., Слаиа„(. 357 Основы Курс Агрегатная функция сяош гис Обсудим теперь один более тонкий вопрос.
Как говорилос1 12, определение столбцов Окрт но и кмР кплтк таблицы кмр появление в этих столбцах неопределенных значений. Поэтому лицы емР могло бы иметь, например, следующий вид: ЕМР оеРТ мс ЕМР МО емР вохте 2440 1950 15000.00 2441 1950 16СОО.ОО 1960 140ОО.ОО 19000.00 1960 17000.00 2451 1950 17000.00 1950 16ООО.ОО 14000.00 2445 2446 1960 1960 2447 20000.00 2449 1950 10000.СО 1960 21000.00 2450 2451 22000.00 1960 2454 13000.00 1950 1950 14000.00 19000.00 2455 моьь 24)б моек ЫЛ.1 358 Может показаться, что запросы, содержаШие раздел СКОир 5 настолько сложны, что их выполнение будет занимать чрезме шое время. Это ошушение является ложным. В действительн выполнении запросов с обычной группировкой вида аео11Р к спапе„..., спегве„, как правило, последовательно выполняется с~ строк таблицы-результата раздела еком в соответствии со зн столбца спагвен затем — в соответствии со значениями столбца сп и в заключение — сортировка в соответствии со значениями столб Во время выполнения каждой сортировки можно заодно вычи~ чения агрегатных функций.
Так что стоимость выполнения за~ держащего раздел ако17Р ву кокк17Р, лишь незначительно отл1 стоимости выполнения запроса с обычной группировкой. Лекция зб С едствв формулировки аналитических и рекурсивных запросов Тогда результат запроса из примера 16.1 имел бы следующий вид": РЕРТ ИО ЕМР ВРАТЕ 22000.00 19000.00 181ЬЬ 14000.00 181ЬЬ 19000.00 НРЬЬ 20000.00 17000.00 Н1!ЬЬ 1950 16000.00 17000.00 1950 1960 20000.00 1950 18000.00 1960 22000.00 14000.00 1950 Рнс.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
