postgreSQL 查询，用于从状态更改数据中获取每日快照

Question

我有一组状态更改数据与一堆项目[Trello卡及其状态更改具体]相关联。 我希望采用这组转换[Item_id，From_state，To_state，时间戳]，并为每个状态生成一组看起来像[州，日，项目计数]的数据。

目前，在返回所有转换并聚合它们后，我正在从 Python 以相当 CPU 密集型的方式构建此列表。 我一直在寻找一种更快的方法来在 PSQL 中执行此操作。

Answer 1

使用 [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

Answer 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