From 31d4558821bfffbc07d5fae54fa063c019a6f7a9 Mon Sep 17 00:00:00 2001
From: Andrew Guschin <guschin.drew@gmail.com>
Date: Tue, 12 Apr 2022 12:42:41 +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=D1=81=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

---
 task10.sql | 444 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 444 insertions(+)
 create mode 100644 task10.sql

(limited to 'task10.sql')

diff --git a/task10.sql b/task10.sql
new file mode 100644
index 0000000..dc1318f
--- /dev/null
+++ b/task10.sql
@@ -0,0 +1,444 @@
+-------------------------------------------
+-- Создание базы данных и файловых групп --
+-------------------------------------------
+
+USE master
+DROP DATABASE shipping_storage
+GO
+
+CREATE DATABASE shipping_storage
+ON PRIMARY
+    (NAME = N'shipping_storage',
+     FILENAME = N'/var/opt/mssql/data/shipping_storage.mdf',
+     SIZE = 51200KB,
+     FILEGROWTH = 10240KB)
+LOG ON
+    (NAME = N'shipping_storage_log',
+     FILENAME = N'/var/opt/mssql/data/shipping_storage_log.ldf',
+     SIZE = 10240KB,
+     FILEGROWTH = 10%)
+COLLATE Cyrillic_General_100_CI_AI
+GO
+ALTER DATABASE shipping_storage SET RECOVERY SIMPLE WITH NO_WAIT;
+GO
+ALTER DATABASE shipping_storage SET AUTO_SHRINK OFF
+GO
+
+ALTER DATABASE shipping_storage ADD FILEGROUP [Indexes]
+ALTER DATABASE shipping_storage ADD FILE
+(NAME = N'Indexes',
+ FILENAME = N'/var/opt/mssql/data/Indexes.ndf',
+ SIZE = 358400KB,
+ FILEGROWTH = 51200KB)
+TO FILEGROUP [Indexes]
+GO
+
+ALTER DATABASE shipping_storage ADD FILEGROUP [Read_Only]
+ALTER DATABASE shipping_storage ADD FILE
+(NAME = N'Read_Only',
+ FILENAME = N'/var/opt/mssql/data/Read_Only.ndf',
+ SIZE = 358400KB,
+ FILEGROWTH = 51200KB)
+TO FILEGROUP [Read_Only]
+GO
+
+ALTER DATABASE shipping_storage ADD FILEGROUP [Frequently_Requested]
+ALTER DATABASE shipping_storage ADD FILE
+(NAME = N'Frequently_Requested',
+ FILENAME = N'/var/opt/mssql/data/Frequently_Requested.ndf',
+ SIZE = 358400KB,
+ FILEGROWTH = 51200KB)
+TO FILEGROUP [Frequently_Requested]
+GO
+
+ALTER DATABASE shipping_storage ADD FILEGROUP [Fast_Growing]
+ALTER DATABASE shipping_storage ADD FILE
+(NAME = N'Fast_Growing',
+ FILENAME = N'/var/opt/mssql/data/Fast_Growing.ndf',
+ SIZE = 358400KB,
+ FILEGROWTH = 51200KB)
+TO FILEGROUP [Fast_Growing]
+GO
+
+ALTER DATABASE shipping_storage ADD FILEGROUP [Slow_Growing]
+ALTER DATABASE shipping_storage ADD FILE
+(NAME = N'Slow_Growing',
+ FILENAME = N'/var/opt/mssql/data/Slow_Growing.ndf',
+ SIZE = 358400KB,
+ FILEGROWTH = 51200KB)
+TO FILEGROUP [Slow_Growing]
+GO
+
+
+---------------------------------------------
+-- Создание схем и функций секционирования --
+---------------------------------------------
+
+USE shipping_storage
+
+CREATE PARTITION FUNCTION shipping_partition_func (BIGINT)
+AS RANGE RIGHT FOR VALUES (2018010100, 2019010100, 2020010100, 2021010100)
+GO
+
+CREATE PARTITION SCHEME shipping_partition_scheme
+AS PARTITION shipping_partition_func TO
+([Frequently_Requested], [Frequently_Requested], [Frequently_Requested], [Frequently_Requested], [Frequently_Requested])
+GO
+
+CREATE PARTITION FUNCTION archive_shipping_partition_func (BIGINT)
+AS RANGE RIGHT FOR VALUES (2018010100)
+GO
+
+CREATE PARTITION SCHEME archive_shipping_partition_scheme
+AS PARTITION archive_shipping_partition_func TO
+([Frequently_Requested], [Frequently_Requested])
+GO
+
+
+---------------------
+-- Создание таблиц --
+---------------------
+
+USE shipping_storage
+CREATE TABLE [fact_delivery] (
+    [delivery_key] INT IDENTITY (1, 1) NOT NULL,
+    [wrapping_key] INT NOT NULL,
+    [payment_kind_key] INT NOT NULL,
+    [volume] FLOAT NOT NULL,
+    [weight] FLOAT NOT NULL,
+    [customer_key] INT NOT NULL,
+    [order_date] BIGINT NOT NULL,
+    [expected_delivery_date] BIGINT NOT NULL,
+    [factual_delivery_date] BIGINT NOT NULL,
+    [geography_from_key] INT NOT NULL,
+    [geography_to_key] INT NOT NULL,
+    [vehicle_key] INT NOT NULL,
+    [subtotal] FLOAT NOT NULL,
+    [discount] FLOAT NOT NULL,
+    [tax_amount] FLOAT NOT NULL,
+    [total] FLOAT NOT NULL,
+    CONSTRAINT PK_fact_delivery PRIMARY KEY ([delivery_key], [order_date])
+    ON shipping_partition_scheme([order_date])
+)
+GO
+
+USE shipping_storage
+CREATE TABLE [archive_fact_delivery] (
+    [delivery_key] INT NOT NULL,
+    [wrapping_key] INT NOT NULL,
+    [payment_kind_key] INT NOT NULL,
+    [volume] FLOAT NOT NULL,
+    [weight] FLOAT NOT NULL,
+    [customer_key] INT NOT NULL,
+    [order_date] BIGINT NOT NULL,
+    [expected_delivery_date] BIGINT NOT NULL,
+    [factual_delivery_date] BIGINT NOT NULL,
+    [geography_from_key] INT NOT NULL,
+    [geography_to_key] INT NOT NULL,
+    [vehicle_key] INT NOT NULL,
+    [subtotal] FLOAT NOT NULL,
+    [discount] FLOAT NOT NULL,
+    [tax_amount] FLOAT NOT NULL,
+    [total] FLOAT NOT NULL,
+    CONSTRAINT PK_archive_fact_delivery PRIMARY KEY ([delivery_key], [order_date])
+    ON archive_shipping_partition_scheme([order_date])
+)
+GO
+
+USE shipping_storage
+CREATE TABLE [dim_wrapping] (
+    [wrapping_key] INT IDENTITY (1, 1) NOT NULL CONSTRAINT PK_wrapping PRIMARY KEY,
+    [name] NVARCHAR(30) NOT NULL,
+    [price] INT NOT NULL,
+) ON [Read_Only]
+GO
+
+USE shipping_storage
+CREATE TABLE [dim_payment_kind] (
+    [payment_kind_key] INT IDENTITY (1, 1) NOT NULL CONSTRAINT PK_payment_kind PRIMARY KEY,
+    [name] NVARCHAR(30) NOT NULL
+) ON [Read_Only]
+GO
+
+USE shipping_storage
+CREATE TABLE [dim_geography] (
+    [geography_key] INT IDENTITY (1, 1) NOT NULL CONSTRAINT PK_geography PRIMARY KEY,
+    [region_name] NVARCHAR(30) NOT NULL,
+    [region_code] INT NOT NULL,
+    [postal_code] INT NOT NULL,
+    [city_name] NVARCHAR(30) NOT NULL
+) ON [Read_Only]
+GO
+
+USE shipping_storage
+CREATE TABLE [dim_date] (
+    [date_key] BIGINT NOT NULL CONSTRAINT PK_date PRIMARY KEY,
+    [date] DATE NOT NULL,
+    [year] INT NOT NULL,
+    [quarter] INT NOT NULL,
+    [month] INT NOT NULL,
+    [week] INT NOT NULL,
+    [day] INT NOT NULL,
+    [hour] INT NOT NULL,
+) ON [Frequently_Requested]
+GO
+
+USE shipping_storage
+CREATE TABLE [dim_customer] (
+    [customer_key] INT IDENTITY (1, 1) NOT NULL CONSTRAINT PK_customer PRIMARY KEY,
+    [demography_key] INT NOT NULL,
+    [name] NVARCHAR(30) NOT NULL,
+    [email] NVARCHAR(30) NOT NULL,
+    [birthday] BIGINT NOT NULL,
+    [gender] NVARCHAR(30) NOT NULL,
+    [phone_number] NVARCHAR(30) NOT NULL,
+    [tax_number] INT NOT NULL,
+    [geography_key] INT NOT NULL,
+    [entry_date] BIGINT NOT NULL,
+    [discount] FLOAT NOT NULL,
+    [discount_start] BIGINT NOT NULL,
+    [discount_end] BIGINT NOT NULL,
+    [flag] BIT NOT NULL
+) ON [Frequently_Requested]
+GO
+
+USE shipping_storage
+CREATE TABLE [dim_customer_demography] (
+    [customer_demography_key] INT IDENTITY (1, 1) NOT NULL CONSTRAINT PK_customer_demography PRIMARY KEY,
+    [age] INT NOT NULL,
+    [marital_status] NVARCHAR(30) NOT NULL,
+    [number_of_children] INT NOT NULL
+) ON [Frequently_Requested]
+GO
+
+USE shipping_storage
+CREATE TABLE [dim_vehicles] (
+    [vehicle_key] INT IDENTITY (1, 1) NOT NULL CONSTRAINT PK_vehicles PRIMARY KEY,
+    [name] NVARCHAR(30) NOT NULL,
+    [tonnage] INT NOT NULL,
+    [gas_consumption] INT NOT NULL,
+    [on_route] BIT NOT NULL,
+    [route_start] BIGINT NOT NULL,
+    [route_end] BIGINT NOT NULL,
+    [flag] BIT NOT NULL
+) ON [Slow_Growing]
+GO
+
+USE shipping_storage
+ALTER TABLE [fact_delivery] ADD FOREIGN KEY ([wrapping_key]) REFERENCES [dim_wrapping] ([wrapping_key])
+GO
+
+USE shipping_storage
+ALTER TABLE [fact_delivery] ADD FOREIGN KEY ([payment_kind_key]) REFERENCES [dim_payment_kind] ([payment_kind_key])
+GO
+
+USE shipping_storage
+ALTER TABLE [fact_delivery] ADD FOREIGN KEY ([customer_key]) REFERENCES [dim_customer] ([customer_key])
+GO
+
+USE shipping_storage
+ALTER TABLE [fact_delivery] ADD FOREIGN KEY ([order_date]) REFERENCES [dim_date] ([date_key])
+GO
+
+USE shipping_storage
+ALTER TABLE [fact_delivery] ADD FOREIGN KEY ([expected_delivery_date]) REFERENCES [dim_date] ([date_key])
+GO
+
+USE shipping_storage
+ALTER TABLE [fact_delivery] ADD FOREIGN KEY ([factual_delivery_date]) REFERENCES [dim_date] ([date_key])
+GO
+
+USE shipping_storage
+ALTER TABLE [fact_delivery] ADD FOREIGN KEY ([geography_from_key]) REFERENCES [dim_geography] ([geography_key])
+GO
+
+USE shipping_storage
+ALTER TABLE [fact_delivery] ADD FOREIGN KEY ([geography_to_key]) REFERENCES [dim_geography] ([geography_key])
+GO
+
+USE shipping_storage
+ALTER TABLE [fact_delivery] ADD FOREIGN KEY ([vehicle_key]) REFERENCES [dim_vehicles] ([vehicle_key])
+GO
+
+USE shipping_storage
+ALTER TABLE [dim_customer] ADD FOREIGN KEY ([demography_key]) REFERENCES [dim_customer_demography] ([customer_demography_key])
+GO
+
+USE shipping_storage
+ALTER TABLE [dim_customer] ADD FOREIGN KEY ([geography_key]) REFERENCES [dim_geography] ([geography_key])
+GO
+
+USE shipping_storage
+ALTER TABLE [dim_customer] ADD FOREIGN KEY ([birthday]) REFERENCES [dim_date] ([date_key])
+GO
+
+USE shipping_storage
+ALTER TABLE [dim_customer] ADD FOREIGN KEY ([entry_date]) REFERENCES [dim_date] ([date_key])
+GO
+
+USE shipping_storage
+ALTER TABLE [dim_customer] ADD FOREIGN KEY ([discount_start]) REFERENCES [dim_date] ([date_key])
+GO
+
+USE shipping_storage
+ALTER TABLE [dim_customer] ADD FOREIGN KEY ([discount_end]) REFERENCES [dim_date] ([date_key])
+GO
+
+USE shipping_storage
+ALTER TABLE [dim_vehicles] ADD FOREIGN KEY ([route_start]) REFERENCES [dim_date] ([date_key])
+GO
+
+USE shipping_storage
+ALTER TABLE [dim_vehicles] ADD FOREIGN KEY ([route_end]) REFERENCES [dim_date] ([date_key])
+GO
+
+
+---------------------
+-- Проверка секций --
+---------------------
+USE shipping_storage
+
+SELECT prt.partition_number, prt.rows, prv.value low_boundary, flg.name name_filegroup
+FROM sys.partitions prt
+JOIN sys.indexes idx ON prt.object_id = idx.object_id
+JOIN sys.data_spaces dts ON dts.data_space_id = idx.data_space_id
+LEFT JOIN sys.partition_schemes prs ON prs.data_space_id = dts.data_space_id
+LEFT JOIN sys.partition_range_values prv ON prv.function_id = prs.function_id AND prv.boundary_id = prt.partition_number - 1
+LEFT JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = prs.data_space_id AND dds.destination_id = prt.partition_number
+LEFT JOIN sys.filegroups flg ON flg.data_space_id = dds.data_space_id
+WHERE prt.object_id = (SELECT object_id FROM Sys.Tables WHERE name = 'fact_delivery')
+
+
+SELECT prt.partition_number, prt.rows, prv.value low_boundary, flg.name name_filegroup
+FROM sys.partitions prt
+JOIN sys.indexes idx ON prt.object_id = idx.object_id
+JOIN sys.data_spaces dts ON dts.data_space_id = idx.data_space_id
+LEFT JOIN sys.partition_schemes prs ON prs.data_space_id = dts.data_space_id
+LEFT JOIN sys.partition_range_values prv ON prv.function_id = prs.function_id AND prv.boundary_id = prt.partition_number - 1
+LEFT JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = prs.data_space_id AND dds.destination_id = prt.partition_number
+LEFT JOIN sys.filegroups flg ON flg.data_space_id = dds.data_space_id
+WHERE prt.object_id = (SELECT object_id FROM Sys.Tables WHERE name = 'archive_fact_delivery')
+
+
+-------------
+-- Индексы --
+-------------
+USE shipping_storage
+
+SELECT SUM(total) FROM shipping_storage.dbo.fact_delivery
+GO
+
+DROP INDEX fact_total ON shipping_storage.dbo.fact_delivery
+DROP INDEX demography_age ON shipping_storage.dbo.dim_customer_demography
+DROP INDEX date_year ON shipping_storage.dbo.dim_date
+
+CREATE NONCLUSTERED COLUMNSTORE INDEX fact_total
+ON fact_delivery(volume, weight, tax_amount, subtotal, total)
+GO
+
+CREATE INDEX demography_age
+ON shipping_storage.dbo.dim_customer_demography(age)
+GO
+
+CREATE INDEX date_year
+ON shipping_storage.dbo.dim_date(year)
+GO
+
+----------------------------
+-- Метод скользящего окна --
+----------------------------
+
+SELECT * FROM sys.partition_range_values
+
+USE shipping_storage
+
+DROP PROCEDURE sliding_window
+GO
+
+CREATE PROCEDURE sliding_window
+AS
+BEGIN
+    DECLARE @DayForMaxPartFactShipping BIGINT
+    DECLARE @DayForMaxPartFactShippingDate DATE
+    DECLARE @DayForMaxPartArchiveFactShipping BIGINT
+    DECLARE @DayForMaxPartArchiveFactShippingDate DATE
+
+    SET @DayForMaxPartFactShipping = CAST((SELECT TOP 1 [value]
+                                           FROM sys.partition_range_values
+                                           WHERE function_id = (SELECT function_id
+                                                                FROM sys.partition_functions
+                                                                WHERE name = 'shipping_partition_func')
+                                           ORDER BY boundary_id DESC) AS BIGINT)
+
+    SET @DayForMaxPartArchiveFactShipping = CAST((SELECT TOP 1 [value]
+                                                  FROM sys.partition_range_values
+                                                  WHERE function_id = (SELECT function_id
+                                                                       FROM sys.partition_functions
+                                                                       WHERE name = 'archive_shipping_partition_func')
+                                                  ORDER BY boundary_id DESC) AS BIGINT)
+
+    SET @DayForMaxPartFactShippingDate = DATEFROMPARTS(CAST(@DayForMaxPartFactShipping / 1000000 as int),
+                                                       CAST(@DayForMaxPartFactShipping / 10000 % 10 as int),
+                                                       CAST(@DayForMaxPartFactShipping / 100 % 10 as int))
+
+    SET @DayForMaxPartArchiveFactShippingDate = DATEFROMPARTS(CAST(@DayForMaxPartArchiveFactShipping / 1000000 as int),
+                                                              CAST(@DayForMaxPartArchiveFactShipping / 10000 % 10 as int),
+                                                              CAST(@DayForMaxPartArchiveFactShipping / 100 % 10 as int))
+
+
+    DECLARE @Day_DT DATE
+    SET @Day_DT = DATEADD(YEAR, 1, @DayForMaxPartFactShippingDate)
+    SELECT @Day_DT
+
+    DECLARE @Day_ArchivalDT DATE
+    SET @Day_ArchivalDT = DATEADD(YEAR, 1, @DayForMaxPartArchiveFactShippingDate)
+    SELECT @Day_ArchivalDT
+
+    ALTER PARTITION SCHEME shipping_partition_scheme
+    NEXT USED [Frequently_Requested]
+
+    ALTER PARTITION SCHEME archive_shipping_partition_scheme
+    NEXT USED [Frequently_Requested]
+
+    ALTER PARTITION FUNCTION shipping_partition_func()
+    SPLIT RANGE ((CAST(CONVERT (VARCHAR(10), @Day_DT, 112) AS BIGINT)) * 100)
+
+    ALTER PARTITION FUNCTION archive_shipping_partition_func()
+    SPLIT RANGE ((CAST(CONVERT (VARCHAR(10), @Day_ArchivalDT, 112) AS BIGINT)) * 100)
+
+    ALTER TABLE fact_delivery
+    SWITCH PARTITION 2
+    TO archive_fact_delivery PARTITION 2
+
+    DECLARE @DayForMinPartFactShipping BIGINT
+    DECLARE @DayForMinPartArchiveFactShipping BIGINT
+
+    SET @DayForMinPartFactShipping = CAST((SELECT TOP 1 [value]
+                                           FROM sys.partition_range_values
+                                           WHERE function_id = (SELECT function_id
+                                                                FROM sys.partition_functions
+                                                                WHERE name = 'shipping_partition_func')
+                                           ORDER BY boundary_id) AS BIGINT)
+
+    SET @DayForMinPartArchiveFactShipping = CAST((SELECT TOP 1 [value]
+                                                  FROM sys.partition_range_values
+                                                  WHERE function_id = (SELECT function_id
+                                                                       FROM sys.partition_functions
+                                                                       WHERE name = 'archive_shipping_partition_func')
+                                                  ORDER BY boundary_id) AS BIGINT)
+
+    ALTER PARTITION FUNCTION shipping_partition_func()
+    MERGE RANGE (@DayForMinPartFactShipping)
+
+    ALTER PARTITION FUNCTION archive_shipping_partition_func()
+    MERGE RANGE (@DayForMinPartArchiveFactShipping)
+END
+GO
+
+EXEC sliding_window
+
+SELECT *
+FROM fact_delivery
+
+SELECT *
+FROM archive_fact_delivery
-- 
cgit v1.2.3

