个人中心
文章发布
退出
作者热门文章
- html - 出于某种原因，IE8 对我的 Sass 文件中继承的 html5 CSS 不友好？
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景？
25
4
我习惯在 SQL Server 中执行这种查询。我不了解 MySQL。
我希望我传达的是，我想根据 monitor_id 和一天中的小时对日期范围内的 noise_detail 记录进行分组，但在每一行，然后我想要每个聚合行的 noise_level 列的平均值，然后是每个聚合行中的 count(*) 行的平均值。
这是我们正在跟踪的原始事件的一些示例数据。
天哪。大量编辑 =AGAIN= 以解释源数据...
此示例数据中的每一行代表来自数十个监视器的“噪声事件”，每个监视器编码为“SEA01、SEA02、SEA03 等”。它们每个每小时、每天都会获取数十个读数。
了解某个日期范围内凌晨 3 点发生的事件的平均数量非常重要。所以:计算 1 月 1 日凌晨 3 点在 SEA01 发生的事件的 COUNT()，然后是 1 月 2 日凌晨 3 点在 SEA01 发生的事件的 COUNT()，等等，然后给出 -average-所有这些日期。
我们还需要知道每台显示器每小时的平均噪音水平。所以类似地，取 1 月 1 日凌晨 3 点在 SEA01 发生的事件的 AVG(噪音水平)，然后是 1 月 2 日凌晨 3 点在 SEA01 发生的事件的平均水平(噪音水平)，等等，然后给我所有这些日期的平均值。
ID monitor_id time noise_level
393211 SEA14 2016-03-22 15:39:00 79
393245 SEA11 2016-03-05 07:20:00 81.6
338262 SEA15 2016-02-28 19:44:00 80.4
338263 SEA14 2016-02-28 19:55:00 74.2
338264 SEA14 2016-02-28 19:54:00 81.5
338265 SEA14 2016-02-28 19:44:00 73.4
338266 SEA13 2016-02-28 19:54:00 81
338267 SEA13 2016-02-28 19:43:00 94.3
338268 SEA12 2016-02-28 19:43:00 80.2
338269 SEA11 2016-02-28 19:53:00 89
338270 SEA11 2016-02-28 19:43:00 89.5
338271 SEA09 2016-02-28 19:43:00 75.2
338272 SEA09 2016-02-28 19:42:00 73.9
338273 SEA09 2016-02-28 19:41:00 85.1
338588 SEA28 2016-02-29 05:04:00 83.3
338589 SEA22 2016-02-29 05:04:00 82.7
338590 SEA21 2016-02-29 05:04:00 82.9
338591 SEA20 2016-02-29 05:04:00 84.1
338592 SEA19 2016-02-29 05:03:00 88.6
338593 SEA18 2016-02-29 05:03:00 85.5
338594 SEA17 2016-02-29 05:03:00 86.6
338749 SEA14 2016-02-28 20:43:00 83
所以我需要的是让它看起来像这样:(注意:这是完整报告中的示例，它不是从上述子集中剔除的。)
NOISE REPORT FROM 01/01/2016 - 06/30/2016 SHOWING AVGS FOR EACH HOUR
=================================================
avg avg
monitor_id hour num_events/hr noise_level for each hour
SEA11 03AM 12 70.3
SEA11 04AM 55 81.6
SEA11 05AM 27 83.2
SEA11 06AM 16 79.6
....etc.
SEA12 03AM 21 72.7
SEA12 04AM 45 83.1
SEA12 05AM 17 87.9
SEA12 06AM 26 77.6
....etc.
...所以我需要为每个监视器/小时记录一行，其中包括在一系列日期范围内该小时内发生的事件的平均数量，然后是 -average- noise_level，再次用于每小时发生的所有事件跨越一系列日期。
例如。第 6 行“Monitor SEA12 从 1 月 1 日到 6 月 30 日凌晨 4 点发生的平均事件数”。
这是我目前所拥有的，它是“伪代码”，显然行不通，但我希望它表达了我正在努力实现的目标:
SELECT time,
monitor_id,
AVG( SELECT COUNT(*) FROM noise_detail nc WHERE nc.monitor_id = n.monitor_id ) AS average_number_of_events, // avg number of events at X'o'clock
SELECT AVG(noise_level) FROM noise_detail nl WHERE nl.monitor_id = n.monitor_id) AS average_noise_level // avg noise level at X'o'clock
FROM noise_detail AS n
GROUP by monitor_id, SUBSTR(time,12,2) // group by monitor and X'o'clock (the hour of the day in 24 hr format)
最佳答案
这是第二次尝试。请注意，使用的数据与问题中的数据不同，请参阅此处工作的版本 SQL Fiddle
MySQL 5.6 架构设置:
CREATE TABLE noise_detail
(`ID` int, `monitor_id` varchar(5), `time` datetime, `noise_level` int)
;
INSERT INTO noise_detail
(`ID`, `monitor_id`, `time`, `noise_level`)
VALUES
(338271, 'SEA09', '2016-02-24 18:43:00', 75.2),
(338272, 'SEA09', '2016-02-24 18:42:00', 73.9),
(338273, 'SEA09', '2016-02-24 18:41:00', 85.1),
(338271, 'SEA09', '2016-02-24 19:43:00', 75.2),
(338272, 'SEA09', '2016-02-24 19:42:00', 73.9),
(338273, 'SEA09', '2016-02-24 19:41:00', 85.1),
(338271, 'SEA09', '2016-02-25 19:43:00', 75.2),
(338272, 'SEA09', '2016-02-25 19:42:00', 73.9),
(338273, 'SEA09', '2016-02-25 19:41:00', 85.1),
(338271, 'SEA09', '2016-02-26 18:43:00', 75.2),
(338272, 'SEA09', '2016-02-26 18:42:00', 73.9),
(338273, 'SEA09', '2016-02-26 18:41:00', 85.1),
(338271, 'SEA09', '2016-02-26 19:43:00', 75.2),
(338272, 'SEA09', '2016-02-26 19:42:00', 73.9),
(338273, 'SEA09', '2016-02-26 19:41:00', 85.1),
(338271, 'SEA09', '2016-02-28 19:43:00', 75.2),
(338272, 'SEA09', '2016-02-28 19:42:00', 73.9),
(338273, 'SEA09', '2016-02-28 19:41:00', 85.1),
(338271, 'SEA09', '2016-02-28 19:43:00', 75.2),
(338272, 'SEA09', '2016-02-28 19:42:00', 73.9),
(338273, 'SEA09', '2016-02-28 19:41:00', 85.1)
;
查询:
SELECT
monitor_id
, HOUR(time)
, COUNT(*) as tot_events_in_hour
, COUNT(*) / COUNT(DISTINCT DAY(time)) as av_events_ph
, AVG(noise_level) AS av_noise_level_in_hour
, AVG(noise_level) / COUNT(DISTINCT DAY(time)) AS av_noise_level_ph
FROM noise_detail n
GROUP BY
monitor_id
, HOUR(time)
ORDER BY
monitor_id
, HOUR(time)
Results :
| monitor_id | HOUR(time) | tot_events_in_hour | av_events_ph | av_noise_level_in_hour | av_noise_level_ph |
|------------|------------|--------------------|--------------|------------------------|-------------------|
| SEA09 | 18 | 6 | 3 | 78 | 39 |
| SEA09 | 19 | 15 | 3.75 | 78 | 19.5 |
日期/时间数据以人类可读的格式存储是一种常见的误解。 time 列很可能不是以这种方式存储的，因此专为字符串设计的 substr() 不适合确定一天中的小时数。为此，您需要日期/时间函数。
MySQL 5.6 架构设置:
CREATE TABLE noise_detail
(`ID` int, `monitor_id` varchar(5), `time` datetime, `noise_level` int)
;
INSERT INTO noise_detail
(`ID`, `monitor_id`, `time`, `noise_level`)
VALUES
(393211, 'SEA14', '2016-03-22 15:39:00', 79),
(393245, 'SEA11', '2016-03-05 07:20:00', 81.6),
(338262, 'SEA15', '2016-02-28 19:44:00', 80.4),
(338263, 'SEA14', '2016-02-28 19:55:00', 74.2),
(338264, 'SEA14', '2016-02-28 19:54:00', 81.5),
(338265, 'SEA14', '2016-02-28 19:44:00', 73.4),
(338266, 'SEA13', '2016-02-28 19:54:00', 81),
(338267, 'SEA13', '2016-02-28 19:43:00', 94.3),
(338268, 'SEA12', '2016-02-28 19:43:00', 80.2),
(338269, 'SEA11', '2016-02-28 19:53:00', 89),
(338270, 'SEA11', '2016-02-28 19:43:00', 89.5),
(338271, 'SEA09', '2016-02-28 19:43:00', 75.2),
(338272, 'SEA09', '2016-02-28 19:42:00', 73.9),
(338273, 'SEA09', '2016-02-28 19:41:00', 85.1),
(338588, 'SEA28', '2016-02-29 05:04:00', 83.3),
(338589, 'SEA22', '2016-02-29 05:04:00', 82.7),
(338590, 'SEA21', '2016-02-29 05:04:00', 82.9),
(338591, 'SEA20', '2016-02-29 05:04:00', 84.1),
(338592, 'SEA19', '2016-02-29 05:03:00', 88.6),
(338593, 'SEA18', '2016-02-29 05:03:00', 85.5),
(338594, 'SEA17', '2016-02-29 05:03:00', 86.6),
(338749, 'SEA14', '2016-02-28 20:43:00', 83)
;
建议的第一个查询:
SELECT
monitor_id
, HOUR(time)
, COUNT(*) as number_of_events
, AVG(noise_level) AS average_noise_level
FROM noise_detail n
GROUP BY
monitor_id
, HOUR(time)
ORDER BY
monitor_id
, HOUR(time)
Results :
| monitor_id | HOUR(time) | number_of_events | average_noise_level |
|------------|------------|------------------|---------------------|
| SEA09 | 19 | 3 | 78 |
| SEA11 | 7 | 1 | 82 |
| SEA11 | 19 | 2 | 89.5 |
| SEA12 | 19 | 1 | 80 |
| SEA13 | 19 | 2 | 87.5 |
| SEA14 | 15 | 1 | 79 |
| SEA14 | 19 | 3 | 76.3333 |
| SEA14 | 20 | 1 | 83 |
| SEA15 | 19 | 1 | 80 |
| SEA17 | 5 | 1 | 87 |
| SEA18 | 5 | 1 | 86 |
| SEA19 | 5 | 1 | 89 |
| SEA20 | 5 | 1 | 84 |
| SEA21 | 5 | 1 | 83 |
| SEA22 | 5 | 1 | 83 |
| SEA28 | 5 | 1 | 83 |
关于mysql - 在 MySQL 中结合 AVG 和 COUNT，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/46613766/
25
4
0
我想做一个平均值:问题是我正在计算每个元素的 AVG 的 1 个项目(工作)但是一旦我想要类别平均值的 GLOBAL 平均值(something and foo) 它不起作用(mysql 向我抛出一个
我使用 MySQL。我有以下格式的数据 - DATE , PAGE , PAGE_LOAD_DURATION , VISIT_TYPE 01-01-15 , A ,
我有下表: +--------+-------+ |Username|Points | +--------+-------+ |User1 | 75.00 | |User1 | 87.50 |
表格: a | b 1 | 15 2 | 10 3 | 20 4 | 30 查询: SELECT AVG(table.b) FROM table ORDER BY table.a ASC LIMIT
这是我的数据库 CREATE TABLE korisnici( name VARCHAR(30) NOT NULL, amount DECIMAL(65,2) ); INSER
这是我的数据库 CREATE TABLE korisnici( name VARCHAR(30) NOT NULL, amount DECIMAL(65,2) ); INSER
我正在尝试获取我的指标的平均交互次数，但此查询获取的是那些进行了交互的人的平均交互次数(那些在 metricsActions 中的人只有在他们已经交互时才会存在，他们的 metricsID 仍然存在于
我与三列有关系:ProductName、CategoryID 和 Price。我需要选择仅那些价格高于给定类别中平均产品价格的产品。(例如，当apple(ProductName)是fruit(Cate
我正在尝试使用 C++ 创建一个简单的程序。它计算用户输入的 5 个数字的平均值，但当我运行它时，它一直给我一个垃圾值。我花了 30 多分钟来解决这个问题，但我似乎无法弄清楚。 #include
总结一个练习题: 我需要从 datetime 列查询平均年份。我最初的解决方案是YEAR(AVG())所有日期。但由于我无法 AVG() 为 datetime，因此我将日期转换为 unix，然后再转换
我想从一个表中的一列 (value_to_count) 获得三个不同的平均值，其中所有这些平均值都有不同的 WHERE 子句(根据时间)。 示例数据: ###services#### Table se
我想创建一个 sql 查询，为 2 个不同的查询一起返回结果。例如，我想要以下形式的结果:产品名称, avg(price), min(price), max(price), avg(order), m
我有以下查询: SELECT ROUND(AVG( p.price ),2) as Avg_value FROM quotes inner join `system_users` ON quotes.
我想在 sql 中使用 AVG 函数来返回某些值的工作平均值(即基于上周而不是整体平均值)。我有两个正在计算的值，体重和 restingHR(心率)。我对每个都有以下 sql 语句: SELECT A
It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, over
我有一个包含多个数字列的评论表。我想计算一个查询中所有列的平均值。 如果表格看起来像这样: { foo : 2, bar : 5, foobar : 10 }, { foo :
我正在为我的应用程序使用 SQL Azure SQL Server。我的应用程序直到最近都运行良好，MAX dtu 使用率为 100%，但 AVG DTU 使用率约为 50%。 我应该监控哪个值来扩展
我正在为我的应用程序使用 SQL Azure SQL Server。我的应用程序直到最近都运行良好，MAX dtu 使用率为 100%，但 AVG DTU 使用率约为 50%。 我应该监控哪个值来扩展
这个问题不太可能对任何 future 的访客有帮助；它只与一个较小的地理区域、一个特定的时间点或一个非常狭窄的情况相关，通常不适用于全世界的互联网受众。如需帮助使此问题更广泛适用，visit the
我有下表: Date | Product | Price 06-12-17 | 1.1 | 10 06-12-17 | 1.2 | 2
我是一名优秀的程序员,十分优秀！