能耗成本核算的数据库Schema设计与异步数据处理问询

首先明确结论：必须拆分跨成本变更时间点的功耗时段来计算。因为能耗成本是按时间区间生效的，只要传感器的功耗覆盖了不同成本生效的时间段，就需要拆分计算才能保证成本核算的准确性——哪怕只是几分钟的偏差，也会影响最终结果的精度。

下面是具体的解决思路，从数据定义、模型优化到实际计算一步步拆解：

1. 先明确数据的时间语义，消除歧义 #

这是解决所有问题的前提，必须先把两张表的字段定义搞清楚：

• 传感器数据表：
  你提到是“按小时记录传感器的功耗数据”，但实际记录时间是00:03:12这种精确到秒的格式，得明确：
  • 选项A：这条记录的Timestamp是时段结束时间，Value是[Timestamp - 1小时, Timestamp)这个固定小时区间的总功耗（哪怕记录延迟到00:03，对应的还是23:00-00:00的功耗）；
  • 选项B：这条记录的Timestamp是数据产生时间，Value是[上一次该传感器的记录时间, 当前Timestamp)这段连续时间的累计功耗。
• 成本变更表：
  每条记录的Timestamp是成本生效的起始时间，Value是单位能耗成本（比如元/千瓦时），该成本会持续到下一条成本变更记录的Timestamp为止；如果是最后一条记录，就持续到你核算的截止时间（比如2024年3月1日00:00）。

2. 优化成本变更数据的查询逻辑 #

为了方便后续关联计算，建议给成本变更表生成一个带生效起止时间的视图（或直接新增字段存储），这样每条成本记录都对应一个明确的时间区间。用SQL窗口函数可以快速实现：

SELECT 
  Timestamp AS cost_start_time,
  -- 用LEAD获取下一条变更的时间，没有的话用核算截止时间
  LEAD(Timestamp, 1, '2024-03-01 00:00:00') OVER (ORDER BY Timestamp) AS cost_end_time,
  Value AS unit_cost
FROM cost_changes;

这个视图会输出类似这样的结果：

3. 关联传感器数据与成本时段，拆分计算 #

根据传感器数据的语义，分两种场景处理：

场景A：传感器对应固定小时时段 #

比如记录时间是00:03:12，但对应的是23:00:00-00:00:00的功耗：

1. 先计算出这条记录对应的固定时段[sensor_start, sensor_end)（比如sensor_start = DATE_FORMAT(Timestamp, '%Y-%m-%d %H:00:00')，sensor_end = DATE_ADD(sensor_start, INTERVAL 1 HOUR)）；
2. 关联成本视图，找出所有与这个时段有重叠的成本区间；
3. 对每个重叠的子时段，计算：

   子时段时长（小时） × （总功耗 ÷ 传感器时段总时长） × 单位成本
   

4. 把所有子时段的成本相加，就是这条传感器记录的总成本。

场景B：传感器对应连续记录时段 #

比如记录时间是00:03:12，对应的是[上一次记录时间, 00:03:12)的累计功耗：

1. 确定该记录的时段[last_record_time, current_record_time)；
2. 关联成本视图，找出所有重叠的成本区间；
3. 同样按子时段拆分计算，公式和场景A一致，只是传感器时段总时长是current_record_time - last_record_time；
4. 求和得到总成本。

4. 处理边缘情况 #

• 如果传感器记录时间早于最早的成本变更时间：直接用最早的成本值计算；
• 如果传感器记录时间晚于最晚的成本变更时间：用最晚的成本值计算；
• 成本变更时间刚好和传感器时段边界重合：统一用左闭右开区间（比如[start, end)），避免重复计算或遗漏。

5. 示例：拆分计算的实际应用 #

假设：

• 传感器记录：Timestamp='2024-01-02 00:10:00'，Value=0.4kWh，对应的时段是[2024-01-01 23:50:00, 2024-01-02 00:10:00)；
• 成本变更：2024-01-01 23:55:00时，成本从0.8元/kWh变为0.9元/kWh。

拆分计算：

1. 子时段1：23:50:00-23:55:00，时长5分钟=1/12小时，占总时段（20分钟）的25%，功耗=0.4×25%=0.1kWh，成本=0.1×0.8=0.08元；
2. 子时段2：23:55:00-00:10:00，时长15分钟=1/4小时，占75%，功耗=0.4×75%=0.3kWh，成本=0.3×0.9=0.27元；
3. 总成本=0.08+0.27=0.35元。

6. 数据库层面的实现建议 #

可以用SQL的关联查询直接完成所有计算，比如：

SELECT 
  s.SensorId,
  s.Timestamp AS sensor_record_time,
  SUM(
    -- 计算重叠子时段的时长（转换为小时）
    (TIMESTAMPDIFF(SECOND, GREATEST(s.sensor_start, c.cost_start_time), LEAST(s.sensor_end, c.cost_end_time)) / 3600)
    -- 计算该子时段对应的功耗
    * (s.Value / TIMESTAMPDIFF(SECOND, s.sensor_start, s.sensor_end) * 3600)
    -- 乘以单位成本
    * c.unit_cost
  ) AS total_cost
FROM sensors s
-- 关联成本视图，匹配重叠时段
JOIN cost_view c 
  ON s.sensor_start < c.cost_end_time 
  AND s.sensor_end > c.cost_start_time
-- 筛选1-2月的数据
WHERE s.sensor_start >= '2024-01-01 00:00:00' 
  AND s.sensor_end <= '2024-03-01 00:00:00'
GROUP BY s.SensorId, s.Timestamp;

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