文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
首页
学习
活动
专区
圈层
工具
MCP广场
返回腾讯云官网
社区首页 >问答首页 >从单个表动态返回多个平均值的SQL语句是什么？

问从单个表动态返回多个平均值的SQL语句是什么？
EN

Stack Overflow用户
提问于 2015-11-19 08:13:36
回答 2查看 83关注 0票数 1

我需要生成一个基于用户设置的动态结果，以减少通过post返回的数量。样本表：

代码语言：javascript
运行
复制
+----------+-------+---------------------+
| 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语句实现此操作)：

代码语言：javascript
运行
复制
$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

，例如 if

$time =1 $unit =小时$sample_factor =1//小时

结果将是：

代码语言：javascript
运行
复制
+----------+-------+---------------------+
| 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 =小时$sample_factor = .5 //小时

结果是

代码语言：javascript
运行
复制
+----------+-------+---------------------+
| 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工作台上进行了实验之后，我认为我已经非常接近于此(仍然很难确定准确性和设置时间间隔，但我认为这是非常接近和简洁的)：

注意:当数据被填充时，添加了一个硬unix时间戳，几乎不需要每秒进行额外的处理，并且确实有助于实现这个部分。

代码语言：javascript
运行
复制
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;
sampling
mysql
logging
average
EN

回答 2

Stack Overflow用户

发布于 2015-11-19 11:20:41

设置

代码语言：javascript
运行
复制
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
;

查询

代码语言：javascript
运行
复制
-- 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
;

输出

代码语言：javascript
运行
复制
+----------+------------+---------------------+
| 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 |
+----------+------------+---------------------+

木琴

票数 2
EN

Stack Overflow用户

发布于 2015-11-19 23:16:11

代码语言：javascript
运行
复制
SELECT cpu_name, AVG(used) as used, unix_timestamp FROM 
     (SELECT cpu_name, used, (". $sql_sample_size. "*ROUND(unix_timestamp/". $sql_sample_size. ")) As unix_timestamp FROM tbl_cpu_use WHERE timestamp>(NOW() - INTERVAL ". $time. " ". $sql_unit. ")) AS sub_table 
GROUP BY cpu_name,unix_timestamp ORDER BY unix_timestamp,cpu_name DESC;

似乎产生了所需的输出：

cpu_name，二手，unix_timestamp，unix_timestamp

代码语言：javascript
运行
复制
CPU 1, 0.0420843, 1447966800.0000, 2015-11-19 16:29:59
CPU 3, 0.0248727, 1447966800.0000, 2015-11-19 16:29:59
CPU 0, 0.0728558, 1447966800.0000, 2015-11-19 16:29:59
CPU 2, 0.0388895, 1447966800.0000, 2015-11-19 16:29:59
CPU 2, 0.0405227, 1447970400.0000, 2015-11-19 17:29:59
CPU 1, 0.0445057, 1447970400.0000, 2015-11-19 17:29:59
CPU 3, 0.0288837, 1447970400.0000, 2015-11-19 17:29:59
CPU 0, 0.0663175, 1447970400.0000, 2015-11-19 17:29:59
CPU 1, 0.0522862, 1447974000.0000, 2015-11-19 18:14:48
CPU 3, 0.0358891, 1447974000.0000, 2015-11-19 18:14:48
CPU 0, 0.0551599, 1447974000.0000, 2015-11-19 18:14:48
CPU 2, 0.0378004, 1447974000.0000, 2015-11-19 18:14:48
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/33798400

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档