以下是我的查询。
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()
这给了我下面的结果
![enter image description here](https://i.stack.imgur.com/u4gto.png)
我想总结一下kwh_diff
并仅显示一行记录而不是多行记录,如下所示
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
and got Error Code: 4074 Window functions can not be used as arguments to group functions.
您想要对连续行之间的差异求和。
例如,假设您有该列的这些值kwh
:
kwh
---
10
12
14
17
25
32
所以差异是:
kwh_diff
--------
0
12-10
14-12
17-14
25-17
32-25
这些差异的总和等于32-10
这是:
最后一个值和第一个值之间的差
所以你需要的是窗口函数FIRST_VALUE()
获得这些值:
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()
并且不需要子查询或聚合。
我保留在我的代码中PARTITION BY n.customer_id
因为你在代码中使用它,尽管你可能需要PARTITION BY n.customer_id, m.msn
.
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)