1. 项目背景与需求分析

在物联网(IoT)平台ThingsBoard的实际应用中，电能数据的统计分析一直是个棘手问题。传统方案通常只计算总量或平均值，但电力行业特有的"尖峰平谷"分时计价机制要求我们对数据进行更精细的处理。每个客户可能有不同的时段划分标准，比如A客户定义早8点到晚10点为峰时段，而B客户可能将下午2点到6点设为尖峰时段。

这种业务特性带来了两个技术挑战：

1. 时段定义存储在客户属性中，是动态可变的JSON结构
2. 设备注册初期可能还未分配客户归属，无法获取时段规则

最初我们尝试用数据库触发器实时计算，但发现当面对百万级设备的海量遥测数据时，触发器频繁解析JSON规则导致数据库性能急剧下降。实测显示，高峰期CPU利用率可达90%以上，严重影响了系统整体响应速度。

2. 技术方案选型

2.1 触发器方案的局限性

触发器(Trigger)看似是实时计算的理想选择，但在我们的场景中存在致命缺陷：

• 性能瓶颈：每条数据插入都会触发规则解析和计算
• 数据完整性风险：设备注册初期没有客户归属时无法计算
• 维护困难：业务规则变更需要重建触发器

通过pgBadger分析慢查询日志发现，单个触发器的执行时间可能长达200ms，这在批量插入场景下完全不可接受。

2.2 定时批处理方案的优势

改用PostgreSQL的定时任务(pgAgent)方案后：

• 计算集中化：每天凌晨统一处理，避开业务高峰
• 资源可控：可以限制任务执行时的CPU和内存占用
• 容错性强：单日数据问题不影响整体系统

我们设计了两个聚合函数：

• put_ts_kv_daily_a：通用统计（总量/平均/最大/最小）
• put_ts_kv_daily_b：专用于电能的分时段统计

3. 数据库结构优化

3.1 表结构变更

为支持分时段统计，我们对ts_kv_daily表进行了扩展：

ALTER TABLE ts_kv_daily ADD COLUMN sum_value_a DOUBLE PRECISION DEFAULT 0; ALTER TABLE ts_kv_daily ADD COLUMN sum_value_b DOUBLE PRECISION DEFAULT 0; ALTER TABLE ts_kv_daily ADD COLUMN sum_value_c DOUBLE PRECISION DEFAULT 0; ALTER TABLE ts_kv_daily ADD COLUMN sum_value_d DOUBLE PRECISION DEFAULT 0;

这四个字段分别对应：

• sum_value_a：尖峰时段总和
• sum_value_b：高峰时段总和
• sum_value_c：平段总和
• sum_value_d：谷段总和

3.2 JSON规则解析函数

创建专用函数解析客户配置的时段规则：

CREATE OR REPLACE FUNCTION public.parse_electric_quantity_rule( rule_json jsonb ) RETURNS TABLE( month integer, time_type text, start_time time, end_time time ) LANGUAGE sql IMMUTABLE AS $$ -- 实现JSON解析逻辑 $$;

4. 核心聚合函数实现

4.1 函数架构设计

put_ts_kv_daily_b函数的主要处理流程：

1. 计算当日时间范围（转为毫秒时间戳）
2. 联查设备-客户-时段规则关系
3. 按时段分类聚合数据
4. 执行UPSERT操作更新日统计表

4.2 关键SQL片段解析

WITH customer_parsed_rules AS ( SELECT ak.entity_id as customer_id, per.month, per.time_type, per.start_time, per.end_time FROM attribute_kv ak INNER JOIN key_dictionary kd ON kd.key_id = ak.attribute_key JOIN public.parse_electric_quantity_rule(ak.json_v::jsonb) per ON TRUE WHERE kd.key = '尖峰平谷' ) INSERT INTO ts_kv_daily (...) SELECT a.entity_id, p_key, v_daily_ts, SUM(...), COUNT(*), AVG(...), MIN(...), MAX(...), SUM(CASE WHEN cpr.time_type = 'times_a' THEN ... END), -- 尖峰 SUM(CASE WHEN cpr.time_type = 'times_b' THEN ... END), -- 高峰 SUM(CASE WHEN cpr.time_type = 'times_c' THEN ... END), -- 平段 SUM(CASE WHEN cpr.time_type = 'times_d' THEN ... END) -- 谷段 FROM ts_kv a /* 关联逻辑 */ ON CONFLICT (entity_id, key, ts) DO UPDATE SET ...;

5. 定时任务配置

5.1 pgAgent任务设置

通过pgAdmin创建定时任务：

1. 每天凌晨2点执行
2. 先处理常规指标（功率因素等）
3. 再处理电能分时段统计

-- 凌晨2:05执行 SELECT put_ts_kv_daily_a(CURRENT_DATE - 1, 96); -- 功率因素 SELECT put_ts_kv_daily_b(CURRENT_DATE - 1, 98); -- 有功电量

5.2 监控与日志

添加任务执行日志检查：

-- 检查任务执行状态 SELECT * FROM pgagent.pga_joblog WHERE jobjclid = (SELECT jobid FROM pgagent.pga_job WHERE jobname = 'daily_stats'); -- 检查错误信息 SELECT * FROM pgagent.pga_jobsteplog WHERE jsljclid = (SELECT jobid FROM pgagent.pga_job WHERE jobname = 'daily_stats') AND jslstatus = 'f';

6. 性能优化技巧

6.1 索引优化建议

必须确保以下索引存在：

-- 原始数据表索引 CREATE INDEX IF NOT EXISTS idx_ts_kv_entity_key_ts ON ts_kv(entity_id, key, ts); -- 日统计表索引 CREATE INDEX IF NOT EXISTS idx_ts_kv_daily_entity_key_ts ON ts_kv_daily(entity_id, key, ts);

6.2 内存参数调整

在postgresql.conf中增加工作内存：

work_mem = 16MB # 原值通常为4MB maintenance_work_mem = 256MB # 原值通常为64MB

6.3 并行查询配置

对于大型客户，启用并行查询：

SET max_parallel_workers_per_gather = 4;

7. 常见问题排查

7.1 数据缺失问题

现象：某设备的分时段统计数据为空 排查步骤：

1. 检查设备是否已分配客户

   SELECT customer_id FROM device WHERE id = '设备ID';

2. 检查客户是否配置时段规则

   SELECT * FROM attribute_kv WHERE entity_id = '客户ID' AND attribute_key = ( SELECT key_id FROM key_dictionary WHERE key = '尖峰平谷' );

7.2 性能下降问题

现象：聚合任务执行时间变长 优化方案：

1. 按客户分批处理：

   -- 先获取客户列表 WITH customers AS ( SELECT DISTINCT customer_id FROM device WHERE id IN ( SELECT entity_id FROM ts_kv WHERE key = 98 AND ts BETWEEN ... AND ... ) ) -- 然后逐个客户处理 SELECT put_ts_kv_daily_b_for_customer( CURRENT_DATE - 1, 98, customer_id ) FROM customers;

8. 实际应用效果

实施该优化后，系统性能指标显著提升：

• 数据库CPU峰值负载从90%降至35%
• 每日统计任务平均耗时从47分钟缩短到8分钟
• 报表查询响应时间从秒级降到毫秒级

某电力公司客户反馈，分时段统计准确率从原来的78%提升到99.9%，极大提升了他们的电费核算效率。