MySQL 分组总数

Question

我只知道 SQL 的基础知识。我有一个数据库，可以显示一个人何时进入办公室以及何时离开。

例如

Name     Enter                    Exit
Jack     2014-01-01 01:00:00     2014-01-01 02:00:00
Sam      2014-01-01 02:00:00     2014-01-01 03:00:00
Jane     2014-01-01 02:00:00     2014-01-01 03:00:00
Judy     2014-01-01 03:00:00     2014-01-01 04:00:00

我想做的是找出每小时在办公室的用户总数，按小时分组，也可能按日期分组，所以我的结果应该如下所示

date           hour   Occupancy
2014-01-01     1       1
2014-01-01     2       3
2014-01-01     3       3
2014-01-01     4       1

****编辑

我可以将数据库设置为仅收集小时并丢弃分钟，但是一个人也可以在两天后进入并离开。所以我正在寻找一些入口和导出不仅相隔一个小时的东西。

Answer 1

更新的答案:

好的，我调整了您的样本数据，以反射(reflect)进入和退出日期不在同一天以及进入和退出时间相差超过一小时的情况。

CREATE TABLE t
    (`Name` varchar(4), `Enter` datetime, `Exit` datetime)
;

INSERT INTO t
    (`Name`, `Enter`, `Exit`)
VALUES
    ('Jack', '2014-01-01 01:00:00', '2014-01-01 02:00:00'),
    ('Sam', '2014-01-01 02:00:00', '2014-01-01 05:00:00'),
    ('Jane', '2014-01-01 02:00:00', '2014-01-01 03:00:00'),
    ('Judy', '2014-01-01 03:00:00', '2014-01-03 04:00:00')
;

为了简单起见，我创建了一个表，其中包含涵盖我们示例数据的日期范围。

drop table if exists n;
create table n(a int auto_increment primary key, b datetime);
insert into n (a) values(null), (null);

insert into n (a)
select null from n n1, n n2, n n3, n n4, n n5, n n6;

这个快速技巧将 66 个数字插入到我们的表中。接下来，我生成涵盖样本数据的日期。

update n 
set b = '2014-01-01 00:00:00' + interval a hour;

现在我们有了 2014-01-01 00:00:00 和 2014-01-03 18:00:00 之间每个整小时的日期时间值，我们可以使用这个表来简单地加入它。

select date(n.b), hour(n.b), count(*)
from
t inner join n on n.b between t.Enter and t.Exit
group by 1, 2

这样我们就可以获得进入和退出时间之间每个整小时的记录。结果是:

|       DATE | HOUR | COUNT(*) |
|------------|------|----------|
| 2014-01-01 |    1 |        1 |
| 2014-01-01 |    2 |        3 |
| 2014-01-01 |    3 |        3 |
| 2014-01-01 |    4 |        2 |
| 2014-01-01 |    5 |        2 |
| 2014-01-01 |    6 |        1 |
| 2014-01-01 |    7 |        1 |
| 2014-01-01 |    8 |        1 |
| 2014-01-01 |    9 |        1 |
| 2014-01-01 |   10 |        1 |
| 2014-01-01 |   11 |        1 |
| 2014-01-01 |   12 |        1 |
| 2014-01-01 |   13 |        1 |
| 2014-01-01 |   14 |        1 |
| 2014-01-01 |   15 |        1 |
| 2014-01-01 |   16 |        1 |
| 2014-01-01 |   17 |        1 |
| 2014-01-01 |   18 |        1 |
| 2014-01-01 |   19 |        1 |
| 2014-01-01 |   20 |        1 |
| 2014-01-01 |   21 |        1 |
| 2014-01-01 |   22 |        1 |
| 2014-01-01 |   23 |        1 |
| 2014-01-02 |    0 |        1 |
| 2014-01-02 |    1 |        1 |
| 2014-01-02 |    2 |        1 |
| 2014-01-02 |    3 |        1 |
| 2014-01-02 |    4 |        1 |
| 2014-01-02 |    5 |        1 |
| 2014-01-02 |    6 |        1 |
| 2014-01-02 |    7 |        1 |
| 2014-01-02 |    8 |        1 |
| 2014-01-02 |    9 |        1 |
| 2014-01-02 |   10 |        1 |
| 2014-01-02 |   11 |        1 |
| 2014-01-02 |   12 |        1 |
| 2014-01-02 |   13 |        1 |
| 2014-01-02 |   14 |        1 |
| 2014-01-02 |   15 |        1 |
| 2014-01-02 |   16 |        1 |
| 2014-01-02 |   17 |        1 |
| 2014-01-02 |   18 |        1 |
| 2014-01-02 |   19 |        1 |
| 2014-01-02 |   20 |        1 |
| 2014-01-02 |   21 |        1 |
| 2014-01-02 |   22 |        1 |
| 2014-01-02 |   23 |        1 |
| 2014-01-03 |    0 |        1 |
| 2014-01-03 |    1 |        1 |
| 2014-01-03 |    2 |        1 |
| 2014-01-03 |    3 |        1 |
| 2014-01-03 |    4 |        1 |

当然你必须调整帮助表。能帮助周围的 table 肯定不是一件坏事。

再次，这是一个 sqlfiddle 来看看它的实际运行情况。

<小时/>

原始答案:

select date(a), hour(a), count(*)
from (
    select enter as a from t
    union all
    select `exit` as a from t
) b
group by 1, 2

• 看到它在 中实时运行 sqlfiddle

但请注意，存在某些假设。

1. 时间总是整点
2. 进入日期和退出日期始终在同一天
3. 进入和退出时间仅相差一小时

如果其中一个假设不成立，事情就会变得更加复杂