问题描述
下面是我的查询.
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
Zeeshan 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
得到了Error Code: 4074 Window functions can not be used as arguments to group functions.
推荐答案
您要对连续行之间的差异求和.
举例来说,您在列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:
因此,您需要的是窗口函数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, m.msn
,但我仍将代码保存在代码PARTITION BY n.customer_id
中,因为您在代码中使用了它.
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
.
这篇关于MySQL显示两个值之和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!