1 of 61

�

Тема:

Побудова SQL запитів.

Нові можливості

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

1

informatika@kture.kharkov.ua

Бази даних

та інформаційні системи

Лекція 11,12,13

2 of 61

Тема: Умовні оператори. Вираз CASE,IFF(Access)��Різне:�- перетворення типів;�- перетворення значень null у не-null значення;�- вибір випадкових рядків

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

2

http://infdbis.blogspot.com/

3 of 61

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

3

informatika@kture.kharkov.ua

GroupSt

Student

Subject

AcademicResults

Структура БД «University2023»

4 of 61

�Умовні оператори�Вираз CASE

Вирази CASE дають змогу реалізовувати в операторах SQL функціональність умовного оператора

if-then-else.

Існує 2 види виразів:

  • прості вирази CASE;
  • пошукові вирази CASE.

(обидва входять до стандарту SQL-92)

​

Синтаксис простого оператора CASE:

​

CASE вираз0

WHEN вираз1 THEN вираз_результат1

[WHEN вираз2 THEN вираз_результат2

[,...n]]

[ELSE вираз_результат_в_прот_випадку]

END

Опис:

  • Якщо не підходить жодна фраза WHEN і не вказана фраза ELSE, вираз повертає NULL;
  • Прості CASE - перевірка на рівність.

​

​

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

4

informatika@kture.kharkov.ua

5 of 61

�Умовні оператори�Вираз CASE

Синтаксис пошукового оператора CASE:

​

CASE

WHEN умовний-вираз1 THEN вираз-результат1

[WHEN умовний-вираз2 THEN вираз-результат2

[,...n]]

[ELSE вираз_результат_в_прот_випадку]

END

​

Опис:

  • Виконується тільки та фраза WHEN, умова якої оцінюється як істинна (TRUE);
  • Якщо істинними є кілька умов, то виконується тільки перша з цих умов;
  • Якщо не підходить жодна фраза WHEN і не вказана фраза ELSE, вираз повертає NULL;

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

5

informatika@kture.kharkov.ua

6 of 61

�Умовні оператори. Вираз CASE

Приклад 1 (SQL Server)

Вивести номер студента з такими коментарями:

"щось знає", якщо студент має оцінки, що перебувають у діапазоні [60;74];

"старається", якщо студент має оцінки, що перебувають у діапазоні [75;89];

"розумничка", якщо студент має оцінки, що перебувають у діапазоні [90;100];

​

select IdStudent, 'щось знає' as Коментар

from dbo. AcademicResults

where Mark between 60 and 74

union

select IdStudent, 'старається'

from dbo. AcademicResults

where Mark between 75 and 89

union

select IdStudent, 'розумничка'

from dbo. AcademicResults

where Mark between 90 and 100

​

Тип2

select distinct IdStudent,

case

when Mark >=90 then 'розумничка'

when Mark >=75 then 'старається'

when Mark >=60 then 'щось знає'

--else 'все погано'

end as Коментар

from dbo. AcademicResults

​

​

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

6

informatika@kture.kharkov.ua

AcademicResults

7 of 61

�Умовні оператори. Вираз CASE

Приклад 2 (SQL Server)

Вивести номер студента з такими коментарями :

"щось знає", якщо середня оцінка студента перебуває в діапазоні [60;74];

"старається", якщо середня оцінка студента перебуває в діапазоні [75;89];

"розумничка", якщо середня оцінка студента перебуває в діапазоні [90;100];

select IdStudent, avg(Mark) as AVG_Mark, 'щось знає' as Comment

from dbo. AcademicResults

group by IdStudent

having avg(Mark) between 60 and 74

union

select IdStudent, avg(Mark), 'старається'

from dbo. AcademicResults

group by IdStudent

having avg(Mark) between 75 and 89

union

select IdStudent, avg(Mark), 'розумничка'

from dbo. AcademicResults

group by IdStudent

having avg(Mark) between 90 and 100

Тип2

select distinct IdStudent, avg(Mark) as AVG_Mark,

case

when avg(Mark) >=90 then 'розумничка'

when avg(Mark) >=75 then 'старається'

when avg(Mark) >=60 then 'щось знає'

-- else 'все погано'

end as Comment

from dbo. AcademicResults

group by IdStudent

​

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

7

informatika@kture.kharkov.ua

AcademicResults

8 of 61

�Умовні оператори. Access - IIf

Приклад 2 (Access)

Вивести номер студента з такими коментарями :

"щось знає", якщо середня оцінка студента перебуває в діапазоні [60;74];

"старається", якщо середня оцінка студента перебуває в діапазоні [75;89];

"розумничка", якщо середня оцінка студента перебуває в діапазоні [90;100];

​

SELECT IdStudent, avg(Mark) as AVG_Mark,

IIF(avg(Mark)>=90,'розумничка', IIF(avg(Mark)>=75,'старається', IIF(avg(Mark)>=60,'щось знає', 'все погано')))) AS Comment

FROM AcademicResults

Group by IdStudent;

 

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

8

informatika@kture.kharkov.ua

Запит1

ІdStudent

AVG_Mark

Comment

1

72

щось знає

2

82

старається

4

86

старається

9 of 61

�Умовні оператори. Вираз CASE

Приклад 3 (SQL Server)

3.1 Підрахувати кожен вид оцінок, відсортувати за оцінками за зростанням.

select Mark, COUNT(*) as Quantity_Of_Marks

from AcademicResults

group by Mark

order by Mark;

​

3.2 Підрахувати скільки іспитів було з позначками "щось знає",

"старається", "розумничка".

select case

when Mark >=90 then 'розумничка'

when Mark >=75 then 'старається'

when Mark >=60 then 'щось знає'

end as Comment, COUNT(*) as Quantity_Of_Marks

from AcademicResults

group by case

when Mark >=90 then 'розумничка'

when Mark >=75 then 'старається'

when Mark >=60 then 'щось знає'

end;

​

​

​

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

9

informatika@kture.kharkov.ua

AcademicResults

10 of 61

�Умовні оператори. Вираз CASE

Приклад 3 (SQL Server)

3.3 Підрахувати скільки іспитів було з позначками "щось знає",

"старається", "розумничка". Відсортувати за коментарями.

select case

when Mark >=90 then 'розумничка'

when Mark >=75 then 'старається'

when Mark >=60 then 'щось знає'

end as Comment, COUNT(*) as Quantity_Of_Marks

from AcademicResults

group by case

when Mark >=90 then 'розумничка'

when Mark >=75 then 'старається'

when Mark >=60 then 'щось знає '

end

order by Comment;

/*order by

case

when Mark >=90 then 'розумничка'

when Mark >=75 then 'старається'

when Mark >=60 then 'щось знає'

end; */

​

​

​

​

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

10

informatika@kture.kharkov.ua

AcademicResults

11 of 61

�Умовні оператори. Вираз CASE

Приклад 4. Вивести результати іспитів (номер студента, прізвище, оцінку) з коментарями:

"супер розумничка" для випадку, коли оцінка максимальна, "майже розумничка", коли оцінка мінімальна,

select Student.IdStudent, Mark, NameStudent,

case

when Mark=(select max(Mark) from AcademicResults) then ' супер розумничка '

when Mark=(select min(Mark) from AcademicResults) then ' майже розумничка '

end as Comment

from AcademicResults INNER JOIN Student ON AcademicResults.IdStudent= Student.IdStudent

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

11

informatika@kture.kharkov.ua

AcademicResults

12 of 61

�Умовні оператори. Вираз CASE

Приклад 5. Підрахувати кількість іспитів з максимальною оцінкою ("відмінників"), з мінімальною оцінкою і всіх інших іспитів

НЕ ВІРНО!

select count(*),

case

when Mark =(select max(Mark) from AcademicResults) then ' супер розумничка '

when Mark =(select min(Mark) from AcademicResults) then ' майже розумничка '

end as Comment

from AcademicResults

group by

case

when Mark =(select max(Mark) from AcademicResults) then ' супер розумничка '

when Mark =(select min(Mark) from AcademicResults) then ' майже розумничка '

end ;

Msg 144, Level 15, State 1, Line 9

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

Msg 144, Level 15, State 1, Line 10

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

----------------------------------

ПРАВИЛЬНО!

select Comment, COUNT(*)

from (

select IdStudent,

case

when Mark=(select max(Mark) from AcademicResults) then ' супер розумничка '

when Mark=(select min(Mark) from AcademicResults) then ' майже розумничка '

end as Comment

from AcademicResults) as T

group by Comment;

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

12

informatika@kture.kharkov.ua

13 of 61

�Умовні оператори. Вираз CASE

Приклад 6 (SQL Server)

Підрахувати скільки середніх балів припало на кожен коментар

("щось знає", "старається", "розумничка")

НЕ ВІРНО!

select

case

when avg(Mark) >=90 then 'розумничка'

when avg(Mark) >=75 then 'старається'

when avg(Mark) >=60 then 'щось знає'

-- else 'все погано'

end as Comment, COUNT (*)

from dbo.AcademicResults

group by case

when avg(Mark) >=90 then 'розумничка'

when avg(Mark) >=75 then 'старається'

when avg(Mark) >=60 then 'щось знає'

-- else 'все погано’

end

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause

----------------------------------

ПРАВИЛЬНО!

select Comment, COUNT(*)

from (select

case

when avg(Mark) >=90 then 'розумничка'

when avg(Mark) >=75 then 'старається'

when avg(Mark) >=60 then 'щось знає'

-- else 'все погано'

end as Comment

from dbo. AcademicResults

group by IdStudent) as T

group by Comment

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

13

informatika@kture.kharkov.ua

AcademicResults

14 of 61

�Умовні оператори. Вираз CASE

Приклад 7 (SQL Server)

Вивести номер студента, замінити максимальні

оцінки мінімальними, а мінімальні максимальними

Варіант a (оцінки > min і <max виводити)

select IdStudent,

case

when Mark=(select max(Mark) from AcademicResults)

then (select min(Mark) from AcademicResults)

when Mark=(select min (Mark) from AcademicResults)

then (select max(Mark) from AcademicResults)

else Mark

end as NewMarks

from AcademicResults

​

Варіант б (оцінки > min і <max не виводити)

1) select IdStudent,

case

when Mark=(select max(Mark) from AcademicResults)

then (select min(Mark) from AcademicResults)

when Mark=(select min (Mark) from AcademicResults)

then (select max(Mark) from AcademicResults)

end as NewMarks

from AcademicResults

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

14

informatika@kture.kharkov.ua

AcademicResults

15 of 61

�Умовні оператори. Вираз CASE

Приклад 7 (SQL Server) (продовження)

Вивести номер студента, замінити максимальні

оцінки мінімальними, а мінімальні максимальними

Варіант б (оцінки > min і <max не виводити)

1) повтор

select IdStudent,

case

when Mark=(select max(Mark) from AcademicResults)

then (select min(Mark) from AcademicResults)

when Mark=(select min (Mark) from AcademicResults)

then (select max(Mark) from AcademicResults)

end as NewMarks

from AcademicResults

​

2) (без NULL)

select IdStudent,

case

when Mark=(select max(Mark) from AcademicResults)

then (select min(Mark) from AcademicResults)

when Mark=(select min (Mark) from AcademicResults)

then (select max(Mark) from AcademicResults)

end as NewMarks

from AcademicResults

Where Mark=(select min (Mark) from AcademicResults) or

Mark=(select max (Mark) from AcademicResults)

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

15

informatika@kture.kharkov.ua

3) (без NULL)

select IdStudent, NewMarks

from

(select IdStudent,

case

when Mark=(select max(Mark) from AcademicResults)

then (select min(Mark) from AcademicResults)

when Mark=(select min (Mark) from AcademicResults)

then (select max(Mark) from AcademicResults)

end as NewMarks

from AcademicResults) as T

where NewMarks IS NOT NULL

16 of 61

�Умовні оператори. Вираз CASE

Використання CASE всередині агрегатної функції

Приклад 8

Вивести номер студента, скільки в нього оцінок усього,�скільки оцінок у кожному з діапазонів �[60;74], [75,89] [90;100].

​

SELECT

IdStudent,

COUNT(*) as ALLMarks,

COUNT(case when Mark between 60 and 74 then Mark end) as Mark60_75,

COUNT(case when Mark between 75 and 89 then Mark end) as Mark76_90,

COUNT(case when Mark between 90 and 100 then Mark end) as Mark90_100

FROM AcademicResults

GROUP BY IdStudent

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

16

informatika@kture.kharkov.ua

AcademicResults

17 of 61

�Умовні оператори. Вираз CASE

Використання CASE всередині агрегатної функції

Приклад 8

Вивести номер студента, прізвище, середній бал за весь час, середній бал в 1 семестрі,

середній бал в 2 семестрі.

�select Student.IdStudent, NameStudent, avg(Mark) as AVGTotal,

avg(case when Semester=1 then Mark end) as AVGSem1,

avg(case when Semester=2 then Mark end) as AVGSem2

from Student inner join AcademicResults on Student.IdStudent=AcademicResults.IdStudent

group by Student.IdStudent, NameStudent

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

17

informatika@kture.kharkov.ua

IdStudent NameStudent AVGTotal AVGSem1 AVGSem2

1 Іванченко І.І. 72 75 66

2 Іванченко І.І. 82 NULL NULL

4 Андрєєва А.А. 86 90 75

18 of 61

�Умовні оператори. Вираз CASE. Конвертування типів (convert)

CAST (значення AS тип даних)

Convert (тип даних, значення)

Приклад 9. Вивести інформацію в такому вигляді: номер студента, прізвище, максимальна оцінка,

якщо студент є консультантом, або позначку "не є консультантом".

Варіант 1 (з групуванням)

select Student.IdStudent, NameStudent,

case

when Student.IdStudent IN (select Consultant from dbo. Student)

then convert(varchar,max(Mark))

else 'не є консультантом'

end

from Student, AcademicResults

where Student.IdStudent=AcademicResults.IdStudent

group by Student.IdStudent, NameStudent

Зауваження:

Якщо просто "then max(Mark) "

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to real.

​

Варіант 2 (корельований підзапит)

select IdStudent, NameStudent,

case when IdStudent IN (select Consultant from dbo. Student)

then convert(varchar,(select max(Mark) from AcademicResults where Student.IdStudent=AcademicResults.IdStudent))

else 'не є консультантом'

end

from Student

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

18

informatika@kture.kharkov.ua

19 of 61

�Витяг з однієї таблиці значень, яких немає в іншій таблиці

Приклад 9.Вивести результати іспитів (усі поля відношення AcademicResults) для студентів, які є консультантами.

select *

from AcademicResults

where IdStudent IN (select Consultant from Student)

Приклад 10.Вивести результати іспитів (усі поля відношення AcademicResults)

для студентів, які НЕ є консультантами.

Варіант 1 (некоректний у разі, якщо поле Consultant містить невизначені значення NULL)

select *

from AcademicResults

where IdStudent NOT IN (select Consultant from Student)

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

19

informatika@kture.kharkov.ua

GroupSt

Student

Subject

AcademicResults

20 of 61

Тризначна логіка

  • При використанні NULL відбувається перехід до роботи з логікою вищого порядку

Якщо X - це значення деякого типу даних або NULL,

оп_а - будь-яка двомісна "арифметична" операція цього типу даних

оп_ср - операція порівняння значень цього типу ,

то за визначенням:

X оп_а NULL = NULL

NULL оп_a X = NULL

X оп_ср NULL = unknown

NULL оп_ср X = unknown

  • Тут unknown - це третє значення логічного, або булевського, типу, що має такі властивості:

NOT unknown = unknown

true AND unknown = unknown

true OR unknown = true

false AND unknown = false

false OR unknown = unknown

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

20

21 of 61

�Витяг з однієї таблиці значень, яких немає в іншій таблиці

select *

from AcademicResults

where IdStudent NOT IN (2,4);

​

select *

from AcademicResults

where IdStudent NOT IN (2,4,NULL);

​

Пояснення:

IdStudent NOT IN (2,4,NULL)

NOT (IdStudent =2 or IdStudent =4 or IdStudent =NULL)

​

Нехай IdStudent=1, тоді

  • NOT (false or false or unknown)
  • NOT (false or unknown)
  • NOT (unknown)
  • unknown

​

Нехай IdStudent=2, тоді

  • NOT (true or false or unknown)
  • NOT (true or unknown)
  • NOT (true)
  • fasle

​

// NOT (IdStudent =2 or IdStudent =4 or IdStudent IS NULL)

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

21

informatika@kture.kharkov.ua

select *

from AcademicResults

where IdStudent IN (2,4,NULL);

​

Пояснення:

IdStudent IN (2,4,NULL)

IdStudent =2 or IdStudent =4 or IdStudent=NULL

​

Нехай IdStudent=1, тоді

  • false or false or unknown
  • false or unknown
  • unknown

​

Нехай IdStudent=2, тоді

  • true or false or unknown
  • true or unknown
  • true

22 of 61

�Витяг з однієї таблиці значень, яких немає в іншій таблиці

Вивести результати іспитів

(усі поля відношення AcademicResults)

для студентів, які НЕ є консультантами

​

Варіант 2 (коректний)

select *

from AcademicResults

where NOT EXISTS (select * from Student

where Student.Consultant=AcademicResults.IdStudent)

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

22

informatika@kture.kharkov.ua

23 of 61

�Перетворення значень NULL у не-NULL значення

​

функції COALESCE, CASE

ISNULL (SQL Server)

NVL, NVL2, DECODE (Oracle)

​

ISNULL ( check_expression , replacement_value )

COALESCE ( вираз [ ,...n ] )

​

Приклад 11. Замініть невизначені значення в полі Consultant числовими значеннями 0 (нуль)

select IdStudent, NameStudent, Address, IdGroup, coalesce (Consultant,0)

from dbo. Student

​

або

​

select IdStudent, NameStudent, Address, IdGroup, ISNULL(Consultant,0)

from dbo. Student

​

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

23

http://infdbis.blogspot.com/

24 of 61

�Вибір із таблиці випадкових рядків

Приклад 12. Вивести 3 рядки з таблиці AcademicResults, обрані випадковим чином

select top 3 *, newid()

from dbo. AcademicResults

order by newid()

​

select top 3 *

from dbo. AcademicResults

order by newid()

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

24

http://infdbis.blogspot.com/

25 of 61

�Умовні оператори. Сортування за невидимим полем

Приклад 13. Вивести код товару,

Сорт, ціну закупівельну.

Відсортувати дані за сортом (правильно: вищ,

перший, другий, не визначений)

за зростанням, потім за ціною за спаданням

​

select IdProduct, Sort,

case

when Sort='перший' then '1'

when Sort='другий' then '2'

when Sort='вищий' then '0'

else 'сорт не визначений'

end as Sort1,

Price

from Product

order by Sort1,

/*case

when Sort='перший' then '1'

when Sort='другий' then '2'

when Sort='вищий' then '0'

else 'сорт не визначений'

end,*/

Price Desc;

Зауваження! Якщо сортування здійснюється на основі case, а поле, що

сформоване на основі case, виводити не потрібно,

тоді case поміщається в секцію order by.

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

25

http://infdbis.blogspot.com/

Product

26 of 61

Тема: Розширення GROUP BY

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

26

http://infdbis.blogspot.com/

27 of 61

Початкова схема даних БД “Trade2023"

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

27

Client (IdClient, LName, FName, MName, Company, CityClient, Phone)

Product (IdProduct, NameProduct, Type, Sort, Price, Stock, CityProduct)

Sale (IdSale, IdClient, IdProduct, Quantity, DateSale)

Client

Product

Sale

28 of 61

�Розширення GROUP BY.

SQL Server, Oracle підтримують такі розширення GROUP BY:

ROLLUP - створює результуючий набір, що містить статистичні вирази ієрархії значень у заданих стовпцях

Приклад:�SELECT a, b, c, SUM (вираз)�FROM T�GROUP BY ROLLUP (a,b,c);�Формується один рядок із підсумком для кожного поєднання значень (a, b, c), (a, b) і (a) плюс обчислюється також рядок загального підсумку. Стовпці згортаються справа наліво, послідовність розташування стовпців впливає на вихідне групування.

CUBE - створює результуючий набір, що містить статистичні вирази для всіх комбінацій значень заданих стовпців

Приклад:�SELECT a, b, c, SUM (вираз)�FROM T�GROUP BY CUBE (a,b,c);�Формується один рядок для кожного унікального поєднання значень (a, b, c), (a, b), (a, c), (b, c), (a), (b) і (c) з підсумом для кожного рядка і рядком загального підсумку. Вихідні дані не залежать від порядку стовпців

GROUPING SETS - вказує від одного до кількох групувань даних (у круглих дужках, розділених комами) в одному запиті. Виконується статистичне опрацювання тільки зазначених груп, а не повного набору статистичних даних, що формуються за допомогою конструкцій CUBE або ROLLUP.

Приклад:�SELECT a, b, c, SUM (вираз)�FROM T�GROUP BY GROUPING SETS ((a,b), (b,c), (a), ());

​

​

​

Дані не підтримуються MySQL, Access

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

28

http://infdbis.blogspot.com/

29 of 61

�Розширення GROUP BY. �ROLLUP

Приклад 1. Підрахувати кількість угод із кожним клієнтом, а також скільки разів кожен клієнт купував кожен товар, а також загальну кількість угод.

(використовуючи корельовані підзапити)

select IdClient, IdProduct, COUNT(*) as col1,

(select COUNT(*) from Sale S2 where S2.IdClient=Sale.IdClient) as col2,

(select COUNT(*) from Sale S2) as col3

from Sale

group by IdClient, IdProduct;

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

29

informatika@kture.kharkov.ua

Sale

30 of 61

�Розширення GROUP BY

Приклад 1. Підрахувати кількість угод із кожним клієнтом, а також скільки разів кожен клієнт купував кожен товар, а також загальну кількість угод.

(використовуючи UNION)

(MS SQL SERVER)

SELECT cast(IdClient as varchar) as IdClient, IdProduct, count(*) as SaleQuantity

FROM Sale

GROUP BY IdClient, IdProduct

UNION

SELECT cast(IdClient as varchar) as IdClient, ' ' as IdProduct, count(*) as SaleQuantity

FROM Sale

GROUP BY IdClient

UNION

SELECT 'Разом' as IdClient, ' ' as IdProduct, count(*) as SaleQuantity

FROM Sale

ORDER BY IdClient;

​

Зауваження! Якщо без функції cast(IdClient as varchar)

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value ‘Разом' to data type int.

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

30

informatika@kture.kharkov.ua

Sale

31 of 61

�Розширення GROUP BY

Приклад 1. Підрахувати кількість угод із кожним клієнтом, а також скільки разів кожен клієнт купував кожен товар, а також загальну кількість угод.

(використовуючи UNION)

(MS ACCESS, ROLLUP MS ACCESS не підтримує)

SELECT IdClient, IdProduct, count(*) as SaleQuantity

FROM Sale

GROUP BY IdClient, IdProduct

UNION

SELECT IdClient, ' ', count(*) as SaleQuantity

FROM Sale

GROUP BY IdClient

UNION

SELECT 'Разом', ' ', count(*) as SaleQuantity

FROM Sale

ORDER BY IdClient;

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

31

informatika@kture.kharkov.ua

Sale

32 of 61

�Розширення GROUP BY. �ROLLUP

Приклад 1. Підрахувати кількість угод із кожним клієнтом, а також скільки разів кожен клієнт купував кожен товар, а також загальну кількість угод.

(ANSI, SQL SERVER, ORACLE)

SELECT IdClient, IdProduct, count(*) as SaleQuantity

FROM Sale

GROUP BY ROLLUP (IdClient,IdProduct);

(SQL SERVER)

SELECT IdClient, IdProduct, count(*) as SaleQuantity

FROM Sale

GROUP BY IdClient, IdProduct WITH ROLLUP;

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

32

informatika@kture.kharkov.ua

Sale

33 of 61

�Розширення GROUP BY. ROLLUP

Приклад 1. Підрахувати кількість угод із кожним клієнтом, а також скільки разів кожен клієнт купував кожен товар, загальну кількість угод не підраховувати.

​

(ANSI, SQL SERVER, ORACLE)

SELECT IdClient, IdProduct, count(*) as SaleQuantity

FROM Sale

GROUP BY IdClient, ROLLUP (IdProduct);

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

33

informatika@kture.kharkov.ua

Sale

34 of 61

�Розширення GROUP BY. CUBE

Приклад 2. Підрахувати кількість угод із кожним клієнтом, кількість угод із кожним товаром, а також скільки разів кожен клієнт купував кожен товар і загальну кількість угод.

(ANSI, SQL SERVER, ORACLE)

SELECT IdClient, IdProduct, count(*) as SaleQuantity

FROM Sale

GROUP BY CUBE(IdClient,IdProduct);

​

(SQL SERVER)

GROUP BY IdClient, IdProduct with CUBE;

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

34

informatika@kture.kharkov.ua

SELECT IdClient, IdProduct, count(*) as SaleQuantity

FROM Sale

GROUP BY CUBE(IdClient,IdProduct)

ORDER BY IdClient, IdProduct;

​

(ORACLE)

ORDER BY IdClient nulls last;

35 of 61

�Розширення GROUP BY. CUBE

Сортування рядків з невизначеними значеннями (NULL)

Приклад 2б. Підрахувати кількість угод з кожним клієнтом, кількість угод з кожним товаром, а також скільки разів кожен клієнт купував кожен товар і загальну кількість угод. Відсортувати результат за кодом клієнта і кодом товару за зростанням.

(ANSI, SQL SERVER)

SELECT IdClient, IdProduct, count(*) as SaleQuantity

FROM Sale

GROUP BY CUBE(IdClient,IdProduct)

ORDER BY IdClient, IdProduct;

​

Зауваження!

  • Розташування рядків з NULL значеннями залежить від СУБД.
  • У SQL SERVER NULL значення виводяться першими.
  • В ORACLE існують функції NULLS FIRST, NULLS LAST,

що дають змогу розташовувати рядки з невизнач.значеннями на початку

або кінці незалежно від порядку сортування рядків з визнач. значеннями

ORDER BY IdClient nulls last;

​

  • В ін. СУБД для вказівки, де в результаті сортування повинні

розташовуватися NULL-значення, можна використовувати вираз CASE, за допомогою якого позначати рядок з NULL спеціальним "прапором". Потім додати стовпець із прапором в оператор ORDER BY (першим ключем сортування).

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

35

http://infdbis.blogspot.com/

36 of 61

�Розширення GROUP BY. �GROUPING SETS

Приклад 3a. В одному запиті підрахувати кількість угод із кожним клієнтом, а також кількість угод за кожним товаром.

(ANSI, SQL SERVER, ORACLE)

SELECT IdClient, IdProduct, count(*) as SaleQuantity

FROM Sale

group by GROUPING sets (IdClient, IdProduct);

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

36

informatika@kture.kharkov.ua

Sale

37 of 61

�Розширення GROUP BY. �GROUPING SETS

Приклад 3б. В одному запиті підрахувати кількість угод з кожним клієнтом, а також скільки разів кожен клієнт купував кожен товар.

(ANSI, SQL SERVER, ORACLE)

SELECT IdClient, IdProduct, count(*) as SaleQuantity

FROM Sale

group by GROUPING sets ((IdClient), (IdClient, IdProduct));

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

37

informatika@kture.kharkov.ua

Sale

38 of 61

�Розширення GROUP BY. �GROUPING SETS

Приклад 3в. В одному запиті підрахувати скільки разів кожен клієнт купував кожен товар, а також скільки угод кожен клієнт здійснив у кожному році.

(ANSI, SQL SERVER, ORACLE)

SELECT IdClient, Year(DateSale) as Year, IdProduct, count(*) as SaleQuantity

FROM Sale

group by GROUPING sets ((IdClient, Year(DateSale)), (IdClient, IdProduct));

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

38

informatika@kture.kharkov.ua

Sale

39 of 61

�Розширення GROUP BY. GROUPING

​

(SQL SERVER, ORACLE)

SELECT IdClient, IdProduct, count(*) as SaleQuantity, GROUPING(IdClient) n1, GROUPING(IdProduct) n2

FROM Sale

GROUP BY ROLLUP (IdClient,IdProduct);

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

39

informatika@kture.kharkov.ua

40 of 61

�Розширення GROUP BY. GROUPING

​

(SQL SERVER, ORACLE)

SELECT IdClient, IdProduct, count(*) as SaleQuantity, GROUPING(IdClient) n1, GROUPING(IdProduct) n2

FROM Sale

GROUP BY CUBE(IdClient,IdProduct)

order by IdClient, IdProduct;

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

40

informatika@kture.kharkov.ua

41 of 61

Тема: Віконні функції over()

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

41

http://infdbis.blogspot.com/

42 of 61

Співвідношення між SELECT і GROUP BY.

Під час використання агрегатних функцій усі атрибути, які містяться в рядку SELECT, мають повторюватися в рядку GROUP BY

Винятками є :

  • константи;
  • скалярні значення, повернуті користувацькими функціями, віконними функціями;
  • підзапити.

Запит1a (літерали, підзапити)

select 'Привіт' as Привітання, IdGroup, count(*) as StudentQuantity,

(select count(*) from dbo. Student) as Total

from Student

group by IdGroup;

​

Запит1б(літерали, підзапити)

select IdGroup, count(*) as StudentQuantity, (select DeanOffice from GroupSt where IdGroup=Student.IdGroup) as Total

from Student

group by IdGroup;

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

42

informatika@kture.kharkov.ua

43 of 61

�Сегментування. Віконні функції

Віконні функції - виконують агрегацію заданого набору (групи) рядків, але замість того, щоб повертати по одному значенню на кожну групу, можуть повертати кілька значень для кожної групи.

Група рядків, що піддається агрегації, - це вікно.

У DB2 - функції оперативного аналізу,

Oracle - аналітичні функції.

Стандарт ISO SQL - віконні функції.

​

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

43

informatika@kture.kharkov.ua

44 of 61

�Сегментування.віконні функції

Запит2. Скільки всього студентів

select count(*) from Student;

​

​

Запит3. Вивести номер студента, прізвище і скільки всього студентів (три стовпці)

Варіант а (без використання віконної функції)

select IdStudent, NameStudent, (select count(*) from Student) as Total

from Student;

​

Варіант б (з використанням віконної функції)

select IdStudent, NameStudent, count(*) over() as Total

from Student;

​

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

44

informatika@kture.kharkov.ua

45 of 61

�Сегментування. Віконні функції

OVER() - показує, що агрегатна функція розглядатиметься як віконна.

Опис:

  • усі агрегатні функції можуть бути використані як віконні.
  • виклик віконної функції: аргатн.ф-ція OVER()

наприклад, COUNT(*) OVER()

  • OVER()(без параметрів) для кожного рядка, що повертається запитом, повертає значення агрегатної функції для всіх рядків у таблиці.
  • порядок виконання: віконні функції виконуються як останній крок в обробці SQL перед оператором ORDER BY.

​

Запит 4а. Вивести всю інформацію про іспити

(номер студента, номер дисципліни, оцінку),

а також загальна кількість іспитів.

select IdStudent, IdSubject, Mark, count(*) over() as Total

from AcademicResults;

​

Запит 4б. Вивести всю інформацію про іспити з дисципліни К2

(номер студента, номер дисципліни, оцінку),

а також загальна кількість іспитів з дисципліни К2

select IdStudent, IdSubject, Mark, count(*) over() as Total

from AcademicResults

where IdSubject='К2';

​

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

45

informatika@kture.kharkov.ua

46 of 61

�Сегменти. PARTITION BY

Оператор PARTITION BY - визначає сегмент (групу рядків), що піддається агрегації.

Оператор PARTITION BY - ковзний GROUP BY

Запит5. Вивести номер студента, прізвище, групу, а також кількість осіб у групі

​

​

​

Варіант а (без використання віконної функції)

select IdStudent, NameStudent, IdGroup,

(select count(*) as AmountGroup from Student St2 where St2.IdGroup=St1.IdGroup) as AmountGroup

from Student St1;

​

Варіант б (з використанням віконної функції)

select IdStudent, NameStudent, IdGroup, count(*) over (partition by IdGroup) as AmountGroup

from Student;

Зауваження!

  • при PARTITION BY IdGroup функція COUNT(*) виконується для кожної групи;
  • значення поля AmountGroup для всіх студентів однієї групи (одного сегмента) буде однаковим

​

​

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

46

informatika@kture.kharkov.ua

47 of 61

�Сегменти. PARTITION BY

Оператор PARTITION BY - виконує обчислення незалежно від інших віконних функцій,

здійснюючи сегментування за іншими стовпцями в тому самому виразі SELECT.

Запит6. Вивести номер студента, прізвище, групу, кількість осіб у групі, факультет, кількість осіб на факультеті. Відсортувати за номером студента

​

​

​

​

Варіант а (без використання віконної функції)

select IdStudent, NameStudent, St1.IdGroup,

(select count(*) as AmountGroup from Student St2 where St2.IdGroup=St1.IdGroup) as AmountGroup,

DeanOffice,

(select count(*) as AmountDekanat from Student St2 inner join GroupSt Gr2 ON St2.IdGroup=Gr2.IdGroup

where Gr2.DeanOffice=Gr1.DeanOffice) as AmountDekanat

from Student St1 inner join GroupSt Gr1 ON St1.IdGroup=Gr1.IdGroup

order by IdStudent;

Варіант б (з використанням віконної функції)

select IdStudent, NameStudent, Student.IdGroup,

count(*) over (partition by Student.IdGroup) as AmountGroup,

DeanOffice,

count(*) over (partition by DeanOffice) as AmountDekanat

from Student inner join GroupSt ON Student.IdGroup=GroupSt.IdGroup

order by IdStudent;

​

​

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

47

informatika@kture.kharkov.ua

48 of 61

�Сегменти. PARTITION BY

Оператор PARTITION BY - працює так само як і GROUP BY, але на нього не впливають інші елементи оператора SELECT, і він не вимагає присутності оператора GROUP BY

​

Запит7. Вивести номер студента,

номер дисципліни, загальний максимальний бал,

макс бал студента, макс бал з дисципліни,

макс бал для студента з кожної дисципліни.

Варіант а (з використанням віконної функції)

select IdStudent, IdSubject, max(Mark) over() as MaxTotal,

max(Mark) over(partition by IdStudent) as MaxStud,

max(Mark) over(partition by IdSubject) as MaxDist,

max(Mark) over(partition by IdStudent,IdSubject) as MaxStudDist

from AcademicResults;

​

якщо distinct

select distinct IdStudent, IdSubject,

max(Mark) over() as MaxTotal,

...

...

...

​

​

​

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

48

informatika@kture.kharkov.ua

49 of 61

�Сегменти. PARTITION BY

Варіант б (з використанням розширень group by)

1) використовуючи cube

select IdStudent, IdSubject, max(Mark) as Max

from AcademicResults

group by cube(IdStudent, IdSubject);

​

2) використовуючи grouping sets

select IdStudent, IdSubject, max(Mark) as Max

from AcademicResults

group by grouping sets(IdStudent, IdSubject, (IdStudent, IdSubject),());

​

​

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

49

informatika@kture.kharkov.ua

50 of 61

�Функція ROW_NUMBER

Оператор ORDER BY в операторі OVER - визначає порядок розташування рядків у сегменті ("сегмент" за відсутності оператора - це вся результуюча множина)

Функція ROW_NUMBER нумерує рядки, що повертаються запитом.

З її допомогою можна виконати складніше впорядкування рядків у звіті, ніж те, що дає речення ORDER BY у межах Стандарту SQL-92.

Використовуючи функцію ROW_NUMBER можна:

  • задати нумерацію, яка відрізнятиметься від порядку сортування рядків результуючого набору;
  • створити "ненаскрізну" нумерацію, тобто виділити групи із загальної безлічі рядків і пронумерувати їх окремо для кожної групи;
  • використовувати одночасно кілька способів нумерації, оскільки, фактично, нумерація не залежить від сортування рядків запиту.

​

Запит8а. Вивести номер студента, номер дисципліни, оцінку,

пронумерувати рядки в порядку зростання Оцінки.

Відсортувати за номером студента, потім за номером дисципліни

select IdStudent, IdSubject, Mark,

row_number() over (order by Mark) as MyCount

from AcademicResults

order by IdStudent, IdSubject;

​

  • для сортування у зворотному порядку використовується DESC:

row_number() over (order by Mark DESC)

​

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

50

informatika@kture.kharkov.ua

51 of 61

�Функція ROW_NUMBER

Запит8б. Вивести номер студента, номер дисципліни, оцінку,

пронумерувати рядки в порядку зростання Оцінки.

Відсортувати за новоствореним номером.

select IdStudent, IdSubject, Mark,

row_number() over (order by Mark) as MyCount

from AcademicResults

order by MyCount desc;

​

або

select IdStudent, IdSubject, Mark,

row_number() over (order by Mark) as MyCount

from AcademicResults

order by Mark desc;

​

або

select IdStudent, IdSubject, Mark,

row_number() over (order by Mark) as MyCount

from AcademicResults

order by row_number() over (order by Mark) desc;

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

51

http://infdbis.blogspot.com/

52 of 61

�Функція ROW_NUMBER і PARTITION BY

  • Конструкція PARTITION BY задає групи рядків, для яких виконується незалежна нумерація;
  • Група визначається рівністю значень у списку стовпців, перелічених у цій конструкції,
  • Відсутність конструкції PARTITION BY означає, що всі рядки результуючого набору утворюють одну єдину групу;

​

Запит9. Вивести номер студента, номер дисципліни, оцінку,

пронумерувати рядки для кожного студента в порядку зростання оцінки Оцінки. Відсортувати за номером студента, потім за номером дисципліни

​

select IdStudent, IdSubject, Mark,

row_number() over (partition by IdStudent order by Mark) as MyCount

from AcademicResults

order by IdStudent, IdSubject;

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

52

informatika@kture.kharkov.ua

53 of 61

�Функції RANK() і DENSE_RANK()

Також нумерують рядки, але роблять це дещо відмінним способом -

рядки, які мають однакові значення в стовпцях, за якими виконується впорядкування, отримують однакові номери (ранги).

​

Наприклад, значення (відсортовані за зростанням)

​

​

​

отримають такі номери:

​

​

​

​

​

​

​

Виникає питання, з якого номера продовжиться нумерація, якщо в послідовності чисел з'явиться 7 і т.д.? Тут є два варіанти:

  • з номера 4, оскільки це наступний номер за порядком;
  • з номера 6, тому що наступний рядок буде шостий за рахунком.

Така "неоднозначність" і призвела до появи двох функцій замість однієї -

RANK і DENSE_RANK, перша з яких продовжить нумерацію з 6, а друга (щільна) - з 4.

​

Зауваження! Як і для функції ROW_NUMBER, у реченні OVER може використовуватися конструкція PARTITION BY, що розбиває весь набір рядків, які повертаються запитом, на групи, до яких потім застосовується відповідна функція.

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

53

informatika@kture.kharkov.ua

1

5

6

6

6

1

1

5

2

6

3

6

3

6

3

54 of 61

�Функції RANK() і DENSE_RANK()

Запит10а. Вивести номер студента, номер дисципліни, оцінку,

пронумерувати рядки в порядку зростання Оцінки трьома

способами:

  • для кожного рядка номер збільшується на 1;
  • з присвоєнням однакових номерів для однакових значень;
  • теж, але "щільний" варіант присвоєнням однакових

номерів для однакових значень.

select IdStudent, IdSubject, Mark,

row_number() over (order by Mark) as MyCount1,

rank () over (order by Mark) as MyCount2,

dense_rank () over (order by Mark) as MyCount3

from AcademicResults

​

Запит10б. Вивести номер студента, номер дисципліни, оцінку,

пронумерувати рядки для кожного студента в порядку зростання Оцінки трьома способами:

  • для кожного рядка номер збільшується на 1;
  • з присвоєнням однакових номерів для однакових значень;
  • теж, але "щільний" варіант присвоєнням однакових номерів

для однакових значень.

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

54

informatika@kture.kharkov.ua

55 of 61

�Функції RANK() і DENSE_RANK()

Запит11. Підрахувати рейтинг студентів. Рейтинг вираховується на основі середнього бала студентів

усього університету.�Варіант 1

select Student.IdStudent, NameStudent, AVG(Mark) as AVG_Mark, dense_rank () over (order by AVG(Mark) desc) as Rating

from Student left join AcademicResults on Student.IdStudent=AcademicResults.IdStudent

group by Student.IdStudent, NameStudent;

Варіант 2

select *, dense_rank() over (order by AVG_Mark desc)

from

(select Student.IdStudent, NameStudent, avg(Mark) as AVG_Mark

from Student Inner Join AcademicResults ON Student.IdStudent=AcademicResults.IdStudent

group by Student.IdStudent, NameStudent) G

-----------------------------------------------------------

Запит12. Підрахувати рейтинг студентів. Рейтинг вираховується на основі середнього бала студентів

у своїй групі.

select IdGroup, AcademicResults.IdStudent, NameStudent, AVG(Mark)as AVG_Mark,

dense_rank () over(partition by IdGroup order by AVG(Mark)) as Rating

from Student left join AcademicResults on Student.IdStudent=AcademicResults.IdStudent

group by IdGroup, AcademicResults.IdStudent, NameStudent

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

55

56 of 61

�Функції NTILE()

NTILE (integer_expression) OVER ( [ [ < partition_by_clause> ] < order_by_clause > )

​

Розподіляє рядки впорядкованої секції в задану кількість груп. Групи нумеруються, починаючи з одиниці. Для кожного рядка функція NTILE повертає номер групи, якій належить рядок.

​

Аргументи

  • integer_expression - позитивний цілий вираз-константа, що вказує кількість груп, на які необхідно розділити кожну секцію. Аргумент integer_expression може мати тип int або bigint.
  • < partition_by_clause> - ділить результуючий набір, отриманий за допомогою речення FROM, на секції, до яких застосовується функція.
  • < order_by_clause> - визначає порядок призначення значень функції NTILE рядкам секції.

​

Запит13. Розділити всі іспити на 2 частини

select *, NTILE(2) OVER(ORDER BY Mark DESC) as part

from dbo.AcademicResults

​

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

56

http://infdbis.blogspot.com

57 of 61

�Функції NTILE()

Зауваження!

Якщо кількість рядків у секції не ділиться на integer_expression, формуються групи двох розмірів, що відрізняються на одиницю.

У порядку, заданому реченням OVER, групи більшого розміру йдуть перед групами меншого розміру.

Наприклад, якщо загальна кількість рядків дорівнює 53, а кількість груп дорівнює п'яти, перші три групи складатимуться з 11 рядків, а дві, що залишилися, - з 10.

З іншого боку, якщо загальне число рядків ділиться на число груп, рядки розподіляються рівномірно по всіх групах.

Наприклад, якщо загальна кількість рядків дорівнює 50 і задано п'ять груп, кожен контейнер складатиметься з 10 рядків.

​

Запит14. Розділити всі іспити на 4 частини

select *, NTILE(4) OVER(ORDER BY Mark DESC) as part

from dbo.AcademicResults

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

57

http://infdbis.blogspot.com

58 of 61

�Функції NTILE()

Запит15. Розділити всі іспити на 2 частини для кожної дисципліни

select *, NTILE(2) OVER(partition by IdSubject ORDER BY Mark DESC) as part

from dbo. AcademicResults

order by IdSubject

​

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

58

http://infdbis.blogspot.com

59 of 61

�Одночасне використання�distinct і top

​

Запит16.

select distinct top 2 Mark

from dbo. AcademicResults

order by Mark desc

​

Distinct працює першим, потім top

​

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

59

http://infdbis.blogspot.com

60 of 61

�Використання UPDATE з CASE

Запит17. У відношення Студент додано поле QuantityTest, яке буде призначене для зберігання інформації про кількість складених іспитів.

Занесіть у це поле позначку "<=2", якщо кількість складених іспитів менша за 2;

якщо їхня кількість понад 2, занесіть кількість складених іспитів;

якщо іспитів студент не складав узагалі, занесіть йому позначку "не складав"

Варіант 1

update Student set QuantityTest=

(select case

when COUNT(*)=0 then 'не складав'

when COUNT(*)<=2 then '<=2'

else cast(COUNT(*) as varchar(5))

end

from AcademicResults

where AcademicResults.IdStudent=Student.IdStudent)

​

Варіант 2

update Student set QuantityTest=

case when (select COUNT(*) from AcademicResults where AcademicResults.IdStudent=Student.IdStudent)=0 then 'не складав'

when (select COUNT(*) from AcademicResults where

AcademicResults.IdStudent =Student.IdStudent)<=2 then '<=2'

else cast( (select COUNT(*) from AcademicResults

where AcademicResults.IdStudent =Student.IdStudent) as varchar(5))

end

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

60

http://infdbis.blogspot.com

61 of 61

�ПЗ "Мережа готелів"

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

61

http://infdbis.blogspot.com