配网终端新数仓设计方案

维度表

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 VARCHAR(50) PRIMARY KEY,
  XTU_ID VARCHAR(50) NOT NULL UNIQUE,
  DeviceName VARCHAR(100),
  DeviceCategory VARCHAR(50),
  DeviceIdentityCode VARCHAR(100),
  XTU_Model VARCHAR(100),
  OperationalStatus VARCHAR(50),
  VoltageLevel VARCHAR(50),
  City VARCHAR(100),
  County VARCHAR(100),
  PowerSupplyOffice VARCHAR(100),
  Substation VARCHAR(200),
  Feeder VARCHAR(100),
  OwnershipUnit VARCHAR(100),
  MaintenanceUnit VARCHAR(100),
  MaintenanceTeam VARCHAR(100),
  DispatchManagementUnit VARCHAR(100),
  FactorySerialNumber VARCHAR(100),
  Manufacturer VARCHAR(100),
  TerrainType VARCHAR(50),
  MaintenanceAttribute VARCHAR(50),
  BureauAsset VARCHAR(50),
  AssetStatus VARCHAR(50),
  IsAssetLevelDevice BOOLEAN,
  FullPath TEXT,
  ParentFunctionalLocationID VARCHAR(100),
  OperationalLocation VARCHAR(200),
  StatusChangeTime TIMESTAMPTZ,
  CommissioningTime TIMESTAMPTZ,
  ManufactureTime TIMESTAMPTZ,
  IP_Address VARCHAR(50),
  Latitude DECIMAL(10,6),
  Longitude DECIMAL(10,6),
  EffectiveStartDate DATE,
  EffectiveEndDate DATE,
  IsCurrent BOOLEAN
);

DimFTU FTU维度表

CREATE TABLE DimFTU (
  FTUKey VARCHAR(50) PRIMARY KEY,
  FTU_ID VARCHAR(50) NOT NULL UNIQUE,
  XTUKey VARCHAR(50) NOT NULL,
  DeviceName VARCHAR(100),
  DeviceCategory VARCHAR(50),
  DeviceIdentityCode VARCHAR(100),
  FTU_Model VARCHAR(100),
  OperationalStatus VARCHAR(50),
  VoltageLevel VARCHAR(50),
  City VARCHAR(100),
  County VARCHAR(100),
  PowerSupplyOffice VARCHAR(100),
  Substation VARCHAR(200),
  Feeder VARCHAR(100),
  OwnershipUnit VARCHAR(100),
  MaintenanceUnit VARCHAR(100),
  MaintenanceTeam VARCHAR(100),
  DispatchManagementUnit VARCHAR(100),
  FactorySerialNumber VARCHAR(100),
  Manufacturer VARCHAR(100),
  TerrainType VARCHAR(50),
  MaintenanceAttribute VARCHAR(50),
  BureauAsset VARCHAR(50),
  AssetStatus VARCHAR(50),
  IsAssetLevelDevice BOOLEAN,
  FullPath TEXT,
  ParentFunctionalLocationID VARCHAR(100),
  OperationalLocation VARCHAR(200),
  StatusChangeTime TIMESTAMPTZ,
  CommissioningTime TIMESTAMPTZ,
  ManufactureTime TIMESTAMPTZ,
  LocationDescription VARCHAR(200),
  Latitude DECIMAL(10,6),
  Longitude DECIMAL(10,6),
  Status VARCHAR(50),
  EffectiveStartDate DATE,
  EffectiveEndDate DATE,
  IsCurrent BOOLEAN 
);

事实表

FactPowerReadings 电力读数事实表

CREATE TABLE FactPowerReadings (
  -- 主键和外键
  TimestampKey BIGINT NOT NULL,
  FTUKey VARCHAR(50) NOT NULL,
  ReadingTimestamp TIMESTAMPTZ NOT NULL,
  
  -- 电流指标
  I0_Current DECIMAL(10,2),
  IA_Current DECIMAL(10,2),
  IB_Current DECIMAL(10,2),
  IC_Current DECIMAL(10,2),
  
  -- 电压指标
  UA_Voltage DECIMAL(10,2),
  UB_Voltage DECIMAL(10,2),
  UC_Voltage DECIMAL(10,2),
  UAB_Voltage DECIMAL(10,2),
  UBC_Voltage DECIMAL(10,2),
  UCA_Voltage DECIMAL(10,2),
  
  -- 功率指标
  ActivePower_Total DECIMAL(10,2),     -- P (有功功率)
  ReactivePower_Total DECIMAL(10,2),   -- Q (无功功率)
  Power_COS DECIMAL(10,2),           -- COS (功率因数)
  
  -- 元数据
  LoadTimestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  
  -- 复合主键
  PRIMARY KEY (ReadingTimestamp, FTUKey)
);

创建超表

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

创建关键索引

-- 为DimFTU表创建XTUKey索引
CREATE INDEX idx_dimftu_xtukey ON DimFTU(XTUKey);

-- 为FactPowerReadings表创建TimestampKey索引
CREATE INDEX idx_factpowerreadings_timestampkey ON FactPowerReadings(TimestampKey);

-- 为FactPowerReadings表创建FTUKey索引
CREATE INDEX idx_factpowerreadings_ftukey ON FactPowerReadings(FTUKey);