При работе с большими базами данных, содержащими времязависимые данные, такие как записи о торгах на бирже с точностью до миллисекунды, может быть сложно эффективно создать таблицу измерения времени. В этой статье мы рассмотрим быстрое решение с использованием кросс-соединения (декартово произведение) в SQL Server.

Допустим, у нас есть клиент с базой данных объемом 50 ТБ, содержащей записи о торгах на бирже с точностью до миллисекунды. Создание таблицы измерения времени с 86 400 000 записями с использованием цикла с вставками займет много времени. Однако мы можем использовать мощь кросс-соединения для быстрого создания таблицы измерения времени.

Вот пример фрагмента кода, демонстрирующего, как создать таблицу измерения времени с использованием кросс-соединения:

/* CREATE TIME PART TABLES TO DO A CROSS JOIN CARTESIAN PRODUCT TO CTAS INTO DIMTIME */

-- CREATE A SINGLE VALUE TABLE TO USE AS DUMMY FROM TABLE.
IF NOT EXISTS (SELECT 1 FROM DirectEdgeDW.sys.tables WHERE name = 'Singleton' AND type = 'U')
BEGIN
    CREATE TABLE DirectEdgeDW.dbo.Singleton
    WITH (DISTRIBUTION = REPLICATE)
    AS
    SELECT DISTINCT 1 AS VALUE FROM SYS.DATABASES
END

-- GENERATE MILLISECOND TABLE
IF EXISTS (SELECT 1 FROM DirectEdgeDW.sys.tables WHERE name = 'MCL_TimePart_MS' AND type = 'U')
BEGIN
    DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_MS
END

CREATE TABLE DirectEdgeDW.dbo.MCL_TimePart_MS
WITH (DISTRIBUTION = REPLICATE)
AS
SELECT TOP 999 ROW_NUMBER() OVER (ORDER BY REQUEST_ID) AS Millisecond
FROM sys.dm_pdw_exec_requests

INSERT INTO MCL_TimePart_MS
VALUES (0)

-- SELECT * FROM MCL_TimePart_MS

IF EXISTS (SELECT 1 FROM DirectEdgeDW.sys.tables WHERE name = 'MCL_TimePart_SS' AND type = 'U')
BEGIN
    DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_SS
END

CREATE TABLE DirectEdgeDW.dbo.MCL_TimePart_SS
WITH (DISTRIBUTION = REPLICATE)
AS
SELECT Millisecond AS Second FROM DirectEdgeDW.dbo.MCL_TimePart_MS WHERE Millisecond < 60

-- SELECT * FROM MCL_TimePart_SS

IF EXISTS (SELECT 1 FROM DirectEdgeDW.sys.tables WHERE name = 'MCL_TimePart_MM' AND type = 'U')
BEGIN
    DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_MM
END

CREATE TABLE DirectEdgeDW.dbo.MCL_TimePart_MM
WITH (DISTRIBUTION = REPLICATE)
AS
SELECT second AS Minute FROM DirectEdgeDW.dbo.MCL_TimePart_SS

-- SELECT * FROM MCL_TimePart_MM

IF EXISTS (SELECT 1 FROM DirectEdgeDW.sys.tables WHERE name = 'MCL_TimePart_HH' AND type = 'U')
BEGIN
    DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_HH
END

CREATE TABLE DirectEdgeDW.dbo.MCL_TimePart_HH
WITH (DISTRIBUTION = REPLICATE)
AS
SELECT second AS Hour FROM DirectEdgeDW.dbo.MCL_TimePart_SS WHERE Second < 24

-- SELECT * FROM MCL_TimePart_HH

-- CROSS JOIN
IF EXISTS (SELECT 1 FROM DirectEdgeDW.sys.tables WHERE name = 'DimTime' AND type = 'U')
BEGIN
    DROP TABLE DirectEdgeDW.dbo.DimTime
END

CREATE TABLE DirectEdgeDW.dbo.DimTime
WITH (CLUSTERED INDEX(TimeKey),DISTRIBUTION = REPLICATE)
AS
SELECT 
    CAST(CAST(hh.Hour AS VARCHAR(2)) + RIGHT('0' + CAST(mm.Minute AS VARCHAR(2)),2) + RIGHT('0' + CAST(ss.Second AS VARCHAR(2)),2) + RIGHT('00' + CAST(ms.Millisecond AS VARCHAR(3)),3) AS INT) AS TimeKey,
    hh.Hour, mm.Minute, ss.Second, ms.Millisecond
FROM
    DirectEdgeDW.dbo.MCL_TimePart_MS ms
    CROSS JOIN DirectEdgeDW.dbo.MCL_TimePart_SS ss
    CROSS JOIN DirectEdgeDW.dbo.MCL_TimePart_MM mm
    CROSS JOIN DirectEdgeDW.dbo.MCL_TimePart_HH hh

INSERT INTO dbo.DimDate (DateKey,FullDateAltKey,DayNumberOfWeek,DayNameOfWeek,DayNumberOfMonth,DayNumberOfYear,WeekNumberOfYear,WeekNameOfYear,[MonthName],MonthNumberOfYear,MonthFlag,QuarterNumber,QuarterName,QuarterFlag,SemesterNumber,SemesterName,SemesterFlag,[Year])
SELECT 
    -1, 
    CAST('1/1/2100' AS DATE), 
    0, 
    'Unknown', 
    0, 
    0, 
    0, 
    'Unknown', 
    'Unknown', 
    0, 
    0, 
    0, 
    'Unknown', 
    0, 
    0, 
    'Unknown', 
    0, 
    0
FROM 
    [DirectEdgeDW].[dbo].[UnknownMembers]

DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_MS
DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_SS
DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_MM
DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_HH

В приведенном выше коде мы сначала создаем отдельные таблицы для миллисекунд, секунд, минут и часов с использованием техники кросс-соединения. Затем мы выполняем кросс-соединение этих таблиц для создания таблицы измерения времени “DimTime”. Наконец, мы вставляем необходимые данные в таблицу “DimDate”.

Используя мощь кросс-соединения, мы можем эффективно создать таблицу измерения времени с точностью до миллисекунды, даже для больших баз данных. Этот подход позволяет избежать затрат времени на циклы с вставками, что приводит к значительной экономии времени.

Помните, что создание таблицы измерения с точностью до миллисекунды является редким и обычно требуется только для конкретных сценариев, таких как анализ торговли на бирже. Однако понимание концепции и техники, лежащих в основе этого подхода, может быть полезным для разработчиков и администраторов SQL Server.

Это все для этой статьи! Мы надеемся, что вы найдете ее информативной и полезной. Следите за новыми советами и трюками по SQL Server.