0
305
VistasMySQL muestra la suma de la diferencia de dos valores
A continuación se muestra mi consulta.
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()Lo que me da el siguiente resultado.
Quiero resumir el kwh_diff y mostrar solo un registro de una fila, no múltiples como a continuación
name customer_id msn sum_kwh_diff
Zeeshan 37010114711 4A60193390663 4.5
he intentado hacer lo siguiente
SUM(m.kwh - LAG(m.kwh) OVER(PARTITION BY n.`customer_id` ORDER BY m.`data_date_time`)) AS sum_kwh_diff y obtuve Error Code: 4074 Window functions can not be used as arguments to group functions.
3 Respuestas
Responde la pregunta0
No puede usar funciones de ventana dentro de una función agregada (aunque es posible lo contrario). Aquí, debe usar una subconsulta y agregar en la consulta externa:
SELECT name, customer_id, SUM(kwh_diff) sum_kwh_diff FROM ( 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() ) t GROUP BY name, customer_id0
HACER UNA CONSULTA EXTERNA
SELECT `name`,`customer_id`,`msn`, SUM(kwh_diff) kwh_diff FROM ( 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() ) t1 GROUP BY `name`,`customer_id`,`msn`0
Desea sumar las diferencias entre filas consecutivas.
Digamos, por ejemplo, que tiene estos valores para la columna kwh :
kwh --- 10 12 14 17 25 32entonces las diferencias son:
kwh_diff -------- 0 12-10 14-12 17-14 25-17 32-25 La suma de estas diferencias es igual a 32-10 que es:
la diferencia entre el último valor y el primer valor
Entonces, lo que necesita es la función de ventana FIRST_VALUE() para obtener estos valores:
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() y no se necesita subconsulta o agregación.
Mantuve en mi código PARTITION BY n.customer_id porque lo usa en su código, aunque es posible que necesite PARTITION BY n.customer_id, m.msn .