我有一个 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 trans 按 date 和 scenario_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;