来自时钟系统的SQL数据透视记录

Question

我目前正在使用一个时钟系统，在这个系统中，时钟进入时间和时钟超时时间在不同的行上标识-如下所示：

|ID | Clocking System | Employee | Date     | Time  | Clocking In / Out|
------------------------------------------------------------------------
|1  | System A        | John Doe | 1 Dec 20 | 05:30 | In               |
|2  | System B        | John Doe | 1 Dec 20 | 17:45 | Out              |

我想改变这样的观点，即时钟输入值和时钟值显示为单个值，例如：

|ID | Clocking System | Employee | Date     | Clocking In | Clocked Out |
---------------------------------------------------------------------------------
|1  | System A        | John Doe | 1 Dec 20 | 05:30       | 17:45       |

我真的很想得到任何帮助。提前谢谢你。

Answer 1

假设您的数据没有异常，这可以使用lead()轻松处理。

select t.*
from (select t.*,
             lead(time) over (partition by employee, date order by time) as checkout_time
      from t
     ) t
where in_out = 'In';

这假设不存在连续丢失/输出记录或同一时间记录的情况。

Answer 2

当我从冰球比赛的事件数据中产生换档时，我遇到了一个非常相似的问题。轮班的开始和结束时间与其他事件混杂在一起。有多个玩家和多个游戏。表eventTable与此类似：

| player |   game   |    event    |  time_sec |
-----------------------------------------------
|   A    |  game-1  | shift-start |       10  |
|   B    |  game-1  | shift-start |       11  |
|   A    |  game-1  |    shot     |       12  |
|   A    |  game-1  |  shift-end  |       50  |
|   B    |  game-1  |    pass     |       55  |
|   B    |  game-1  |  shift-end  |       60  |
|   A    |  game-1  | shift-start |       90  |
|   A    |  game-1  |  shift-end  |      120  |
   ...        ...         ...           ...
|   X    |  game-n  |     ...     |     ...   |

我想重塑数据，这样就可以很容易地回答以下问题：

• 1在游戏1中有多少次轮班？
• 哪个换班时间最长(按玩家计算)？
• 按玩家1的平均换班时间是多少？

。

然后，需要的输出shiftTable是：

| shift_number | player |  game  | shift_start | shift_end | duration |
-----------------------------------------------------------------------
|           1  |    A   | game-1 |      10     |      50   |     40   |
|           2  |    A   | game-1 |      90     |     120   |     30   |
|           1  |    B   | game-1 |      11     |      60   |     49   |
          ...     ...      ...         ...           ...        ...
|           n  |    X   | game-n |     ...     |     ...   |    ...   |

我的解决方案是为每个shift_start和shift_end创建一个子查询，其中包含一个名为shift_number的新值，这个值由row_number() SQLITE函数生成，该函数在每个玩家的一个新的shift事件实例上递增，并在每个新游戏中重置，如下所示，用于shift_start

SELECT
    row_number() OVER (
          PARTITION BY player, game
          ORDER BY time_sec) shift_number,
    player,
    game_name,
    time_sec AS shift_start
FROM
    eventTable
WHERE
    event = 'shift_start'

它产生了：

(subquery shift_start)                             
| shift_number | player |  game  | shift_start |
------------------------------------------------
|           1  |    A   | game-1 |      10     |
|           2  |    A   | game-1 |      90     |
|           1  |    B   | game-1 |      11     |

   
(subquery shift_end)
| shift_number | player |  game  | shift_end   |
------------------------------------------------
|           1  |    A   | game-1 |      50     |
|           2  |    A   | game-1 |     120     |
|           1  |    B   | game-1 |      60     |

在player, game, shift_number上加入时，生成所需的输出表shiftTable。

使用SQLITE生成shiftTable的完整查询如下：

SELECT
    shift_number,
    player,
    game,
    shift_start,
    shift_end,
    shift_end - shift_start AS duration
FROM
    (SELECT
         row_number() OVER (
               PARTITION BY player, game
               ORDER BY time_sec) shift_number,
         player,
         game_name,
         time_sec AS shift_start
     FROM eventTable
     WHERE event = 'shift_start') 
LEFT JOIN
    (SELECT
         row_number() OVER (
               PARTITION BY player, game
               ORDER BY time_sec) shift_number,
         player,
         game_name,
         time_sec AS shift_end
     FROM eventTable
     WHERE event = 'shift_end') 
USING (player, game, shift_number)

我使用的是LEFT JOIN而不是JOIN，因为shift_start可能缺少对shift_end，反之亦然。这将为缺失的一对引入一个NULL，然后我可以捕获并处理它。

在shiftTable中，我觉得更好的做法是预先计算我的工作流程的持续时间，而我所处理的数据量意味着时空权衡对我都没有影响。