sql - 计算项目团队成员的自主因素

Question

我有一个包含多项任务的项目。人们从事这些任务，我想从下表中获得“自主因素/人”。下表说明了每个任务由谁执行了什么操作:

    +-------+-------------+------------------+-----------+--------------+    | Task# | Action Type |       Time       | Action By | Completed By |    +-------+-------------+------------------+-----------+--------------+    |     1 | Assigment   | 2015-07-23 10:00 | Nick      | Nick         |    |     1 | Resolution  | 2015-07-23 10:40 | Nick      | Nick         |    |     1 | Closure     | 2015-07-23 12:41 | Nick      | Nick         |    +-------+-------------+------------------+-----------+--------------+    |     2 | Assigment   | 2015-07-24 10:00 | Nick      | Nick         |    |     2 | Assigment   | 2015-07-24 10:30 | John      | Nick         |    |     2 | Resolution  | 2015-07-24 11:30 | Nick      | Nick         |    |     2 | Closure     | 2015-07-24 12:45 | Nick      | Nick         |    +-------+-------------+------------------+-----------+--------------+    |     3 | Assigment   | 2015-07-25 10:00 | John      | Nick         |    |     3 | Resolution  | 2015-07-25 11:00 | Nick      | Nick         |    |     3 | Closure     | 2015-07-25 13:50 | Nick      | Nick         |    +-------+-------------+------------------+-----------+--------------+    |     4 | Assigment   | 2015-07-26 10:00 | Nick      | Nick         |    |     4 | Assigment   | 2015-07-26 10:30 | John      | Nick         |    |     4 | Resolution  | 2015-07-26 10:40 | Nick      | Nick         |    |     4 | Assigment   | 2015-07-26 11:50 | John      | Nick         |    |     4 | Closure     | 2015-07-26 14:00 | Nick      | Nick         |    +-------+-------------+------------------+-----------+--------------+

A person is considered autonomous when task come to him, he finish it and close from 1st time.

For example:

1. Task# 1: Nick closed this task and no one touch the task except him=> Nick is AUTONOMOUS in this task
2. Task# 2: Nick closed this task but it was assigned to someone else (John) before closure => Nick is NOT AUTONOMOUS in this task
3. Task# 3: Nick closed this task and he didn't touch this task before John => Nick is AUTONOMOUS in this task
4. Task# 4: Nick closed this task but someone else (John) worked on before he closed => George is NOT AUTONOMOUS in this task

Hence, Nick autonomous faster is 50% (he worked on and closed 4 tasks but he closed only 2 autonomously). And John autonomous factor is 0% (he didn't close any task).

In summary, Nick considered AUTONOMOUS, if: 1. Nick is the one closed the task and no one touch it before him (ex: Task# 1), 2. or if multiple persons worked on the task before Nick but not Nick included, and he is the last one and he closed it (like Task# 3, but not like Task# 2, 4).

So the question is, is it possible to have a SQL query or simple code that run on above table that can get the autonomous factor/person, i.e. result expected to be like:

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

Answer 1

这听起来像是聚合的聚合。我认为“自主”逻辑很简单，就是在有人关闭任务和第一次看到任务之间没有人接触任务。这可以用这些规则来表达:

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

要收集此信息，您需要考虑分配给任务的每个人。这需要在任务人员和任务数据之间进行交叉连接。

结果查询:

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;