mysql - 给定日期每小时计算 mysql 行数

Question

我有一个带有日期列的 mysql 表。日期列数据类型为 TIMESTAMP，默认设置为 CURRENT_TIMESTAMP，同时记录日期和时间

现在我想计算给定日期下的行数

举个例子

 ++++++++++ 8am 9am 10am 11am
 ++user1+++  15  10  11    10
 ++user2+++  10  10  20    30

每个小时的计数应该像这样单独记录。

我尝试过这个，但它不起作用

SELECT COUNT(*) FROM mytable 
WHERE `date` = '2015-01-26' 
GROUP BY HOUR(`TIMESTAMP`)

我怎样才能实现这个目标？

我不知道如何与用户分组。 sproc也可以

我做了一个这样的存储过程。但这个存储过程包含错误。有人可以帮我吗，我想算一下相隔 9 个小时

  DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test22`(IN datestamp DATE)
BEGIN
SELECT username, 

       COUNT(if(disblid,1,null)) '8:00 AM', where time between '08:00' and '09:00':
        COUNT(if(disblid,1,null)) '9:00 AM' , where time between '09:00' and '10:00';




FROM claimloans 
WHERE DATE(date) = datestamp
group by Username;

   END

感谢所有帮助我的人，我想出了一个工作得非常好的存储过程。

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `claimscounter`(IN datestamp DATE)
BEGIN
SELECT username, 

    COUNT(IF(HOUR(date)=0,1,NULL)) AS '12am',
    COUNT(IF(HOUR(date)=1,1,NULL)) AS '1am',
    COUNT(IF(HOUR(date)=2,1,NULL)) AS '2am',
    COUNT(IF(HOUR(date)=3,1,NULL)) AS '3am',
    COUNT(IF(HOUR(date)=4,1,NULL)) AS '4am',
    COUNT(IF(HOUR(date)=5,1,NULL)) AS '5am',
    COUNT(IF(HOUR(date)=6,1,NULL)) AS '6am',
    COUNT(IF(HOUR(date)=7,1,NULL)) AS '7am',
    COUNT(IF(HOUR(date)=8,1,NULL)) AS '8am',
    COUNT(IF(HOUR(date)=9,1,NULL)) AS '9am',
    COUNT(IF(HOUR(date)=10,1,NULL)) AS '10am',
    COUNT(IF(HOUR(date)=11,1,NULL)) AS '11am',
    COUNT(IF(HOUR(date)=12,1,NULL)) AS '12pm',
    COUNT(IF(HOUR(date)=13,1,NULL)) AS '1pm',
    COUNT(IF(HOUR(date)=14,1,NULL)) AS '2pm',
    COUNT(IF(HOUR(date)=15,1,NULL)) AS '3pm',
    COUNT(IF(HOUR(date)=16,1,NULL)) AS '4pm',
    COUNT(IF(HOUR(date)=17,1,NULL)) AS '5pm',
    COUNT(IF(HOUR(date)=18,1,NULL)) AS '6pm',
    COUNT(IF(HOUR(date)=19,1,NULL)) AS '7pm',
    COUNT(IF(HOUR(date)=20,1,NULL)) AS '8pm',
    COUNT(IF(HOUR(date)=21,1,NULL)) AS '9pm',
    COUNT(IF(HOUR(date)=22,1,NULL)) AS '10pm',
    COUNT(IF(HOUR(date)=23,1,NULL)) AS '11pm'


FROM claimloans 
WHERE DATE(date) = datestamp
group by username;

   END

但是现在我还有另一个小问题。这算所有的时间。如果一小时内没有进入，则算作零。我想计算只有记录的时间有人可以帮我吗

谢谢

Answer 1

我会将其作为 View 来解决，如下所示:

CREATE TABLE foo (id int not null, val timestamp);

CREATE VIEW foo_by_hours AS (
    SELECT
        id,
        DATE(val) AS 'day',
        COUNT(IF(HOUR(val)=0,1,NULL)) AS '12am',
        COUNT(IF(HOUR(val)=1,1,NULL)) AS '1am',
        COUNT(IF(HOUR(val)=2,1,NULL)) AS '2am',
        COUNT(IF(HOUR(val)=3,1,NULL)) AS '3am',
        ...
    FROM foo
    GROUP BY id, day);

SELECT * FROM foo_by_hours;

SQL Fiddle 上的完整示例我还添加了一个使用 SUM 而不是 COUNT 的 View 。结果是一样的，只是方法不同。