mysql - 单表动态返回多个平均值的SQL语句是什么

Question

我需要根据用户设置生成动态结果，以减少邮寄退回的数量。示例表:

+----------+-------+---------------------+
| cpu_name | used  | timestamp           |
+----------+-------+---------------------+
| CPU 3    | 0.200 | 2015-11-19 03:09:11 |
| CPU 2    | 0.000 | 2015-11-19 03:09:11 |
| CPU 1    | 0.000 | 2015-11-19 03:09:11 |
| CPU 0    | 0.025 | 2015-11-19 03:09:11 |
| CPU 3    | 0.000 | 2015-11-19 03:09:10 |
| CPU 2    | 0.000 | 2015-11-19 03:09:10 |
| CPU 1    | 0.000 | 2015-11-19 03:09:10 |
| CPU 0    | 0.000 | 2015-11-19 03:09:10 |
| CPU 3    | 0.000 | 2015-11-19 03:09:09 |
| CPU 2    | 0.000 | 2015-11-19 03:09:09 |
| CPU 1    | 0.000 | 2015-11-19 03:09:09 |
| CPU 0    | 0.122 | 2015-11-19 03:09:09 |
| CPU 3    | 0.000 | 2015-11-19 03:09:07 |
| CPU 2    | 0.225 | 2015-11-19 03:09:07 |
| CPU 1    | 0.000 | 2015-11-19 03:09:07 |
| CPU 0    | 0.000 | 2015-11-19 03:09:07 |
| CPU 0    | 0.025 | 2015-11-19 04:45:01 |
+----------+-------+---------------------+

需要对每个 cpu 的行进行平均，每隔 X 小时/天/等。

伪 SLQ(我如何用一个 SQL 语句做到这一点):

$time = 10
$unit = DAYS
$sample_factor = 1 //hour
for each CPU:
     $sql = "SELECT AVERAGE_every_hour(cpu_use) FROM tbl_cpu_use WHERE timestamp > (NOW() - INTERVAL ". $time. " ". $unit)"
     RETURN RESULTS BUT AS IF IT WERE ONE QUERY

例如如果

$time = 1
$unit = Hour
$sample_factor = 1 //hour

结果是:

+----------+-------+---------------------+
| cpu_name | used  | timestamp           |
+----------+-------+---------------------+
| CPU 3    | 0.200 | 2015-11-19 03 |
| CPU 2    | 0.000 | 2015-11-19 03 |
| CPU 1    | 0.000 | 2015-11-19 03 |
| CPU 0    | 0.025 | 2015-11-19 03 |
| CPU 0    | 0.025 | 2015-11-19 05 |

但是如果

$time = 1
$unit = Hour
$sample_factor = .5 //hour

结果是

+----------+-------+---------------------+
| cpu_name | used  | timestamp           |
+----------+-------+---------------------+
| CPU 3    | 0.200 | 2015-11-19 03:00 |
| CPU 2    | 0.000 | 2015-11-19 03:00 |
| CPU 1    | 0.000 | 2015-11-19 03:00 |
| CPU 0    | 0.025 | 2015-11-19 03:00 |
| CPU 3    | 0.200 | 2015-11-19 03:30 |
| CPU 2    | 0.000 | 2015-11-19 03:30 |
| CPU 1    | 0.000 | 2015-11-19 03:30 |
| CPU 0    | 0.025 | 2015-11-19 03:30 |
| CPU 0    | 0.025 | 2015-11-19 05:00 |

注意:忽略结果中的“已使用”列值，假设它们是该时间段内的平均值。 “时间戳”列和平均值很重要。

编辑: 在 mySQL workbench 中进行试验后，我认为我已经非常接近这个(仍然很难确定准确性和设置间隔的时间，但我认为这已经非常接近了简洁):

注意:在填充数据时添加了一个硬性 unix 时间戳，每秒进行很少的额外处理，这对这部分确实有帮助。

SET @time_interval := date_sub(NOW(), INTERVAL 2 HOUR);
SET @sample_interval := 60;

SELECT cpu_name, AVG(used) as used, @sample_interval*AVG(ROUND(unix_timestamp/@sample_interval)) as unix_timestamp FROM 
    (SELECT cpu_name, used, @sample_interval*ROUND(unix_timestamp/@sample_interval) As unix_timestamp, `timestamp` FROM BH_DB.tbl_cpu_use WHERE `timestamp`>@time_interval ORDER BY id DESC LIMIT 18446744073709551615) AS sorted_table
GROUP BY cpu_name, unix_timestamp ORDER BY unix_timestamp;

Answer 1

设置

create table tbl_cpu_use
(
  cpu_name varchar(10) not null,
  used decimal(5,4) not null,
  `timestamp` timestamp not null,
  primary key ( cpu_name, `timestamp` )
);

insert into tbl_cpu_use
( cpu_name, used, `timestamp` )
values
( 'CPU 3'    , 0.200 , '2015-11-19 03:39:11' ),
( 'CPU 2'    , 0.000 , '2015-11-19 03:39:11' ),
( 'CPU 1'    , 0.000 , '2015-11-19 03:39:11' ),
( 'CPU 0'    , 0.025 , '2015-11-19 03:39:11' ),
( 'CPU 3'    , 0.200 , '2015-11-19 03:09:11' ),
( 'CPU 2'    , 0.000 , '2015-11-19 03:09:11' ),
( 'CPU 1'    , 0.000 , '2015-11-19 03:09:11' ),
( 'CPU 0'    , 0.025 , '2015-11-19 03:09:11' ),
( 'CPU 3'    , 0.000 , '2015-11-19 03:09:10' ),
( 'CPU 2'    , 0.000 , '2015-11-19 03:09:10' ),
( 'CPU 1'    , 0.000 , '2015-11-19 03:09:10' ),
( 'CPU 0'    , 0.000 , '2015-11-19 03:09:10' ),
( 'CPU 3'    , 0.000 , '2015-11-19 03:09:09' ),
( 'CPU 2'    , 0.000 , '2015-11-19 03:09:09' ),
( 'CPU 1'    , 0.000 , '2015-11-19 03:09:09' ),
( 'CPU 0'    , 0.122 , '2015-11-19 03:09:09' ),
( 'CPU 3'    , 0.000 , '2015-11-19 03:09:07' ),
( 'CPU 2'    , 0.225 , '2015-11-19 03:09:07' ),
( 'CPU 1'    , 0.000 , '2015-11-19 03:09:07' ),
( 'CPU 0'    , 0.000 , '2015-11-19 03:09:07' )
;

create view digits
as
select 0 as num
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
;

查询

-- define the sampling interval size
set @interval_seconds := 600;

select slots.cpu_name, 
-- for when cpu isnt active or no data for, use 0
avg(coalesce(cpu.used, 0)) as avg_used, 
slots.`time`
from 
(
-- construct consecutive timeslots starting from minimum timestamp
-- and definition of a decimal number as weighted sum of powers of 10
select `min` + interval (a2.num*100 + a1.num*10 + a0.num) * @interval_seconds second as `time`, cpu_names.cpu_name
from
-- get the minimum and maximum timestamp from tbl_cpu_use timeseries
(
  select max(`timestamp`) as `max`, min(`timestamp`) as `min`
  from tbl_cpu_use
) bounds
cross join
-- get all cpu_names to duplicate across timeslots when cpus arent active
(
  select distinct cpu_name
  from tbl_cpu_use
) cpu_names
cross join digits a2
cross join digits a1
cross join digits a0
-- filter timeslots between timeseries min and max
where `min` + interval (a2.num*100 + a1.num*10 + a0.num) * @interval_seconds second
<=     `max`
) slots
-- include also information for timeslots when cpus arent active
left join tbl_cpu_use cpu
on timestampdiff(second, slots.`time`, cpu.`timestamp`) between 0 and @interval_seconds
and slots.cpu_name = cpu.cpu_name
group by slots.cpu_name, slots.`time`
order by slots.`time`, slots.cpu_name
;

输出

+----------+------------+---------------------+
| cpu_name | avg_used   | time                |
+----------+------------+---------------------+
| CPU 0    | 0.03675000 | 2015-11-19 03:09:07 |
| CPU 1    | 0.00000000 | 2015-11-19 03:09:07 |
| CPU 2    | 0.05625000 | 2015-11-19 03:09:07 |
| CPU 3    | 0.05000000 | 2015-11-19 03:09:07 |
| CPU 0    | 0.00000000 | 2015-11-19 03:19:07 |
| CPU 1    | 0.00000000 | 2015-11-19 03:19:07 |
| CPU 2    | 0.00000000 | 2015-11-19 03:19:07 |
| CPU 3    | 0.00000000 | 2015-11-19 03:19:07 |
| CPU 0    | 0.00000000 | 2015-11-19 03:29:07 |
| CPU 1    | 0.00000000 | 2015-11-19 03:29:07 |
| CPU 2    | 0.00000000 | 2015-11-19 03:29:07 |
| CPU 3    | 0.00000000 | 2015-11-19 03:29:07 |
| CPU 0    | 0.02500000 | 2015-11-19 03:39:07 |
| CPU 1    | 0.00000000 | 2015-11-19 03:39:07 |
| CPU 2    | 0.00000000 | 2015-11-19 03:39:07 |
| CPU 3    | 0.20000000 | 2015-11-19 03:39:07 |
+----------+------------+---------------------+

sqlfiddle