Функции SQL

Содержание

Слайд 2

А.М. Гудов

Групповые функции

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

А.М. Гудов Групповые функции Групповые функции работают с множествами строк и возвращают
на группу.
Групповые функции могут быть заданы в списках SELECT и предложениях HAVING.
Предложение GROUP BY в команде SELECT разбивает множество строк на группы.
Предложение HAVING исключает из результата некоторые группы.

Слайд 3

А.М. Гудов

Предложения GROUP BY и HAVING

Предложение GROUP BY делит строки на группы.
Предложение

А.М. Гудов Предложения GROUP BY и HAVING Предложение GROUP BY делит строки
HAVING исключает из рассмотрения некоторые группы.

SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

Слайд 4

А.М. Гудов

Групповые функции

AVG (DISTINCT|ALL|n)
COUNT (DISTINCT|ALL|expr|*)
MAX (DISTINCT|ALL|expr)
MIN (DISTINCT|ALL|expr)
STDDEV (DISTINCT|ALL|n)
SUM (DISTINCT|ALL|n)
VARIANCE (DISTINCT|ALL|n)

А.М. Гудов Групповые функции AVG (DISTINCT|ALL|n) COUNT (DISTINCT|ALL|expr|*) MAX (DISTINCT|ALL|expr) MIN (DISTINCT|ALL|expr)

Слайд 5

А.М. Гудов

Групповые функции: пример

Функции MAX и MIN применяются к данным любого типа.

SQL>

А.М. Гудов Групповые функции: пример Функции MAX и MIN применяются к данным
SELECT MIN(last_name), MAX(last_name)
2 FROM s_emp;

SQL> SELECT AVG(salary), MAX(salary),
2 MIN(salary), SUM(salary)
3 FROM s_emp
4 WHERE UPPER(title) LIKE 'SALES%';

Функции AVG и SUM применяются к столбцам с числовыми данными.

Слайд 6

А.М. Гудов

Функция COUNT: примеры

COUNT(*) возвращает количество строк в таблице.
COUNT(expr) возвращает количество строк

А.М. Гудов Функция COUNT: примеры COUNT(*) возвращает количество строк в таблице. COUNT(expr)
с определенными значениями (не NULL).

SQL> SELECT COUNT(*)
2 FROM s_emp
3 WHERE dept_id = 31;

SQL> SELECT COUNT(commission_pct)
2 FROM s_emp
3 WHERE dept_id = 31;

Слайд 7

А.М. Гудов

Предложение GROUP BY

Предложение GROUP BY разбивает строки таблицы на группы.
Если в

А.М. Гудов Предложение GROUP BY Предложение GROUP BY разбивает строки таблицы на
предложении SELECT заданы столбцы, их список должен использоваться и в предложении GROUP BY.
С помощью предложения ORDER BY можно изменить порядок сортировки, используемый по умолчанию.

SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

Слайд 8

А.М. Гудов

Без предложения GROUP BY

ID LAST_NAME DEPARTMENT -- --------- ---------- 2 Ngao 41 6 Urguhart 41 16 Maduro 41 17 Smith 41

Номер 41 повторяется четыре раза, т.к. является номером

А.М. Гудов Без предложения GROUP BY ID LAST_NAME DEPARTMENT -- --------- ----------
отдела для четырех служащих.

SQL> SELECT id, last_name, dept_id DEPARTMENT
2 FROM s_emp
3 WHERE dept_id = 41;

Слайд 9

А.М. Гудов

С предложением GROUP BY

Благодаря предложению GROUP BY на каждый отдел,
заданный в

А.М. Гудов С предложением GROUP BY Благодаря предложению GROUP BY на каждый
предложении WHERE, выводится одна строка, а функция COUNT(*) возвращает количество служащих в каждом выбранном отделе (группе).

SQL> SELECT dept_id, COUNT(*) ”Number” 2 FROM s_emp 3 WHERE dept_id = 41 4 GROUP BY dept_id;

DEPT_ID Number ------- ------ 41 4

Слайд 10

А.М. Гудов

Предложение GROUP BY: примеры

Количество клиентов в каждой категории по кредитному рейтингу.
Должности

А.М. Гудов Предложение GROUP BY: примеры Количество клиентов в каждой категории по
и месячная заработная плата для каждой должности.

SQL> SELECT credit_rating, COUNT(*) "# Cust"
2 FROM s_customer
3 GROUP BY credit_rating;

SQL> SELECT title, SUM(salary) PAYROLL
2 FROM s_emp
3 WHERE title NOT LIKE 'VP%'
4 GROUP BY title
5 ORDER BY SUM(salary);

Слайд 11

А.М. Гудов

Предложение GROUP BY

Все столбцы из списка SELECT, не входящие в групповые

А.М. Гудов Предложение GROUP BY Все столбцы из списка SELECT, не входящие
функции, должны быть включены в предложение GROUP BY.
Столбец, заданный в предложении GROUP BY, не обязательно должен быть задан в предложении SELECT.
Если столбец из предложения GROUP BY входит в список SELECT, результат имеет больше смысла.

SQL> SELECT title, MAX(salary)
2 FROM s_emp
3 GROUP BY title;

Слайд 12

А.М. Гудов

Недействительные запросы

Если предложение GROUP BY отсутствует или неправильно, выдается сообщение об

А.М. Гудов Недействительные запросы Если предложение GROUP BY отсутствует или неправильно, выдается
ошибке.
Все столбцы или выражения из списка SELECT, не являющиеся групповой функцией, должны быть включены в предложение GROUP BY.

SQL> SELECT region_id, COUNT(name)
2 FROM s_dept;
SELECT region_id, COUNT(name)
*
ERROR at line 1:
ORA-00937: not a single-group group function

Слайд 13

А.М. Гудов

Недействительные запросы

Предложение WHERE для исключения групп не используется.
Для исключения некоторых групп

А.М. Гудов Недействительные запросы Предложение WHERE для исключения групп не используется. Для
следует пользоваться предложением HAVING.

SQL> SELECT dept_id, AVG(salary)
2 FROM s_emp
3 WHERE AVG(salary) > 2000
4 GROUP BY dept_id;
WHERE AVG(salary) > 2000
*
ERROR at line 3:
ORA-00934: group function is not allowed here

Слайд 14

А.М. Гудов

Группы внутри групп

Для получения сводных результатов по нескольким группам и подгруппам

А.М. Гудов Группы внутри групп Для получения сводных результатов по нескольким группам
можно указать в предложении GROUP BY более одного столбца.
Порядок сортировки, используемый по умолчанию, определяется порядком столбцов в предложении GROUP BY.

SQL> SELECT dept_id, title, COUNT(*)
2 FROM s_emp
3 GROUP BY dept_id, title;

Слайд 15

А.М. Гудов

Вывод конкретных строк с помощью предложения WHERE

SQL> SELECT last_name, title 2 FROM s_emp 3 WHERE last_name

А.М. Гудов Вывод конкретных строк с помощью предложения WHERE SQL> SELECT last_name,
LIKE ’V%’;

LAST_NAME TITLE --------- ---------- Velasquez President

Ïðåäëîæåíèå WHERE (îãðàíè÷èâàåò ÷èñëî âûáèðàåìûõ ñòðîê)

Âûâîä äàííûõ î êîíêðåòíîì ñëóæàùåì â ñîîòâåòñòâèè ñ êðèòåðèÿìè â ïðåäëîæåíèè
WHERE.

Слайд 16

А.М. Гудов

SQL> COLUMN ”ANNUAL SALARY” FORMAT $99,999.99 SQL> SELECT title, 12 * AVG(salary) ”ANNUAL

А.М. Гудов SQL> COLUMN ”ANNUAL SALARY” FORMAT $99,999.99 SQL> SELECT title, 12
SALARY”, 2 COUNT(*) ”NUMBER OF EMPLOYEES” 3 FROM s_emp 4 GROUP BY title 5 HAVING COUNT(*) > 2;

Вывод конкретных групп с помощью предложения HAVING

TITLE ANNUAL SALARY NUMBER OF EMPLOYEES -------------------- -------------- ------------------- Sales Representative $17,712.00 5 Stock Clerk $11,388.00 10 Warehouse Manager $14,776.80 5

Предложение HAVING (исключение групп)

Вывод групп по должностям в соответствии с ограничениями в предложении HAVING.

Слайд 17

А.М. Гудов

Предложение HAVING

Предложение HAVING используется для дальнейшего ограничения количества групп.
Шаг 1: Группирование строк.
Шаг

А.М. Гудов Предложение HAVING Предложение HAVING используется для дальнейшего ограничения количества групп.
2: Применение групповых функций к группам.
Шаг 3: Вывод групп, удовлетворяющих условию предложения HAVING.

SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

Слайд 18

А.М. Гудов

Предложение HAVING: пример

Группа "President" в выходных данных отсутствует, т.к. не удовлетворяет

А.М. Гудов Предложение HAVING: пример Группа "President" в выходных данных отсутствует, т.к.
заданному критерию.

SQL> SELECT title, SUM(salary) PAYROLL
2 FROM s_emp
3 WHERE title NOT LIKE 'VP%'
4 GROUP BY title
5 HAVING SUM(salary) > 5000
6 ORDER BY SUM(salary);

Слайд 19

А.М. Гудов

Предложение HAVING: пример

Предложение GROUP BY можно использовать без указания групповой функции

А.М. Гудов Предложение HAVING: пример Предложение GROUP BY можно использовать без указания
в списке SELECT.
Если отбор строк производится по результатам групповой функции, то использование как предложения GROUP BY, так и предложения HAVING обязательно.

SQL> SELECT dept_id
2 FROM s_emp
3 GROUP BY dept_id
4 HAVING SUM(salary) > 4000;

Слайд 20

А.М. Гудов

Заключение

Имеется семь групповых функций: AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE.
С

А.М. Гудов Заключение Имеется семь групповых функций: AVG, COUNT, MAX, MIN, STDDEV,
помощью предложения GROUP BY создаются группы.
Некоторые группы исключаются с помощью предложения HAVING.

SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

Слайд 21

А.М. Гудов

Обзор практического занятия

Демонстрация запросов с использованием всех групповых функций, кроме STDDEV

А.М. Гудов Обзор практического занятия Демонстрация запросов с использованием всех групповых функций,
и VARIANCE.
Разбиение строк на группы для получения более, чем одного результата.
Исключение групп с помощью предложения HAVING.