Блог

В настоящее время я работаю с системой синхронизации, в которой время включения и выключения определяется в отдельных строках, как показано ниже:

 |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       |
 

Я был бы очень признателен за любую помощь.
Заранее благодарю вас.

У меня была очень похожая проблема при генерации сдвигов из данных событий хоккейных матчей. Время начала и окончания сдвигов было смешано с другими событиями. Было несколько игроков и несколько игр. Таблица 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_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 .

Полный запрос для создания shiftTable с использованием SQLITE выглядит следующим образом:

 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 , потому что было возможно, что в a shift_start отсутствует его пара shift_end , и наоборот. Это вводит a NULL для отсутствующей пары, которую я мог бы затем поймать и обработать.

В shiftTable я чувствовал, что было бы лучше предварительно рассчитать продолжительность для моего рабочего процесса, и с объемами данных, с которыми я имел дело, это означало, что компромисс между пространством и временем не повлиял на меня в любом случае.

Предполагая, что ваши данные не имеют аномалий, это легко обрабатывается с помощью 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';
 

Предполагается, что не существует случаев, когда отсутствуют записи ввода / вывода или записи одного и того же времени подряд.