Группировка данных с помощью функций агрегирования 


Мы поможем в написании ваших работ!



ЗНАЕТЕ ЛИ ВЫ?

Группировка данных с помощью функций агрегирования



Подведение итогов по столбцам таблиц, а именно правильно - "агрегатные функции". Агрегатные (или их еще называют СТАТИЧЕСКИЕ) функции, позволяют подводить промежуточные итоги, оперируя числовыми или иного вида исчислимыми столбцами. Агрегатная функция принимает в качестве аргумента столбец таблицы целиком и возвращает одно значение. Например, агрегатная функция AVG() принимает столбец и вычисляет среднее значение всех чисел находящихся в столбце. То есть, берет все записи этого столбца, складывает все числа в этом столбце, а потом делит полученное на количество слагаемых

Пример 15: использование функций агрегирования

select AVG(min_salary), avg(max_salary)

from jobs

 

Рисунок №34 – Группировка данных с функцией агригирования

 

 

Таблица 3 – Функции агрегирования SQL Server

Функция Результат
AVG Возвращает среднее арифметическое для значений выражения; null-значения игнорируются
COUNT Возвращает количество элементов в выражении (равное количеству строк)
COUNT_BIG То же самое, что и COUNT, но результат имеет тип данных Bigint, а не Int
GROUPING Возвращает специальную дополнительную колонку; применяется, только когда предложение GROUP BY содержит операцию CUBE или ROLLUP.
MAX Возвращает наибольшее значение из значений выражения
MIN Возвращает наименьшее значение из значений выражения
STDEV Возвращает статистическое стандартное отклонение для всех величин выражения. Эта функция предполагает, что выражения, используемые в расчёте, являются образцом всей совокупности данных
STDEVP Возвращает статистическое стандартное отклонение для всех величин выражения. Эта функция предполагает, что выражения, используемые в расчёте, являются всей совокупностью данных
SUM   Возвращает сумму всех значений в выражении (либо по всем записям, либо только по уникальным)
VAR Возвращает статистическое отклонение для всех значений выражения. Эта функция предполагает, что выражения, используемые в расчёте, являются образцом всей совокупности данных
VARP Возвращает статистическое отклонение для всех значений выражения. Эта функция предполагает, что выражения, используемые в расчёте, являются всей совокупностью данных

 

Функции SUM и AVG могут использоваться только с числовыми выражениями. Остальные функции можно применять с выражениями любого типа. Использование опции DISTINCT позволяет не учитывать дублирующие друг друга значения из выборки перед применением функций SUM, AVG, COUNT. Использовать функции агрегирования в предложении WHERE нельзя. Тем не менее, можно задействовать предложение WHERE для выбора. А вот, так это выглядит в представлении синтаксиса команд:

SUM (выражение) DISTINCT имя столбца AVG(выражение) DISTINCT имя столбца MIN (выражение) MAX (выражение) COUNT (имя столбца)DISTINCTCOUNT(*)

 

Аргументом агрегирования может служить не только имя столбца, но и любое математическое выражение. Например, как в следующем запросе: Каков средний процент выполнения плана в вашей компании?

Пример 16:

select AVG(max_salary/min_salary)

from jobs

Рисунок №35 – Результат выборки

Или же

Пример 17:

select AVG(max_salary/min_salary) sr_otn

from jobs

Рисунок 17

Вычисление суммы столбцов (пример 18):

select sum(max_salary) max_sum, sum(min_salary) min_sum

from jobs

Рисунок №36 – Результат вычисления суммы столбцов

Это снова числа. Так же можно работать со строками. Но учтите, что разные виды кодировок, применяемые в серверах SQL, могут давать различные результаты. Например, прописные буквы в моей системе идут первыми, потом строчные, а например в системе EBCDIC фирмы IBM все наоборот. А как, например, просто подсчитать количество записей? Для этого есть функция COUNT(). Эта функция всегда возвращает целое число, не зависимо от типа передаваемых данных.

Пример 19:

select COUNT(max_salary)

from jobs

Рисунок №37 – Результат выборки

 

Многотабличные запросы

Объединять таблицы можно с помощью предложения WHERE. Общий синтаксис объединения следующий:

SELECT < Список столбцов>FROM <Имя таблицы 1>, <Имя таблицы 2> [, <Имя таблицы 3>].....WHERE [<Имя таблицы 1>.]<Имя столбца> <Оператор объединения> [<Имя таблицы 2>.]<Имя столбца>

Предложение FROM должно включать, по крайней мере, две таблицы, а столбцы, указанные в предложении WHERE, должны быть совместимы. Когда столбцы объединения имеют одинаковые имена, необходимо указать перед ними названия таблиц с точкой, для того чтобы их можно было различить.Обычно при связывании двух или более таблиц используют оператор «=», но можно задействовать и другие операторы. В таблице 4 приводятся операторы, которые можно использовать для связи таблиц.

Таблица 4 – Операторы связывания таблиц

Символ Значение
= Равно
> Больше чем
< Меньше чем
>= Не меньше
<= Не больше
!=, <> Не равно
!> Не больше чем
!< Не меньше чем
=* Внешнее правое соединение
*= Внешнее левое соединение

В некоторых случаях необходимо объединить те или иные записи таблицы с другими записями той же таблицы. Такое объединение проще представить как объединение двух копий таблиц. По сути, запрос такого рода ничем не отличается от других многотабличных запросов, за исключением того, что таблицы абсолютно идентичны. Для того чтобы различать поля в копиях (на самом деле никаких копий не создается, но так понятнее), можно воспользоваться временными именами, которые присваивают в предложении FROM. Их чаще называют псевдонимами, но, если вам встретится «переменная диапазона» или «переменная корреляции», не пугайтесь – вы имеете дело с одним и тем же объектом. В предложении FROM вы отделяете пробелом имя таблицы от псевдонима. Итак, пример 20 иллюстрирует простой запрос из разных таблиц:

select region_name, Country_ID, Country_name

from regions,countries

 

Рисунок №38 - Результаты выборки

 

Пример 21 - Многотабличные запрос с одинаковыми столбцами

select regions.region_id, region_name, country_name, country_id

from regions, countries

Рисунок №39 – Результаты выборки примера 21

 

Пример 22 - исключает совпадения, используя индивидуальное имя таблицы

select countries.*, region_name

from countries, regions

 

Рисунок №40 – Результаты выборки примера №22

 

Оно же с условием (пример 23):

select countries.region_id, country_name, region_name

from countries, regions

where countries.region_id =3

Триггеры

Триггером называется последовательность команд, выполняемая автоматически каждый раз при возникновении определенного события. Стандарт SQL99 предусматривает наличие триггеров, но никак не описывает их синтаксис, поэтому каждый производитель СУБД реализует триггеры по-своему. В частности, в Oracle триггеры пишутся на языке PL/SQL, что позволяет выполнять внутри триггеров достаточно сложные операции.

Создание триггеров

Триггер срабатывает при возникновении некоторого события. В Oracle таким собы­тием является изменение (insert, update или delete) данных в таблице. Oracle позволяет также определить триггеры, срабатывающие при изменении схемы базы данных (create, alter или drop), при запуске или останове сервера, при начале или завершении сессии. Oracle позволяет создавать два типа триггеров: триггер на уровне команды (statement trigger) и триггер на уровне строки (row trigger). Первые выполняются всякий раз при вы­полнении команды, независимо от того, сколько строк затрагивает команда, пусть даже и ни одной. Вторые выполняются для каждой затронутой изменением строки. Чтобы указать, что триггер должен срабатывать для каждой строки, используется фраза for each row. В этой же фразе после ключевого слова when можно указать дополнительные условия срабатывания триггера.По времени срабатывания триггеры делятся на две категории: до выполнения команды (before), и после (after). При выдаче команды на изменение данных Oracle выполняет сле­дующую последовательность действий:

-Выполняются триггеры before на уровне команды (before statement);

-Для каждой строки, на которую действует команда

-Выполняются триггеры before на уровне строки (before each row);

-Выполняется изменение данных. При этом строка блокируется, и блокировка не от­пускается до конца транзакции;

-Выполняется проверка правил целостности для измененной строки;

-Выполняются триггеры after на уровне строки (after each row).

-Завершается проверка правил целостности;

-Выполняются триггеры after на уровне команды (after statement).

Определение этой модели рекурсивно, так как команды внутри триггеров могут изме­нять данные в других таблицах, вызывая тем самым выполнение триггеров для них. Если для таблицы создано несколько одинаковых (срабатывающих на одно и то же событие) тригге­ров, то порядок их выполнения непредсказуем. Триггеры before и after можно создавать только для физических таблиц. Кроме того, в Oracle 8 появилась возможность создавать триггеры для представлений. С их помощью мож­но организовать изменение представлений в случаях, когда это не поддерживается автома тически. Такие триггеры составляют третью категорию по времени срабатывания — «вме­сто» (instead of), и всегда работают на уровне строки. При выполнении команды, изменяю­щей данные представления, на самом деле вызывается триггер.

 

В основном триггеры используются для:

1. Реализации сложных ограничений целостности данных, которые невозможно осуществить через описательные ограничения, устанавливаемые при создании таблиц.

2. Организации всевозможных видов аудита. Например, слежения за изменениями в какой-либо важной таблице БД.

3. Автоматического оповещения других модулей о том, что делать в случае изменения информации содержащейся в таблице БД.

4. Для реализации так называемых "бизнес правил".

5. Для организации каскадных воздействий на таблицы БД.

 

Синтаксис команды для создания триггера, следующий:

CREATE [OR REPLACE] TRIGGER имя_триггера

BEFORE | AFTER активизирующее_событие ON ссылка_на_таблицу

FOR EACH ROW [WHEN условие_срабатывания]

тело_триггера

Где:

- имя_триггера - собственно имя вашего триггера.

- активизирующее_событие - указывает момент активации триггера BEFORE до срабатывания оператора DML, AFTER после срабатывания оператора DML.

- ссылка_на_таблицу - собственно таблица, для которой создан триггер.

- FOR EACH ROW - если указано активируется от воздействия на строку если нет, то после любого оператора DML.

- условие_срабатывания - если TRUE триггер срабатывает, если FALSE нет.

- тело_триггера - собственно тело триггера.

 

Для полноты картины определимся с таким фактом, что триггеры имеют собственное пространство имен (namespace). Что это означает, само понятие namespace - применимо вообще во многих языках программирования. В пределах одного пространства имен не может быть двух функций или процедур с одинаковым именем! Так вот, так как пространство имен у триггеров свое, то может иметь место ситуация, когда какой-либо триггер имеет тоже имя, что и процедура или функция в пределах одной схемы (не путать с пространством имен это разные вещи!). Но двух триггеров с одинаковым именем не бывает. Так же триггер, может иметь имя совпадающее с именем таблицы, для которой он создан. Но лучше этого не делать, а дать триггеру имя указывающее на то, что он производит. Вообще, по моему мнению, такое понятие как имя для триггера по большому счету архаично. Ведь триггер нельзя "позвать" из процедуры или функции - это запрещено. Можно было сделать что-то вроде универсальной цифровой маркировки. Хотя имя дает триггеру осмысленность, что улучшает чтение кода БД. Теперь давайте посмотрим на типы триггеров и моменты срабатывания и что все это значит (таблица 5). Итак:

 

Таблица 5 - Типы триггеров и моменты срабатывания

Категории Значение Комментарии
Оператор INSERT, UPDATE, DELETE Определяет какой оператор DML вызывает активацию (firing) триггера.
Момент времени BEFORE, AFTER Определяет момент активации триггера: до или после выполнения оператора.
Уровень Строка или оператор Если триггер является строковым он активируется один раз для каждой из строк, на которую воздействует оператор вызывающий срабатывания триггера (опция FOR EACH ROW). Если триггер является операторным то он активируется один раз до или после оператора.

 

Приведем теперь пример 24 - создание триггера. Создадим вначале тестовую табличку:

CREATE TABLE test(

USAL VARCHAR2(50),

TISP DATE)

 

 

Рисунок №41- Тестовая таблица

 

Затем создадим уже сам триггер:

CREATE OR REPLACE TRIGGER testTrg

AFTER INSERT OR DELETE OR UPDATE ON regions

DECLARE

BEGIN

INSERT INTO test(USAL, TISP)

VALUES(USER, SYSDATE);

END testTrg;

 

Рисунок №42 – Выполнение команды создания триггера

 

 

Теперь вставим какое-либо значении в нашу таблицу:

INSERT INTO REGIONS (region_id,region_name)

values(7,'test7')

 

Рисунок №43 – Результат работы

 

Проверяем работу триггера, просмотрев нашу новую табличку:

SELECT USAL, TO_CHAR(TISP,'DD/MM/YYYY, HH24:MI:SS') TISM

FROM test

 

 

Рисунок №44 – Просмотр результата

 

Мы получили, что триггер работает. Теперь посмотрим как же он выглядит и как определен:

SELECT TRIGGER_NAME, TRIGGER_TYPE, TABLE_NAME, TRIGGERING_EVENT

FROM USER_TRIGGERS

WHERE TRIGGER_NAME = 'TESTTRG'

 

Рисунок №45 – Просмотр определение триггера

 

Если необходимо его временно отключить то мы пишем:

 

ALTER TRIGGER TESTTRG DISABLE

Рисунок 47 – Отключение триггера

 

Триггер отключился. Если обновить данные, то ничего не произойдет

Ну а удаляется триггер операцией:

DROP TRIGGER TESTTRG

 

Рисунок 48 – Удаление триггера

 

3. Задание для самостоятельной работы

1. Согласно предложенному вам варианту осуществите выборку данных:

- по условию из одной таблицы;

- из нескольких таблиц с сортировкой по условию;

- всех столбцов из таблицы;

- с использованием предикатов like, between;

- с использованием выражения order by;

- с использованием функций агрегирования.

 

2. Создайте любой из триггеров: вставки, модификации или удаления.

3. Произведите объединение выборок.

4 Контрольные вопросы

1. Общая структура команды SELECT.

2. Где и для каких целей применяется выборка?

3. Как просмотреть результаты выборки?

4. Приведите примеры условий формирования условий отбора?

5. Перечислить операторы, которые могут применяться в предложении WHERE?

6. Дайте определения функции агрегирования.

7. Перечислить функции агрегирования, опишите их назначение?

8. Какое предложение применяется для сортировки данных выборки, условия его применения?

9. Каким образом осуществляется выборка из нескольких страниц?

10. Приведите пример создания таблицы на основе выборки.

11. Дайте определения триггеру.

12. Приведите примеры возможных триггеров?

13. Описать синтаксис набора команд создания триггеров?

14. Что необходимо учитывать при использовании триггеров?

 

 


 

Лабораторная работа №5



Поделиться:


Последнее изменение этой страницы: 2017-02-09; просмотров: 499; Нарушение авторского права страницы; Мы поможем в написании вашей работы!

infopedia.su Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав. Обратная связь - 3.89.72.221 (0.081 с.)