MySQL 查询捕获事件之间的工作时间

Question

我正在尝试创建一个查询来捕获事件发生所需的工作时间。在下面的数据中，我想显示帐户从“激活”变为“停用”所需的工作时间。

ENCODEDKEY  TRANSACTIONID   LOANPRODUCTKEY  TIMESTAMP              TYPE
1           2067            aa1             2015/02/06 15:29:00    LOAN_PRODUCT_ACTIVATED
2           2162            aa1             2015/02/16 14:07:00    LOAN_PRODUCT_EDITED
3           2666            aa1             2015/02/16 15:29:00    LOAN_PRODUCT_DEACTIVATED
4           3456            aa2             2015/03/06 12:01:00    LOAN_PRODUCT_ACTIVATED
5           3478            aa2             2015/03/08 13:15:00    LOAN_PRODUCT_EDITED
6           3908            aa2             2015/03/18 13:15:00    LOAN_PRODUCT_DEACTIVATED

所以结果会是这样的

LOANPRODUCTKEY          TIME
aa1                     24:00:00
aa2                     12:00:00

(我知道这些数字是错误的!)

我还需要它只考虑工作时间(即上午 9 点到下午 5 点)这可能吗？

感谢所有可以帮助我的人。

更新。 非常感谢迄今为止提供帮助的人们!

因此，我成功创建了一个查询，它将返回每个贷款产品 key 的激活和停用打字机的正确日期。然而，我仍然在努力计算两个计算日期之间的工作时间。我的查询如下:

SELECT

att.LOANPRODUCTKEY
,sub1.time_activated
,sub2.time_deactivated

from 
activity att

left join (select
min(att.TIMESTAMP) as time_activated
,att.loanproductkey
from
activity att
where
att.`TYPE` = "LOAN_PRODUCT_ACTIVATED"
group by
att.LOANPRODUCTKEY) AS sub1
ON att.LOANPRODUCTKEY = sub1.LOANPRODUCTKEY

left join 
(select
max(att.timestamp) as time_deactivated
,att.LOANPRODUCTKEY
from
activity att
where
att.`TYPE` = "LOAN_PRODUCT_DEACTIVATED"
group by
att.LOANPRODUCTKEY) AS sub2
ON att.LOANPRODUCTKEY = sub2.LOANPRODUCTKEY

group by
att.loanproductkey

Answer 1

好的，所以我认为下面的查询应该可以工作，尽管它可能可以进行很多调整。我想也很可能做得更聪明:)

逻辑如下:

1. 首先，它使用数字表来生成每个贷款产品 key 的开始日期和结束日期之间的日期序列(这是在派生表中完成的)。

2. 然后，它连接此表并计算开始和结束(不含)之间的整个工作日数，并将此值(乘以 240 以获得分钟数)添加到开始天数之间的差值分钟数时间和 17:00，以及 09:00 和结束日期时间之间的分钟差。

所以计算是这样的:

(minutes from start time of first day to 17:00) -- eg. '17:00:00'-'15:29:00'
+
(minutes from 09:00 to end time of last day) -- eg. '15:29:00'-'09:00'
+
(number of working days between start and end) * 240

您可能需要对数学进行一些微调，但逻辑应该是合理的。

所做的假设:

• 周末是由 weekday 函数返回的工作日 5 和 6 - 这可能取决于本地服务器设置，我不确定。

• 存在一个名为 numbers 的表，其列 num 包含数字 1 到 至少需要覆盖最大日期范围开始到结束。如果您没有这个，我在最后描述了如何创建它。

这会得到如下结果:

LOANPRODUCTKEY  total_min   total_time (hhh:mm:ss)
aa1             4800        80:00:00
aa2             5834        97:14:00

Sample SQL Fiddle (没有total_time列，因为fiddle使用java，它不喜欢小时部分的大值。

SELECT 
    t_start.LOANPRODUCTKEY

    , (
        TIMESTAMPDIFF(MINUTE, CAST(t_start.timestamp AS time), CAST('17:00:00' AS time))
      + TIMESTAMPDIFF(MINUTE, CAST('09:00:00' AS time), CAST(t_end.timestamp AS time))
      + COUNT(WEEKDAY(t_start.timestamp) NOT IN (5,6)) * 8 * 60
    ) AS total_minutes

    , SEC_TO_TIME(
        TIMESTAMPDIFF(SECOND, CAST(t_start.timestamp AS time), CAST('17:00:00' AS time)) 
      + TIMESTAMPDIFF(SECOND, CAST('09:00:00' AS time), CAST(t_end.timestamp AS time)) 
      + COUNT(WEEKDAY(t_start.timestamp) NOT IN (5,6)) * 8 * 60 * 60
    ) AS total_time

FROM 
    t t_start
JOIN 
    t t_end ON t_start.LOANPRODUCTKEY = t_end.LOANPRODUCTKEY
JOIN 
(
    SELECT 
        ts.LOANPRODUCTKEY
        , DATE(DATE_ADD(ts.timestamp,INTERVAL num DAY)) AS datesSeries
    FROM 
        t ts
    JOIN 
        t te ON ts.LOANPRODUCTKEY = te.LOANPRODUCTKEY
    CROSS JOIN
        numbers r
    WHERE 
        num < DATEDIFF(te.timestamp, ts.timestamp) 
     AND 
        ts.TYPE = 'LOAN_PRODUCT_ACTIVATED'
     AND 
        te.TYPE = 'LOAN_PRODUCT_DEACTIVATED'
) dates ON t_start.LOANPRODUCTKEY = dates.LOANPRODUCTKEY

WHERE 
    t_start.TYPE = 'LOAN_PRODUCT_ACTIVATED' AND t_end.TYPE = 'LOAN_PRODUCT_DEACTIVATED'
GROUP BY 
    t_start.LOANPRODUCTKEY, t_start.TIMESTAMP, t_end.TIMESTAMP
ORDER BY 
    t_start.LOANPRODUCTKEY;

如果您还没有合适的表格，其数字序列涵盖开始日期和结束日期之间的最大天数，您可以使用下面的查询创建一个填充数字 1-1000 的表格，这是我使用的来自this answer .

CREATE TABLE numbers (num int primary key);

INSERT INTO numbers
SELECT SEQ.SeqValue
FROM (
    SELECT (HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue) SeqValue
    FROM (
        SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue UNION ALL
        SELECT 2 SeqValue UNION ALL SELECT 3 SeqValue UNION ALL
        SELECT 4 SeqValue UNION ALL SELECT 5 SeqValue UNION ALL
        SELECT 6 SeqValue UNION ALL SELECT 7 SeqValue UNION ALL
        SELECT 8 SeqValue UNION ALL SELECT 9 SeqValue
    ) ONES
    CROSS JOIN (
        SELECT  0 SeqValue UNION ALL SELECT 10 SeqValue UNION ALL
        SELECT 20 SeqValue UNION ALL SELECT 30 SeqValue UNION ALL
        SELECT 40 SeqValue UNION ALL SELECT 50 SeqValue UNION ALL
        SELECT 60 SeqValue UNION ALL SELECT 70 SeqValue UNION ALL
        SELECT 80 SeqValue UNION ALL SELECT 90 SeqValue
    ) TENS
    CROSS JOIN (
        SELECT   0 SeqValue UNION ALL SELECT 100 SeqValue UNION ALL
        SELECT 200 SeqValue UNION ALL SELECT 300 SeqValue UNION ALL
        SELECT 400 SeqValue UNION ALL SELECT 500 SeqValue UNION ALL
        SELECT 600 SeqValue UNION ALL SELECT 700 SeqValue UNION ALL
        SELECT 800 SeqValue UNION ALL SELECT 900 SeqValue
    ) HUNDREDS
) SEQ WHERE SEQ.SeqValue > 0;