diff options
| -rw-r--r-- | mac-compose.yml | 13 | ||||
| -rw-r--r-- | task10-generate.sql | 164 | ||||
| -rw-r--r-- | task10.sql | 444 | ||||
| -rw-r--r-- | task10_sectioning.sql | 10 |
4 files changed, 631 insertions, 0 deletions
diff --git a/mac-compose.yml b/mac-compose.yml new file mode 100644 index 0000000..8db78a9 --- /dev/null +++ b/mac-compose.yml @@ -0,0 +1,13 @@ +version: '3' + +services: + sqledge-db: + container_name: sqledge-db + image: mcr.microsoft.com/azure-sql-edge:latest + ports: + - 1433:1433 + environment: + ACCEPT_EULA: Y + SA_PASSWORD: Datab@sed1337 + volumes: + - ./mssql-data:/var/opt/mssql diff --git a/task10-generate.sql b/task10-generate.sql new file mode 100644 index 0000000..14b8e34 --- /dev/null +++ b/task10-generate.sql @@ -0,0 +1,164 @@ +-------------- +-- dim_date -- +-------------- + +SET LANGUAGE Russian +use shipping_storage + +DECLARE @startday datetime = '2018-01-01T12:00:00' +DECLARE @endday datetime = '2026-01-01T12:00:00' +DECLARE @inc datetime +SET @inc = @startday +DECLARE @keyDate bigint + +WHILE @inc <= @endday +BEGIN +SET @keyDate = ( SELECT DATEPART(hour, @inc) + + DATEPART(day, @inc) * 100 + + DATEPART(month, @inc) * 10000 + + DATEPART(year, @inc) * 1000000 ) + + INSERT INTO shipping_storage.dbo.dim_date + (date_key, date, year, quarter, month, week, day, hour) + VALUES + ( @keyDate, + @inc, + year(@inc), + datepart(quarter, @inc), + month(@inc), + datepart(week,@inc), + day(@inc), + datename(hour, @inc) ) + SET @inc = DATEADD(DAY, 1, @inc) +END +GO + +------------------- +-- dim_geography -- +------------------- + +SET LANGUAGE Russian +use shipping_storage + +INSERT INTO shipping_storage.dbo.dim_geography +(region_name, region_code, postal_code, city_name) +VALUES +(N'Саратовская область', 64, 410065, N'Саратов'), +(N'Московская область', 77, 410065, N'Подольск'), +(N'Московская область', 77, 410065, N'Москва') +GO + + +------------------ +-- dim_wrapping -- +------------------ + +INSERT INTO shipping_storage.dbo.dim_wrapping +(name, price) +VALUES (N'Жёсткая', 100), + (N'Полужёсткая', 100), + (N'Мягкая', 100), + (N'Воздушно-Пузырьковая', 100), + (N'Рулон', 100) +GO + + +---------------------- +-- dim_payment_kind -- +---------------------- + +INSERT INTO shipping_storage.dbo.dim_payment_kind +(name) +VALUES (N'Наличными'), + (N'Картой'), + (N'Картой МИР'), + (N'Чеком') +GO + + +----------------------------- +-- dim_customer_demography -- +----------------------------- + +SET LANGUAGE Russian +use shipping_storage + +DECLARE @n INT +SET @n = 1 + +WHILE @n <= 1000 +BEGIN + INSERT INTO shipping_storage.dbo.dim_customer_demography + (age, marital_status, number_of_children) + VALUES (20, N'Не женат', 0) + SET @n = @n + 1 +END +GO + +------------------ +-- dim_customer -- +------------------ + +SET LANGUAGE Russian +use shipping_storage + +DECLARE @n INT +SET @n = 1 + +WHILE @n <= 1000 +BEGIN + INSERT INTO shipping_storage.dbo.dim_customer + (demography_key, name, email, birthday, gender, phone_number, tax_number, geography_key, entry_date, discount, discount_start, discount_end, flag) + VALUES (@n, N'Иван Улитин', 'ivan@ulitin.ru', 2019043012, N'Мужской', N'+7900112233', 11223344, 1, 2022043012, 0.2, 2022042512, 2022043012, 1) + SET @n = @n + 1 +END +GO + +------------------ +-- dim_vehicles -- +------------------ + +SET LANGUAGE Russian +USE shipping_storage + +INSERT INTO shipping_storage.dbo.dim_vehicles +(name, tonnage, gas_consumption, on_route, route_start, route_end, flag) +VALUES +(N'Камаз', 100, 100, 0, 2022042512, 2022042612, 0) +GO + + +------------------- +-- fact_delivery -- +------------------- + +SET LANGUAGE Russian +USE shipping_storage + +DECLARE @startday datetime = '2018-01-01T12:00:00' +DECLARE @endday datetime = '2026-01-01T12:00:00' +DECLARE @inc DATETIME +SET @inc = @startday +DECLARE @keyDate BIGINT + +DECLARE @n INT +SET @n = 1 + +WHILE @inc < @endday +BEGIN + SET @keyDate = ( SELECT DATEPART(hour, @inc) + + DATEPART(day, @inc) * 100 + + DATEPART(month, @inc) * 10000 + + DATEPART(year, @inc) * 1000000 ) + + INSERT INTO shipping_storage.dbo.fact_delivery + (wrapping_key, payment_kind_key, volume, weight, customer_key, order_date, expected_delivery_date, factual_delivery_date, geography_from_key, geography_to_key, vehicle_key, subtotal, discount, tax_amount, total) + VALUES + (1, 1, 10, 10, @n % 1000 + 1, @keyDate, @keyDate, @keyDate, 1, 1, 1, 1000, 0, 200, 1200) + + SET @inc = DATEADD(DAY, 1, @inc) + SET @n = @n + 1 +END +GO + +-- DELETE FROM shipping_storage.dbo.archive_fact_delivery
\ No newline at end of file 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 diff --git a/task10_sectioning.sql b/task10_sectioning.sql new file mode 100644 index 0000000..f771c2b --- /dev/null +++ b/task10_sectioning.sql @@ -0,0 +1,10 @@ +USE master +DROP DATABASE shipping_storage +GO + +USE shipping_storage + +DROP PARTITION SCHEME shipping_partition_scheme; +DROP PARTITION SCHEME archive_shipping_partition_scheme; +DROP PARTITION FUNCTION shipping_partition_func; +DROP PARTITION FUNCTION archive_shipping_partition_func;
\ No newline at end of file |