用于从事务表生成定期快照的SQL

事后，我尝试从数据库的事务表创建定期快照视图。TRANSACTION表有以下字段：

• Account_id(外键)
• Event_id
• STATUS_DT
• STATUS_CD

每次帐户在应用程序中更改状态时，都会在TRANSACTION表中添加一个具有新状态的新行。我想生成一个按状态显示每个日期的帐户计数的视图；它应该有以下字段：

• SNAPSHOT_DT
• STATUS_CD
• 账户计数

这将获取任意给定日期的计数，但不是所有日期的计数：

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

谢谢！

---

解决方案

好的，这将很难解释。

在每个状态的每个日期，您应该累计两个值：

• 开始时处于该状态的客户数量。
• 以该状态离开的客户数量。

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

第二个值几乎同样简单。您将获得上一个状态代码，并计算该状态代码在该日期"离开"的次数。

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

我坦率地承认以下代码没有经过测试(如果您有一个SQL Fdle，我很乐意对其进行测试)。但结果查询如下所示：

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在结果集中创建行。目前还不清楚这是否是必需的，因此我省略了这一复杂性。