□What you want to do
Could you tell me about the SQL query statement when I divide the 'value' column for each 'name' below and redisplay it?
□mysql version
+---------------+
| version()|
+------------+
| 5.1.73-log |
+------------+
□ Current Output Results
select from_unixtime(history.clock, '%Y-%m-%d%H:%i', hosts.host, items.name, history.value from history
inner join items using (itemid)
inner join hosts using (hostid)
where hosts.host like '%hoge%'
and items.name like '%Temp%'
and from_unixtime(clock) between'2019-03-0700:00' and'2020-03-0800:00'
+------------------------------+-------------------+-----------+----------+
| from_unixtime(history.clock) | host | name | value |
+------------------------------+-------------------+-----------+----------+
| 2019-12-0700:02 | hoge1 | Temp_CPU0 | -99.0000 |
| 2019-12-0700:07 | hoge1 | Temp_CPU0 | -98.0000 |
| 2019-12-0700:02 | hoge1 | Temp_CPU1 | -97.0000 |
| 2019-12-0700:07 | hoge1 | Temp_CPU1 | -96.0000 |
| 2019-12-0700:02 | hoge2 | Temp_CPU0 | -95.0000 |
| 2019-12-0700:07 | hoge2 | Temp_CPU0 | -94.0000 |
| 2019-12-0700:02 | hoge2 | Temp_CPU1 | -93.0000 |
| 2019-12-0700:07 | hoge2 | Temp_CPU1 | -92.0000 |
□ Expected output
+----------------------------------------------------------------------------------------------------------------
| from_unixtime(history.clock) | host | Temp_CPU0 | Temp_CPU1
+------------------------------+-------------------+----------+----------+
| 2019-12-0700:02 | hoge1 | -99.0000 | -97.0000 |
| 2019-12-0700:07 | hoge1 | -98.0000 | -96.0000 |
| 2019-12-0700:02 | hoge2 | -95.0000 | -93.0000 |
| 2019-12-0700:07 | hoge2 | -94.0000 | -92.0000 |
[Answer]
Although it is not SQL that lists 'value' for every 'name', it is possible to write SQL that lists Temp_CPU0, Temp_CPU1 fixed 'value' in one line.
Use a sub-query.
[SQL using sub-queries]
select from_unixtime (CPU0.clock, '%Y-%m-%d%H:%i', CPU0.host, CPU0.value as Temp_CPU0, CPU1.value as Temp_CPU1
from
(
select xx.clock, xx.host, xx.name, xx.value
from
(
select history.clock, hosts.host, items.name, history.value
from history
inner join items using (itemid)
inner join hosts using (hostid)
where hosts.host like '%hoge%'
and items.name like '%Temp%'
and from_unixtime(clock) between'2019-03-0700:00' and'2020-03-0800:00'
)xx
where name like '%Temp_CPU0%'
US>CPU0
,
(
select xx.clock, xx.host, xx.name, xx.value
from
(
select history.clock, hosts.host, items.name, history.value
from history
inner join items using (itemid)
inner join hosts using (hostid)
where hosts.host like '%hoge%'
and items.name like '%Temp%'
and from_unixtime(clock) between'2019-03-0700:00' and'2020-03-0800:00'
)xx
where name like '%Temp_CPU1%'
US>CPU1
where
CPU0.host=CPU1.host and
CPU0.clock = CPU1.clock
;
[SQL using WITH clause]
You can only use MySQL 8.0 or later, but you can use the WITH clause to do the same.
with
source as(
select history.clock, hosts.host, items.name, history.value
from history
inner join items using (itemid)
inner join hosts using (hostid)
where hosts.host like '%hoge%'
and items.name like '%Temp%'
and from_unixtime(clock) between'2019-03-0700:00' and'2020-03-0800:00'
),
cpu0 as(
select clock, host, name, value from source where name like '%Temp_CPU0%'
),
cpu1 as(
select clock, host, name, value from source where name like '%Temp_CPU1%'
)
select from_unixtime(cpu0.clock, '%Y-%m-%d%H:%i', cpu0.host, cpu0.value as Temp_CPU0, cpu1.value as Temp_CPU1
from
(select clock, host, name, value from source where name like '%Temp_CPU0%') cpu0
,
(select clock, host, name, value from source where name like '%Temp_CPU1%') cpu1
where
cpu0.host=cpu1.host and
cpu0.clock = cpu1.clock
;
© 2024 OneMinuteCode. All rights reserved.