某水表量每15分钟一报，然后某天示数清0了，重新报示值了 ，如何写sql 计算每日水量
要计算每日电量，需处理电表清零的情况。以下是针对不同数据库的解决方案：
方法思路
- 识别清零点：通过比较当前值与前一个值，若当前值明显变小（如小于前值的10%），则视为清零。
- 分段累计：将数据按清零点分段，每段单独累计电量。
- 每日汇总：按日期分组，累加每日各段的电量总和。
SQL 实现（以 MySQL 为例）
WITH RECURSIVE
-- 1. 添加行号和前一个值
t1 AS (SELECT`timestamp`,value,ROW_NUMBER() OVER (ORDER BY `timestamp`) AS rn,LAG(value) OVER (ORDER BY `timestamp`) AS prev_valueFROM meter_data
),
-- 2. 标记清零点（当前值 < 前值的10%）
t2 AS (SELECT`timestamp`,value,rn,prev_value,CASE WHEN prev_value IS NOT NULL AND value < prev_value * 0.1 THEN 1 ELSE 0 END AS is_resetFROM t1
),
-- 3. 为每个清零点分配组ID（使用递归CTE）
t3 AS (SELECT`timestamp`,value,rn,is_reset,rn AS group_idFROM t2WHERE rn = 1UNION ALLSELECTt2.`timestamp`,t2.value,t2.rn,t2.is_reset,CASE WHEN t2.is_reset = 1 THEN t2.rn ELSE t3.group_id ENDFROM t2JOIN t3 ON t2.rn = t3.rn + 1
),
-- 4. 计算每组的增量并累加
t4 AS (SELECT`timestamp`,DATE(`timestamp`) AS date,value,group_id,value - LAG(value, 1, value) OVER (PARTITION BY group_id ORDER BY `timestamp`) AS incrementFROM t3
)
-- 5. 按日期汇总电量（忽略负值，可能是异常或清零残留）
SELECTdate,SUM(CASE WHEN increment >= 0 THEN increment ELSE 0 END) AS daily_energy
FROM t4
GROUP BY date
ORDER BY date;
其他数据库适配
- PostgreSQL：与 MySQL 类似，但递归 CTE 语法更简洁：
WITH RECURSIVE t3 AS (...) - SQL Server：使用
ISNULL替代COALESCE，窗口函数语法相同。 - Oracle：使用
CONNECT BY替代递归 CTE，或用MATCH_RECOGNIZE简化模式匹配。
关键点说明
- 清零判断：
value < prev_value * 0.1可根据实际情况调整阈值。 - 分组逻辑：通过递归 CTE 为每个清零点分配新的
group_id。 - 处理异常值：
SUM(CASE WHEN increment >= 0 ...)过滤可能的负值（如清零残留）。
示例数据验证
假设有以下数据（每15分钟记录一次）：
| timestamp | value |
|---|---|
| 2023-01-01 00:00:00 | 100 |
| 2023-01-01 00:15:00 | 110 |
| 2023-01-01 23:45:00 | 900 |
| 2023-01-02 00:00:00 | 50 |
| 2023-01-02 23:45:00 | 800 |
计算结果：
| date | daily_energy |
|---|---|
| 2023-01-01 | 800 |
| 2023-01-02 | 750 |
优化建议
- 预处理数据：若频繁查询，可将分组结果存储为物化视图。
- 索引优化：确保
timestamp字段有索引，加速排序和窗口函数计算。 - 动态阈值：根据历史数据动态调整清零判断阈值，提高准确性。
方法二
with a1 as(select * from (--查询有清0 的表SELECTId, ProjectId, fd,val,dot,PointId,LAG(val) OVER (PARTITION BY ProjectId,PointId ORDER BY dot asc,val desc) AS prev_value , LAG(Id) OVER (PARTITION BY ProjectId,PointId ORDER BY dot asc,val desc) AS prev_Id FROM table1where fd >='2025-05-06'and fd<='2025-05-07'and dot<='2025-05-07 00:00:00'and ProjectId=170132 ) as twhere 1=1and val < prev_value * 0.1),av3 as(--查询有清0 的表的最后一条数据select * from (select a.id,a.PointId,a.fd, ROW_NUMBER() OVER (PARTITION BY a.ProjectId,a.PointId ORDER BY a.dot desc,a.val desc) AS rnfrom a1 t1,table1 awhere t1.PointId=a.pointId and t1.dot<a.dotand a.fd >='2025-05-06'and a.fd<='2025-05-07'and a.dot<='2025-05-07 00:00:00') as twhere t.rn=1) ,a2 as (--查询有清0 的表的前一条数据，当前数据 和最后一条数据的select a.*,-1 as preItem from a1,table1 awhere a1.prev_Id=a.Id union allselect a.*,0 as preItem from a1,table1 awhere a1.id=a.Id union allselect a.*,1 as preItem from av3 a3,table1 awhere a3.id=a.Id )select * from av3