Блог

У меня есть таблица с именем tbl_device_log . Он вставляет запись при запуске обновления в другую таблицу.

таблица модели

  ------------ ------------ --------------------- 
| device_id  | status     | last_updated        |
 ============ ============ ===================== 
| ab1        | S1         | 2020-10-05 10:00:00 |
 ------------ ------------ --------------------- 
| ab2        | S1         | 2020-10-05 11:00:00 |
 ------------ ------------ --------------------- 
| ab1        | S2         | 2020-10-05 12:00:00 |
 ------------ ------------ --------------------- 
| ab2        | S2         | 2020-10-05 12:00:00 |
 ------------ ------------ --------------------- 
| ab1        | S3         | 2020-10-05 14:00:00 |
 ------------ ------------ --------------------- 

  

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

Нравится

  ------------ ------------ ---------------------- 
| device_id  | status | last_status | date       |
 ============ ============ ====================== 
| ab1        | S1     | S3          | 2020-10-05 |
 ------------ ------------ ---------------------- 
| ab2        | S1     | S2          | 2020-10-05 |
 ------------ ------------ ---------------------- 
  

Я попытался присоединиться к таблице в порядке возрастания и убывания следующим образом

 select tbl_device_log.device_id, tbl_device_log.status, dl.status as last_status, date(tbl_device_log.last_updated) as date,tbl_
from tbl_device_log
join (select * from tbl_device_log group by device_id, date(last_updated) order by last_updated desc) as 
    dl ON (dl.device_id = tbl_device_log.device_id and date(dl.last_updated) = date(tbl_device_log.last_updated))
group by tbl_device_log.device_id, date(tbl_device_log.last_updated)
order by tbl_device_log.last_updated asc;
  

Но я получаю следующие данные

  ------------ ------------ ---------------------- 
| device_id  | status | last_status | date       |
 ============ ============ ====================== 
| ab1        | S1     | S1          | 2020-10-05 |
 ------------ ------------ ---------------------- 
| ab2        | S1     | S1          | 2020-10-05 |
 ------------ ------------ ---------------------- 
  

Что я делаю не так?

Я бы предложил оконные функции:

 select dl.*
from (select dl.*,
             row_number() over (partition by device_id, date(date) order by last_updated) as seqnum_asc,
             row_number() over (partition by device_id, date(date) order by last_updated desc) as seqnum_desc
      from tbl_device_log dl
     ) dl
where seqnum_asc = 1 or seqnum_desc = 1;
  

Пожалуйста, проверьте следующий запрос как решение:

 select distinct -- prevent duplicates
    device_id,
    -- get first status per device per day
    first_value(status) over (PARTITION BY device_id, date(last_updated) order by last_updated asc) as status,
    -- get last status per device per day
    first_value(status) over (PARTITION BY device_id, date(last_updated) order by last_updated desc) as last_status,
    date(last_updated) as date
from tbl_device_log;
  

Протестируйте DB fiddle на SQLize.online

Результат:

 device_id    status    last_status    date
ab1          S1        S3             2020-10-05
ab2          S1        S2             2020-10-05