how to calculate of sum of multiple conditional values in mysql

debugcn 投稿 Dev

user3325207

I'm trying to get sums of conditional values and group by minute.

I succeeded with the following query but results takes some time and wonder is there any efficient way?

SELECT x.query, x.value, x.time FROM (
    SELECT id, query, SUM(VALUE) AS value, time FROM `modbuslogs` WHERE query IN ("sensor1","sensor2") AND time LIKE '2020-12-04%' GROUP BY HOUR(time), MINUTE(TIME) 
    UNION
    SELECT id, query, SUM(VALUE) AS value, time FROM `modbuslogs` WHERE query IN ("sensor3","sensor4") AND time LIKE '2020-12-04%' GROUP BY HOUR(time), MINUTE(TIME) 
) x
GROUP BY x.query, HOUR(time), MINUTE(TIME) 
ORDER BY x.id

Table structure:

+--------+-------+-----------------+
|  query | value |       time      |
+--------+-------+-----------------+
|sensor1 |   2   |2012-02-10 00:00 |
|sensor2 |   2   |2012-02-10 00:00 |
|sensor3 |   3   |2012-02-10 00:00 |
|sensor4 |   3   |2012-02-10 00:00 |
|sensor1 |   2   |2012-02-10 00:01 |
|sensor2 |   3   |2012-02-10 00:01 |
|sensor3 |   3   |2012-02-10 00:01 |
|sensor4 |   2   |2012-02-10 00:01 |
+--------+-------+-----------------+

Obtained and expected Output:

+--------+-------+-----------------+
|  query | value |       time      |
+--------+-------+-----------------+
|sensor1 |   4   |2012-02-10 00:00 |
|sensor3 |   6   |2012-02-10 00:00 |
|sensor1 |   5   |2012-02-10 00:01 |
|sensor3 |   5   |2012-02-10 00:01 |
+--------+-------+-----------------+

GMB

There is no point for the union subquery in the first place. This is equivalent to your query:

select id, query, sum(value) as value, min(time) as time
from modbuslogs
where 
    query in ('sensor1', 'sensor2', 'sensor3', 'sensor4')
    and time >= '2020-12-04'
    and time <  '2020-12-05'
group by query, hour(time), minute(time)

This gives one row per query and per minute, with the sum of value. Not that we need an aggregate function around time, so the select clause is consistent with the group by clause. Also, the where clause uses date filtering rather than string matching.

一方、2つの異なる列に2つのセンサーグループを配置する場合は、条件付き集計を使用します。

select id, min(time) as time,
    sum(case when query in ('sensor1', 'sensor2') then value else 0 end) as value_1_2, 
    sum(case when query in ('sensor3', 'sensor4') then value else 0 end) as value_3_4
from modbuslogs
where 
    query in ('sensor1', 'sensor2', 'sensor3', 'sensor4')
    and time >= '2020-12-04'
    and time <  '2020-12-05'
group by hour(time), minute(time)

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]