diff options
Diffstat (limited to 'task10.sql')
| -rw-r--r-- | task10.sql | 444 |
1 files changed, 444 insertions, 0 deletions
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 |