主页 / dba / 问题 / 264363
Accepted
giannis christofakis
Asked: 2020-04-06 09:55:54 +0800 CST

在给定的日期时间窗口内计算加班时间 3 小时

  • 772

我必须为一家公司实施访问控制系统，该系统将能够报告建筑物特定区域中每个员工的访问情况，以便提供报告。其中一项要求是提供在给定日期时间窗口内加班时间超过 3 小时的员工名单。

包含所需信息的表的表架构如下。 ACTION说明员工是否进入或离开建筑物的特定楼层。剩下的就是时间、员工和水平。

create table access_action
(
    ID          bigint auto_increment primary key,
    ACTION      int      null,
    TIMESTAMP   datetime null,
    level_id    bigint   null,
    employee_id bigint   null,
    constraint FK_access_action_employee_id
        foreign key (employee_id) references employee (ID),
    constraint FK_access_action_level_id
        foreign key (level_id) references level (ID)
);

样本结果集

ID  ACTION      TIMESTAMP       EMPLOYEE_ID
1   IN  2017-05-10 09:28:00 3   9
2   IN  2017-04-24 07:49:00 3   8
3   IN  2017-02-27 07:08:00 1   9
4   IN  2017-04-26 06:15:00 1   25
5   IN  2017-02-02 09:37:00 3   24
6   OUT 2017-05-29 08:03:00 3   7
7   IN  2017-04-25 07:07:00 1   1
8   IN  2017-01-09 08:54:00 3   8
9   IN  2017-05-12 07:57:00 2   1
10  OUT 2017-02-09 08:03:00 3   30
11  IN  2017-03-09 08:56:00 3   30
12  OUT 2017-02-03 11:26:00 3   5

我相信我知道如何估计加班时间，考虑到每个员工在建筑物内花费的时间总和，这将给出总小时数。但我只能想象如何在应用层做到这一点。

有没有办法可以纯粹用 SQL 来估计加班时间？

编辑

根据评论，进出动作之间必须有严格的顺序，因此创建了以下数据集。

mysql

1 个回答

  1. Best Answer

    根据@Akina 的评论，我创建了两组，一组用于 IN 动作，另一组用于 OUT，我加入它们并减去在场所内花费的时间。因此，我可以计算每个员工在任何特定日子的总工作时间。

    我想出了以下查询。

    SET @row_number = 0;
SET @row_number1 = 0;

select SEC_TO_TIME( SUM( TIME_TO_SEC(abb.overtimes) ) ) overtimes, employee_id
from
    (select TIMEDIFF(ab.working_time,'08:00:00') overtimes, ab.employee_id from
        (select SEC_TO_TIME( SUM(aaaa.timespent) ) working_time, accessDate, aaaa.employee_id
            from
            (select aaa.OUTtime as OUTtime, aaa.INtime, TIME_TO_SEC( TIMEDIFF(OUTtime, INtime) ) as timespent, DATE(OUTtime) as accessDate, aaa.level_id, aaa.employee_id
             from
                 (select aa1.TIMESTAMP as OUTtime, aa2.TIMESTAMP as INtime, aa1.level_id, aa1.employee_id
                  from (select
                            (@row_number:=@row_number + 1) row_num,
                            a1.TIMESTAMP, a1.employee_id, a1.level_id
                        from access_action a1
                        where a1.ACTION = 'OUT'
                        order by employee_id asc, TIMESTAMP desc) aa1
                           INNER JOIN
                       (select
                            (@row_number1:=@row_number1 + 1) row_num,
                            a2.TIMESTAMP, a2.employee_id, a2.level_id
                        from access_action a2
                        where a2.ACTION = 'IN'
                        order by employee_id asc, TIMESTAMP desc) aa2
                       ON aa1.row_num = aa2.row_num) aaa) aaaa
        group by accessDate, aaaa.employee_id
        having accessDate >= '2017-05-26' and accessDate <= '2017-05-30'
        AND working_time > '08:00:00') ab) abb
group by employee_id
having overtimes > '03:00:00';

    在数据中执行查询，结果如下。

    overtimes   employee_id
03:50:00    4
    • 0

相关问题