sql - 如何在未发生事务的情况下插入 dates 时通过 date 获得运行总计

我有一个 table 的交易,我已成功查询这些交易以获得每天的总金额,按scenario_id 分区,如以下示例所示:

Tables:

交易

Transaction Date Scenario_id transaction_amount
5/19/2022 00000000 $.01
5/25/2022 00000000 $5.00
5/18/2022 10000000 $50
5/19/2022 00000000 $.01
5/25/2022 00000000 $5.00
5/18/2022 10000000 $50

过滤器

starting_cash start_date end_date
$50,000 5/19/2022 5/25/2022

代码:

SELECT   transaction_date, scenario_id, SUM(transaction_amount) AS net_daily,
                             (SELECT   filters.starting_cash
                                FROM         filters) + SUM(SUM(transaction_amount)) OVER (PARTITION BY scenario_id
ORDER BY transaction_date) AS forecasted_cash
FROM         Transactions
WHERE     transaction_date >=
                             (SELECT   filters.start_date
                                FROM         filters)
GROUP BY transaction_date, scenario_id

当前结果

Transaction Date Scenario_id net_daily Forecasted_cash
5/19/2022 00000000 $.02 $50,000.02
5/25/2022 00000000 $10 $50,010.02
5/18/2022 10000000 $100 $50,100

但是,我希望在过滤的时间线中将所有空的 dates 填充为每天净 0 美元,同时从前一天进行预测的现金运行总额:

期望的结果

Transaction Date Scenario_id net_daily Forecasted_cash
5/19/2022 00000000 $.02 $50,000.02
5/20/2022 00000000 $0. $50,000.02
5/21/2022 00000000 $0. $50,000.02
5/22/2022 00000000 $0. $50,000.02
5/23/2022 00000000 $0. $50,000.02
5/24/2022 00000000 $0. $50,000.02
5/25/2022 00000000 $10 $50,010.02
5/18/2022 10000000 $100 $50,100
5/19/2022 10000000 $0 $50,100
5/20/2022 10000000 $0 $50,100
5/21/2022 10000000 $0 $50,100
5/22/2022 10000000 $0 $50,100
5/23/2022 10000000 $0 $50,100
5/24/2022 10000000 $0 $50,100
5/25/2022 10000000 $0 $50,100

实现这一目标的最佳方法是什么?

回答1

您需要生成一个 dates 列表,然后左连接到 Transactions table。

CTE filters_dates 递归生成 dates 的列表。 CTE scenario 得到不同的 Scenario_id。 CTE transdatescenario_id 汇总事务,因为您有多个具有相同 date 的条目。 Forecasted_cash 的最终结果基本上是 Transaction _Amount + starting_cash 的总和

with 
filters_dates as
(
    select starting_cash, start_date, end_date, trans_date = start_date
    from   Filters
    union all
    select starting_cash, start_date, end_date, trans_date = dateadd(day, 1, trans_date)
    from   filters_dates
    where  trans_date < end_date
),
scenario as
(
    select distinct Scenario_id 
    from   Transactions
),
trans as
(
    select trans_date = transaction_date, Scenario_id, trans_amount = sum(transaction_amount)
    from   Transactions
    group by transaction_date, Scenario_id
)
select f.trans_date, 
       s.Scenario_id,
       net_daily = isnull(t.trans_amount, 0),
       Forecasted_cash = f.starting_cash
                       + sum(isnull(t.trans_amount, 0)) over (partition by s.Scenario_id
                                                                  order by f.trans_date)
from   filters_dates f
       cross join scenario s
       left join trans t  on  f.trans_date = t.Trans_Date
                          and s.Scenario_id = t.Scenario_id
order by s.Scenario_id, f.trans_date;

相似文章