Презентация на тему "Группировка данных в запросах" по дисциплине Технология разработки и защиты баз данных
презентация к уроку

Слайд 1

Слайд 2

АГРЕГАТНЫЕ ФУНКЦИИ Агрегатные функции в обрабатывают набор значений и возвращают одно значение. Обычно они используются с оператором SELECT и могут применяться к различным столбцам в таблице. AVG : вычисляет среднее значение SUM : вычисляет сумму значений MIN : вычисляет наименьшее значение MAX : вычисляет наибольшее значение COUNT : вычисляет количество строк в запросе 2

Слайд 3

АГРЕГАТНЫЕ ФУНКЦИИ Все агрегатные функции принимают в качестве параметра выражение, которое представляет критерий для определения значений. Зачастую, в качестве выражения выступает название столбца, над значениями которого надо проводить вычисления. Выражения в функциях AVG и SUM должно представлять числовое значение (например, столбец, который хранит числовые значения). Выражение в функциях MIN , MAX и COUNT может представлять числовое или строковое значение или дату. Все агрегатные функции за исключением COUNT(*) игнорируют значения NULL. 3

Слайд 4

АГРЕГАТНЫЕ ФУНКЦИИ Id ProductName Manufacturer ProductCount Price 1 iPhone X Apple 3 76000 2 iPhone 8 Apple 2 51000 3 iPhone 7 Apple 5 32000 4 Galaxy S9 Samsung 2 56000 5 Galaxy S8 Samsung 1 46000 6 Honor 10 Huawei 5 28000 7 Nokia 8 HMD Global 6 38000 Найдем среднюю цену товаров в таблице Products SELECT AVG( Price ) AS Average_Price FROM Products ; Average_Price 46714.2857 Результат Для поиска среднего значения в качестве выражения в функцию передается столбец Price . Для получаемого значения устанавливается псевдоним Average_Price , хотя в принципе устанавливать псевдоним необязательно. 4

Слайд 5

АГРЕГАТНЫЕ ФУНКЦИИ . AVG На этапе выборки можно применять фильтрацию. Например, найдем среднюю цену для товаров определенного производителя : Также можно находить среднее значение для более сложных выражений. Найдем среднюю сумму всех товаров, учитывая их количество : Id ProductName Manufacturer ProductCount Price 1 iPhone X Apple 3 76000 2 iPhone 8 Apple 2 51000 3 iPhone 7 Apple 5 32000 4 Galaxy S9 Samsung 2 56000 5 Galaxy S8 Samsung 1 46000 6 Honor 10 Huawei 5 28000 7 Nokia 8 HMD Global 6 38000 SELECT AVG( Price * ProductCount ) FROM Products ; SELECT AVG( Price ) FROM Products WHERE Manufacturer=' Apple ' ; 5

Слайд 6

АГРЕГАТНЫЕ ФУНКЦИИ . COUNT Функция C OUNT вычисляет количество строк в выборке. Есть две формы этой функции. Первая форма COUNT ( * ) подсчитывает число строк в выборке : Вторая форма функции вычисляет количество строк по определенному столбцу, при этом строки со значениями NULL игнорируются : COUNT(* ) 7 Результат SELECT COUNT( * ) FROM Products ; Id ProductName Manufacturer ProductCount Price 1 iPhone X Apple 3 76000 2 iPhone 8 Apple 2 51000 3 iPhone 7 Apple 5 32000 4 Galaxy S9 Samsung 2 56000 5 Galaxy S8 Samsung 1 46000 6 Honor 10 Huawei 5 28000 7 Nokia 8 6 38000 SELECT COUNT ( Manufacturer ) FROM Products ; Результат COUNT( Manufacturer ) 6 6

Слайд 7

7 Функции Min и Max вычисляют минимальное и максимальное значение по столбцу соответственно. Например, найдем минимальную цену среди товаров: SELECT MIN ( Price ) , MAX( Price ) FROM Products ; Данные функции также игнорируют значения NULL и не учитывают их при подсчете. MIN(Price) MAX(Price) 28000 76000 Результат АГРЕГАТНЫЕ ФУНКЦИИ . MIN и MAX Id ProductName Manufacturer ProductCount Price 1 iPhone X Apple 3 76000 2 iPhone 8 Apple 2 51000 3 iPhone 7 Apple 5 32000 4 Galaxy S9 Samsung 2 56000 5 Galaxy S8 Samsung 1 46000 6 Honor 10 Huawei 5 28000 7 Nokia 8 HMD Global 6 38000

Слайд 8

АГРЕГАТНЫЕ ФУНКЦИИ . SUM Функция Sum вычисляет сумму значений столбца. Например , подсчитаем общее количество товаров : SELECT SUM( ProductCount ) FROM Products ; Результат Также вместо имени столбца может передаваться вычисляемое выражение. Например, найдем общую стоимость всех имеющихся товаров: SELECT SUM( ProductCount * Price ) FROM Products ; 8 Id ProductName Manufacturer ProductCount Price 1 iPhone X Apple 3 76000 2 iPhone 8 Apple 2 51000 3 iPhone 7 Apple 5 32000 4 Galaxy S9 Samsung 2 56000 5 Galaxy S8 Samsung 1 46000 6 Honor 10 Huawei 5 28000 7 Nokia 8 HMD Global 6 38000 SUM( ProductCount ) 24

Слайд 9

АГРЕГАТНЫЕ ФУНКЦИИ По умолчанию все вышеперечисленных пять функций учитывают все строки выборки для вычисления результата. Но выборка может содержать повторяющие значения. Если необходимо выполнить вычисления только над уникальными значениями, исключив из набора значений повторяющиеся данные, то для этого применяется оператор DISTINCT . SELECT COUNT( DISTINCT Manufacturer) FROM Products ; Результат Manufacturer Apple Samsung Huawei HMD Global Id ProductName Manufacturer ProductCount Price 1 iPhone X Apple 3 76000 2 iPhone 8 Apple 2 51000 3 iPhone 7 Apple 5 32000 4 Galaxy S9 Samsung 2 56000 5 Galaxy S8 Samsung 1 46000 6 Honor 10 Huawei 5 28000 7 Nokia 8 HMD Global 6 38000 9

Слайд 10

АГРЕГАТНЫЕ ФУНКЦИИ Объединим применение нескольких функций: SELECT COUNT( * ) AS ProdCount , SUM( ProductCount ) AS TotalCount , MIN( Price ) AS MinPrice , MAX( Price ) AS MaxPrice , AVG( Price ) AS AvgPrice FROM Products ; Агрегатные функции, такие как COUNT(), SUM(), AVG(), MAX() и MIN(), являются мощными инструментами для обобщения и анализа данных. Они позволяют выполнять сложные вычисления и получать значимую информацию из больших наборов данных. Понимание того, как эффективно использовать эти функции, может расширить ваши возможности по анализу данных. 10

Слайд 11

Оператор GROUP BY используется в SELECT- предложении для сбора данных по нескольким записям и группировки результатов по одному или нескольким столбцам. SELECT c1, c2,..., cn , aggregate_function (ci) FROM table_name [ WHERE conditions] GROUP BY c1 , c2 ,..., cn ; ГРУППИРОВКА ДАННЫХ. ОПЕРАТОР GROUP BY 11

Слайд 12

Для минимальной группировки достаточно использовать SELECT, FROM и GROUP BY. Остальные операторы — опциональны. Оператор GROUP BY позволяет сгруппировать данные по уникальному значению определенного столбца. SELECT Manufacturer FROM Products GROUP BY Manufacturer ; Manufacturer Apple Samsung Huawei HMD Global Id ProductName Manufacturer ProductCount Price 1 iPhone X Apple 3 76000 2 iPhone 8 Apple 2 51000 3 iPhone 7 Apple 5 32000 4 Galaxy S9 Samsung 2 56000 5 Galaxy S8 Samsung 1 46000 6 Honor 10 Huawei 5 28000 7 Nokia 8 HMD Global 6 38000 Результат Products ГРУППИРОВКА ДАННЫХ. ОПЕРАТОР GROUP BY 12

Слайд 13

ГРУППИРОВКА ДАННЫХ. ОПЕРАТОР GROUP BY При использовании с предложением GROUP BY агрегатные функции могут выполнять вычисления для каждой группы строк, имеющих общее значение в указанном столбце. Это позволяет получать сводную статистику для каждой группы, а не для всего набора данных . SELECT Manufacturer, COUNT ( ProductCount ) AS ModelsCount FROM Products GROUP BY Manufacturer ; Manufacturer ModelsCount Apple 3 Samsung 2 Huawei 1 HMD Global 1 Результат Id ProductName Manufacturer ProductCount Price 1 iPhone X Apple 3 76000 2 iPhone 8 Apple 2 51000 3 iPhone 7 Apple 5 32000 4 Galaxy S9 Samsung 2 56000 5 Galaxy S8 Samsung 1 46000 6 Honor 10 Huawei 5 28000 7 Nokia 8 HMD Global 6 38000 Products 13

Слайд 14

14 Manufacturer ModelsCount Apple 3 Samsung 2 HMD Global 1 Результат Id ProductName Manufacturer ProductCount Price 1 iPhone X Apple 3 76000 2 iPhone 8 Apple 2 51000 3 iPhone 7 Apple 5 32000 4 Galaxy S9 Samsung 2 56000 5 Galaxy S8 Samsung 1 46000 6 Honor 10 Huawei 5 28000 7 Nokia 8 HMD Global 6 38000 Products Следует учитывать, что выражение GROUP BY должно идти после выражения WHERE , но до выражения ORDER BY . SELECT Manufacturer, COUNT (*) AS Models Count FROM Products WHERE Price > 30000 GROUP BY Manufacturer ORDER BY ModelsCount DESC ; ГРУППИРОВКА ДАННЫХ. ОПЕРАТОР GROUP BY

Слайд 15

15 Оператор GROUP BY может выполнять группировку по множеству столбцов. Например, узнаем общий объем продаж по каждому продукту за каждый день, можно. product_name sale_date amount Product A 2024-08-01 150.00 Product B 2024-08-01 200.00 Product A 2024-08-02 170.00 Product B 2024-08-02 220.00 Product A 2024-08-03 180.00 Product B 2024-08-02 17 0.00 SELECT product_name , sale_date , SUM ( amount ) AS total_sales FROM sales GROUP BY product_name , sale_date ; product_name sale_date total_sales Product A 2024-08-01 150.00 Product A 2024-08-02 170.00 Product A 2024-08-03 180.00 Product B 2024-08-01 200.00 Product B 2024-08-02 390 . 0 0 sales Результат ГРУППИРОВКА ДАННЫХ. ОПЕРАТОР GROUP BY

Слайд 16

16 ГРУППИРОВКА ДАННЫХ. ФИЛЬТРАЦИЯ ГРУПП. HAVING О ператор HAVING используется в сочетании с оператором GROUP BY, чтобы ограничить группы возвращаемых строк только тех, чье условие TRUE. SELECT expression1, expression2, ... expression_n , aggregate_function (expression) FROM tables [ WHERE conditions] GROUP BY expression1, expression2, ... expression_n HAVING condition;

Слайд 17

17 ГРУППИРОВКА ДАННЫХ. ФИЛЬТРАЦИЯ ГРУПП. HAVING Manufacturer ModelsCount Apple 3 Samsung 2 Результат Id ProductName Manufacturer ProductCount Price 1 iPhone X Apple 3 76000 2 iPhone 8 Apple 2 51000 3 iPhone 7 Apple 5 32000 4 Galaxy S9 Samsung 2 56000 5 Galaxy S8 Samsung 1 46000 6 Honor 10 Huawei 5 28000 7 Nokia 8 HMD Global 6 38000 Products Оператор HAVING позволяет выполнить фильтрацию групп, то есть определяет, какие группы будут включены в выходной результат. Найдем все группы товаров по производителям, для которых определено более 1 модели: SELECT Manufacturer, COUNT (*) AS ModelsCount FROM Products GROUP BY Manufacturer HAVING COUNT (*) > 1

Слайд 18

18 ГРУППИРОВКА ДАННЫХ. ФИЛЬТРАЦИЯ ГРУПП. HAVING В одной команде также можно сочетать выражения WHERE и HAVING : SELECT Manufacturer, COUNT (*) AS Models Count , SUM ( Proguct Count ) AS Units FROM Products WHERE Price * Product Count > 80000 GROUP BY Manufacturer HAVING SUM ( Product Count ) > 2 ORDER BY Units DESC ; Manufacturer ModelsCount Units Apple 3 10 HMD Slobal 1 6 Huawei 1 5 Результат Id ProductName Manufacturer ProductCount Price 1 iPhone X Apple 3 76000 2 iPhone 8 Apple 2 51000 3 iPhone 7 Apple 5 32000 4 Galaxy S9 Samsung 2 56000 5 Galaxy S8 Samsung 1 46000 6 Honor 10 Huawei 5 28000 7 Nokia 8 HMD Global 6 38000 Products

Слайд 19

19 В ыведем для каждого покупателя количество заказов, которые он сделал: Критерием группировки выступают имя покупателя. Оператор SELECT выбирает имя покупателя и количество заказов, используя столбец CustomerId из таблицы Orders . Так как это INNER JOIN, то в группах будут только те покупатели, у которых есть заказы. Id FirstName 1 Анатолий 2 Иван 3 Ирина Castomers Id ProductId CustomerId CreatedAt ModelsCount Price 1 4 1 2024-07-11 2 56000 2 2 1 2024-07-13 1 51000 3 2 3 2024-07-11 1 51000 Orders SELECT Customer . FirstName , COUNT ( Orders . Castomer Id ) AS Count FROM Customers JOIN Orders ON Orders.CustomerId = Customers.Id GROUP BY Customers.FirstName ; FirstName Count Анатолий 2 Ирина 1 Результат ОБЪЕДИНЕНИЕ ТАБЛИЦ С ГРУППИРОВКОЙ

Слайд 20

20 ОБЪЕДИНЕНИЕ ТАБЛИЦ С ГРУППИРОВКОЙ SELECT Customers. FirstName , COUNT ( Orders . Castomer Id ) AS Count FROM Customers LEFT JOIN Orders ON Orders.CustomerId = Customers.Id GROUP BY Customers.FirstName ; Получить также и тех покупателей , у которых нет заказов. Для этого можно использовать OUTER JOIN : Id FirstName 1 Анатолий 2 Иван 3 Ирина Castomers Id ProductId CustomerId CreatedAt ModelsCount Price 1 4 1 2024-07-11 2 56000 2 2 1 2024-07-13 1 51000 3 2 3 2024-07-11 1 51000 Orders FirstName Count Анатолий 2 Иван Ирина 1 Результат

Слайд 21

21 SELECT Products.ProductName , P roducts . Manufactorer , SUM ( Orders.ProductCount * Orders.Price ) AS TotalSum FROM Products LEFT JOIN Orders ON Orders.ProductId = Products.Id GROUP BY Products.ProductName , Products . Manufactorer ORDER BY Products.ProductName , P roducts . Manufactorer ; Id ProductName Manufacturer ProductCount Price 1 iPhone X Apple 3 76000 2 iPhone 8 Apple 2 51000 3 iPhone 7 Apple 5 32000 4 Galaxy S9 Samsung 2 56000 5 Galaxy S8 Samsung 1 46000 6 Honor 10 Huawei 5 28000 7 Nokia 8 HMD Global 6 38000 Products Id ProductId CustomerId CreatedAt ProductCount Price 1 4 1 2024-07-11 2 56000 2 2 1 2024-07-13 1 51000 3 2 3 2024-07-11 1 51000 Orders ProductName Manufactorer TotalSum Galaxy S8 Samsung Galaxy S9 Sumsung 112000 iPhone 8 Apple 102000 Honor 10 Huawei iPhone X Apple iPhone 7 Apple Nokia 9 HDM Global Результат ОБЪЕДИНЕНИЕ ТАБЛИЦ С ГРУППИРОВКОЙ

Слайд 22

22 МОДИФИКАТОР ROLLAP При проведении аналитики часто требуется посмотреть на статистические показатели в разных разрезах. Например, для анализа средней зарплаты интересно посмотреть на среднюю зарплату по каждому подразделению и по всей организации в целом. Для решения этой задачи SQL поддерживает наборы группирования, реализованные через ROLLUP . Модификатор ROLLUP является расширением предложения GROUP BY . Оно используется с агрегатными функциями для поиска общего итога или сводки значений столбца (также известной как суперагрегат столбца ) в дополнительной строке таблицы.

Слайд 23

23 МОДИФИКАТОР ROLLAP SELECT col1, col2,… coln , AggregateFunction FROM table_name GROUP BY col1, col2,… coln WITH ROLLUP ; Предположим, что в таблице продаж есть столбцы год year , страна country , продукт product и прибыль profit . Чтобы суммировать содержимое таблицы за год, нужно использовать простую группу GROUP BY следующим образом: SELECT year , SUM (profit) AS profit FROM sales GROUP BY year ; year profit 202 4 4525 202 5 3010 Результат

Слайд 24

24 МОДИФИКАТОР ROLLAP Выходные данные показывают общую (совокупную) прибыль profit за каждый год. Чтобы также определить общую прибыль, суммированную за все годы, необходимо самостоятельно сложить отдельные значения или выполнить дополнительный запрос. Или можно использовать необязательный оператор ROLLUP , который обеспечивает оба уровня анализа с помощью одного запроса. Добавление модификатора WITH ROLLUP к предложению GROUP BY приводит к тому, что запрос создает еще одну ( суперагрегированную ) строку, которая показывает общую сумму значений за все годы: SELECT year , SUM( profit ) AS profit FROM sales GROUP BY year WITH ROLLUP ; year profit 202 4 4525 202 5 3010 NULL 7535 Результат

Слайд 25

25 МОДИФИКАТОР ROLLUP Теперь вывод включает сводную информацию на трех уровнях анализа, а не только на одном: SELECT year , country, product, SUM( profit ) AS profit FROM sales GROUP BY year , country, product WITH ROLLUP ; Подсчитывается сумма продаж всех продуктов для определенного года и страны. Подсчитывается сумма продаж всех продуктов за каждый год. В конце появляется дополнительная сводная строка суперагрегата , показывающая общий итог по всем годам, странам и продуктам.

Слайд 26

26 Домашнее задание