partition by子句不支持窗口函数

Question

select t1.lease_number ,t2.rec_bal, to_date(t2.date_dim_id,'YYYYMMDD') as issue_date,t2.paid_to as pay_date,
(case when pay_date <= lag(pay_date) over (order by issue_date) then null when pay_date > lag(pay_date) over (order by issue_date) then issue_date end) as payment_date,
dense_rank() over (partition by pay_date order by issue_date) as row_numbers,
(case when row_numbers = max(row_numbers) over (partition by payment_date) then payment_date else null end) as paymentmade_date,
remain_months_upd,remaining_pymt_periods, t2.dealer_dim_id, t2.lease_contract_dim_id
from dm_business_ops_tcci.v_tcci_lease_contract_dim t1
, dm_business_ops_tcci.v_tcci_lease_transaction_fact t2
where t1.lease_contract_dim_id=t2.lease_contract_dim_id
and t2.date_dim_id >=20210301 -- can be changed to latest busienss date
and lease_number in (1633014)
order by issue_date

我正在尝试通过使用窗口函数创建的列进行分区，我不能这样做。错误来自行"(当row_numbers = max(row_numbers) over(分区由payment_date)然后payment_date否则null结束的情况下)作为paymentmade_date"。Payment_date使用前面一行中的窗口函数进行计算。有解决这个问题的方法吗?

Answer 1

在对窗口函数的值执行任何类型的过滤、分区或条件操作之前，您需要具体化窗口函数的值。

有几种方法可以做到这一点，适合您用例的方法将根据此范围之外的因素而变化。

您可以在尝试此分区操作之前使用视图、CTE、临时表或表变量来完成此操作。这不是一个详尽的列表。