Блог

У меня есть ряд записей с временными метками, которые дают количество людей в местоположениях. Это происходит не через равные промежутки времени, счетчик указывается каждый раз при обнаружении изменения. Например :

     TIMESTAMP         LOCATION       OCCUPANTS  
 ---------------- ----------------- ----------- 
  1602476920649    SOL2-5-MARS               4  
  1602476840140    SOL2-5-NEPTUNE           10  
  1602476821435    SOL2-4-SATURN             1  
  1602476709256    SOL2-4-SATURN             0  
  1602476505331    SOL2-5-NEPTUNE            8  
  1602476508223    SOL2-5-MARS               5  
  1602476438886    SOL2-4-JUPITER            4  
  

Я хотел бы перенести это в записи, которые имеют начальную и конечную временные метки для каждого местоположения и количества людей, как показано ниже. Последней конечной временной меткой будет текущая временная метка, в которую был запущен запрос. Для этого должен использоваться Oracle SQL.

      LOCATION         START_TS          END_TS        OCCUPANTS  
 ----------------- ---------------- ---------------- ----------- 
  SOL2-4-SATURN     1602476821435    1602476821435            0  
  SOL2-4-SATURN     1602476821435    1602477395000            1  
  SOL2-5-MARS       1602476508223    1602476920649            5  
  SOL2-5-MARS       1602476920649    1602477395000            4  
  SOL2-5-NEPTUNE    1602476505331    1602476840140            8  
  SOL2-5-NEPTUNE    1602476840140    1602477395000           10  
  SOL2-4-JUPITER    1602476753698    1602476753698            4  
  SOL2-4-JUPITER    1602476753698    1602477395000            4  
  

Вы ищете LEAD . С LEAD помощью вы можете считывать значения следующих строк. Когда следующей строки нет, вы получаете NULL . Следовательно, используйте COALESCE для последних строк для каждого местоположения.

 select
  location,
  timestamp as start_ts,
  coalesce(
    lead(timestamp) over (partition by location order by timestamp),
    systimestamp) as end_ts,
  occupants
from mytable
order by location, timestamp;
  

(В этом запросе используются правильные временные метки. Вместо этого в вашем примере данных отображаются числа. Если вы храните числа вместо временных меток, вам придется преобразовать SYSTIMESTAMP приведенный выше запрос в такое число.)