某水表量每15分钟一报，然后某天示数清0了，重新报示值了 ，如何写sql 计算每日水量

要计算每日电量，需处理电表清零的情况。以下是针对不同数据库的解决方案：

方法思路

1. 识别清零点：通过比较当前值与前一个值，若当前值明显变小（如小于前值的10%），则视为清零。
2. 分段累计：将数据按清零点分段，每段单独累计电量。
3. 每日汇总：按日期分组，累加每日各段的电量总和。

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 简化模式匹配。

关键点说明

1. 清零判断：value < prev_value * 0.1 可根据实际情况调整阈值。
2. 分组逻辑：通过递归 CTE 为每个清零点分配新的 group_id。
3. 处理异常值：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

优化建议

1. 预处理数据：若频繁查询，可将分组结果存储为物化视图。
2. 索引优化：确保 timestamp 字段有索引，加速排序和窗口函数计算。
3. 动态阈值：根据历史数据动态调整清零判断阈值，提高准确性。

方法二

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