MySQL显示两个值的差异之和

2022-01-09 00:00:00 sql sum window-functions select mysql

以下是我的查询.

SELECT n.`name`,n.`customer_id`,m.`msn`, m.kwh,
   m.kwh - LAG(m.kwh) OVER(PARTITION BY n.`customer_id` ORDER BY m.`data_date_time`) AS kwh_diff
FROM mdc_node n
INNER JOIN `mdc_meters_data` m ON n.`customer_id` = m.`cust_id`
WHERE n.`lft` = 5 AND n.`icon` NOT IN ('folder')
AND m.`data_date_time` BETWEEN NOW() - INTERVAL 30 DAY AND NOW()

这给了我下面的结果

我想总结 kwh_diff 并只显示一行记录而不是如下所示的多个记录

I want to sum up the kwh_diff and to show only one-row record not multiple like below

name customer_id msn sum_kwh_diff

泽山37010114711 4A60193390663 4.5

我尝试过以下操作

 SUM(m.kwh - LAG(m.kwh) OVER(PARTITION BY n.`customer_id` ORDER BY m.`data_date_time`)) AS sum_kwh_diff

并得到错误代码:4074 窗口函数不能用作组函数的参数.

推荐答案

你想对连续行之间的差异求和.
例如，假设您有列 kwh 的这些值:

You want to sum the differences between consecutive rows.
Say, for example, that you have these values for the column kwh:

kwh
---
10
12
14
17
25
32

所以区别是:

kwh_diff
--------
0
12-10
14-12
17-14
25-17
32-25

这些差值之和等于 32-10，即:

The sum of these differences is equal to 32-10 which is:

最后一个值和第一个值的区别

the diffference between the last value and the first value

所以你需要的是窗口函数 FIRST_VALUE() 来获取这些值:

So what you need is window function FIRST_VALUE() to obtain these values:

SELECT DISTINCT n.`name`, n.`customer_id`, m.`msn`, 
   FIRST_VALUE(m.kwh) OVER (PARTITION BY n.`customer_id` ORDER BY m.`data_date_time` DESC) -
   FIRST_VALUE(m.kwh) OVER (PARTITION BY n.`customer_id` ORDER BY m.`data_date_time` ASC) AS kwh_diff
FROM mdc_node n
INNER JOIN `mdc_meters_data` m ON n.`customer_id` = m.`cust_id`
WHERE n.`lft` = 5 AND n.`icon` NOT IN ('folder')
AND m.`data_date_time` BETWEEN NOW() - INTERVAL 30 DAY AND NOW()

并且不需要子查询或聚合.

and no subquery or aggregation is needed.

我将 PARTITION BY n.customer_id 保留在我的代码中，因为您在代码中使用了它，尽管您可能需要 PARTITION BY n.customer_id, m.msn.

I kept in my code PARTITION BY n.customer_id because you use it in your code, although you may need PARTITION BY n.customer_id, m.msn.

相关文章