计算团队成员在项目中的自主因素

Question

我有一个有几个任务的项目。人们完成这些任务，我想从下表中得到"自主因素/人"。下表说明了每个任务由谁执行了什么操作:

<>之前+-------+-------------+------------------+-----------+--------------+任务# |动作类型|时间|动作由|完成由|+-------+-------------+------------------+-----------+--------------[phone]:00 | Nick | Nick2015-07-23 10:40 | Nick | Nick2015-07-23 12:41 | Nick | Nick+-------+-------------+------------------+-----------+--------------[phone]:00 | Nick | Nick2015-07-24 10:30 |约翰|尼克|2015-07-24 11:30 | Nick | Nick2015-07-24 12:45 | Nick | Nick+-------+-------------+------------------+-----------+--------------[phone]:00 |约翰|尼克|2015-07-25 11:00 | Nick | Nick2015-07-25 13:50 | Nick | Nick+-------+-------------+------------------+-----------+--------------[phone]:00 | Nick | Nick2015-07-26 10:30 |约翰|尼克|2015-07-26 10:40 | Nick | Nick2015-07-26 11:50 |约翰|尼克2015-07-26 14:00 | Nick | Nick+-------+-------------+------------------+-----------+--------------+之前

当任务来临时，一个人被认为是自主的，他完成它并从第一次结束。

例如:

任务# 1:Nick关闭了这个任务，除了他没有人碰这个任务=> Nick在这个任务中是自治的
1. 任务# 2:Nick关闭了这个任务，但是在关闭之前它被分配给了其他人(John) => Nick在这个任务中不是自治的
2. 任务# 3:Nick关闭了这个任务，在John => Nick在这个任务中是自治的
3. 任务# 4:Nick关闭了这个任务，但是其他人(John)在他关闭之前正在做这个任务=> George在这个任务中不是自治的

因此，Nick的自主速度是50%(他完成并完成了4个任务，但他只自主完成了2个任务)。而John的自主因子为0%(他没有完成任何任务)。

总之，尼克认为自治，如果:1. 尼克是完成任务的人，在他之前没有人碰它(例如:任务# 1)，2. 或者有很多人在Nick之前完成任务，但不包括Nick，他是最后一个完成任务的人(就像任务# 3一样，但不像任务# 2,4)。

所以问题是，是否有可能有一个SQL查询或简单的代码在上面的表上运行，可以得到自治因素/人，即预期的结果是:

+------+-------------------+
| Name | Autonomous Factor |
+------+-------------------+
| Nick | 50%               |
| John | 0%                |
+------+-------------------+

Answer 1

这听起来像是聚合的聚合。我认为"自主"的逻辑很简单，即从某人关闭任务到此人第一次看到任务之间，没有人碰过任务。这可以用以下规则表示:

• 任务中其他任何人的最大时间小于该人员的最小时间
• 人员在任务上的最长时间与任务结束的最长时间相同。

要收集这些信息，您需要考虑分配给该任务的每个人。这需要在任务人员和任务数据之间建立一个cross join。

结果查询:

select t.task, p.person,
       (case when min(t.actionby) = max(t.actionby) then 1
             when (max(case when t.actionby <> p.person then time end) >
                   min(case when t.actionby = p.person then time end)
                  ) and
                  (max(case when t.actionby = p.person then time end) =
                   max(case when t.action = 'Closure' then time end)
                  )
             then  1 else 0
        end) as IsAutonomous
from (select distinct task, actionby as person from tasks) p join
     tasks t
     on p.task = t.task
group by t.task, p.person;

一旦你有了这些信息，剩下的只是一个额外的聚合:

select person, avg(IsAutonomous)
from (select t.task, p.person,
             (case when min(t.actionby) = max(t.actionby) then 1
                       (max(case when t.actionby <> p.person then time end) >
                         min(case when t.actionby = p.person then time end)
                        ) and
                        (max(case when t.actionby = p.person then time end) =
                         max(case when t.action = 'Closure' then time end)
                        )
                   then  1 else 0
              end) as IsAutonomous
      from (select distinct task, actionby as person from tasks) p join
           tasks t
           on p.task = t.task
      group by t.task, p.person
     ) tp
group by person;