如何使用分析函数计算各账户BAL列的平均值？

现有SQL代码 #

with cte as (
select distinct t.DATE_ID,
        ad.ACCOUNT_ID
        from TIMEDATE t 
         ,  ACCOUNT_DLY ad)
         
select  cte.date_id,
        cte.Account_ID, 
        NVL(current_bal,lag (ad.current_bal) ignore nulls over (PARTITION by cte.account_id order by cte.date_id )) as bal
from cte left join ACCOUNT_DLY ad
on cte.date_id = ad.SRC_EXTRACT_DT
and cte.ACCOUNT_ID = ad.ACCOUNT_ID
order by 2,1;

表结构与预期结果 #

当前涉及表结构 #

预期输出结果 #

需在原有查询的bal列基础上，新增每个账户的bal列平均值，示例格式如下：

修改后的SQL方案 #

基于原有查询生成的bal值，通过分区分析函数计算每个账户的平均值即可，代码如下：

with cte as (
select distinct t.DATE_ID,
        ad.ACCOUNT_ID
        from TIMEDATE t 
         ,  ACCOUNT_DLY ad),
bal_cte as (
select  cte.date_id,
        cte.Account_ID, 
        NVL(current_bal,lag (ad.current_bal) ignore nulls over (PARTITION by cte.account_id order by cte.date_id )) as bal
from cte left join ACCOUNT_DLY ad
on cte.date_id = ad.SRC_EXTRACT_DT
and cte.ACCOUNT_ID = ad.ACCOUNT_ID
)
select 
    date_id,
    Account_ID,
    bal,
    AVG(bal) over (PARTITION by Account_ID) as avg_bal
from bal_cte
order by Account_ID, date_id;

逻辑说明 #

1. 新增bal_cte子查询，先完成原有逻辑：补全每个账户每日的余额空值，生成bal列
2. 外层查询通过AVG(bal) over (PARTITION by Account_ID)，按账户分组计算该账户所有bal值的平均值，同一账户的所有行将显示相同的平均值结果

内容的提问来源于stack exchange，提问作者Jony_Junior