mysql - SQL 查询根据时间戳选择按小时(列)和工作日(行)分组的值

Question

我已经搜索了这个问题并发现了稍微相似的帖子，但无法适应我的需求。

我有一个数据库，其中包含永远的服务器请求，每个请求都有一个时间戳，我正在尝试提出一个查询，允许我创建一个热矩阵图表(CCC HeatGrid)。

sql查询结果必须代表每个工作日每小时分组的服务器负载。

像这样:Example table

我只需要 SQL 查询，我知道如何创建图表。

谢谢，

Answer 1

这些看起来像是行的“计数”。

问题之一是“稀疏”数据，我们可以稍后解决。

要返回星期几(“星期日”、“星期一”等)，您可以使用 DATE_FORMAT 函数。为了使这些按顺序排列，我们需要包含 0 到 6 或 1 到 7 之间的整数值。我们可以在该表达式上使用 ORDER BY 子句来获取按我们想要的顺序返回的行。

为了获得顶部的“小时”，我们可以在 SELECT 列表中使用表达式来有条件地增加计数。

假设您的时间戳列名为 ts，并假设您想要提取 2014 年的所有行，我们从以下内容开始:

SELECT DAYOFWEEK(t.ts)
     , DATE_FORMAT(t.ts,'%W')

  FROM mytable t
 WHERE t.ts >= '2014-01-01'
   AND t.ts <  '2015-01-01'
 GROUP BY DAYOFWEEK(t.ts)
 ORDER BY DAYOFWEEK(t.ts)

(我需要检查 MySQL 文档， WEEKDAY 和 DAYOFWEEK 非常相似，但我们想要返回周日最低值和最高值的那个周六的值...我想我们想要 DAYOFWEEK，很容易稍后修复)

现在的“技巧”是顶部的列。

我们可以使用 DATE_FORMAT() 函数、HOUR() 函数或 EXTRACT() 从时间戳中提取“小时” > 功能...任你选择。

如果时间戳位于指定小时内，我们想要的表达式将返回 1，否则返回 0。然后，我们可以使用 SUM() 聚合来计算 1。 bool 表达式返回值 1 表示 TRUE，0 表示 FALSE。

     , SUM( HOUR(t.ts)=0  )  AS `h0`
     , SUM( HOUR(t.ts)=1  )  AS `h1`
     , SUM( HOUR(t.ts)=2  )  AS `h2`
     , '...'
     , SUM( HOUR(t.ts)=22 )  AS `h22`
     , SUM( HOUR(t.ts)=23 )  AS `h23`

bool 表达式也可以计算为 NULL，但由于我们有一个谓词(即 WHERE 子句中的条件)来确保 ts 不能为 NULL，所以这不会是问题。

我们可能遇到的另一个问题(正如我之前提到的)是“稀疏”数据。为了说明这一点，请考虑如果没有行具有 Monday 的 ts 值(对于我们的查询)会发生什么情况。发生的情况是我们在星期一的结果集中没有得到行。如果星期一(或一周中的任何一天)确实发生“丢失”行，我们确实知道“丢失”星期一的所有每小时计数> 行将全部为零。