供水监测系统水表读数存储设计及历史读数获取问题咨询

1. 读数数据表结构设计方案 #

直接选单张统一读数表的方案，完全没必要为每个水表单独建表，理由如下：

• 单表结构可维护性极强，后续用户量增长到几十万、几百万级也不用额外调整表结构，分表方案会随用户增长出现表数量爆炸，备份、查询、改字段都要操作海量表，完全不可行
• 所有读数统一存储，后续做区域用水量统计、漏损分析、批量对账等操作时，直接查表即可，不用跨N张表聚合查询，性能和开发成本都低很多

推荐的读数表基础字段如下：

注：你写的耗水量公式Previous Reading - Current Reading大概率是笔误，正常应该是当前读数 - 上次读数才是正向的耗水量，注意校验逻辑里别搞反了。

2. 录入新读数时自动填充上次读数的实现方式 #

两种常用实现方案，根据你的技术栈选即可：

方案1：应用层逻辑实现（更灵活，推荐） #

每次抄表员提交新的当前读数时，业务代码先执行一次查询，拿到该水表最新的一条记录的current_reading值，作为本次插入记录的previous_reading，再执行插入操作即可。
示例伪代码：

// 拿到本次提交的水表编号和新读数
newMeterNo = req.meterNo
newCurrent = req.currentReading
// 查询该水表最新读数
lastRecord = db.query("SELECT current_reading FROM meter_reading WHERE meter_no = ? ORDER BY reading_time DESC LIMIT 1", newMeterNo)
// 组装数据插入
insertData = {
  meter_no: newMeterNo,
  current_reading: newCurrent,
  previous_reading: lastRecord ? lastRecord.current_reading : 0, // 新水表第一次读数默认上次为0
  reading_time: now()
}
db.insert("meter_reading", insertData)

方案2：数据库触发器实现（无需改业务代码，适合逻辑固定的场景） #

在数据库层面给meter_reading表建BEFORE INSERT触发器，插入新记录前自动查询同水表的最新读数赋值给previous_reading字段，业务代码只需要传meter_no和current_reading即可。

3. 从已有记录中获取对应上次读数的方法 #

如果你的历史数据里没有存previous_reading字段，或者需要临时校验取值，直接用数据库的窗口函数LAG()即可实现，不用自己写关联查询。
示例SQL：

SELECT 
  id,
  meter_no,
  current_reading,
  -- 取同水表按抄表时间排序的上一条记录的current_reading作为上次读数
  LAG(current_reading, 1, 0) OVER (PARTITION BY meter_no ORDER BY reading_time ASC) AS previous_reading,
  reading_time
FROM meter_reading

如果用的是不支持窗口函数的低版本数据库，可以用自关联的方式查询：

SELECT 
  a.id,
  a.meter_no,
  a.current_reading,
  IFNULL(b.current_reading, 0) AS previous_reading,
  a.reading_time
FROM meter_reading a
LEFT JOIN meter_reading b 
  ON a.meter_no = b.meter_no 
  AND b.reading_time = (SELECT MAX(reading_time) FROM meter_reading WHERE meter_no = a.meter_no AND reading_time < a.reading_time)

内容的提问来源于stack exchange，提问作者Vega Aldrin M