Слияние нескольких временных рядов "изменение состояния"
Учитывая ряд таблиц, подобных приведенным ниже, представляющих изменения состояния во время t объекта, идентифицированного id:
| A | | B |
| t | id | a | | t | id | b |
| - | -- | - | | - | -- | - |
| 0 | 1 | 1 | | 0 | 1 | 3 |
| 1 | 1 | 2 | | 2 | 1 | 2 |
| 5 | 1 | 3 | | 3 | 1 | 1 |
где t на самом деле является полем DateTime с точностью до миллисекунды (что делает дискретизацию невозможной), как мне создать следующий вывод?
| output |
| t | id | a | b |
| - | -- | - | - |
| 0 | 1 | 1 | 3 |
| 1 | 1 | 2 | 3 |
| 2 | 1 | 2 | 2 |
| 3 | 1 | 2 | 1 |
| 5 | 1 | 3 | 1 |
Идея состоит в том, что для любой заданной временной метки ввода можно извлечь все состояние выбранного объекта, выбрав одну строку из результирующей таблицы. Поэтому самое последнее состояние каждой переменной, соответствующее любому времени, должно присутствовать в каждой строке.
Я пробовал различные операторы JOIN, но, похоже, ничего не получаю.
Обратите внимание, что в моем случае использования:
- строки также должны быть соединены сущностью
id - может быть более двух исходных таблиц для слияния
- Я запускаю PostgreSQL, но в конечном итоге я переведу запрос в SQLAlchemy, поэтому чистое решение SQLAlchemy было бы еще лучше.
Я создал db<>fiddle с данными примера.
Ответы 1
Я думаю, вы хотите full join и некоторые другие манипуляции. Идеалом было бы:
select t, id,
last_value(a.a ignore nulls) over (partition by id order by t) as a,
last_value(b.b ignore nulls) over (partition by id order by t) as b
from a full join
b
using (t, id);
Но . . . Postgres не поддерживает ignore nulls. Итак, альтернативный метод:
select t, id,
max(a) over (partition by id, grp_a) as a,
max(b) over (partition by id, grp_b) as b
from (select *,
count(a.a) over (partition by id order by t) as grp_a,
count(b.b) over (partition by id order by t) as grp_b
from a full join
b
using (t, id)
) ab;
@Себ. . . Я исправил опечатки.