Аналитическая выборка данных из базы данных опирается на возможности Transact-SQL создавать запросы с агрегатными функциями:
Avg ([all | distinct] выражение) – среднее арифметическое всех значений.
Count ([all | distinct] выражение | *) – количество значений в списке, отличных от NULL. При использовании символа * подсчитывается количество значений, включая значения NULL или повторяющиеся значения.
Sum ([all | distinct] выражение) – сумма всех значений списка.
Max ([all | distinct] выражение) –максимальное значение.
Min ([all | distinct] выражение) – минимальное значение.
Ключевое слово all предписывает выполнять агрегирование всех записей в результирующем наборе данных, distinct – агрегирование только уникальных записей. По умолчанию используется all.
Например, вычисление средней цены продукции можно осуществить с помощью следующего запроса:
SELECT max(ListPrice) FROM DimProduct
При выполнении агрегатной функции осуществляется объединение значений отдельного поля таблицы или части записей, после чего выполняется указанное агрегирование.
Агрегатная функция возвращается одно единственное значение, поэтому использование других имен полей в списке выборки запрещено.
Задание 1
a) Определить количество сотрудников в БД AdventureWorksDW.
b) Подсчитать максимальную цену заказанной через Интернет продукции.
6.2. Подзапросы
Подзапрос – это оператор SELECT, включенный в другие запросы. Подзапросы применяются для разбивки сложного запроса на серию логических этапов. Их применение эффективно, если запрос использует записи, возвращенные другим запросом.
Существует два вида подзапросов: вложенные и связанные.
Вложенные подзапросы возвращают единственное значение либо список значений. Вложенный запрос выполняется один раз, а затем результирующее значение используется во внешнем запросе.
Например, чтобы определить имена заказчиков, разместивших заказы в первый учетный день, сначала можно воспользоваться следующим запросом:
SELECT CustomerKey
FROM FactInternetSales
WHERE OrderDateKey = (SELECT Min(TimeKey) FROM DimTime)
Выполнение этого запроса осуществляется в два этапа: на первом – осуществляется выполнение подзапроса, как самостоятельного запроса, который возвращает значение, используемое на втором этапе при выполнении внешнего запроса – когда получаем коды заказчиков. Полученные коды заказчиков могут быть использованы далее в подзапросе для получения имен заказчиков:
SELECT FirstName, LastName
FROM DimCustomer
WHERE CustomerKey IN
(SELECT CustomerKey
FROM FactInternetSales
WHERE OrderDateKey=(SELECT Min(TimeKey) FROM DimTime))
Такой запрос может быть оформлен и в виде соединения таблиц. Запросы с соединением таблиц обрабатываются значительно быстрее, поэтому если выполнение запроса не требуется выполнять в несколько этапов, использование подзапросов не обязательно.
Задание 2
a) Вывести список товаров, цена которых выше средней цены.
b) Решить задачу, описанную в примере (имена заказчиков, разместивших заказы в первый учетный день) с использованием соединения таблиц.
Связанные подзапросы используют данные внешнего запроса, причем связанный запрос выполняется один раз для каждой записи внешнего запроса.
Например, для определения списка заказчиков, заказавших в общей сложности более 20 единиц товара, необходимо:
SELECT FirstName, LastName
FROM DimCustomer C
WHERE 20<(SELECT sum(OrderQuantity) FROM FactInternetSales S WHERE C.CustomerKey=S.CustomerKey) В связанных подзапросах для краткости можно применять псевдонимы таблиц. В примере для таблицы DimCustomer определен псевдоним C, а для таблицы FactInternetSales – S. Однако, использование связанных подзапросов неэффективно, лучше преобразовывать их в соединения таблиц, что позволяет оптимизатору запросов выбирать наилучший способ обработки данных. Например, для товаров отобразить сведения о самом последнем их заказе: количество заказанного товара и номер заказа. SELECT EnglishProductName, OrderQuantity, SalesOrderNumber FROM DimProduct P INNER JOIN FactInternetSales S1 ON P.ProductKey=S1.ProductKey WHERE OrderDateKey = (SELECT max(OrderDateKey) FROM FactInternetSales S2 WHERE S2.ProductKey=P.ProductKey) 6.3. Группировка записей Для группировки записей по полям или выражениям применяется раздел GROUP BY оператора SELECT, что позволяет применять для каждой группы функции агрегирования. Синтаксис данной части следующий: [ GROUP BY ВыражениеГруппировки, […n] ] Например, чтобы определить количество товаров в каждом заказе, необходимо сгруппировать записи с одинаковым идентификационным номером заказа SalesOrderNumber и подсчитать количество товаров в каждой группе: SELECT SalesOrderNumber, count(ProductKey) AS Количество FROM FactInternetSales GROUP BY SalesOrderNumber При использовании GROUP BY для каждой определенной группы значений выводится только одна запись в итоговом наборе данных. При группировке записей допускается также использование раздела WHERE, в этом случае группируются записи, удовлетворяющие этому условию. Задание 3. Для каждого товара отобразить сведения о самом крупном (по стоимости) его заказе: наименование товара и стоимость заказа. Раздел WHERE позволяет определить, какие записи должны подвергнуться группировке, а раздел HAVING – какие группы должны быть выведены в итоговый набор данных. Ключевое слово HAVING можно использовать только в разделе GROUP BY. Так с помощью этого раздела можно в итоговый набор данных поместить только те заказы, сумма заказанных товаров в которых превышает 7 единиц: SELECT SalesOrderNumber, sum(OrderQuantity) as [Общая Сумма Товаров] FROM FactInternetSales GROUP BY SalesOrderNumber HAVING sum(OrderQuantity)>=7
Задание 4
a) Вывести список товаров, на которые поступило не менее 1000 заказов.
b) Получить список только тех товаров, чье суммарное заказанное количество превышает 900 штук.
Задание 5 (решения всех задач оформить в виде хранимых процедур)
a) Определить 10 наиболее популярных у заказчиков товаров.
b) Определить общее количество и суммарную стоимость всех заказов для каждого промоутера.
c) Выяснить, товары какого типа (субкатегории) пользуются у заказчиков наибольшим спросом.
http://62.105.135.100/images/default/constructor/logo_294290820.gif
Знакомства, общение, развлечения в Чехии – Seznamka.Ru
