SQL从事务中生成定期快照表

Question

我试图在事实之后从数据库的事务表中创建定期快照视图。交易表具有以下字段：

• ACCOUNT_ID（外键）
• EVENT_ID
• status_dt
• status_cd

每次帐户改变了应用程序的状态，新的行以新状态添加到交易表中。我想创建一个视图，显示每个日期的状态帐户数量;它应该具有以下字段：

• snapshot_dt
• status_cd
• count_of_accounts

这将让任何给定的一天计数，但不是所有的日子：

SELECT status_cd, COUNT(account_id) AS count_of_accounts 
FROM transactions 
JOIN (
     SELECT account_id, MAX(event_id) AS event_id 
     FROM transactions 
     WHERE status_dt <= DATE '2014-12-05') latest 
USING (account_id, event_id) 
GROUP BY status_cd 

谢谢！

Answer 1

好的，这将很难解释。

在每个日期对应每种状态，你应该算两个值：

• 谁与状态开始客户的数量。
• 以此状态离开的客户数量。

第一个值很简单。它只是按日期和状态汇总交易。

第二个值几乎一样容易。您将获得之前的状态码，并计算该状态码在该日期“离开”的次数。

然后，关键是第一个值的累加和减去第二个值的累加和。

我坦率地承认，下面的代码没有经过测试（如果你有一个SQL小提琴，我很乐意测试它）。但是，这是生成的查询是什么样子：

select status_dte, status_cd, 
     (sum(inc_cnt) over (partition by status_cd order by status_dt) - 
     sum(dec_cnt) over (partition by status_cd order by status_dt) 
     ) as dateamount 
from ((select t.status_dt, t.status_cd, count(*) as inc_cnt, 0 as dec_cnt 
     from transactions t 
     group by t.status_dt, t.status_cd 
    ) union all 
     (select t.status_dt, prev_status_cd, 0, count(*) 
     from (select t.* 
        lag(t.status_cd) over (partition by t.account_id order by status_dt) as prev_status_cd 
      from transactions t 
      ) t 
     where prev_status_cd is null 
     group by t.status_dt, prev_status_cd 
    ) 
    ) t; 

如果您有日期，其中有一个或多个状态和要包括那些在输出没有变化，那么上面的查询需要使用cross join首先在结果集中创建行。目前尚不清楚这是否是一项要求，所以我将这一并发症排除在外。