Лабораторные_БД_ЭВМ_20 (AutoRecovered). Методические указания по выполнению лабораторных работ по дисциплине (модулю)

• Название: Методические указания по выполнению лабораторных работ по дисциплине (модулю)
• Дата: 05.05.2022
• Размер: 0.75 Mb.
• Имя файла: Лабораторные_БД_ЭВМ_20 (AutoRecovered).docx
• Тип: Методические указания; #512616
• страница: 12 из 12

3. Оформление отчета

Отчет должен содержать: титульный лист, цель работы, описание пунктов выполнения лабораторной работы в соответствии с заданием, ответы на контрольные вопросы и выводы по работе.
4. Теоретические сведения

Иерархические запросы (ИЗ) в PostgreSQL реализовано на базе стандратной SQL clause WITH. Не рекурсивный WITH позволяет удешевить повторяющиеся подзапросы, разделить сложный запрос на несколько меньших, является удобным так сказать ярлыком для обращения к подзапросу и само по себе удобно в плане экономии времени при написании кода. В примере ниже удалось избежать использования подзапроса в WHERE за счет применения временой таблицы top_regions сформированой специально для этого запроса.

1. 
   WITH regional_sales AS (
   
2. 
   SELECT region, SUM(amount) AS total_sales
   
3. 
   FROM orders
   
4. 
   GROUP BY region
   
5. 
   ), top_regions AS (
   
6. 
   SELECT region
   
7. 
   FROM regional_sales
   
8. 
   WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
   
9. 
   )
   
10. 
    SELECT region,
    
11. 
    product,
    
12. 
    SUM(quantity) AS product_units,
    
13. 
    SUM(amount) AS product_sales
    
14. 
    FROM orders
    
15. 
    WHERE region IN (SELECT region FROM top_regions)
    
16. 
    GROUP BY region, product;
    

Добавление необязательного оператора RECURSEVE позволяет запросу в Postgre обращаться к своим же выходным данным. Алгоритм запроса должен состоять из двух частей, первая часть это основа, обычно возвращающий одну строку с исходной точкой иерархии или части иерархии. То есть место в иерархии откуда будет начат отсчет (например, корень), и вторя рекурсивная часть которая будет связываться с временной таблицей которую мы объявили после WITH. Объединяются первая и вторая части оператором UNION или UNION ALL. Создадим таблицу в которой будет описана структура одной компании:
CREATE TABLE KPO

(

"ID" character varying(55),

"DESCRIPTION" character varying(255),

"PARENT" character varying(55

) ;

после внесения туда данных: 

Select * from kpo

ID	DESCRIPTION	PARENT
== =====	================================	=======
KPO	KARACHAGANAK PETROLEUM OPERATING	{null}
AKSAY	AKSAY	KPO
URALSK	KPO	KPO
LONDON	LONDON	KPO
KPC	KPC	AKSAY
U2	UNIT-2	AKSAY
U3	UNIT-3	AKSAY
PROD	PRODACTION	KPC
MAINT	MAINTENANCE	AKSAY
CMMS	CMMS TEAM	MAINT

Теперь сам рекурсивный запрос:

1. 
   WITH RECURSIVE temp1 ( "ID","PARENT","DESCRIPTION",PATH, LEVEL ) AS (
   
2. 
   SELECT T1."ID",T1."PARENT", T1."DESCRIPTION", CAST (T1."ID" AS VARCHAR (50)) asPATH, 1
   
3. 
       FROM KPO T1 WHERE T1."PARENT" IS NULL
   
4. 
   union
   
5. 
   select T2."ID", T2."PARENT", T2."DESCRIPTION", CAST ( temp1.PATH ||'->'|| T2."ID" ASVARCHAR(50)) ,LEVEL + 1
   
6. 
        FROM KPO T2 INNER JOIN temp1 ON( temp1."ID"= T2."PARENT")      )
   
7. 
   select * from temp1 ORDER BY PATH LIMIT 100
   

Первая часть (строки 2-3) возвращает во временную таблицу первую строку в данном случае корневую запись нашей структуры, от которой будет начинаться отсчет в нашей иерархии. Вторая часть (строки 4-5) добавляет в эту же временную таблицу записи связанные с уже содержащейся в temp1 строкой через JOIN (ID = PARENT) и так до конца пока все листья нашего ROOTa не окажутся в temp1.

Так же в данном примере была сымитирована Ораколавская функция sys_connect_by_path.

«ID»	«PARENT»	«DESCRIPTION»	«path»	«level»
KPO		KARACHAGANAK PETROLEUM OPERATING	KPO	1
AKSAY	KPO	AKSAY	KPO->AKSAY	2
KPC	AKSAY	KPC	KPO->AKSAY->KPC	3
PROD	KPC	PRODAUCTION	KPO->AKSAY->KPC->PROD	4
MAINT	AKSAY	MAINTENANCE	KPO->AKSAY->MAINT	3
CMMS	MAINT	CMMS TEAM	KPO->AKSAY->MAINT->CMMS	4
U2	AKSAY	UNIT-2	KPO->AKSAY->U2	3
U3	AKSAY	UNIT-3	KPO->AKSAY->U3	3
LONDON	KPO	LONDON	KPO->LONDON	2
URALSK	KPO	URALSK	KPO->URALSK	2

В Postgre нет встроенной проверки на зацикливание, поэтому если данные получили от тех, кто занимался непосредственно созданием структуры в Excel, то необходимо проверить эту структуру на целостность. Иногда достаточно использовать UNION вместо UNION ALL, но это только иногда. Если в первой части задали отправную точку в иерархии, и если даже где-то в иерархии есть обрывы в принципе запустив вышеупомянутый квери ошибки не будет, просто строки «отщипенцы» будут проигнорированы. Но нам же надо знать где ошибка, и реализовать это можно внедрив дополнительную проверку перед выполнением UNION.

1. 
   WITH RECURSIVE temp1 ( "ID","PARENT","DESCRIPTION",PATH, LEVEL, cycle ) AS (
   
2. 
   SELECT T1."ID",T1."PARENT", T1."DESCRIPTION", cast (array[T1."ID"] as varchar(100)[]) , 1 , FALSE
   
3. 
       FROM KPO T1
   
4. 
   union all
   
5. 
   select T2."ID", T2."PARENT", T2."DESCRIPTION", cast(temp1.PATH || T2."ID" asvarchar(100) []) ,LEVEL + 1 ,
   
6. 
       T2."ID" = any (temp1.PATH)
   
7. 
        FROM KPO T2 INNER JOIN temp1 ON( temp1."ID"= T2."PARENT") AND NOT CYCLE     )
   
8. 
    
   
9. 
   select * from temp1 WHERE CYCLE IS TRUE LIMIT 100;
   

Здесь создается такое же поле Path но уже все «предшествующие родители» организованны в массиве, что дает сравнивать каждый новый “ID” на дубликат, и если в массиве уже есть такая запись тогда во временную таблицу строка заносится с флагом и в следующий проход уже не используют эту строку для поиска потомков, благодаря этому избегается зацикливание (union all… WHERE … AND NOT CYCLE). 
Более подробная информация: Рекурсивные запросы в PostgreSQL (WITH RECURSIVE) / Хабрахабр, Запросы WITH (Общие табличные выражения) | PostgreSQL, Рекурсивные (Иерархические) запросы в PostgreSQL / Хабрахабр, Nested Sets + PostgreSQL TRIGGER / Хабрахабр, http://www.ibase.ru/treedb/
5. Оборудование

персональный компьютер с установленной операционной системой Windows XP/7/8, браузер (Например, InternetExplorer, GoogleChrome, Opera), СУБД PostgreSQL.
6. Задание на работу

1. 
   Опишите, для чего в вашей предметной области могла бы понадобиться древовидная структура.
   
2. 
   Создайте древовидную структуру (таблицу, содержащую внешний ключ, ссылающийся на эту же таблицу). Наполните ее данными. Создайте рекурсивный вопрос, выводящий данные в соответствии с их иерархией. Проанализируйте зависимость времени выполнения запроса от количества записей в таблице (определите для вашего компьютера, при каком количестве записей запрос будет выполняться 0,1 секунду, 1 секунду и 10 секунд).
   
3. 
   Создайте структуру для хранения древовидных данных типа Nested Sets и все необходимые триггеры. Покажите на примерах, как работает эта структура.
   
4. 
   Проанализируйте зависимость времени добавления новых данных от числа строк в таблице (определите для вашего компьютера, при каком количестве записей добавление новых данных будет выполняться 0.1 секунду, 1 секунду и 10 секунд).
   

7. Контрольные вопросы

1. Как представить дерево данных в Postgres?

2. Как эффективно получить произвольный узел и всех его потомков (и потомков потомков)?

3. Как выглядит общая схема рекурсивного запроса?
Используемая литература
Основная литература

1. 
   Крис Фиайли SQL [Электронный ресурс] / Фиайли Крис; пер. А. В. Хаванов. – Электрон. текстовые данные. – Саратов: Профобразование, 2017. – 452 c. – 978-5-4488-0103-7. – Режим доступа: http://www.iprbookshop.ru/63823.html, по паролю.
   
2. 
   Полякова Л. Н. Основы SQL [Электронный ресурс] / Л. Н. Полякова. — Электрон. текстовые данные. – М.: Интернет-Университет Информационных Технологий (ИНТУИТ), 2016. – 273 c. – 978-5-94774-649-5. – Режим доступа: http://www.iprbookshop.ru/52210.html, по паролю.
   
3. 
   Советов Б.Я. Базы данных. Учебник: учебник для вузов / Б.Я.Советов, В.В. Цехановский, В.Д. Чертовской. – Юрайт: Профессиональное образование, 2017. – 463с.
   
4. 
   Смирнов Олег Игоревич. Базы данных: модели и проектирование: учеб. пособие / О. И. Смирнов; ТулГУ.– Тула: Изд-во ТулГУ, 2007. – 106 с.
   

Дополнительная литература

1. 
   Автоматизированные базы данных и банки знаний: Учеб. пособие для вузов / В.Ю. Анцев, А.Н. Иноземцев, Д.И. Троицкий; ТулГУ. – Тула, 2002. – 96с.
   
2. 
   Малыхина М.П. Базы данных: основы, проектирование, использование: учеб. пособие для вузов / М. П. Малыхина. – 2-е изд. – СПб: БХВ-Петербург, 2006. – 517 с.
   
3. 
   Базы данных: проектирование и использование: учебник для вузов / С.М. Диго. – М.: Финансы и статистика, 2005. – 592с.:
   
4. 
   Базы данных / С.В. Глушаков, Д.В. Ломотько. – М.: АСТ, 2002. – 504с.
   

Ресурсы информационно-телекоммуникационной сети «Интернет»

1. 
   Т.С. Карпова. Базы данных: модели, разработка, реализация (Электронный ресурс) – Режим доступа: http://www.intuit.ru/studies/courses/1001/297/info
   
2. 
   Форум ЦИТ. Базы данных (Электронный ресурс) – Режим доступа: http://citforum.ru/database/
   
3. 
   Научная Электронная Библиотека eLibrary – библиотека электронной периодики, режим доступа: http://elibrary.ru/ , по паролю.