БД. ИТМО. Практикум санктПетербург 2020
 Скачать 0.94 Mb.
|
|
Задания для самостоятельной работы 1 Найти название самого продаваемого продукта. 2 Найти покупателя, совершившего покупку на самую большую сумм, считая сумму покупки исходя из цены товара без скидки (UnitPrice). 3 Найти такие продукты, которые покупал только один покупатель. 4 Вывести список продуктов, цена которых выше средней цены товаров в подкатегории, к которой относится товар. 41 5 Найти такие товары, которые были куплены более чем одним покупателем, при этом все покупатели этих товаров покупали товары только одного цвета и товары не входят в список покупок покупателей, купивших товары только двух цветов. 6 Найти такие товары, которые были куплены такими покупателями, у которых они присутствовали в каждой их покупке. 7 Найти покупателей, у которых есть товар, присутствующий в каждой покупке/чеке. 8 Найти такой товар или товары, которые были куплены не более чем тремя различными покупателями. 9 Найти все товары, такие что их покупали всегда с товаром, цена которого максимальна в своей категории. 10 Найти номера тех покупателей, у которых есть как минимум два чека, и каждый из этих чеков содержит как минимум три товара, каждый из которых как минимум был куплен другими покупателями три раза. 11 Найти все чеки, в которых каждый товар был куплен дважды этим же покупателем. 12 Найти товары, которые были куплены минимум три раза различными покупателями. 13 Найти такую подкатегорию или подкатегории товаров, которые содержат более трех товаров, купленных более трех раз. 14 Найти те товары, которые не были куплены более трех раз, и как минимум дважды одним и тем же покупателем. 42 Практическая работа 5 Цель работы: использование результатов выборки как источник данных, использование обобщенного табличного выражения (ОТВ). В подавляющем большинстве случаев операция выборки предполагает, что источником данных будут служить таблицы. В некоторых случаях это создает определенные неудобства, в частности, не позволяет получить выборку из результата выполнения предыдущего запроса. Решить эту задачу можно несколькими способами. Представление. Представление является именованным результатом выполнения запроса. Поскольку представление – это объект базы данных, то для его создания и использования требуются дополнительные права. Альтернативным вариантом, не требующим дополнительных прав, является использование запросов как источников данных или обобщенных табличных выражений. Использование запроса как источника данных подразумевает, что пользователь создает запрос, дает ему псевдоним и использует этот псевдоним аналогично имени таблицы. Рассмотрим сложный и объемный пример. Необходимо найти для каждой подкатегории количество товаров, у которых цена выше средней цены в подкатегории, и количество товаров, у которых цена ниже средней цены в подкатегории. Оформить вывод в виде трех столбцов: номер подкатегории, первый показатель, второй показатель. Решить подобную задачу традиционным способом достаточно сложно, но она относительно легко решается с использованием псевдонимов для результата выполнения запроса. SELECT T1.PS, T1.c, T2.c FROM (SELECT COUNT(*) AS c, [ProductSubcategoryID] AS PS FROM [Production].[Product] AS P WHERE [ListPrice]<(SELECT avg([ListPrice]) FROM [Production].[Product] AS PT WHERE pt.ProductSubcategoryID=p.ProductSubcategoryID) GROUP BY [ProductSubcategoryID]) AS T1 INNER JOIN (SELECT count(*) AS c, [ProductSubcategoryID] AS PS FROM [Production].[Product] AS P WHERE [ListPrice]>=(SELECT avg([ListPrice]) FROM [Production].[Product] AS PT WHERE pt.ProductSubcategoryID=p.ProductSubcategoryID) GROUP BY [ProductSubcategoryID]) AS T2 ON 43 T1.PS=T2.PS В данном примере созданы два независимых запроса, один из которых находит номер категории и количество товаров, цена у которых ниже средней цены в той же категории, к которой он относится, второй – такой же, но знак заменен на «больше или равно». Каждый запрос получил свой псевдоним, T1 и T2 соответственно, и каждому столбцу в запросе дан свой псевдоним. Запрос верхнего уровня использует запросы T1 и T2 точно так же, как обычные таблицы, проводит операцию INNER JOIN и выводит искомый результат. К сожалению, использование такого подхода удобно, когда запрос, формирующий источник данных, не очень длинный. В противном случае читаемость кода резко падает, и в этом случае удобнее использовать обобщенное табличное выражение. Также обобщенное табличное выражение можно использовать при выполнении однократной операции модификации данных. Общий синтаксис: WITH <имя ОТВ> ( столбец 1, столбец 2, … ] AS (запрос) В нерекурсивных ОТВ недопустимо использовать операцию упорядочивания, за исключением случаев использования инструкции TOP. Рассмотрим пример, в котором необходимо найти количество чеков, приходящихся на одного покупателя на каждый год. WITH Sales_CTE (SalesPersonID, SalesORDERID, SalesYear) AS ( SELECT SalesPersonID, SalesORDERID, YEAR(ORDERDate) AS SalesYear FROM Sales.SalesORDERHeader WHERE SalesPersonID IS NOT NULL ) SELECT SalesPersonID, COUNT(SalesORDERID) AS TotalSales, SalesYear FROM Sales_CTE GROUP BY SalesYear, SalesPersonID ORDER BY SalesPersonID, SalesYear В данном пример определено ОТВ с именем Sales_CTE, которое использовано как источник данных для запроса. 44 Если необходимо использовать несколько ОТВ в одном запросе, то их можно определить следующим образом. WITH <имя ОТВ1> ( столбец 1, столбец 2, … ] AS (запрос), <имя ОТВ2> ( столбец 1, столбец 2, … ] AS (запрос) Примеры запросов с решениями 1 Найти покупателя, который каждый раз имел разный список товаров в чеке (по номенклатуре) SELECT tmp.c FROM (SELECT soh.CustomerID AS c , soh.SalesORDERID AS o , CHECKSUM_AGG(sod.ProductID) AS ch FROM [Sales].[SalesORDERDetail] AS sod JOIN [Sales].[SalesORDERHeader] AS soh ON sod.SalesORDERID=soh.SalesORDERID GROUP BY soh.CustomerID, soh.SalesORDERID) tmp GROUP BY tmp.c HAVING count(tmp.ch)=count(DISTINCT tmp.ch) AND count(tmp.ch)>1 2 Найти пары таких покупателей, что список названий товаров, которые они когда-либо покупали, не пересекается ни в одной позиции. SELECT top 3 t1.c, t2.c FROM (SELECT soh.CustomerID AS c, sod.ProductID AS p FROM [Sales].[SalesORDERDetail] AS sod JOIN [Sales].[SalesORDERHeader] AS soh ON sod.SalesORDERID=sod.SalesORDERID) t1, 45 (SELECT soh.CustomerID AS c, sod.ProductID AS p FROM [Sales].[SalesORDERDetail] AS sod JOIN [Sales].[SalesORDERHeader] AS soh ON sod.SalesORDERID=sod.SalesORDERID) t2 WHERE t1.p!=all(SELECT sod.ProductID AS p FROM [Sales].[SalesORDERDetail] AS sod JOIN [Sales].[SalesORDERHeader] AS soh ON sod.SalesORDERID=sod.SalesORDERID WHERE soh.CustomerID=t2.c) 3 Вывести номера продуктов, таких, что их цена выше средней цены продукта в подкатегории, к которой относится продукт. Запрос реализовать двумя способами. В одном из решений допускается использование обобщенного табличного выражения. Вариант 1 SELECT p1.ProductID FROM [Production].[Product] AS p1 WHERE p1.ListPrice> ( SELECT avg(p2.[ListPrice]) FROM [Production].[Product] AS p2 WHERE p2.ProductSubcategoryID=p1.ProductSubcategoryID ) Вариант 2 with tmp (pscid, acgLP) AS (SELECT p.ProductSubcategoryID, avg([ListPrice]) FROM [Production].[Product] AS p GROUP BY p.ProductSubcategoryID) SELECT p.ProductID FROM [Production].[Product] AS p JOIN tmp ON p.ProductSubcategoryID=tmp.pscid WHERE [ListPrice]>tmp.acgLP 46 Задания для самостоятельной работы 1 Найти среднее количество покупок на чек для каждого покупателя (2 способа). 2 Найти для каждого продукта и каждого покупателя соотношение количества фактов покупки данного товара данным покупателем к общему количеству фактов покупки товаров данным покупателем 3 Вывести на экран следящую информацию: Название продукта, Общее количество фактов покупки этого продукта, Общее количество покупателей этого продукта 4 Вывести для каждого покупателя информацию о максимальной и минимальной стоимости одной покупки, чеке, в виде таблицы: номер покупателя, максимальная сумма, минимальная сумма. 5 Найти номера покупателей, у которых не было нет ни одной пары чеков с одинаковым количеством наименований товаров. 6 Найти номера покупателей, у которых все купленные ими товары были куплены как минимум дважды, т.е. на два разных чека. 47 Практическая работа 6 Цель работы: Использование оконных функций и предложения OVER. Предложение OVER определяет секционирование и упорядочение набора строк до применения соответствующей оконной функции. OVER ( [ ] [ [ ) PARTITION BY разделяет результирующий набор запроса на секции. Оконная функция применяется к каждой секции отдельно, и вычисление начинается заново для каждой секции. ORDER BY определяет логический порядок строк в каждой секции результирующего набора. ROW or RANGE ограничивает строки в пределах секции, указывая начальную и конечную точки. Параметр CURRENT ROW указывает, что окно начинается или заканчивается на текущей строке при использовании совместно с предложением ROWS или что окно заканчивается на текущем значении при использовании с предложением RANGE. CURRENT ROW может быть задана и как начальная, и как конечная точка. Параметр BETWEEN <граница рамки окна > AND <граница рамки окна > используется совместно с предложением ROWS или RANGE для указания нижней (начальной) или верхней (конечной) граничной точки окна. <граница рамки окна> определяет граничную начальную точку, а <граница рамки окна> определяет граничную конечную точку. Параметр UNBOUNDED FOLLOWING указывает, что окно заканчивается на последней строке секции. UNBOUNDED FOLLOWING может быть указано только как конечная точка окна. Пример: RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING В данном случае параметр определяет, что окно начинается на текущей строке и заканчивается на последней строке секции. ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWIN В этом примере параметр определяет, что окно начинается на второй строке после текущей и заканчивается на десятой строке после текущей строки. Эта спецификация не допускается в предложении RANGE. 48 Фактически предложение OVER виртуально разбивает выбранные строки на наборы, окна, определяемые в условии PARTITION BY, упорядочивает эти строки по столбцам определенным ORDER BY. В рамках этих наборов, окон, выполняются те или иные агрегирующие, статистические и иные функции. Результат выполнения этих функций формирует отдельный столбец с одинаковыми значениями для каждой строки в наборе, окне. Однако можно для каждой строки в наборе формировать свое значение функции, для чего используют параметр ROW или RANGE, который определяет диапазон строк, в наборе, окне, с которыми будет работать функция. SELECT SalesORDERID, ProductID, ORDERQty ,SUM(ORDERQty) OVER(PARTITION BY SalesORDERID) AS Total ,AVG(ORDERQty) OVER(PARTITION BY SalesORDERID) AS "Avg" ,COUNT(ORDERQty) OVER(PARTITION BY SalesORDERID) AS "Count" ,MIN(ORDERQty) OVER(PARTITION BY SalesORDERID) AS "MIN" ,MAX(ORDERQty) OVER(PARTITION BY SalesORDERID) AS "Max" FROM Sales.SalesORDERDetail WHERE SalesORDERID IN(43659,43664) Следующий запрос использует приведение типа. SELECT SalesORDERID, ProductID, ORDERQty ,SUM(ORDERQty) OVER(PARTITION BY SalesORDERID) AS Total ,CAST(1. * ORDERQty / SUM(ORDERQty) OVER(PARTITION BY SalesORDERID) *100 AS DECIMAL(5,2))AS "Percent BY ProductID" FROM Sales.SalesORDERDetail WHERE SalesORDERID IN(43659,43664) Можно выполнять функцию не на всем наборе, но на части набора. Часть определяется в зависимости от строки и ее положения в наборе. SELECT BusINessEntityID, TerritoryID ,CONVERT(varchar(20),SalesYTD,1) AS SalesYTD ,DATEPART(yy,ModifiedDate) AS SalesYear ,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy,ModifiedDate) ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ),1) AS CumulativeTotal FROM Sales.SalesPerson WHERE TerritoryID IS NULL OR TerritoryID < 5 Аналитические функции. 49 Функция FIRST_VALUE возвращает первое значение из упорядоченного набора значений. Синтаксис FIRST_VALUE ( [scalar_expressiON ] ) OVER ( [ partitiON_BY_clause ] ORDER_BY_clause [ rows_range_clause ] ) scalar_expressiON может быть столбцом, вложенным запросом. Получение имени самого дешевого продукта в заданной категории продуктов. SELECT Name, ListPrice, FIRST_VALUE(Name) OVER (ORDER BY ListPrice ASC) AS LeAStExpensive FROM Production.Product WHERE ProductSubcategoryID = 37 Функция LAST_VALUE возвращает последнее значение из упорядоченного набора значений. Синтаксис LAST_VALUE ( [ scalar_expressiON ] ) OVER ( [ partitiON_BY_clause ] ORDER_BY_clause rows_range_clause ) scalar_expressiON может быть столбцом, вложенным запросом. Возвращение даты найма последнего сотрудника каждого отдела для указанной заработной платы (Rate). Предложение PARTITION BY разделяет сотрудников по отделам, а функция LAST_VALUE применяется к каждой секции в отдельности. Предложение ORDER BY, указанное в предложении OVER, определяет логический порядок, в котором функция LAST_VALUE применяется к строкам каждой секции. SELECT Department, LastName, Rate, HireDate, LAST_VALUE(HireDate) OVER (PARTITION BY Department ORDER BY Rate) AS LAStValue FROM HumanResources.vEmployeeDepartmentHistory AS edh INNER JOIN HumanResources.EmployeePayHistory AS eph ON eph.BusINessEntityID = edh.BusINessEntityID INNER JOIN HumanResources.Employee AS e ON e.BusINessEntityID = edh.BusINessEntityID WHERE Department IN (N'INformatiON Services',N'Document CONtrol') Функция LAG обеспечивает доступ к строке с заданным физическим смещением перед началом текущей строки. LAG (scalar_expressiON [,offset] [,default]) 50 OVER ( [ partitiON_BY_clause ] ORDER_BY_clause ) scalar_expressiON – возвращаемое значение основано на указанном смещении. offset – количество строк до строки перед текущей строкой, из которой необходимо получить значение. default – возвращаемое значение, когда offset находится за пределами секции. Нахождение квоты для работника за год и предыдущий год. Квоты менялись несколько раз в год, но для первой установленной в году квоты нет предыдущего значения от 2010 года, их не включают в выборку. SELECT BusINessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota FROM Sales.SalesPersonQuotaHistory WHERE BusINessEntityID = 275 AND YEAR(QuotaDate) IN ('2011','2012') Функция LEAD – доступ к строке на заданном физическом смещении после текущей строки. Синтаксис LEAD ( scalar_expressiON [ ,offset ] , [ default ] ) OVER ( [ partitiON_BY_clause ] ORDER_BY_clause ) Пример, получение квот продаж для указанного работника за последующие годы. SELECT BusINessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota FROM Sales.SalesPersonQuotaHistory WHERE BusINessEntityID = 275 AND YEAR(QuotaDate) IN ('2011','2012') Функция NTILE распределяет строки упорядоченной секции в заданное количество групп. Группы нумеруются, начиная с единицы. Для каждой строки функция NTILE возвращает номер группы, которой принадлежит строка. Синтаксис NTILE (INteger_expressiON) OVER ( [ ] < ORDER_BY_clause > ) INteger_expressiON – положительное целое выражение, указывающее число групп, на которые необходимо разделить каждую секцию. Функция ROW_NUMBER нумерует выходные данные результирующего набора. 51 ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expressiON , ... [ n ] ] ORDER_BY_clause ) Пример использования функций. SELECT p.FirstName, p.LastName ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number" ,NTILE(4) OVER (ORDER BY a.PostalCode) AS Quartile ,s.SalesYTD ,a.PostalCode FROM Sales.SalesPerson AS s INNER JOIN Person.Person AS p ON s.BusINessEntityID = p.BusINessEntityID INNER JOIN Person.Address AS a ON a.AddressID = p.BusINessEntityID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0 Примеры запросов с решениями 1 Найти долю затрат каждого покупателя на каждый купленный им продукт среди общих его затрат в данной сети магазинов. Можно использовать обобщенное табличное выражение. SELECT [SalesORDERID], p.[ProductID], [ProductSubcategoryID], [ORDERQty]*[UnitPrice], [ORDERQty]*[UnitPrice]/sum([ORDERQty]*[UnitPrice]) OVER(partitiON BY [SalesORDERID] , [ProductSubcategoryID]) FROM [Sales].[SalesORDERDetail] AS SOD INner JOIN [Production].[Product] AS p ON SOD.ProductID=p.ProductID 2 Для одного выбранного покупателя вывести, для каждой покупки (чека), разницу в деньгах между этой и следующей покупкой. Вариант 1 with tmp (customer, ORDERid, total) AS (SELECT soh.CustomerID, soh.SalesORDERID, sum(sod.[ORDERQty]*[UnitPrice]) AS total 52 FROM [Sales].[SalesORDERHeader] AS SOH INner JOIN [Sales].[SalesORDERDetail] AS SOD ON soh.SalesORDERID=sod.SalesORDERID GROUP BY soh.CustomerID, soh.SalesORDERID) SELECT customer, ORDERid, total, total-LEAD(total,1,0) OVER(partitiON BY customer ORDER BY ORDERid) FROM tmp 3 Вывести следующую информацию: номер покупателя, номер чека этого покупателя, отсортированные по покупателям, номерам чека (по возрастанию). Третья колонка должна содержать в каждой своей строке сумму текущего чека покупателя со всеми предыдущими чеками этого покупателя. Вариант 1 with tmp (cus, ord, ORDERsum) AS (SELECT oh.CustomerID, od.SalesORDERID, sum(od.[UnitPrice]*[ORDERQty]) FROM [Sales].[SalesORDERDetail] AS OD INner JOIN [Sales].[SalesORDERHeader] AS OH ON od.SalesORDERID=oh.SalesORDERID GROUP BY oh.CustomerID, od.SalesORDERID) SELECT cus, ord, ORDERsum, sum(ORDERsum) OVER(partitiON BY cus ORDER BY ord desc RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM tmp |