scala做网站,长春宣传片拍摄,城市建设厅官方网站,网站焦点图怎么做计算思路#xff0c;把每小时的电表最大记录取出来#xff0c;然后用当前小时的最大值减去上个小时的最大值即可。 使用了MYSQL8窗口函数进行计算。
SELECT b.*,b.epimp - b.lastEmimp ecValue FROM (
SELECT a.deviceId,a.ctime,a.epimp, lag(epimp) over (ORDER BY a.dev…计算思路把每小时的电表最大记录取出来然后用当前小时的最大值减去上个小时的最大值即可。 使用了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-01GROUP 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-01GROUP 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;
