当前位置: 首页 > 面试题库 >

Mysql:按小时分组,如果没有数据,则为0

慕容博涛
2023-03-14
问题内容

我有以下查询:

SELECT count(*) as 'totalCalls', HOUR(`end`) as 'Hour'
FROM callsDataTable 
WHERE company IN (
    SELECT number 
    FROM products 
    WHERE products.id IN (@_PRODUCTS)) 
    AND YEAR(`end`) = @_YEAR AND MONTH(`end`) = @_MONTH 
 group by HOUR(`end`)

上面的查询仅返回进行呼叫的时间:

totalCalls  Hour
    2       0
    1       2
    4       7
   98       8
  325       9
  629      10
  824      13
  665      15
  678      16
  665      17
  606      18     
   89      22
    5      23

期望的输出应该是所有小时,并且在没有呼叫的情况下,该小时应该是0个呼叫,如下所示:

totalCalls  Hour
    0       0
    0       1
    1       2
    0       3
    0       4
    0       5
    0       6
    4       7
   98       8
  325       9
  629      10
    0      11
    0      12
  824      13
    0      14
  665      15
  678      16
  665      17
  606      18
    0      19
    0      20
    0      21
   89      22
    5      23

问题答案:

首先,您的查询可以用一种更简单的方式表示为:

SELECT COUNT(*) AS totalCalls, HOUR(`end`) AS `Hour`
FROM callsDataTable c
  INNER JOIN products p ON c.company = p.number
    AND p.id IN (@_PRODUCTS)
    AND YEAR(`end`) = @_YEAR AND MONTH(`end`) = @_MONTH
GROUP BY HOUR(`end`) AS `Hour`
ORDER BY `Hour` ASC

使用@NoDisplayName在他们的答案中提出的想法:

CREATE TABLE hours_table (hours INT);

INSERT INTO hours_table VALUES(0), (1), (2), 
    /* put the missing values here */ (23);

您可以加入包含小时数的表,以获取所需的结果:

SELECT COUNT(*) AS totalCalls, h.hours AS `Hour`
FROM callsDataTable c
  INNER JOIN products p ON c.company = p.number
  RIGHT JOIN hours_table h ON h.hours = HOUR(c.`end`)
    AND p.id IN (@_PRODUCTS)
    AND YEAR(`end`) = @_YEAR AND MONTH(`end`) = @_MONTH
GROUP BY h.hours
ORDER BY h.hours ASC

如果运行太慢(我确定运行速度很慢),则应该研究一种使用类似方法的方法,end BETWEEN '2015-01-01 00:00:00' AND '2015-01-31 23:59:59'而不是比较YEAR(end)MONTH(end)

可以这样完成:

SET @start = STR_TO_DATE(CONCAT(@_YEAR, '-', @_MONTH, '-01 00:00:00'), '%Y-%m-%d %H:%i:%s');
SET @end   = DATE_SUB(DATE_ADD(@start, INTERVAL 1 MONTH), INTERVAL 1 SECOND);

SELECT ...
...
    AND `end` BETWEEN @start AND @end
...

但是这种改变本身并没有帮助。它需要一个现场索引end来带来所需的速度改进:

ALTER TABLE callsDataTable ADD INDEX(end);

使用HOUR(c.end)的连接条件是另一个原因运行缓慢。

可以通过将表hours_table与第一个查询(的简化版本)生成的结果集结合在一起来进行改进:

SELECT IFNULL(totalCalls, 0) AS totalCalls, h.hours AS `Hour`
FROM hours_table h
   LEFT JOIN (
        SELECT COUNT(*) AS totalCalls, HOUR(`end`) as `Hour`
        FROM callsDataTable c
          INNER JOIN products p ON c.company = p.number
            AND p.id IN (@_PRODUCTS)
            AND YEAR(`end`) = @_YEAR AND MONTH(`end`) = @_MONTH
        GROUP BY HOUR(`end`) AS `Hour`
   ) d ON h.hours = d.`Hour`
ORDER BY h.hours ASC


 类似资料:
  • 问题内容: 我正在尝试按小时使用量从历史记录表中获取报告。表是; 我想通过组和在指定日期范围内的所有记录。 我使用了类似这样的查询,但是它并不能显示全部时间。 仅在有记录时显示。 问题答案: 您现有的查询可以简化为: 要显示 每一个 小时,包括那些没有数据,就需要外,其所有您想要的数据小时表连接。您可以使用以下命令在查询中建立这样的表: 但是,对于没有数据的组的处理实际上是业务逻辑的问题,最好将其

  • 问题内容: 我有一个包含会话事件时间戳的表。我想根据时间戳查询两个会话之间至少有10分钟的事件间隔时,我有多少会话。我可以使用MySql查询来计算会话数吗? 谢谢 问题答案: 在您的桌子上只有很少的信息,这对您来说只是一个基本的想法,但是您可以执行以下操作: 获取所有时间戳,并将它们与所有其他较大的时间戳合并,然后使用MIN将其缩小到下一个最大的时间戳。然后从中选择差异小于600秒的计数(假设使用

  • 问题内容: 我有一个看起来与此类似的MySQL数据库: 我需要在html表中对结果进行排序,如下所示: 我不知道这是否应该通过SQL查询(concat_group或类似的东西)或PHP完成,有人可以帮帮我吗? 伙计们,感谢您的以下帮助,但我还需要完成类似的工作,例如: 我需要对上面的示例进行排序/显示,如下所示: 现在,我的问题有了新的维度。我的数据库如下所示: (我正在使用表) 我需要实现: ¿

  • 问题内容: 这是我的mysql收入表。 字段“日期”是标准sql日期。我执行此查询的目的是按月计算收入总和,如果某个月没有收入,则返回零。如果某个月没有任何收入,我想要零,因为我想在图表中显示这些数据。 这是查询。 但是我只会得到如下输出。如果其他月份没有值,则没有零。这是输出。 我希望上表中的其他月份和total_num为零。我怎样才能做到这一点?那里也有同样的问题。但没有有效的答案。 按月分组

  • 我想在应用GROUPBY条件后取消对数据帧的分组。我一组一组地用熊猫 现在我想将数据解组。列名是由下划线连接的列的组合。例如:Amt\u 8\u red\u 17:列名是(Amount、Count、Color、Id)输出的样子 有没有更快的办法?

  • 问题内容: 我有一个包含大量数据的数据库。我想选择所有数据,但是按一列分组。 因此,例如: 在示例列中,A是具有重复值且需要分组的列。但是B列具有不同的值,我想选择所有这些值。 最后,我想得到一个查询,导致: B列的值在jQuery手风琴中。 我已经尝试并搜索了几个小时,但仍然没有找到一种方法来进行此操作。 编辑: 我已经设法使用戈登提供的答案解决问题。使用PHP,我分解了查询结果,并通过whil