太阳能加热能耗统计技术问询：PostgreSQL与Grafana数据处理

你之前尝试的Grafana分组填充方法精度不足，本质是因为它需要生成每一秒的冗余点，而实际数据仅记录功率变化节点。更高效准确的方式是直接计算每个功率值的持续时长，再累加得到能耗，以下是具体实现方案：

1. PostgreSQL SQL 核心解决方案 #

能耗的本质是「功率(W) × 时间(s)」，我们通过窗口函数获取每条记录的下一个时间戳，计算当前功率的持续时长，再完成能耗计算与维度统计，同时支持单位转换。

基础逻辑：单设备全周期能耗统计 #

WITH device_data AS (
    SELECT
        id,
        ts,
        val AS power_w,
        -- 获取下一条记录的时间戳，最后一条用当前时间作为结束节点（可替换为固定结束时间）
        LEAD(ts, 1, EXTRACT(EPOCH FROM NOW())*1000) OVER (PARTITION BY id ORDER BY ts) AS next_ts
    FROM ts_number
    -- 可选：过滤指定设备 WHERE id = 23
    -- 可选：适配Grafana时间范围 WHERE ts >= $__unixEpochFrom()*1000 AND ts <= $__unixEpochTo()*1000
),
energy_calculations AS (
    SELECT
        id,
        -- 计算当前功率的持续秒数
        (next_ts - ts)/1000 AS duration_s,
        -- 单段能耗：W × s = Ws（瓦秒）
        power_w * ((next_ts - ts)/1000) AS energy_ws
    FROM device_data
)
-- 汇总并转换单位
SELECT
    id,
    SUM(energy_ws) AS total_ws,
    SUM(energy_ws)/3600 AS total_wh,   -- 1Wh = 3600Ws
    SUM(energy_ws)/3600000 AS total_kwh -- 1kWh = 3.6×10^6Ws
FROM energy_calculations
GROUP BY id;

按日/月/年统计能耗 #

以按日统计为例，只需将时间戳转换为日期维度后分组：

WITH device_data AS (
    SELECT
        id,
        ts,
        val AS power_w,
        LEAD(ts, 1, EXTRACT(EPOCH FROM NOW())*1000) OVER (PARTITION BY id ORDER BY ts) AS next_ts,
        -- 将毫秒时间戳转换为日期
        DATE_TRUNC('day', TO_TIMESTAMP(ts/1000)) AS record_date
    FROM ts_number
    -- 适配Grafana时间范围 WHERE ts >= $__unixEpochFrom()*1000 AND ts <= $__unixEpochTo()*1000
),
energy_calculations AS (
    SELECT
        id,
        record_date,
        power_w * ((next_ts - ts)/1000) AS energy_ws
    FROM device_data
)
SELECT
    id,
    record_date,
    SUM(energy_ws) AS daily_ws,
    SUM(energy_ws)/3600 AS daily_wh,
    SUM(energy_ws)/3600000 AS daily_kwh
FROM energy_calculations
GROUP BY id, record_date
ORDER BY record_date;

关键说明 #

• LEAD()窗口函数：自动获取同设备下的下一条记录时间戳，解决了「缺失记录的持续时间」问题
• 单位转换：严格遵循物理单位换算规则，避免统计误差
• Grafana适配：直接替换为$__unixEpochFrom()/$__unixEpochTo()变量，实现时间选择器联动

2. 自定义代码预处理方案（Docker兼容） #

如果SQL灵活性无法满足需求，可编写脚本定时预处理数据，生成全量时间粒度的统计结果，存储到新表供Grafana快速查询：

步骤1：创建统计结果表 #

CREATE TABLE IF NOT EXISTS energy_daily (
    id INTEGER NOT NULL,
    record_date DATE NOT NULL,
    total_kwh REAL NOT NULL,
    PRIMARY KEY (id, record_date)
);

步骤2：Python预处理脚本（示例） #

import psycopg2
from datetime import datetime, timedelta

# Docker容器间连接：用PostgreSQL容器名/IP作为host
conn = psycopg2.connect(
    dbname="iobroker",
    user="your_db_user",
    password="your_db_pass",
    host="postgres-container"
)
cur = conn.cursor()

# 处理指定设备（可循环遍历所有设备id）
device_id = 23
start_date = datetime(2023, 1, 1)
end_date = datetime.now()

current_date = start_date
while current_date <= end_date:
    # 获取当日的所有功率变化记录
    cur.execute("""
        SELECT ts, val FROM ts_number
        WHERE id = %s AND ts >= %s AND ts <= %s
        ORDER BY ts
    """, (
        device_id,
        int(current_date.timestamp())*1000,
        int((current_date + timedelta(days=1)).timestamp())*1000 - 1
    ))
    records = cur.fetchall()
    
    total_ws = 0
    prev_ts, prev_power = None, None
    for ts, power in records:
        if prev_ts is not None:
            duration_s = (ts - prev_ts)/1000
            total_ws += prev_power * duration_s
        prev_ts, prev_power = ts, power
    
    # 处理最后一条记录到当日结束的时长
    if prev_ts is not None:
        end_of_day_ts = int((current_date + timedelta(days=1)).timestamp())*1000 - 1
        duration_s = (end_of_day_ts - prev_ts)/1000
        total_ws += prev_power * duration_s
    
    # 转换为kWh并存入统计表（存在则更新）
    total_kwh = total_ws / 3600000
    cur.execute("""
        INSERT INTO energy_daily (id, record_date, total_kwh)
        VALUES (%s, %s, %s)
        ON CONFLICT (id, record_date) DO UPDATE SET total_kwh = EXCLUDED.total_kwh
    """, (device_id, current_date.date(), total_kwh))
    
    current_date += timedelta(days=1)

conn.commit()
cur.close()
conn.close()

步骤3：Docker部署 #

将脚本打包为镜像，用cron调度（例如每天凌晨运行一次），实现自动化预处理。

3. 精度优化建议 #

• 将ts_number表的val字段从REAL改为NUMERIC，避免浮点精度损失
• 统计时保留至少3位小数，确保kWh级别的统计精度

内容的提问来源于stack exchange，提问作者IceBoosteR