# 设备表
CREATE TABLE DEVICE (
DEVICE_ID VARCHAR(32) NOT NULL,
DEVICE_CODE VARCHAR(50) NOT NULL,
DEVICE_NAME VARCHAR(100) NOT NULL,
DEVICE_TYPE VARCHAR(20) NOT NULL,
DEVICE_MODEL VARCHAR(100),
MANUFACTURER VARCHAR(100),
INSTALL_DATE DATE,
LOCATION_ID VARCHAR(20),
DEVICE_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_DEVICE PRIMARY KEY (DEVICE_ID)
) ORGANIZE BY ROW;

# 位置表
CREATE TABLE LOCATION (
LOCATION_ID VARCHAR(20) NOT NULL,
LOCATION_NAME VARCHAR(100) NOT NULL,
PARENT_LOCATION_ID VARCHAR(20),
LOCATION_LEVEL INTEGER,
LOCATION_TYPE VARCHAR(20),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_LOCATION PRIMARY KEY (LOCATION_ID)
) ORGANIZE BY ROW;

# 设备参数表
CREATE TABLE DEVICE_PARAM (
PARAM_ID VARCHAR(32) NOT NULL,
DEVICE_ID VARCHAR(32) NOT NULL,
PARAM_CODE VARCHAR(50) NOT NULL,
PARAM_NAME VARCHAR(100) NOT NULL,
PARAM_TYPE VARCHAR(20),
UNIT VARCHAR(20),
MIN_VALUE DECIMAL(20, 6),
MAX_VALUE DECIMAL(20, 6),
ALARM_LOW DECIMAL(20, 6),
ALARM_HIGH DECIMAL(20, 6),
PARAM_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_DEVICE_PARAM PRIMARY KEY (PARAM_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE UNIQUE INDEX IDX_DEVICE_CODE ON DEVICE(DEVICE_CODE);
CREATE INDEX IDX_DEVICE_TYPE ON DEVICE(DEVICE_TYPE, DEVICE_STATUS);
CREATE INDEX IDX_DEVICE_LOCATION ON DEVICE(LOCATION_ID, DEVICE_STATUS);
CREATE INDEX IDX_DEVICE_PARAM_DEVICE ON DEVICE_PARAM(DEVICE_ID);
CREATE UNIQUE INDEX IDX_DEVICE_PARAM_CODE ON DEVICE_PARAM(DEVICE_ID, PARAM_CODE);

# 采集数据表
CREATE TABLE DEVICE_DATA (
DATA_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
DEVICE_ID VARCHAR(32) NOT NULL,
PARAM_ID VARCHAR(32) NOT NULL,
COLLECT_TIME TIMESTAMP NOT NULL,
PARAM_VALUE DECIMAL(20, 6),
QUALITY INTEGER,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_DEVICE_DATA PRIMARY KEY (DATA_ID)
) PARTITION BY RANGE (COLLECT_TIME)
(
STARTING ‘2026-01-01-00.00.00.000000’
ENDING ‘2026-12-31-23.59.59.999999’
EVERY 1 HOURS
);

# 告警表
CREATE TABLE ALARM (
ALARM_ID VARCHAR(32) NOT NULL,
DEVICE_ID VARCHAR(32) NOT NULL,
PARAM_ID VARCHAR(32),
ALARM_TYPE VARCHAR(20) NOT NULL,
ALARM_LEVEL VARCHAR(20) NOT NULL,
ALARM_TITLE VARCHAR(200),
ALARM_CONTENT CLOB,
ALARM_TIME TIMESTAMP NOT NULL,
RECOVER_TIME TIMESTAMP,
ALARM_STATUS VARCHAR(20) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_ALARM PRIMARY KEY (ALARM_ID)
) PARTITION BY RANGE (ALARM_TIME)
(
STARTING ‘2026-01-01-00.00.00.000000’
ENDING ‘2026-12-31-23.59.59.999999’
EVERY 1 DAY
);

# 创建索引
CREATE INDEX IDX_DEVICE_DATA_DEVICE ON DEVICE_DATA(DEVICE_ID, COLLECT_TIME DESC) PARTITIONED;
CREATE INDEX IDX_DEVICE_DATA_PARAM ON DEVICE_DATA(PARAM_ID, COLLECT_TIME DESC) PARTITIONED;
CREATE INDEX IDX_ALARM_DEVICE ON ALARM(DEVICE_ID, ALARM_TIME DESC) PARTITIONED;
CREATE INDEX IDX_ALARM_STATUS ON ALARM(ALARM_STATUS, ALARM_TIME DESC) PARTITIONED;

# 启用表压缩
ALTER TABLE DEVICE_DATA COMPRESS YES ADAPTIVE;

# 创建压缩表
CREATE TABLE DEVICE_DATA_ARCHIVE (
DATA_ID BIGINT NOT NULL,
DEVICE_ID VARCHAR(32) NOT NULL,
PARAM_ID VARCHAR(32) NOT NULL,
COLLECT_TIME TIMESTAMP NOT NULL,
PARAM_VALUE DECIMAL(20, 6),
QUALITY INTEGER,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP
) COMPRESS YES STATIC
PARTITION BY RANGE (COLLECT_TIME)
(
STARTING ‘2025-01-01-00.00.00.000000’
ENDING ‘2025-12-31-23.59.59.999999’
EVERY 1 DAY
);

# 数据归档
INSERT INTO DEVICE_DATA_ARCHIVE
SELECT * FROM DEVICE_DATA
WHERE COLLECT_TIME < '2026-01-01'; DELETE FROM DEVICE_DATA WHERE COLLECT_TIME < '2026-01-01'; # 查看压缩信息 SELECT TABSCHEMA, TABNAME, COMPRESSION, ROWCOMPMODE, PCTPAGESSAVED FROM SYSCAT.TABLES WHERE TABNAME = 'DEVICE_DATA'; # 重组表以应用压缩 REORG TABLE DEVICE_DATA; RUNSTATS ON TABLE DEVICE_DATA AND INDEXES ALL;

# 小时聚合表
CREATE TABLE DEVICE_DATA_HOUR (
AGG_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
DEVICE_ID VARCHAR(32) NOT NULL,
PARAM_ID VARCHAR(32) NOT NULL,
AGG_TIME TIMESTAMP NOT NULL,
MIN_VALUE DECIMAL(20, 6),
MAX_VALUE DECIMAL(20, 6),
AVG_VALUE DECIMAL(20, 6),
SUM_VALUE DECIMAL(20, 6),
DATA_COUNT INTEGER,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_DEVICE_DATA_HOUR PRIMARY KEY (AGG_ID)
) PARTITION BY RANGE (AGG_TIME)
(
STARTING ‘2026-01-01-00.00.00.000000’
ENDING ‘2026-12-31-23.59.59.999999’
EVERY 1 DAY
);

# 天聚合表
CREATE TABLE DEVICE_DATA_DAY (
AGG_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
DEVICE_ID VARCHAR(32) NOT NULL,
PARAM_ID VARCHAR(32) NOT NULL,
AGG_DATE DATE NOT NULL,
MIN_VALUE DECIMAL(20, 6),
MAX_VALUE DECIMAL(20, 6),
AVG_VALUE DECIMAL(20, 6),
SUM_VALUE DECIMAL(20, 6),
DATA_COUNT INTEGER,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_DEVICE_DATA_DAY PRIMARY KEY (AGG_ID)
) PARTITION BY RANGE (AGG_DATE)
(
STARTING ‘2026-01-01’ ENDING ‘2026-12-31’ EVERY 1 MONTH
);

# 聚合存储过程
CREATE OR REPLACE PROCEDURE SP_AGGREGATE_DATA(
IN p_start_time TIMESTAMP,
IN p_end_time TIMESTAMP
)
LANGUAGE SQL
BEGIN
INSERT INTO DEVICE_DATA_HOUR (
DEVICE_ID, PARAM_ID, AGG_TIME,
MIN_VALUE, MAX_VALUE, AVG_VALUE, SUM_VALUE, DATA_COUNT
)
SELECT
DEVICE_ID,
PARAM_ID,
DATE_TRUNC(‘HOUR’, COLLECT_TIME) AS AGG_TIME,
MIN(PARAM_VALUE) AS MIN_VALUE,
MAX(PARAM_VALUE) AS MAX_VALUE,
AVG(PARAM_VALUE) AS AVG_VALUE,
SUM(PARAM_VALUE) AS SUM_VALUE,
COUNT(*) AS DATA_COUNT
FROM DEVICE_DATA
WHERE COLLECT_TIME BETWEEN p_start_time AND p_end_time
GROUP BY DEVICE_ID, PARAM_ID, DATE_TRUNC(‘HOUR’, COLLECT_TIME);

END;

# 设备实时状态视图
CREATE OR REPLACE VIEW V_DEVICE_REALTIME AS
SELECT
d.DEVICE_ID,
d.DEVICE_CODE,
d.DEVICE_NAME,
d.DEVICE_TYPE,
d.DEVICE_STATUS,
l.LOCATION_NAME,
(SELECT PARAM_VALUE
FROM DEVICE_DATA
WHERE DEVICE_ID = d.DEVICE_ID
ORDER BY COLLECT_TIME DESC
FETCH FIRST 1 ROW ONLY) AS LAST_VALUE,
(SELECT COLLECT_TIME
FROM DEVICE_DATA
WHERE DEVICE_ID = d.DEVICE_ID
ORDER BY COLLECT_TIME DESC
FETCH FIRST 1 ROW ONLY) AS LAST_COLLECT_TIME
FROM DEVICE d
LEFT JOIN LOCATION l ON d.LOCATION_ID = l.LOCATION_ID;

# 设备告警统计
SELECT
d.DEVICE_ID,
d.DEVICE_NAME,
COUNT(*) AS ALARM_COUNT,
SUM(CASE WHEN a.ALARM_LEVEL = ‘CRITICAL’ THEN 1 ELSE 0 END) AS CRITICAL_COUNT,
SUM(CASE WHEN a.ALARM_LEVEL = ‘WARNING’ THEN 1 ELSE 0 END) AS WARNING_COUNT
FROM DEVICE d
LEFT JOIN ALARM a ON d.DEVICE_ID = a.DEVICE_ID
AND a.ALARM_TIME BETWEEN CURRENT TIMESTAMP – 7 DAYS AND CURRENT TIMESTAMP
GROUP BY d.DEVICE_ID, d.DEVICE_NAME
ORDER BY ALARM_COUNT DESC;

# 能耗统计
SELECT
d.DEVICE_ID,
d.DEVICE_NAME,
l.LOCATION_NAME,
DATE(dd.COLLECT_TIME) AS DATA_DATE,
SUM(dd.PARAM_VALUE) AS TOTAL_CONSUMPTION,
AVG(dd.PARAM_VALUE) AS AVG_CONSUMPTION
FROM DEVICE d
INNER JOIN LOCATION l ON d.LOCATION_ID = l.LOCATION_ID
INNER JOIN DEVICE_DATA dd ON d.DEVICE_ID = dd.DEVICE_ID
WHERE d.DEVICE_TYPE = ‘METER’
AND dd.COLLECT_TIME BETWEEN ‘2026-04-01’ AND ‘2026-04-30’
GROUP BY d.DEVICE_ID, d.DEVICE_NAME, l.LOCATION_NAME, DATE(dd.COLLECT_TIME)
ORDER BY DATA_DATE DESC, TOTAL_CONSUMPTION DESC;