Что такое findslide.org?

FindSlide.org - это сайт презентаций, докладов, шаблонов в формате PowerPoint.


Для правообладателей

Обратная связь

Email: Нажмите что бы посмотреть 

Яндекс.Метрика

Презентация на тему Иерархия в SQL. Способы представления иерархических данных

Содержание

Способы представления иерархических данныхродители-потомки тип hierarchyidXML
Иерархия в SQL Способы представления иерархических данныхродители-потомки тип hierarchyidXML Родители-потомкиCREATE TABLE Parent_Child (	Id INT PRIMARY KEY,	Par_id INT REFERENCES Parent_Child(Id),	Name Char(20),	…	) Найти каждому его руководителяSELECT * FROM Parent_Child C JOIN Parent_Child PON C. Par_id = P.Id Как найти всех подчиненных? Обобщенные табличные выражения (CTE) Обобщенные табличные выражения (CTE) можно представить себе как Структура CTEWITH expression_name ( column_name [,...n] ) AS( CTE_query_definition )Инструкция для обращения к ОТВ:SELECT FROM expression_name; CTE предназначены для:Создания рекурсивных запросов. Группирования по столбцу, производного от скалярного подзапроса Рекурсивное выполнение имеет следующую семантику:разбиение CTE на закрепленный и рекурсивный элементы;запуск закрепленных Структура CTEWITH expression_name ( column_name [,...n] ) AS( CTE_query_definition )Инструкция для обращения к ОТВ:SELECT FROM expression_name; Create Employees tableCREATE TABLE Employees(  empid  int Employees table - insert valuesINSERT INTO Employees VALUES(1 , NULL, 'Nancy' Employees Все дерево от корняWITH tree1 (manager, employe, employe_name, emp_salary, emp_level)AS (SELECT mgrid, Задание 1 – добавить в выборку имя менеджера Задание 2Написать функцию, возвращающую таблицу всех подчиненных сотрудников с параметром Id менеджера.Задание Функция, возвращающая таблицуCREATE FUNCTION dbo.fn_getsubtree(@empid AS INT)   RETURNS @TREE TABLE Departments Create Departments table and insert valuesCREATE TABLE Departments(  deptid  INT Оператор APPLYпозволяет вызывать возвращающую табличное значение функцию для каждой строки, возвращаемой внешним Типы оператора APPLYCROSS APPLY возвращает только строки из внешней таблицы, которые создает Задание 4.Вывести названия отделов и всех работников этих отделов  hierarchyid  Таблица Employees с полем hierarchyid CREATE TABLE Emp_hierarchy(   Id hierarchyid PRIMARY KEY   ,empid Предложение OVERОпределяет секционирование и упорядочение набора строк до применения соответствующей оконной функции. То Рассмотрим пример SELECT 	id, dept, salary from Employees Сумма нарастающим итогом SELECT 	id, dept, salary	, SUM(salary) OVER (ORDER BY id) Сумма с группировкой SELECT 	id, dept, salary	, SUM(salary) OVER (partition by dept) Сумма с группировкой SELECT 	id, dept, salary	, SUM(salary) OVER (partition by dept ROW_NUMBER() SELECT  S.*, ROW_NUMBER() OVER (ORDER BY empName) AS RowNum FROM Employees S Номер строки SELECT 	id, dept, salary	, ROW_NUMBER() OVER (ORDER BY id) AS RowNum  from Employees ROW_NUMBER() + PARTITION SELECT  S.*, ROW_NUMBER() OVER (PARTITION BY S.mgrid ORDER Номер строки с группировкой SELECT 	id, dept, salary	, ROW_NUMBER() OVER (PARTITION BY RANK ( ) / DENSE_RANK ( )Rank - возвращает ранг каждой строки RANK ( ) OVER (ORDER by smth)Распределяет строки упорядоченной секции в заданное NTILE ( N )Распределяет строки упорядоченной секции в заданное количество групп. SELECT SELECT  S.* , ROW_NUMBER() OVER  	(PARTITION BY S.mgrid ORDER BY Структура CTEWITH expression_name ( column_name [,...n] ) AS( CTE_query_definition )Инструкция для обращения к ОТВ:SELECT FROM expression_name; ROW_NUMBER()  + PARTITION Перенос данных  из Emloyees в Emp_hierarchyWITH paths(path, EmployeeID) AS (-- This Обход дереваselect s.*from Emp_hierarchy s Обход дерева с путем и уровнямиselect s.*, Id.ToString() AS Path,  Id.GetLevel() Id.GetAncestor(n int)Res Hierarchyid/*найти детей '/2/‘ */select * FROM Emp_hierarchy sWHERE s.id.GetAncestor(1)='/2/'/*найти внуков parent.GetDescendant ( child1 , child2 ) Res HierarchyidДля генерации кодов дочерних узлов GetRoothierarchyid::GetRoot ( ) insert into Emp_hierarchy(Id, empid, empname, salary)values(hierarchyid::GetRoot(), 1, ‘Anna-Maria', 10000) id.GetLevelRes smallintВозвращает целое число, представляющее глубину этого узла в дереве. child. IsDescendantOf ( parent )Res true|falseВозвращает значение true, если объект this является потомком объекта parent. node. GetReparentedValue ( oldRoot, newRoot )Возвращаемый тип данных SQL Server: hierarchyidПереносит ветку Id.ToString()преобразование из типа hierarchyid в строковый тип0x5AC0  /1/1/ Parseпреобразование из строкового типа тип в hierarchyid hierarchyid::Parse(@StringValue) /1/1/  0x5AC0 Обход поддерева Выборка всех потомковDECLARE @parent_hid HIERARCHYID; SELECT @parent_hid = id FROM Обход дерева  с суммой зарплаты по всей веткеselect s.*, Id.ToString() AS Добавить для Robert нового подчиненного Boris между Ron и DanДобавить ему двух DECLARE    @reparented_node AS HIERARCHYID, -- Код узла, который мы хотим переподчинить со всеми
Слайды презентации

Слайд 2 Способы представления иерархических данных
родители-потомки
 тип hierarchyid
XML

Способы представления иерархических данныхродители-потомки тип hierarchyidXML

Слайд 3 Родители-потомки
CREATE TABLE Parent_Child (
Id INT PRIMARY KEY,
Par_id INT

Родители-потомкиCREATE TABLE Parent_Child (	Id INT PRIMARY KEY,	Par_id INT REFERENCES Parent_Child(Id),	Name Char(20),	…	)

REFERENCES Parent_Child(Id),
Name Char(20),

)


Слайд 4 Найти каждому его руководителя
SELECT *
FROM Parent_Child C

Найти каждому его руководителяSELECT * FROM Parent_Child C JOIN Parent_Child PON C. Par_id = P.Id

JOIN Parent_Child P
ON C. Par_id = P.Id


Слайд 5 Как найти всех подчиненных?

Как найти всех подчиненных?

Слайд 6 Обобщенные табличные выражения (CTE)
Обобщенные табличные выражения (CTE) можно

Обобщенные табличные выражения (CTE) Обобщенные табличные выражения (CTE) можно представить себе

представить себе как временные результирующие наборы, определенные в области

выполнения единичных инструкций SELECT, INSERT, UPDATE, DELETE или CREATE VIEW.
CTE не сохраняются в базе данных в виде объектов, время их жизни ограничено продолжительностью запроса.
CTE могут ссылаться сами на себя, а на них один и тот же запрос может ссылаться несколько раз.

Слайд 7 Структура CTE
WITH expression_name ( column_name [,...n] )
AS
(

Структура CTEWITH expression_name ( column_name [,...n] ) AS( CTE_query_definition )Инструкция для обращения к ОТВ:SELECT FROM expression_name;

CTE_query_definition )

Инструкция для обращения к ОТВ:
SELECT
FROM expression_name;


Слайд 8 CTE предназначены для:
Создания рекурсивных запросов.
Группирования по столбцу,

CTE предназначены для:Создания рекурсивных запросов. Группирования по столбцу, производного от скалярного

производного от скалярного подзапроса выборки
Многократных ссылок на результирующую

таблицу из одной и той же инструкции.

Слайд 9 Рекурсивное выполнение имеет следующую семантику:
разбиение CTE на закрепленный

Рекурсивное выполнение имеет следующую семантику:разбиение CTE на закрепленный и рекурсивный элементы;запуск

и рекурсивный элементы;
запуск закрепленных элементов с созданием первого вызова

или базового результирующего набора (T0);
запуск рекурсивных элементов, где Ti — это вход, а Ti+1 — это выход;
повторение шага 3 до тех пор, пока не вернется пустой набор;
возвращение результирующего набора. Результирующий набор получается с помощью инструкции UNION ALL от T0 до Tn.


Слайд 10 Структура CTE
WITH expression_name ( column_name [,...n] )
AS
(

Структура CTEWITH expression_name ( column_name [,...n] ) AS( CTE_query_definition )Инструкция для обращения к ОТВ:SELECT FROM expression_name;

CTE_query_definition )

Инструкция для обращения к ОТВ:
SELECT
FROM expression_name;


Слайд 11 Create Employees table
CREATE TABLE Employees
(
empid

Create Employees tableCREATE TABLE Employees( empid int   NOT NULL

int NOT NULL
,mgrid

int NULL
,empname varchar(25) NOT NULL
,salary money NOT NULL
CONSTRAINT PK_Employees PRIMARY KEY(empid)
);








Слайд 12 Employees table - insert values
INSERT INTO Employees VALUES(1

Employees table - insert valuesINSERT INTO Employees VALUES(1 , NULL, 'Nancy'

, NULL, 'Nancy' , $10000.00);
INSERT INTO Employees VALUES(2

, 1 , 'Andrew' , $5000.00);
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00);
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00);
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00);
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00);
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00);
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00);
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00);
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00);
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00);
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00);
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00);
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00);

Слайд 13 Employees

Employees

Слайд 14 Все дерево от корня
WITH tree1 (manager, employe, employe_name,

Все дерево от корняWITH tree1 (manager, employe, employe_name, emp_salary, emp_level)AS (SELECT

emp_salary, emp_level)
AS
(SELECT mgrid, empid, empname, salary, 0 FROM

Employees
WHERE mgrid IS NULL /* закрепленный элемент

UNION ALL
SELECT mgrid, empid, empname, salary, emp_level+1 FROM Employees
JOIN tree1 ON mgrid= employe /* рекурсивный элемент

)
SELECT * from tree1
ORDER BY manager;


Слайд 15 Задание 1 – добавить в выборку имя менеджера

Задание 1 – добавить в выборку имя менеджера

Слайд 16
Задание 2
Написать функцию, возвращающую таблицу всех подчиненных сотрудников

Задание 2Написать функцию, возвращающую таблицу всех подчиненных сотрудников с параметром Id

с параметром Id менеджера.
Задание 3
Написать функцию, возвращающую сумму зарплаты

всех подчиненных сотрудников с параметром Id менеджера.


Слайд 17 Функция, возвращающая таблицу
CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT)

Функция, возвращающая таблицуCREATE FUNCTION dbo.fn_getsubtree(@empid AS INT)  RETURNS @TREE TABLE

RETURNS @TREE TABLE (
empid INT

NOT NULL
,empname VARCHAR(25) NOT NULL
,mgrid INT NULL
,lvl INT NOT NULL
)
AS
BEGIN

INSERT INTO @TREE
SELECT * FROM Employees_Subtree;
RETURN
END

Слайд 18 Departments

Departments

Слайд 19 Create Departments table and insert values
CREATE TABLE Departments
(

Create Departments table and insert valuesCREATE TABLE Departments( deptid INT NOT

deptid INT NOT NULL PRIMARY KEY

,deptname VARCHAR(25) NOT NULL
,deptmgrid INT NULL REFERENCES Employees
);
GO
INSERT INTO Departments VALUES(1, 'HR', 2);
INSERT INTO Departments VALUES(2, 'Marketing', 7);
INSERT INTO Departments VALUES(3, 'Finance', 8);
INSERT INTO Departments VALUES(4, 'R&D', 9);
INSERT INTO Departments VALUES(5, 'Training', 4);
INSERT INTO Departments VALUES(6, 'Gardening', NULL);

Слайд 20 Оператор APPLY
позволяет вызывать возвращающую табличное значение функцию для

Оператор APPLYпозволяет вызывать возвращающую табличное значение функцию для каждой строки, возвращаемой

каждой строки, возвращаемой внешним табличным выражением запроса.


SELECT Tl.*,

Tr.*
FROM Table AS Tl
CROSS APPLY function(Tl.field1) AS Tr;


Слайд 21 Типы оператора APPLY
CROSS APPLY возвращает только строки из

Типы оператора APPLYCROSS APPLY возвращает только строки из внешней таблицы, которые

внешней таблицы, которые создает результирующий набор из возвращающего табличное

значение функции.
OUTER APPLY возвращает и строки, которые формируют результирующий набор, и строки, которые этого не делают, со значениями NULL в столбцах, созданных возвращающей табличное значение функцией.

Слайд 22 Задание 4.
Вывести названия отделов и всех работников этих

Задание 4.Вывести названия отделов и всех работников этих отделов

отделов


Слайд 23  hierarchyid 

 hierarchyid 

Слайд 24 Таблица Employees с полем hierarchyid

Таблица Employees с полем hierarchyid

Слайд 25
CREATE TABLE Emp_hierarchy
(
Id hierarchyid PRIMARY

CREATE TABLE Emp_hierarchy(  Id hierarchyid PRIMARY KEY  ,empid int

KEY
,empid int

NOT NULL
,empname varchar(25) NOT NULL
,salary money NOT NULL
);



Слайд 26 Предложение OVER
Определяет секционирование и упорядочение набора строк до

Предложение OVERОпределяет секционирование и упорядочение набора строк до применения соответствующей оконной

применения соответствующей оконной функции. То есть предложение OVER определяет окно

или определяемый пользователем набор строк внутри результирующего набора запроса. 
OVER (
[ ]
[ ]
)

Слайд 27 Рассмотрим пример
SELECT
id, dept, salary
from Employees

Рассмотрим пример SELECT 	id, dept, salary from Employees

Слайд 28 Сумма нарастающим итогом
SELECT
id, dept, salary
, SUM(salary)

Сумма нарастающим итогом SELECT 	id, dept, salary	, SUM(salary) OVER (ORDER BY id) AS Running_Sum from Employees

OVER (ORDER BY id) AS Running_Sum
from Employees


Слайд 29 Сумма с группировкой
SELECT
id, dept, salary
, SUM(salary)

Сумма с группировкой SELECT 	id, dept, salary	, SUM(salary) OVER (partition by

OVER (partition by dept) AS Dept_Sum
, AVG(salary) OVER

(partition by dept) AS Dept_AVG
from Employees


Слайд 30 Сумма с группировкой
SELECT
id, dept, salary
, SUM(salary)

Сумма с группировкой SELECT 	id, dept, salary	, SUM(salary) OVER (partition by

OVER (partition by dept ORDER by id) AS Dept_Sum


, AVG(salary) OVER (partition by dept) AS Dept_AVG
from Employees


Слайд 31 ROW_NUMBER()
SELECT
S.*, ROW_NUMBER() OVER (ORDER BY

ROW_NUMBER() SELECT S.*, ROW_NUMBER() OVER (ORDER BY empName) AS RowNum FROM Employees S

empName) AS RowNum
FROM Employees S


Слайд 32 Номер строки
SELECT
id, dept, salary
, ROW_NUMBER() OVER

Номер строки SELECT 	id, dept, salary	, ROW_NUMBER() OVER (ORDER BY id) AS RowNum from Employees

(ORDER BY id) AS RowNum
from Employees


Слайд 33 ROW_NUMBER() + PARTITION
SELECT
S.*, ROW_NUMBER() OVER

ROW_NUMBER() + PARTITION SELECT S.*, ROW_NUMBER() OVER (PARTITION BY S.mgrid ORDER

(PARTITION BY S.mgrid
ORDER BY S.empName) AS LocalRowNum
FROM

Employees S

Слайд 34 Номер строки с группировкой
SELECT
id, dept, salary
,

Номер строки с группировкой SELECT 	id, dept, salary	, ROW_NUMBER() OVER (PARTITION

ROW_NUMBER() OVER (PARTITION BY dept ORDER BY id) AS

RowNum
from Employees


Слайд 35 RANK ( ) / DENSE_RANK ( )
Rank -

RANK ( ) / DENSE_RANK ( )Rank - возвращает ранг каждой

возвращает ранг каждой строки в секции результирующего набора. Ранг строки

вычисляется как единица плюс количество рангов, находящихся до этой строки.(1, 1, 1, 4)
Dense_rank - возвращает ранг строк в секции результирующего набора без промежутков в ранжировании. Ранг строки равен количеству различных значений рангов, предшествующих строке, увеличенному на единицу. (1, 1, 2)

Слайд 36 RANK ( ) OVER (ORDER by smth)
Распределяет строки

RANK ( ) OVER (ORDER by smth)Распределяет строки упорядоченной секции в

упорядоченной секции в заданное количество групп. 

SELECT
S.*
,

RANK ( ) OVER (ORDER by salary desc) AS Gr
FROM Employees S


Слайд 37 NTILE ( N )
Распределяет строки упорядоченной секции в

NTILE ( N )Распределяет строки упорядоченной секции в заданное количество групп. SELECT

заданное количество групп. 

SELECT
S.*
, NTILE(3) OVER (ORDER

BY S.salary) AS Gr
FROM Employees S


Слайд 38
SELECT
S.*
, ROW_NUMBER() OVER (PARTITION BY

SELECT S.* , ROW_NUMBER() OVER 	(PARTITION BY S.mgrid ORDER BY S.empName)

S.mgrid ORDER BY S.empName) AS LocalRowNum
, RANK()

OVER (ORDER BY S.salary) AS Rank
, COUNT(*) OVER (PARTITION BY S.mgrid ) AS Amount
FROM Employees S


Слайд 39 Структура CTE
WITH expression_name ( column_name [,...n] )
AS
(

Структура CTEWITH expression_name ( column_name [,...n] ) AS( CTE_query_definition )Инструкция для обращения к ОТВ:SELECT FROM expression_name;

CTE_query_definition )

Инструкция для обращения к ОТВ:
SELECT
FROM expression_name;


Слайд 40 ROW_NUMBER() + PARTITION

ROW_NUMBER() + PARTITION

Слайд 41 Перенос данных из Emloyees в Emp_hierarchy
WITH paths(path, EmployeeID)

Перенос данных из Emloyees в Emp_hierarchyWITH paths(path, EmployeeID) AS (-- This


AS (
-- This section provides the value for the

root of the hierarchy
SELECT hierarchyid::GetRoot() AS OrgNode, empid
FROM Employees AS C
WHERE …

UNION ALL
-- This section provides values for all nodes except the root
SELECT
CAST(p.path.ToString() + CAST(( ROW_NUMBER() OVER (PARTITION BY mgrid ORDER BY mgrid) ) AS varchar(30)) + '/' AS hierarchyid),
C.empid
FROM Employees AS C
JOIN paths AS p
ON …
)


Слайд 42 Обход дерева
select s.*
from Emp_hierarchy s

Обход дереваselect s.*from Emp_hierarchy s

Слайд 43 Обход дерева с путем и уровнями
select s.*, Id.ToString()

Обход дерева с путем и уровнямиselect s.*, Id.ToString() AS Path, Id.GetLevel() AS Level from Emp_hierarchy s

AS Path,
Id.GetLevel() AS Level
from Emp_hierarchy s


Слайд 45 Id.GetAncestor(n int)
Res Hierarchyid
/*найти детей '/2/‘ */
select *
FROM

Id.GetAncestor(n int)Res Hierarchyid/*найти детей '/2/‘ */select * FROM Emp_hierarchy sWHERE s.id.GetAncestor(1)='/2/'/*найти

Emp_hierarchy s
WHERE s.id.GetAncestor(1)='/2/'

/*найти внуков '/2/‘ */
select *
FROM Emp_hierarchy

s
WHERE s.id.GetAncestor(2)='/2/'

Слайд 46 parent.GetDescendant ( child1 , child2 )
Res Hierarchyid
Для

parent.GetDescendant ( child1 , child2 ) Res HierarchyidДля генерации кодов дочерних

генерации кодов дочерних узлов предназначен метод GetDescendant. У него

есть два параметра, определяющих, между какими двумя узлами следует поместить новый узел (любой из параметров может быть равен null). Если это первый дочерний узел, то оба этих параметра должны быть равны null:
SET @new_node = @node.GetDescendant(@max_child_node, null);

Слайд 47 GetRoot
hierarchyid::GetRoot ( )

insert into Emp_hierarchy
(Id, empid, empname,

GetRoothierarchyid::GetRoot ( ) insert into Emp_hierarchy(Id, empid, empname, salary)values(hierarchyid::GetRoot(), 1, ‘Anna-Maria', 10000)

salary)
values
(hierarchyid::GetRoot(), 1, ‘Anna-Maria', 10000)


Слайд 48 id.GetLevel
Res smallint
Возвращает целое число, представляющее глубину этого узла в дереве.

id.GetLevelRes smallintВозвращает целое число, представляющее глубину этого узла в дереве.

Слайд 49 child. IsDescendantOf ( parent )
Res true|false
Возвращает значение true,

child. IsDescendantOf ( parent )Res true|falseВозвращает значение true, если объект this является потомком объекта parent.

если объект this является потомком объекта parent.


Слайд 50 node. GetReparentedValue ( oldRoot, newRoot )
Возвращаемый тип данных

node. GetReparentedValue ( oldRoot, newRoot )Возвращаемый тип данных SQL Server: hierarchyidПереносит

SQL Server: hierarchyid
Переносит ветку дерева

UPDATE Employees
SET id = id.GetReparentedValue(@old_node,

@new_node)
WHERE employee_hid.IsDescendantOf(@old_node) = 1;


Слайд 51 Id.ToString()
преобразование из типа hierarchyid в строковый тип

0x5AC0

Id.ToString()преобразование из типа hierarchyid в строковый тип0x5AC0 /1/1/

/1/1/


Слайд 52 Parse
преобразование из строкового типа тип в hierarchyid

hierarchyid::Parse(@StringValue)

Parseпреобразование из строкового типа тип в hierarchyid hierarchyid::Parse(@StringValue) /1/1/ 0x5AC0


/1/1/ 0x5AC0




Слайд 53 Обход поддерева Выборка всех потомков
DECLARE @parent_hid HIERARCHYID;

SELECT @parent_hid

Обход поддерева Выборка всех потомковDECLARE @parent_hid HIERARCHYID; SELECT @parent_hid = id

= id
FROM Emp_hierarchy
WHERE empname = ‘Laura’


select s.*, Id.ToString() AS [Path],
Id.GetLevel() AS [Level]
FROM Emp_hierarchy s
WHERE Id.IsDescendantOf(@parent_hid) = 1;

Слайд 54 Обход дерева с суммой зарплаты по всей ветке

select

Обход дерева с суммой зарплаты по всей веткеselect s.*, Id.ToString() AS

s.*, Id.ToString() AS [Path],
Id.GetLevel() AS [Level] ,

(select sum(salary) from Emp_hierarchy where Id.IsDescendantOf(S.id)=1) as Total
from Emp_hierarchy s


Слайд 55
Добавить для Robert нового подчиненного Boris между Ron

Добавить для Robert нового подчиненного Boris между Ron и DanДобавить ему

и Dan
Добавить ему двух любых подчиненных
Отправить Margaret в

подчинение Janet

  • Имя файла: ierarhiya-v-sql-sposoby-predstavleniya-ierarhicheskih-dannyh.pptx
  • Количество просмотров: 127
  • Количество скачиваний: 0