ОТЧЕТ ПО ЛАБОРАТОРНОЙ РАБОТЕ №3
«Упорядочивание и группировка данных. Агрегирующие функции и итоговые поля. Графическая визуализация результатов запросов»
Выполнила:
Группа: ВТ – 40
Проверил:
I. Упорядочение данных
SELECT Films. number, Films. name, Films. cost, Films. time, Films. codk,; Films. regiser, Films. producer, Films. datep, Films. datev, Films. volume,; Films. codg, Films. vid, Films. year, Codc. country;
FROM films! films INNER JOIN films! codc ; ON Films. codc = Codc. codc
1.1 Сортировка по полям таблицы
Сортировка по одному полю:
Сортировка по названию:
ORDER BY Films. name
Сортировка по времени:
ORDER BY Films. time
Сортировка по дате выпуска:
ORDER BY Films. datev
Сортировка по логическому полю (боевик/не боевик):
ORDER BY Films. vid
1.2 Совместная сортировка по нескольким полям
Совместная сортировка по 2-м полям (логическое – боевик/не боевик, страна):
ORDER BY Films. vid, Codc. country
Совместная сортировка по 3-м полям (логическое – боевик/не боевик, страна, длительность):
ORDER BY Films. vid, Codc. country, Films. time
Выводы по совместной сортировке: при упорядочивании по нескольким полям одновременно, записи сначала сортируются по первому полю, далее в получившихся группах из одинаковых значений происходит сортировка по второму полю и т. д.
1.3 Упорядочивание по вычисляемым полям
По месяцу выпуска фильма
SELECT MONTH(Films. datev) AS MONTH; FROM films! codc INNER JOIN films! films ; ON Codc. codc = Films. codc;
ORDER BY 1
По возрастанию длины названия фильма:
SELECT LEN(ALLTRIM(Films. name)) as length;
FROM films! codc INNER JOIN films! films ; ON Codc. codc = Films. codc;
ORDER BY 1
По возрастанию количества слов в поле «название»:
SELECT (OCCURS(" ",ALLTRIM(Films. name))+1) as occur;
FROM films! codc INNER JOIN films! films ; ON Codc. codc = Films. codc;
ORDER BY 1
По возрастанию дробной части длительности:
SELECT Films. time - INT(Films. time) AS drob_time;
FROM films! codc INNER JOIN films! films ; ON Codc. codc = Films. codc;
ORDER BY 1
По последней букве названия фильма
SELECT RIGHT(ALLTRIM(Films. name),1);:
FROM films! codc INNER JOIN films! films ; ON Codc. codc = Films. codc;
ORDER BY 1
По возрастанию дня недели даты выпуска фильма:
SELECT DOW( _3.datеv) as dow;
FROM films! codc INNER JOIN films! films ; ON Codc. codc = Films. codc;
ORDER BY 1
По алфавиту названия дня недели выпуска фильма:
SELECT CDOW( _3.datev) as day;
FROM films! codc INNER JOIN films! films ; ON Codc. codc = Films. codc;
ORDER BY 1
Выводы: при сортировке по вычисляемым полям следует задать в Expression Builder выражение, описывающее вычисляемое поле, после чего во вкладке Order By задать данное поле как поле, подлежащее сортировке. В данном случае сортировка будет производиться по вычисляемому выражению.
1.3.1 Упорядочение по сложным ключам
Совместная сортировка по типу фильма (боевик/не боевик), длине названия, по длительности фильма:
SELECT IIF(Films. vid,”Боевик”+SPACE(3),”Не боевик”) + STR(LEN(ALLTRIM(Films. name)),2) + STR(Films. time,3 );
FROM animals!3 _3; ORDER BY 1
Совместное упорядочение по кварталу выпуска фильма с символьным днем недели, длительности фильма:
STR( MONTH(Films. datev)/3.3+1)+ SPACE(9 - LEN(CDOW(Films. datev))) + CDOW(Films. datev) AS Day;) + STR(INT(Films. time),5) as month;
ORDER BY 6
Выводы: каждый тип данных можно привести к символьному выражению, на этом основано использование сложных ключей. Сложный ключ – это вычисляемое поле, содержащее информацию из нескольких полей(текстовые, логические, поля типа дата, числовые), собранную по определенным правилам. Сложные ключи дают нам возможность создания сложно-смысловых выражений, хранящих логическую информацию о нескольких полях таблицы. При создании выражения, описывающее сложный ключ, следует выравнивать поля по максимально возможной длине в конкретной задаче с помощью функции STR(n1,n2)
II. ГРУППИРОВКА ДАННЫХ
2.2 Формирование основного поля группировки.
SELECT Films. name, Codc. country, Codc. codc,;FROM films! codc INNER JOIN films! films ;
ON Codc. codc = Films. codc; GROUP BY Films. codc
Совместное использование группировки, фильтрации и сортировки.
Сортировка по названию фильма:
SELECT Films. name, Codc. country, Codc. codc, Films. codc;
FROM films! codc INNER JOIN films! films ;
ON Codc. codc = Films. codc;
ORDER BY Films. name
Группировка по коду страны:
SELECT Films. name, Codc. country, Codc. codc, Films. codc;
FROM films! codc INNER JOIN films! films ;
ON Codc. codc = Films. codc;
GROUP BY Films. codc
Фильтрация по коду страны(код страны>3)
SELECT Films. name, Codc. country, Codc. codc, Films. codc;
FROM films! codc INNER JOIN films! films ;
ON Codc. codc = Films. codc;
WHERE Films. codc > 3
Фильтрация и сортировка по коду страны:
SELECT Films. name, Codc. country, Codc. codc, Films. codc;
FROM films! codc INNER JOIN films! films ;
ON Codc. codc = Films. codc;
WHERE Films. codc > 3;
ORDER BY Codc. codc
Приоритет: 1- фильтрация, 2-сортировка.
Фильтрация и группировка по коду страны:
SELECT Films. name, Codc. country, Codc. codc, Films. codc;
FROM films! codc INNER JOIN films! films ;
ON Codc. codc = Films. codc;
WHERE Films. codc > 3;
GROUP BY Codc. codc
Приоритет: 1-фильтрация, 2-группировка.
Сортировка по названию фильма с группировкой по коду страны:
SELECT Films. name, Codc. country, Codc. codc, Films. codc;
FROM films! codc INNER JOIN films! films ;
ON Codc. codc = Films. codc;
GROUP BY Codc. codc
ORDER BY Films. name
Приоритет: 1-группировка, 2-сортировка
Совместное применение фильтрации(код страны > 3), группировки(по коду страны) и сортировки(по названию фильма):
SELECT Films. name, Codc. country, Codc. codc, Films. codc;
FROM films! codc INNER JOIN films! films ;
ON Codc. codc = Films. codc;
WHERE Films. codc > 3;
GROUP BY Films. codc;
ORDER BY Films. name
Вывод: Из вышеприведенных SQL-кодов видно, что между группировкой и сортировкой предпочтение отдается группировке, между фильтрацией и группировкой - фильтрации, между фильтрацией и сортировкой – фильтрации,
при совместном использовании группировки, сортировки и фильтрации наивысший компилятор формирует SQL-код в порядке: фильтрация, группировка и сортировка, независимо от того, какой порядок фильтрации, группировки и сортировки мы устанавливаем самостоятельно.
2.3. Получение сопутствующих значений при группировке
SELECT Films. name, Codc. country, Codc. codc, Films. codc, Films. time; FROM films! codc INNER JOIN films! films ;
ON Codc. codc = Films. codc; GROUP BY Films. codc; ORDER BY Films. time
Вывод: при использовании функции группировки и включении в запрос сопутствующего поля, в данное поле поместиться последнее встретившиеся значение (в естественной исходной последовательности данных)
2.4 Получение итоговых значений
Число повторений в таблице каждой страны:
SELECT Films. name, Codc. country, Films. codc, Films. time,; COUNT(Films. name) as count; FROM films! codc INNER JOIN films! films ; ON Codc. codc = Films. codc; GROUP BY Films. codc;
Сумма тиража в каждой группе:
SELECT Films. name, Codc. country, Films. codc, Films. time,; SUM(Films. volume) as volume; FROM films! codc INNER JOIN films! films ; ON Codc. codc = Films. codc; GROUP BY Films. codc
Среднеарифметическое значение тиража в группах:
SELECT Films. name, Codc. country, Films. codc, Films. time,; AVG(Films. volume) as volume; FROM films! codc INNER JOIN films! films ; ON Codc. codc = Films. codc; GROUP BY Films. codc
Максимальное значение тиража в группе:
SELECT Films. name, Codc. country, Films. codc, Films. time,; MAX(Films. volume) as volume; FROM films! codc INNER JOIN films! films ; ON Codc. codc = Films. codc; GROUP BY Films. codc
Минимальное значение тиража в группе:
SELECT Films. name, Codc. country, Films. codc, Films. time,; MIN(Films. volume) as volume; FROM films! codc INNER JOIN films! films ; ON Codc. codc = Films. codc; GROUP BY Films. codc
Самая поздняя дата выпуска фильма в группе:
SELECT Films. name, Codc. country, Films. codc, Films. time,; MAX(Films. datev) as volume; FROM films! codc INNER JOIN films! films ; ON Codc. codc = Films. codc; GROUP BY Films. codc
Самая ранняя дата выпуска фильма в группе:
SELECT Films. name, Codc. country, Films. codc, Films. time,; MIN(Films. datev) as volume; FROM films! codc INNER JOIN films! films ; ON Codc. codc = Films. codc; GROUP BY Films. codc
Включение в итоговую таблицу нескольких агрегирующих полей
MIN (Films. datev) AS date_min, MAX(Films. datev) AS date_max; MIN(Films. volume) AS min_vol; MAX(Films. volume) AS max_vol, count(*); FROM films! codc INNER JOIN films! films ; ON Codc. codc = Films. codc; GROUP BY Films. codc
Выводы: функции итоговых значений в группах позволяют нам получить количественные и качественные значения в каждой группе. Функция Count() игнорирует передаваемое ей значение аргумента, остальные функции данной группы работают с конкретными значениями полей.
2.5 Использование опции Having в команде Select языка SQL
Подсчет количества вхождения значения справочника в оперативную таблицу, и отбор только тех записей результирующей таблицы, в которых количество вхождений >2
SELECT Films. name, Codc. country, Films. regiser, Films. time,; COUNT(*) as count;
FROM films! codc INNER JOIN films! films ; ON Codc. codc = Films. codc;
GROUP BY Films. codc;
HAVING count > 2
Выводы: при фильтрации сгруппированного запроса, применяя для фильтрации опцию WHERE, мы получаем в результате сначала результат фильтрации, а затем - группировки, т. к. WHERE имеет больший приоритет перед GROUP BY. Для того, приоритет группировки был выше приоритета фильтрации, используется опция HAVING.
GROUP BY перед HAVING имеет более высокий приоритет.
2.6 Графическая визуализация результатов запросов.
2.6.1 Выводы по мастеру графических диаграмм и Cross-Tab Wizard
Мастер перекрестных таблиц (Cross-Tab Wizard) позволяет выбрать таблицу или представление и построить специальную таблицу. Это таблицу можно построить на основе только одной таблицы, поэтому, как правило, прежде требуется сформировать представление, в котором будут представлены все требуемые поля.
Для перекрестной таблицы требуется три поля: первое размещается по горизонтали, второе по вертикали, третье внутри таблицы. Также следует указать, требуется ли дополнительный столбец агрегирующих данных, и, если требуется, тип агрегирующей информации (сумма, число ячеек, содержащих данные, процентное соотношение от общего количества)
Рассмотрим алгоритм работы Cross-Tab запроса на примере следующих полей: название фильма, страна, в которой он выпущен, и режиссер. В итоговой таблице по вертикали располагаются неповторяющиеся названия стран, по горизонтали - названия всех фильмов, а на пересечении столбцов и строк располагаются режиссеры, причем, только если такое животное, существует в исходной таблице, в противном случае, ячейка остается пустой, либо заполняется значением NULL (зависит от настроек мастера). В дополнительном столбце будет отражена статистическая информация по каждой получившейся строке.
Мастер графических диаграмм позволяет построить различные виды графиков на основе одной таблицы или одного представления. По оси X можно выбрать одно поле любого типа данных, по оси Y несколько полей только числовых типов данных. Если по Y выбрано несколько полей, все графики отображаются вместе на одной форме. Типы графиков, поддерживаемых мастером: это двухмерный или трехмерный график, двухмерная или трехмерная круговая диаграмма, линейный график, столбчатая диаграмма и др.
2.6.2. Построение столбчатой диаграммы
Количество вхождений каждой страны (из справочника) в оперативную таблицу
2.6.3. Построение секторной диаграммы
Процентное соотношение года выпуска фильма
