每小时电量的计算sql

吉星9527ABC

已于 2024-05-28 19:47:20 修改

阅读量421

点赞数 3

文章标签： sql 数据库前端

于 2024-05-28 19:46:27 首次发布

本文链接：https://blog.csdn.net/jinsedeme0881/article/details/139276221

版权

计算思路，把每小时的电表最大记录取出来，然后用当前小时的最大值减去上个小时的最大值即可。使用了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 (SELECT
        deviceId,
        DATE_FORMAT(CTIME, '%Y-%m-%d %H') ctime,
        MAX(EP_IMP)    epimp
      FROM electricity
      WHERE 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)    epimp
    FROM electricity
    WHERE 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 lastEmimp
    FROM a
)
SELECT b.*, b.epimp - b.lastEmimp ecValue
FROM b;