Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Oracle-Олимпиада 2008. Задание 7. Построение дерева объектов компании  [new]
Валерий Юринский
Member

Откуда: Москва, "ФОРС Дистрибуция"
Сообщений: 1233
Oracle-Олимпиада 2008. Задание 7. Построение дерева объектов компании (6 баллов)

Используя таблицы REGIONS, COUNTRIES, LOCATIONS, DEPARTMENTS, построить (показать) иерархию объектов "Регион – Страна – Местоположение – Подразделение" для региона name = ' Americas '.

Иерархия должна быть построена (показана) одной командой SELECT.

В результате вывести:
- номер уровня, на котором находится в иерархии данный объект (LEVEL),
- имя объекта, дополненное слева (LEVEL -1)*3 пробелами.

Объекты одного уровня должны быть отсортированы по именам.

Пример результата:
 Уровень     Единица 
------------------------------------------------------------- 
1	Europe 
2	   Belgium 
2	   Denmark 
2	   France 
2	   Germany 
3	      Munich 
4	         Public Relations 
2	   Italy 
3	      Roma 
3	      Venice 
2	   Netherlands 
. . .	      . . .
16 янв 09, 12:25    [6695813]     Ответить | Цитировать Сообщить модератору
 Re: Oracle-Олимпиада 2008. Задание 7. Построение дерева объектов компании  [new]
Валерий Юринский
Member

Откуда: Москва, "ФОРС Дистрибуция"
Сообщений: 1233
Описание и ER-диаграмму стандартной демо-схемы HR можно посмотреть здесь:
Oracle-Олимпиада 2008. Схема HR: описание, скрипты создания и заполнения таблиц
16 янв 09, 12:26    [6695817]     Ответить | Цитировать Сообщить модератору
 Re: Oracle-Олимпиада 2008. Задание 7. Построение дерева объектов компании  [new]
Валерий Юринский
Member

Откуда: Москва, "ФОРС Дистрибуция"
Сообщений: 1233
Общие наблюдения

Таблицы REGIONS, COUNTRIES, LOCATIONS, DEPARTMENTS по отдельности не содержат все сведения об общей иерархической структуре. Эта информация есть только в совокупности всех четырех таблиц. Поэтому нужно сформулировать на их основе обобщающий запрос (inline view или WITH), по которому затем строить требуемое дерево объектов компании, указав корневой узел и порядок сортировки узлов-братьев.

При написании обобщающего запроса участники упорно предпочитали использовать для объединения оператор UNION вместо достаточного в данном случае оператора UNION ALL.
16 янв 09, 12:27    [6695832]     Ответить | Цитировать Сообщить модератору
 Re: Oracle-Олимпиада 2008. Задание 7. Построение дерева объектов компании  [new]
Валерий Юринский
Member

Откуда: Москва, "ФОРС Дистрибуция"
Сообщений: 1233
Далее приведены решения участников Олимпиады 2008
16 янв 09, 12:27    [6695839]     Ответить | Цитировать Сообщить модератору
 Re: Oracle-Олимпиада 2008. Задание 7. Построение дерева объектов компании  [new]
Валерий Юринский
Member

Откуда: Москва, "ФОРС Дистрибуция"
Сообщений: 1233
Решение 1. WITH, CAST, UNION

WITH qq AS (
SELECT CAST(country_id AS VARCHAR2(30)) AS stp
     , CAST(region_id AS VARCHAR2(30)) AS enp
     , country_name name 
FROM countries
UNION 
SELECT CAST (region_id AS VARCHAR2(30)), null, region_name 
FROM regions
UNION 
SELECT CAST(location_id AS VARCHAR2(30))
     , CAST(country_id AS VARCHAR2(30))
     , city 
FROM locations
UNION
SELECT CAST(department_id AS VARCHAR2(30))
     , CAST(location_id AS VARCHAR2(30))
     , department_name 
FROM departments
)
SELECT LEVEL AS "Уровень"
     , LPAD(' ', (level - 1)*3 , ' ') || name AS "Единица"
FROM qq
START WITH LOWER(name) = 'americas' AND enp IS NULL
CONNECT BY PRIOR stp = enp
ORDER SIBLINGS BY name;
16 янв 09, 12:29    [6695861]     Ответить | Цитировать Сообщить модератору
 Re: Oracle-Олимпиада 2008. Задание 7. Построение дерева объектов компании  [new]
Валерий Юринский
Member

Откуда: Москва, "ФОРС Дистрибуция"
Сообщений: 1233
Решение 2. Inline view, CAST, UNION

Практически то же самое, что Решение 1.
SELECT LEVEL, LPAD(' ',(LEVEL-1)*3,' ')||name 
FROM (SELECT CAST(region_id AS VARCHAR2(100)) AS id
           , NULL AS parent
           , region_name as name, 1 as type 
      FROM regions WHERE region_name='Americas'
      UNION 
      SELECT CAST(country_id AS VARCHAR2(100)) AS id
           , CAST(region_id AS VARCHAR2(100)) AS parent
           , country_name AS name, 2 AS type 
      FROM countries 
      UNION 
      SELECT CAST(location_id AS VARCHAR2(100)) AS id
           , CAST(country_id AS VARCHAR2(100)) AS parent
           , city AS name, 3 AS type 
      FROM locations 
      UNION 
      SELECT CAST(department_id AS VARCHAR2(100)) AS id
           , CAST(location_id AS VARCHAR2(100)) AS parent
           , department_name AS name, 4 AS type 
      FROM departments) t1
CONNECT BY PRIOR id = parent 
START WITH parent IS NULL 
ORDER SIBLINGS BY name;
16 янв 09, 12:30    [6695863]     Ответить | Цитировать Сообщить модератору
 Re: Oracle-Олимпиада 2008. Задание 7. Построение дерева объектов компании  [new]
Валерий Юринский
Member

Откуда: Москва, "ФОРС Дистрибуция"
Сообщений: 1233
Решение 3. Inline view, TO_CHAR, UNION

SELECT LEVEL
     , LPAD(name, LENGTH(name)+3*(LEVEL-1),' ') 
FROM (SELECT region_name AS name, TO_CHAR(region_id) AS id, '0' AS id_id 
      FROM regions 
      WHERE region_name = 'Americas'
      UNION
      SELECT country_name, TO_CHAR(country_id), TO_CHAR(region_id)
      FROM countries
      UNION
      SELECT city, TO_CHAR(location_id), TO_CHAR(country_id) 
      FROM locations
      UNION
      SELECT department_name, TO_CHAR(department_id), TO_CHAR(location_id) 
      FROM departments)
CONNECT BY PRIOR id = id_id
START WITH id_id = '0'
ORDER SIBLINGS BY name;
16 янв 09, 12:32    [6695883]     Ответить | Цитировать Сообщить модератору
 Re: Oracle-Олимпиада 2008. Задание 7. Построение дерева объектов компании  [new]
Валерий Юринский
Member

Откуда: Москва, "ФОРС Дистрибуция"
Сообщений: 1233
Решение 4. WITH, TO_CHAR, JOINS, UNION

Зачем-то выполняются соединения таблиц (joins), результат которых никак не используется.

WITH MY AS (
SELECT c.country_id AS id, c.country_name AS name, TO_CHAR(r.region_id) AS f_id
FROM regions r, countries c
WHERE r.region_id = c.region_id
UNION
SELECT TO_CHAR(region_id), region_name, ''
FROM regions
UNION
SELECT TO_CHAR(location_id), city, TO_CHAR(c.country_id)
FROM locations l, countries c
WHERE l.country_id = c.country_id
UNION
SELECT TO_CHAR(department_id), department_name, TO_CHAR(l.location_id)
FROM departments d, locations l
WHERE l.location_id = d.location_id
)
SELECT level AS "Уровень"
     , LPAD(name, length(name)+(LEVEL -1)*3, ' ') AS "Единица"
FROM my 
CONNECT BY PRIOR id like f_id
START WITH name like 'Americas'
ORDER SIBLINGS BY name;
16 янв 09, 12:34    [6695904]     Ответить | Цитировать Сообщить модератору
 Re: Oracle-Олимпиада 2008. Задание 7. Построение дерева объектов компании  [new]
Валерий Юринский
Member

Откуда: Москва, "ФОРС Дистрибуция"
Сообщений: 1233
Решение 5. WITH, JOINS, UNION ALL

Сортировка результатов обобщающего запроса, а не узлов дерева.
Использование для построения дерева имен, а не идентификаторов.
WITH tmp AS (
SELECT * 
FROM (SELECT region_name AS name1, country_name AS name2 
      FROM regions, countries 
      WHERE regions.region_id = countries.region_id 
        AND region_name = 'Americas'
      UNION ALL
      SELECT country_name, city 
      FROM countries, locations 
      WHERE countries.country_id = locations.country_id
      UNION ALL
      SELECT city, department_name 
      FROM locations, departments 
      WHERE locations.location_id = departments.location_id)
ORDER BY name1, name2
)
SELECT 1 Уровень, 'Americas' Единица FROM dual
UNION ALL
SELECT LEVEL+1, LPAD(' ',(LEVEL)*3,' ') || name2 
FROM tmp
START WITH tmp.name1 = 'Americas'
CONNECT BY PRIOR tmp.name2 = tmp.name1;
16 янв 09, 12:36    [6695928]     Ответить | Цитировать Сообщить модератору
 Re: Oracle-Олимпиада 2008. Задание 7. Построение дерева объектов компании  [new]
Валерий Юринский
Member

Откуда: Москва, "ФОРС Дистрибуция"
Сообщений: 1233
Незасчитанное решение

Участников подвела невнимательность:
– во фразе START WITH неверно задали ("недозадали") условия выбора корневого узла,
- не указали порядок сортировки узлов-братьев (sibling nodes).
SELECT LEVEL, LPAD(' ', (LEVEL-1) * 3, ' ') || title  
FROM (SELECT region_name AS title, 'r' || region_id AS id, null AS parent 
      FROM regions
      UNION
      SELECT country_name, 'c' || country_id, 'r' || region_id 
      FROM countries
      UNION
      SELECT city, '_' || location_id, 'c' || country_id 
      FROM locations
      UNION
      SELECT department_name, 'd' || department_id, '_' || location_id 
      FROM departments)
CONNECT BY PRIOR id = parent
START WITH parent IS NULL;
16 янв 09, 12:37    [6695941]     Ответить | Цитировать Сообщить модератору
 Re: Oracle-Олимпиада 2008. Задание 7. Построение дерева объектов компании  [new]
Валерий Юринский
Member

Откуда: Москва, "ФОРС Дистрибуция"
Сообщений: 1233
Конец публикации Задания 7
16 янв 09, 12:38    [6695949]     Ответить | Цитировать Сообщить модератору
 Re: Oracle-Олимпиада 2008. Задание 7. Построение дерева объектов компании  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54376
Есть ли гарантия непересечения значений region_id country_id location_id department_id?
Есть ли у них один сиквенс на 4 таблицы?
16 янв 09, 13:16    [6696221]     Ответить | Цитировать Сообщить модератору
 Re: Oracle-Олимпиада 2008. Задание 7. Построение дерева объектов компании  [new]
Wadim S
Member

Откуда: Ukraine
Сообщений: 178
andreymx
Есть ли гарантия непересечения значений region_id country_id location_id department_id?
Есть ли у них один сиквенс на 4 таблицы?

Судя по скриптам в описании схемы, сиквенса у них два:
CREATE SEQUENCE locations_seq
START WITH 3300
INCREMENT BY 100
MAXVALUE 9900
NOCACHE
NOCYCLE;

CREATE SEQUENCE departments_seq
START WITH 280
INCREMENT BY 10
MAXVALUE 9990
NOCACHE
NOCYCLE;
регионы вообще без сиквенса вручную заполняются.
16 янв 09, 14:02    [6696589]     Ответить | Цитировать Сообщить модератору
 Re: Oracle-Олимпиада 2008. Задание 7. Построение дерева объектов компании  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54376
что-то мне кажется, что тут вообще нет правильных решений :(
кроме незасчитанного :)
16 янв 09, 14:34    [6696882]     Ответить | Цитировать Сообщить модератору
 Re: Oracle-Олимпиада 2008. Задание 7. Построение дерева объектов компании  [new]
Wadim S
Member

Откуда: Ukraine
Сообщений: 178
andreymx
что-то мне кажется, что тут вообще нет правильных решений :(
кроме незасчитанного :)

Ага, магия исходных данных и всем повезло...
16 янв 09, 14:40    [6696948]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить