Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 46: формирование и анализ иерархических структур
Единственная особенность заключается в последовательности размещения идентификаторов: во всех ранее рассмотренных вариантах корневая вершина находилась справа (например, для вершины 14 последовательность была 14,6,2,1), а здесь корневая вершина будет находиться слева (т.е. получится 1,2,6,14).
На этом решение данной задачи завершено.
Задание 7.1.1.TSK.A: создать функцию, возвращающую список идентификаторов всех дочерних вершин заданной вершины (например, идентификаторов всех подстраниц страницы «Читателям») на глубину, не более
заданной.
Задание 7.1.1.TSK.B: написать запрос для показа всего поддерева заданной вершины дерева, включая саму родительскую вершину (например, всех подстраниц страницы «Читателям», включая саму эту страницу), в котором с каждого уровня иерархии в выборку попадает не более одной вершины.
Задание 7.1.1.TSK.C: написать функцию, возвращающую список идентификаторов вершин на пути от корня дерева к заданной вершине (например, идентификаторов всех вершин на пути от страницы «Главная» к странице «Архивная»).
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 520/545
Пример 47: формирование и анализ связанных структур
Сохраним в таблице connections следующий набор данных.
cn_from |
cn_to |
cn_cost |
cn_bidir |
1 |
5 |
10 |
Y |
1 |
7 |
20 |
N |
7 |
1 |
25 |
N |
7 |
2 |
15 |
Y |
2 |
6 |
50 |
N |
6 |
8 |
40 |
Y |
8 |
4 |
30 |
N |
4 |
8 |
35 |
N |
8 |
9 |
15 |
Y |
9 |
1 |
20 |
N |
7 |
3 |
5 |
N |
3 |
6 |
5 |
N |
Теперь, когда все данные подготовлены, мы можем переходить к задачам.
Задача 7.1.2.a{492}: доработать модель базы данных таким образом, чтобы для прямых маршрутов (без пересадок), цена перемещения по которым «туда» и «обратно» одинакова, в запросе на поиск такого маршрута можно было произвольно менять местами точки отправки и назначения.
Задача 7.1.2.b{493}: написать хранимую процедуру, проверяющую существование маршрута (с возможными пересадками) между двумя указанными городами, и вычисляющую стоимость отправки книги по такому маршруту (при его наличии).
Ожидаемый результат 7.1.2.a.
Запрос вида
1SELECT *
2FROM {источник данных}
3WHERE {откуда} = 5
4AND{куда} = 1
должен возвращать такой результат (обратите внимание: в представленных выше
cn_from |
cn_to |
cn_cost |
cn_bidir |
5 |
1 |
10 |
Y |
данные нет маршрута из города 5 в город 1, есть только из 1 в 5, но этот маршрут
— двунаправленный):
Например, для городов с идентификаторами 1 и 6 хранимая процедура должна возвратить такие данные.
Ожидаемый результат 7.1.2.b.
cn_from |
cn_to |
cn_cost |
cn_bidir |
cn_steps |
cn_route |
1 |
6 |
31 |
N |
3 |
1,7,3,6 |
1 |
6 |
85 |
N |
3 |
1,7,2,6 |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 522/545
Пример 47: формирование и анализ связанных структур
Решение 7.1.2.a{491}.
Для решения этой задачи нам необходимо обеспечить такое поведение СУБД, чтобы для двунаправленных маршрутов при указании условия поиска как cn_from=A ADN cn_to=B в выборку попадали также и маршруты, для которых
выполняется условие cn_from=B AND cn_to=A. Проще всего такого эффекта можно добиться с использованием представлений.
В строке 8 представленного ниже кода для MySQL можно было не писать ключевое слово DISTINCT (т.к. по умолчанию (без ключевого слова ALL) оператор UNION работает в DISTINCT-РЄЖИМЄ), но оно там есть для наглядности, чтобы подчеркнуть необходимость устранения дублирующихся записей.
MySQL I Решение 7.1.2.a |
1 |
CREATE |
REPLACE VIEW 'connections bidir' |
2 |
AS |
|
3 |
SELECT |
'cn from', |
4 |
|
'cn to', |
5 |
|
'cn cost' |
6 |
|
'cn bidir' |
7 |
FROM |
'connections' |
8UNION DISTINCT
9SELECT 'cn to',
10'cn from' ,
11'cn cost'
12'cn bidir'
13 |
FROM |
'connections' |
14 |
WHERE |
'cn_bidir' = 'Y' |
На примере решения для MySQL рассмотрим подробно, как работает такое представление. Если выбрать из него все данные, получится следующая картина. Серым фоном отмечены строки, появившиеся в результате выполнения UNION-
части запроса: для всех двунаправленных маршрутов добавились записи с инвертированными пунктами отправки и назначения.
cn_from |
cn_to |
cn_cost |
cn_bidir |
1 |
5 |
10 |
Y |
1 |
7 |
20 |
N |
2 |
6 |
50 |
N |
3 |
6 |
6 |
N |
4 |
8 |
35 |
N |
6 |
8 |
40 |
Y |
7 |
1 |
25 |
N |
7 |
2 |
15 |
Y |
7 |
3 |
5 |
N |
8 |
4 |
30 |
N |
8 |
9 |
15 |
Y |
9 |
1 |
20 |
N |
5 |
1 |
10 |
Y |
8 |
6 |
40 |
Y |
2 |
7 |
15 |
Y |
9 |
8 |
15 |
Y |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 523/545
Пример 47: формирование и анализ связанных структур
Теперь выполнение запроса
MySQL Решение 7.1.2.a (проверка работоспособности)
1 SELECT *
2 FROM 'connections bidir'
3WHERE 'cn from' = 5
4AND 'cn to' = 1
вернёт корректный ожидаемый результат.
cn_from |
cn_to |
cn_cost |
cn_bidir |
5 |
1 |
10 |
Y |
В MS SQL Server и Oracle синтаксис оператора UNOIN не допускает явного указания слова DISTINCT (строка 8 двух показанных ниже запросов), но это — не проблема, т.к. по умолчанию (без ключевого слова ALL) оператор UNION работает в DISTINCT-режиме.
MS SQL |
|
Решение 7.1.2.a |
|
|
||
|
CREATE VIEW [connections_bidir] |
|||||
2 |
AS |
|
|
|
|
|
3 |
|
SELECT [cn_from], |
|
|||
4 |
|
|
|
[cn_to], |
|
|
5 |
|
|
|
[cn_cost], |
|
|
6 |
|
|
|
[cn_bidir] |
|
|
|
|
|
FROM |
[connections] |
|
|
8 |
|
UNION |
|
|
|
|
9 |
|
SELECT [cn_to], |
|
|||
10 |
|
|
|
[cn_from], |
|
|
11 |
|
|
|
[cn_cost], |
|
|
12 |
|
|
|
[cn_bidir] |
|
|
13 |
|
FROM |
[connections] |
|
||
14 |
|
WHERE |
[cn_bidir] = 'Y' |
|||
|
|
|
|
|||
Oracle |
|
Решение 7.1.2.a |
|
|
||
1 |
CREATE OR REPLACE VIEW |
"connections_bidir" |
||||
2 |
AS |
|
|
|
|
|
3 |
|
SELECT "cn_from", |
|
|||
4 |
|
|
|
"cn_to" |
|
|
5 |
|
|
|
"cn_cost", |
|
|
6 |
|
|
|
"cn_bidir" |
|
|
|
|
|
FROM |
"connections" |
|
|
8 |
|
UNION |
|
|
|
|
9 |
|
SELECT "cn_to" |
|
|||
10 |
|
|
|
"cn_from", |
|
|
11 |
|
|
|
"cn_cost", |
|
|
12 |
|
|
|
"cn_bidir" |
|
|
13 |
|
FROM |
"connections" |
|
||
14 |
|
WHERE |
"cn _bidir"= |
Y' |
||
|
|
|
|
|
|
|
На этом решение данной задачи завершено.
Решение 7.1.2.b{491}.
Решение данной задачи стоит начать с подчёркивания того факта, что реляционные СУБД не оптимизированы для хранения графовых структур и выполнения над ними подобных операций. Потому представленные ниже решения могут показаться излишне громоздкими (с использованием классических языков программирования можно создать гораздо более компактный и оптимальный код).
Традиционно начнём с MySQL и рассмотрим два варианта решения, первый из которых максимально использует возможности СУБД, а второй эмулирует классическое алгоритмическое решение.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 524/545