Как писать эффективные SQL запросы Графеева Н Г

Как писать эффективные SQL запросы Графеева Н. Г. 2014

Классификация запросов 2 типа запросов • <Короткие запросы> ( OLTP –on-line transation processing). Для выполнения таких запросов, как правило, обрабатывается лишь часть содержимого таблиц. Результат также невелик. • <Длинные запросы> (OLAP – on-line analitical processing). Для получения результата необходимо обработать все или значительную часть строк таблиц.

Рекомендации для написания эффективных запросов • Избегать многократных просмотров данных. • Активнее использовать индексы для коротких запросов. • Активнее использовать теоретико-множественные операции (произведение, объединение - UNION, минус - MINUS, пересечение - INTERSECTION) * • Использовать операции группировки как можно раньше *. • При необходимости выполнять операции соединения – выполнять их в правильной последовательности (минимизируя количество соединяемых записей) *. • Использовать избыточные критерии селекции для сокращения количества выбираемых записей *. • Примечание. * - особенно актуально для длинных запросов.

Упражнение 1 (использование операции MINUS) • Штаты, не заказывающие товар или (SQL). Исходные данные: State(id, name) Customer(id, name, state_id) Order(id, order_date, customer_id) Order_item(id, order_id, product_id, quantity) Product_info(id, name)

Вариант 1. Козмирчук Антон. SELECT NAME FROM STATE MINUS select Name from state where id in (select state_id from Customer where id in (select customer_id from Order where id in (select order_id from Order_item where product_id in (SELECT ID FROM Product_info WHERE NAME = ‘Jacket’ OR NAME = ‘Blouse’) ) Вариант 2. Брусинец Кирилл SELECT NAME FROM STATE MINUS SELECT STATE. NAME FROM STATE, CUSTOMER, ORDER_ITEM, (SELECT ID FROM Product_info WHERE NAME = ‘Jacket’ OR NAME = ‘Blouse’) PRODUCT_INFO WHERE STATE. ID=CUSTOMER. STATE_ID AND CUSTOMER. ID = ORDER. CUSTOMER_ID AND ORDER. ID=ORDER_ITEM. ORDER_ID AND ORDER_ITEM. PRODUCT_ID=PRODUCT_INFO. ID

Упражнение 2 (произведение, MINUS) • Сформировать все подарочные наборы из трех различных видов продукции (SQL). • (исходные данные - product_info(id, name))

Вариант 1. Малькевич SELECT SET 1. NAME, SET 2. NAME, SET 3. NAME FROM PRODUCT_INFO SET 1, PRODUCT_INFO SET 2, PRODUCT_INFO SET 3 WHERE (SET 1. ID<>SET 2. ID ) AND (SET 1. ID<>SET 3. ID) AND (SET 2. ID<>SET 3. ID) Вариант 2. Копытов Дмитрий SELECT SET 1. NAME, SET 2. NAME, SET 3. NAME FROM PRODUCT_INFO SET 1, PRODUCT_INFO SET 2, PRODUCT_INFO SET 3 Minus Select Name, Name From Product_Info Minus Select Set 1. Name, Set 2. Name From FROM PRODUCT_INFO SET 1, PRODUCT_INFO SET 2 Minus Select Set 2. Name, Set 1. Name, Set 2. Name From FROM PRODUCT_INFO SET 1, PRODUCT_INFO SET 2

Упражнение 3 • Как заказы продукции (Bag, Blouse, Jacket) распределяются по дням недели (понедельник, вторник и т. п. ) (SQL). Исходные данные: Order(id, order_date, customer_id) Order_item(id, order_id, product_id, quantity) Product_info(id, name) • Примечание: день недели по дате можно узнать с помощью выражения to_char(sysdate, 'dy'). Bag Monday Tuesday Wednesday Thursday Friday Saturday Sunday Blouse Jacket

Вариант 1, Волжина Select to_char(order_date, ‘dy’) Day, sum(decode (product_info. name, ‘Jacket’, quantity, 0)) Jacket, sum(decode (product_info. name, ‘Blouse’, quantity, 0)) Blouse, sum(decode (product_info. name, ‘Bag’, quantity, 0)) Bag from order, order_item, product_info Where order. id = order_item. order_id and order_item. product_id = product_info. id Group by to_char(order_date, ‘dy’)

Упражнение 4 • • В таблице PARAM(Id, Name) содержатся сведения о разного рода параметрах (электроэнергия, мощность и т. п. ), в таблице – STATION (Id, Name) – названия электростанций, в таблице PARAM_VALUE(Id_Station, Id_Param, Period, Value) – сведения о том, какая станция, когда и сколько сгенерировала электроэнергии, мощности и т. п. Как построить запрос (SQL), в котором будет отображено сколько электроэнергии и мощности сгенерировала каждая электростанция в каждом месяце 2013 года? январь Лесогорская ГЭС (электроэнергия) Лесогорская ГЭС (мощность) Выборгская ГЭС (электроэнергия) Выборгская ГЭС (мощность) февраль март апрель май … … …

Вариант 1. Волобой, Шавкунова, Копытов Select station. name || ‘(’|| temp. name||’)’ name, temp. January, temp. February, temp. March, … from Station, (Select station. id id, param. name, sum(decode(extract(month from period), 1, value, 0)) January , sum(decode(extract(month from period), 2, value, 0)) February, sum(decode(extract(month from period), 3, value, 0)) March … from param_value, (select id, name from param where name = ‘Электроэнергия’ or name = ‘Мощность’) param where extract(year from period ) = ‘ 2013’ and param_id = param. id group by station_id, param. name) temp where temp. id=station. id order by name

Домашнее задание 7 • Загрузите данные о потреблении электроэнергии (XML-файл electric power. xml 2009 -2010 прилагается ). Проанализируйте среднестатистическое потребление электроэнергии по временам года и дням недели. Результат выдайте в виде таблицы и соответствующих графиков (для времен года). Примерный вид таблицы: воскресенье понедельник вторник среда четверг пятница суббота зима весна лето осень • • Ссылку на приложение, логин и пароль для входа отправить по адресу: N. Grafeeva@spbu. ru Тема - DB_Applcation_2014_job 7