mysql 选择两个值显示出来,MySQL显示两个值之和

Below is my query.

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()

Which gives me below result

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

Zeeshan 37010114711 4A60193390663 4.5

I have tried to do the following

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

and got Error Code: 4074 Window functions can not be used as arguments to group functions.

解决方案

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

so the differences are:

kwh_diff

--------

0

12-10

14-12

17-14

25-17

32-25

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

the diffference between the last value and the 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.

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.