У меня есть набор данных об изменении состояния, привязанных к группе элементов [Карты Trello и их изменения состояния, чтобы быть конкретными]. Я хочу взять этот набор переходов [Item_id, From_state, To_state, Timestamp] и создать набор данных, который выглядит как [State, Day, Item Count] для каждого состояния.
В настоящее время я создаю этот список с довольно интенсивным использованием процессора из Python после возврата всех переходов и их агрегирования. Я искал более быстрый способ сделать это в PSQL.
🤔 А знаете ли вы, что...
SQL поддерживает подзапросы (subqueries) для выполнения вложенных запросов.
Вы ищете запрос Postgres, который собирает данные по дням и штатам?
В зависимости от вашего метода вычисления состояний это должно выглядеть примерно так:
SELECT
t.from_state,
t.timestamp::date as day,
COUNT(*) as item_count
FROM mytable t
GROUP BY
t.from_state,
t.timestamp::date
С [Item_id, From_state, To_state, Timestamp] вам нужно много работать, чтобы рассчитать моментальный снимок, но если бы у вас были такие данные, это было бы очень просто: [Item_id, состояние, start_timestamp, end_timestamp]
К счастью, можно перейти от одного формата к другому:
Для такого рода проблем я считаю, что самый простой подход заключается в следующем:
Имея это в виду, что-то, следующее приведенному ниже шаблону, должно работать.
--CTE for step 1
with days as (SELECT day::date as d
FROM generate_series(timestamp '2004-03-07'
, timestamp '2004-08-16'
, interval '1 day') day)
--CTE FOR step 2
, state_list as (select from_state as s from transition_table t group by from_state)
--CTE for step 3
, time_in_state as( select t.item_id, t.to_state as item_state, t."Timestamp" as start_timestamp
, (select min(t2."Timestamp") from transition_table t2 where t2.item_id = t.item_id and t2."Timestamp" > t."Timestamp" ) as end_timestamp
from transition_table t )
--finally, the actual query is straightforward
select days.d
, state_list.s as item_state
, count(distinct t.item_id) as items_in_state_at_some_point_in_day
from days
join state_list on TRUE --full join
left join time_in_state t on t.item_state = state_list.s and days.d >= date_trunc('day', t.start_timestamp) and days.d < coalesce(t.end_timestamp, now() )
group by days.d, state_list.s