还原效果

思路，通过统计开始到前1行 电量差 < 0 出现次数，次数*进制+电量 

select *  ,进位次数=isnull(--前所有行到上1行进位归0的次数  sum(iif(isnull(电量差, 0)<0,1,0)) over(partition by 设备名 order by ID rows between unbounded preceding and 1 preceding)   ,0)  ,累计电量=isnull(  sum(iif(isnull(电量差, 0)<0,1,0)) over(partition by 设备名 order by ID rows between unbounded preceding and 1 preceding)   ,0) * 104857.6 + 电量 --65536*1.6=104857.6，进位次数*进制
from 段采表m

rows between unbounded preceding and 1 preceding

)。

扩展理解4个关键参数即可：

rows between 2 preceding and current row #取当前行和前面两行
rows between unbounded preceding and current row #包括本行和之前所有的行
rows between current row and unbounded fo1lowing #包禧本行和之后所有的行
rows between 3 preceding and current row #包括本行和前面三行
rows between 3 preceding and 1 fo1lowing#从前面三行和下面一行，总共五行
#当order by后面缺少窗口从句条件，窗口规范默认是
rows between unbounded preceding and currentrow.
#当order by和窗口从句都缺失，窗口规范默认是
rows between unbounded preceding and unbounded fo1lowing

iif和over各自单独用OK，一起就报错?

改用sql创建就能一起用了

select *
	,表电量差=iif(电量差<0 , 1.6 , 电量差)
	,进位次数=isnull(--前所有行到上1行进位归0的次数
			sum(iif(isnull(电量差, 0)<0,1,0)) over(partition by 设备名 order by ID rows between unbounded preceding and 1 preceding) 
			,0)
	,表电量=isnull(
			sum(iif(isnull(电量差, 0)<0,1,0)) over(partition by 设备名 order by ID rows between unbounded preceding and 1 preceding) 
			,0) * 104857.6 + 电量		--65536*1.6=104857.6，进位次数*进制
from 段采表m

扩展：错行计数

缩小窗口范围