每小时电量的计算sql

计算思路，把每小时的电表最大记录取出来，然后用当前小时的最大值减去上个小时的最大值即可。 使用了MYSQL8窗口函数进行计算。

SELECT b.*,b.epimp - b.lastEmimp ecValue  FROM (
SELECT a.deviceId,a.ctime,a.epimp, lag(epimp) over (ORDER BY a.deviceId,a.ctime) AS lastEmimp
FROM (SELECTdeviceId,DATE_FORMAT(CTIME, '%Y-%m-%d %H') ctime,MAX(EP_IMP)    epimpFROM electricityWHERE deviceId IN('AAA1','AAAA2')AND ctime > '2024-05-01'GROUP BY deviceId, DATE_FORMAT(CTIME, '%Y-%m-%d %H')) a)b 

经过AI优化后的sql

WITH a AS (SELECT deviceId,DATE_FORMAT(CTIME, '%Y-%m-%d %H') ctime,MAX(EP_IMP)    epimpFROM electricityWHERE deviceId IN('1001','1002')AND ctime > '2024-05-01'GROUP BY deviceId, DATE_FORMAT(CTIME, '%Y-%m-%d %H')
),
b AS (SELECT a.deviceId,a.ctime,a.epimp,LAG(a.epimp) OVER (ORDER BY a.deviceId, a.ctime) AS lastEmimpFROM a
)
SELECT b.*, b.epimp - b.lastEmimp ecValue
FROM b;