遥测数仓表结构定义

维度表和事实表SQL定义

以下是配网数仓的表结构SQL定义，包括3个维度表和1个事实表。

维度表

DimTime 时间维度表

CREATE TABLE DimTime (
    TimestampKey BIGINT PRIMARY KEY,
    FullDateTime TIMESTAMPTZ NOT NULL,
    Date DATE NOT NULL,
    Year INTEGER NOT NULL,
    Quarter INTEGER NOT NULL,
    Month INTEGER NOT NULL,
    DayOfMonth INTEGER NOT NULL,
    DayOfWeek INTEGER NOT NULL,
    WeekOfYear INTEGER NOT NULL,
    Hour INTEGER NOT NULL,
    Minute INTEGER NOT NULL,
    TimeOfDay VARCHAR(20) NOT NULL,
    IsWeekend BOOLEAN NOT NULL
);

说明：存储时间相关的维度信息。

DimXTU XTU维度表

CREATE TABLE DimXTU (
    XTUKey BIGINT PRIMARY KEY,
    XTU_ID VARCHAR(50) NOT NULL UNIQUE,
    XTU_Model VARCHAR(100),
    Substation VARCHAR(200),
    IP_Address VARCHAR(50),
    InstallationDate DATE,
    Status VARCHAR(50)
);

说明：存储XTU相关的维度信息。

DimFTU FTU维度表

CREATE TABLE DimFTU (
    FTUKey BIGINT PRIMARY KEY,
    FTU_ID VARCHAR(50) NOT NULL,
    XTUKey BIGINT NOT NULL,
    FTU_Model VARCHAR(100),
    InstallationDate DATE,
    LocationDescription VARCHAR(200),
    Latitude DECIMAL(10, 6),
    Longitude DECIMAL(10, 6),
    Status VARCHAR(50),
    EffectiveStartDate DATE NOT NULL,
    EffectiveEndDate DATE,
    IsCurrent BOOLEAN NOT NULL
);

说明：存储FTU相关的维度信息，包括与XTU的关联。

事实表

FactPowerReadings 电力读数事实表

CREATE TABLE FactPowerReadings (
    -- 主键和外键
    TimestampKey BIGINT NOT NULL,
    FTUKey BIGINT NOT NULL,
    ReadingTimestamp TIMESTAMPTZ NOT NULL,
    
    -- 电压指标
    UAB_Voltage DECIMAL(10, 2),
    UBC_Voltage DECIMAL(10, 2),
    UCA_Voltage DECIMAL(10, 2),
    UA_Voltage DECIMAL(10, 2),
    UB_Voltage DECIMAL(10, 2),
    UC_Voltage DECIMAL(10, 2),
    
    -- 电流指标
    IA_Current DECIMAL(10, 2),
    IB_Current DECIMAL(10, 2),
    IC_Current DECIMAL(10, 2),
    I0_Current DECIMAL(10, 2),
    
    -- 功率指标
    ActivePower_Total DECIMAL(10, 2),
    ActivePower_A DECIMAL(10, 2),
    ActivePower_B DECIMAL(10, 2),
    ActivePower_C DECIMAL(10, 2),
    ReactivePower_Total DECIMAL(10, 2),
    ReactivePower_A DECIMAL(10, 2),
    ReactivePower_B DECIMAL(10, 2),
    ReactivePower_C DECIMAL(10, 2),
    ApparentPower_Total DECIMAL(10, 2),
    ApparentPower_A DECIMAL(10, 2),
    ApparentPower_B DECIMAL(10, 2),
    ApparentPower_C DECIMAL(10, 2),
    
    -- 元数据
    LoadTimestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    
    -- 复合主键
    PRIMARY KEY (ReadingTimestamp, FTUKey)
);

说明：存储电力读数的事实数据，包括电压、电流、功率等指标。

创建超表

为了提高时间序列数据的存储和查询效率，将FactPowerReadings表转换为超表，并根据ReadingTimestamp进行时间分区。

SELECT create_hypertable('FactPowerReadings', 'ReadingTimestamp',
                        chunk_time_interval => INTERVAL '1 day');

说明：使用TimescaleDB的create_hypertable函数，将FactPowerReadings转换为超表，并设置按天进行分区。