本文介紹了用于從事務表生成定期快照的SQL的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！
問題描述
事后，我嘗試從數(shù)據(jù)庫的事務表創(chuàng)建定期快照視圖。TRANSACTION表有以下字段：
Account_id(外鍵)
Event_id
STATUS_DT
STATUS_CD
每次帳戶在應用程序中更改狀態(tài)時，都會在TRANSACTION表中添加一個具有新狀態(tài)的新行。我想生成一個按狀態(tài)顯示每個日期的帳戶計數(shù)的視圖；它應該有以下字段：
SNAPSHOT_DT
STATUS_CD
賬戶計數(shù)
這將獲取任意給定日期的計數(shù)，但不是所有日期的計數(shù)：
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
謝謝！
推薦答案
好的，這將很難解釋。
在每個狀態(tài)的每個日期，您應該累計兩個值：
開始時處于該狀態(tài)的客戶數(shù)量。
以該狀態(tài)離開的客戶數(shù)量。
第一個值很簡單。它只是按日期和狀態(tài)匯總的交易。
第二個值幾乎同樣簡單。您將獲得上一個狀態(tài)代碼，并計算該狀態(tài)代碼在該日期”離開”的次數(shù)。
然后，關鍵字是第一個值的累計和減去第二個值的累計和。
我坦率地承認以下代碼沒有經過測試(如果您有一個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;
如果您有一個或多個狀態(tài)不變的日期和您希望在輸出中包括這些日期，則上面的查詢需要首先使用cross join在結果集中創(chuàng)建行。目前還不清楚這是否是必需的，因此我省略了這一復雜性。
這篇關于用于從事務表生成定期快照的SQL的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，
