mysql - MySQL查询返回结果集显示添加空行

Question

我将每小时的结果存储在一个 MySQL 数据库表中，其格式为:

ResultId,CreatedDateTime,Keyword,Frequency,PositiveResult,NegativeResult 
349,2015-07-17 00:00:00,Homer Simpson,0.0,0.0,0.0
349,2015-07-17 01:00:00,Homer Simpson,3.0,4.0,-2.0
349,2015-07-17 01:00:00,Homer Simpson,1.0,1.0,-1.0
349,2015-07-17 04:00:00,Homer Simpson,1.0,1.0,0.0
349,2015-07-17 05:00:00,Homer Simpson,8.0,3.0,-2.0
349,2015-07-17 05:00:00,Homer Simpson,1.0,0.0,0.0

给定时间可能有多个结果，但某些时间没有结果。

如果我想生成每小时结果的平均值，我可以这样做:

SELECT ItemCreatedDateTime AS 'Created on', 
KeywordText AS 'Keyword', ROUND(AVG(KeywordFrequency), 2) AS 'Average frequency', 
ROUND(AVG(PositiveResult), 2) AS 'Average positive result',     
ROUND(AVG(NegativeResult), 2) AS 'Average negative result' 
FROM Results
WHERE ResultsNo = 349 AND CreatedDateTime BETWEEN '2015-07-13 00:00:00' AND '2015-07-19 23:59:00' 
GROUP BY KeywordText, CreatedDateTime
ORDER BY KeywordText, CreatedDateTime

但是，结果只包括数据存在的时间，例如:

349,2015-07-17 01:00:00,Homer Simpson,2.0,2.5,-1.5    
349,2015-07-17 04:00:00,Homer Simpson,1.0,1.0,0.0
349,2015-07-17 05:00:00,Homer Simpson,4.5,1.5,-1.0

但我需要为缺失的时间显示空白行，例如

349,2015-07-17 01:00:00,Homer Simpson,2.0,2.5,-1.5   
349,2015-07-17 02:00:00,Homer Simpson,0.0,0.0,0.0
349,2015-07-17 03:00:00,Homer Simpson,0.0,0.0,0.0
349,2015-07-17 04:00:00,Homer Simpson,1.0,1.0,0.0
349,2015-07-17 05:00:00,Homer Simpson,4.5,1.5,-1.0  

如果在显示结果之前没有在结果中插入空白，我不确定如何继续:我可以使用 MySQL 完全包含空白行吗？

Answer 1

SQL 通常不了解数据，因此您必须自己添加。在这种情况下，您将不得不以某种方式插入未使用的时间。这可以通过插入空行来完成，或者通过计算小时数并为此调整平均值来稍微不同。

计算小时数并调整平均值:

• 用数据 (A) 计算所有时间
• 计算期间(B)的小时数
• 像之前那样计算平均值，乘以 A 除以 B

获取小时数的示例代码:

SELECT COUNT(*) AS number_of_records_with_data, 
(TO_SECONDS('2015-07-19 23:59:00')-TO_SECONDS('2015-07-13 00:00:00'))/3600 
  AS number_of_hours_in_interval
FROM Results
WHERE ResultsNo = 349 AND CreatedDateTime 
  BETWEEN '2015-07-13 00:00:00' AND '2015-07-19 23:59:00' 
GROUP BY KeywordText, CreatedDateTime;

并将其与您的其余查询集成。