path: root/task10.sql

-------------------------------------------
-- Создание базы данных и файловых групп --
-------------------------------------------

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