SQL-запрос для получения ежедневных снимков из данных об изменении состояния

У меня есть набор данных об изменении состояния, привязанных к группе элементов [Карты Trello и их изменения состояния, чтобы быть конкретными]. Я хочу взять этот набор переходов [Item_id, From_state, To_state, Timestamp] и создать набор данных, который выглядит как [State, Day, Item Count] для каждого состояния.

В настоящее время я создаю этот список с довольно интенсивным использованием процессора из Python после возврата всех переходов и их агрегирования. Я искал более быстрый способ сделать это в PSQL.

🤔 А знаете ли вы, что...
SQL поддерживает подзапросы (subqueries) для выполнения вложенных запросов.


231
2

Ответы:

Вы ищете запрос 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]

К счастью, можно перейти от одного формата к другому:

Для такого рода проблем я считаю, что самый простой подход заключается в следующем:

  • Сгенерировать список дней
  • Сгенерируйте список интересующих штатов (этот список вам нужен, потому что могут быть дни с нулевыми картами в штате на конкретную дату, и вы, вероятно, хотите, чтобы строка содержала ноль, а не строку)
  • Преобразование данных в формат [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