From b14a35b894d5440c9d52a042bab6f7236ce2506f Mon Sep 17 00:00:00 2001 From: Andrew Guschin Date: Mon, 11 Apr 2022 23:24:34 +0400 Subject: =?UTF-8?q?=D0=94=D0=BE=D0=B1=D0=B0=D0=B2=D0=B8=D0=BB=20=D0=B4?= =?UTF-8?q?=D0=B5=D0=B2=D1=8F=D1=82=D0=BE=D0=B5=20=D0=B7=D0=B0=D0=B4=D0=B0?= =?UTF-8?q?=D0=BD=D0=B8=D0=B5?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- task9_requests.sql | 255 +++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 255 insertions(+) create mode 100644 task9_requests.sql (limited to 'task9_requests.sql') diff --git a/task9_requests.sql b/task9_requests.sql new file mode 100644 index 0000000..0beece0 --- /dev/null +++ b/task9_requests.sql @@ -0,0 +1,255 @@ +---------------------------------------------------- +-- Запрос с использованием автономных подзапросов -- +---------------------------------------------------- + +SELECT * FROM shipping.dbo.Payments +WHERE amount > (SELECT AVG(amount) + FROM shipping.dbo.Payments + WHERE date > '2021-04-25'); + +------------------------------------------------------- +-- Создание запроса с использованием коррелированных -- +-- подзапросов в предложении SELECT и WHERE -- +------------------------------------------------------- + +-- Выбрать минимальные в соответствующие дни платежи +SELECT * FROM shipping.dbo.Payments AS P1 +WHERE amount = ( + SELECT MIN(amount) + FROM shipping.dbo.Payments P2 + WHERE P1.date = P2.date +); + +SELECT *, ( SELECT MAX(weight) + FROM shipping.dbo.Boxes + WHERE order_id = O.id ) max_weight +FROM shipping.dbo.Orders O; + +---------------------------------------------- +-- Запрос с использованием временных таблиц -- +---------------------------------------------- + +DROP TABLE #OrderPayments + +SELECT Payments.id, amount, Payments.date, customer_id +INTO #OrderPayments +FROM shipping.dbo.Orders INNER JOIN shipping.dbo.Payments +ON Orders.id = Payments.order_id +WHERE Orders.date > '2021-04-25'; + +SELECT * +FROM #OrderPayments +WHERE amount > (SELECT AVG(amount) FROM #OrderPayments); + +------------------------------------------------------------------ +-- Запрос с использованием обобщенных табличных выражений (CTE) -- +------------------------------------------------------------------ + +WITH OrderPaymentsCTE AS ( + SELECT amount, Payments.date + FROM shipping.dbo.Orders INNER JOIN shipping.dbo.Payments + ON Orders.id = Payments.order_id + WHERE Orders.date > '2021-04-25' ) +SELECT * +FROM OrderPaymentsCTE +WHERE amount > (SELECT AVG(amount) FROM OrderPaymentsCTE); + +---------------------------------------------------------------- +-- Слияние данных (INSERT, UPDATE) c помощью инструкции MERGE -- +---------------------------------------------------------------- + +CREATE PROCEDURE add_payment + @order_id int, + @amount int, + @date datetime +AS BEGIN + MERGE INTO shipping.dbo.Payments as tgt + USING (SELECT @order_id, @amount, @date) + AS src (order_id, amount, date) + ON src.order_id = tgt.order_id + WHEN MATCHED THEN + UPDATE SET amount=src.amount, date=src.date + WHEN NOT MATCHED THEN + INSERT (order_id, amount, date) + VALUES (src.order_id, src.amount, src.date); +END + +EXEC add_payment 3, 500, '2021-04-25'; + +--------------------------------------------- +-- Запрос с использованием оператора PIVOT -- +--------------------------------------------- + +SELECT * FROM ( + SELECT Boxes.id, Wrappings.type + FROM shipping.dbo.Boxes + INNER JOIN shipping.dbo.Wrappings + ON Boxes.wrapping_id = Wrappings.id +) t +PIVOT ( + COUNT(id) + FOR type IN ( + [Жёсткая], + [Полужёсткая], + [Мягкая], + [Воздушно-Пузырьковая], + [Рулон] + ) +) AS PivotTable; + +----------------------------------------------- +-- Запрос с использованием оператора UNPIVOT -- +----------------------------------------------- + +SELECT * FROM ( + SELECT Boxes.id, Wrappings.type + FROM shipping.dbo.Boxes + INNER JOIN shipping.dbo.Wrappings + ON Boxes.wrapping_id = Wrappings.id +) t +PIVOT ( + COUNT(id) + FOR type IN ( + [Жёсткая], + [Полужёсткая], + [Мягкая], + [Воздушно-Пузырьковая], + [Рулон] + ) +) pvt +UNPIVOT ( + count + FOR type IN ( + [Жёсткая], + [Полужёсткая], + [Мягкая], + [Воздушно-Пузырьковая], + [Рулон] + ) +) AS UnpivotTable; + +---------------------------------------------- +-- Запрос с использованием GROUP BY с -- +-- операторами ROLLUP, CUBE и GROUPING SETS -- +---------------------------------------------- + +SELECT COUNT(Boxes.id) count, Wrappings.type, warehouse_id +FROM shipping.dbo.Boxes +INNER JOIN shipping.dbo.Wrappings +ON Boxes.wrapping_id = Wrappings.id +GROUP BY ROLLUP (Wrappings.type, warehouse_id); + +SELECT COUNT(Boxes.id) count, Wrappings.type, warehouse_id +FROM shipping.dbo.Boxes +INNER JOIN shipping.dbo.Wrappings +ON Boxes.wrapping_id = Wrappings.id +GROUP BY CUBE (Wrappings.type, warehouse_id); + +SELECT COUNT(Boxes.id) count, Wrappings.type, warehouse_id +FROM shipping.dbo.Boxes +INNER JOIN shipping.dbo.Wrappings +ON Boxes.wrapping_id = Wrappings.id +GROUP BY GROUPING SETS ((Wrappings.type, warehouse_id)); + +--------------------------------------------------- +-- Секционирование с использованием OFFSET FETCH -- +--------------------------------------------------- + +SELECT * FROM shipping.dbo.Boxes +ORDER BY volume +OFFSET 0 ROW FETCH NEXT 20 ROWS ONLY; + +-------------------------------------------------------------- +-- Запросы с использованием ранжирующих оконных функций. -- +-- ROW_NUMBER() нумерация строк. Использовать для нумерации -- +-- внутри групп. RANK(), DENSE_RANK(), NTILE(). -- +-------------------------------------------------------------- + +SELECT ROW_NUMBER() +OVER (ORDER BY volume DESC) volume_number, * +FROM shipping.dbo.Boxes; + +SELECT RANK() +OVER (ORDER BY DATEPART(year, date) DESC) year_rank, * +FROM shipping.dbo.Orders; + +SELECT DENSE_RANK() +OVER (ORDER BY DATEPART(year, date) DESC) year_rank, * +FROM shipping.dbo.Orders; + +SELECT NTILE(10) +OVER (ORDER BY volume DESC) volume_tile, * +FROM shipping.dbo.Boxes; + +---------------------------------------- +-- Перенаправление ошибки в TRY/CATCH -- +---------------------------------------- + +BEGIN TRY + INSERT INTO shipping.dbo.Boxes (order_id, warehouse_id, wrapping_id, volume, weight) + VALUES (3, 2134, 2, 12, 50); +END TRY +BEGIN CATCH + RAISERROR(N'Неверный warehouse_id', 10, 1); +END CATCH + +-------------------------------------------------------------- +-- 1. Создание процедуры обработки ошибок в блоке CATCH с -- +-- использованием функций ERROR; -- +-- 2. Контроль транзакций с BEGIN и COMMIT; -- +-- 3. Добавление логики обработки транзакций в блоке CATCH. -- +-------------------------------------------------------------- + +SET XACT_ABORT ON +DROP PROCEDURE getError +GO + +CREATE PROCEDURE getError +AS SELECT + ERROR_NUMBER() AS ErrorNumber, + ERROR_STATE() AS ErrorState, + ERROR_MESSAGE() AS ErrorMessage; +GO + +BEGIN TRY + BEGIN TRANSACTION + DELETE FROM shipping.dbo.Orders WHERE id=1; + COMMIT TRANSACTION +END TRY +BEGIN CATCH + EXECUTE getError; + ROLLBACK TRANSACTION; +END CATCH +GO + +--------------------------------------------------------------------- +-- Использование THROW, чтобы передать сообщение об ошибке клиенту -- +--------------------------------------------------------------------- + +BEGIN TRY + INSERT INTO shipping.dbo.Boxes (order_id, warehouse_id, wrapping_id, volume, weight) + VALUES (3, 2134, 2, 12, 50); +END TRY +BEGIN CATCH + THROW 50001, N'Неверный warehouse_id', 1; +END CATCH + +------------------------------ +-- Использование XACT_ABORT -- +------------------------------ + +SET XACT_ABORT OFF; + +BEGIN TRANSACTION + INSERT INTO shipping.dbo.Boxes (order_id, warehouse_id, wrapping_id, volume, weight) + VALUES (3, 2134, 2, 12, 1337); + + INSERT INTO shipping.dbo.Boxes (order_id, warehouse_id, wrapping_id, volume, weight) + VALUES (3, 401, 2, 12, 1337); + + INSERT INTO shipping.dbo.Boxes (order_id, warehouse_id, wrapping_id, volume, weight) + VALUES (3, 400, 2, 12, 1337); + + INSERT INTO shipping.dbo.Boxes (order_id, warehouse_id, wrapping_id, volume, weight) + VALUES (3, 123, 2, 12, 1337); +COMMIT TRANSACTION -- cgit v1.2.3