Блог

У меня есть таблица устройств (dev) и таблица device_date (dev_data). Отношение 1: M

таблица разработчиков:

 | id  |name   |status |
|-----|-------|-------|
| 1   |a      |111    |
|-----|-------|-------|
| 2   |b      |123    |
|-----|-------|-------|
| ....|.....  |....   |
 

таблица dev_data:

 |id |dev_id |status  |date                    |
|---|-------|--------|------------------------|
|1  |1      | 123    |2019-04-16T18:53:07.908Z|
|---|-------|--------|------------------------|
|2  |1      | 120    |2019-04-16T18:54:07.908Z|
|---|-------|--------|------------------------|
|3  |1      | 1207   |2019-04-16T18:55:07.908Z|
|---|-------|--------|------------------------|
|4  |2      | 123    |2019-04-16T18:53:08.908Z|
|---|-------|--------|------------------------|
|5  |2      | 121    |2019-04-16T18:54:08.908Z|
|---|-------|--------|------------------------|
|6  |2      | 127    |2019-04-16T18:55:08.908Z|
|...|.......|........|........................|
 

Мне нужно выбрать всех разработчиков и объединить dev_data, но добавить только 2 последние записи (по дате)

окончательный ответ должен выглядеть следующим образом:

status_calc_1 и status_calc_2 разница между status in dev и dev_data

status_calc_1 => status отличие последней строки от dev_data и dev

status_calc_2 => status отличие предыдущей строки от dev_data и dev

 |id  |name  |status_calc_1  | status_calc_2 |
|----|------|---------------|---------------|
|1   |a     |1207-111       |120-111        |
|----|------|---------------|---------------|
|2   |b     |127-123        |121-123        |
 

Я попробовал это:

 select id, "name", status, max(dd.date) as last,
       (select date from device_data p where p.dev_id = device.id and date < dd.date limit 1) as prelast
from device
  inner join device_data dd on device.id = dd.dev_id
group by id, "name", status;
 

но получите сообщение об ошибке:

 ERROR: subquery uses ungrouped column "device.id" from outer query
 

и этот:

 select id, "name", status, max(dd.date) as last, max(dd2.date) as prelast,
from device
  inner join device_data dd on device.id = dd.dev_id
  inner join device_data dd2 on device.id = dd2.dev_id and dd2.date < dd.date
group by id, "name", status;
 

Я получаю правильные 2 последних dev_data, но, тем не менее, понятия не имею, как сделать 2 столбца status_calc_1 и status_calc_2

status_calc_1 = последняя строка dev_data.status — dev.status

status_calc_2 = последняя строка dev_data.status — dev.status

Вы можете использовать условную агрегацию:

 select d.id, d.name, d.status,
       max(dd.date) as last,
       max(case when dd.seqnum = 2 then dd.date end) as prelast,
       (max(case when dd.seqnum = 1 then dd.status end) - d.status) as status_calc_1,
       (max(case when dd.seqnum = 2 then dd.status end) - d.status) as status_calc_2
from device d join
     (select dd.*,
             row_number() over (partition by dd.dev_id order by dd.date desc) as seqnum
      from device_data dd
     ) dd
     on d.id = dd.dev_id
where seqnum <= 2
group by d.id, d.name, d.status;