时间戳

首页 > 解决方案 > Oracle SQL 从离散数据计算平均/期初/期末余额

sql - Oracle SQL 从离散数据计算平均/期初/期末余额

问题描述

我有这样的账户余额

acc_no      balance  balance_date
account1    5000     2020-01-01
account1    6000     2020-01-05
account2    3000     2020-01-01
account1    3500     2020-01-08
account2    7500     2020-01-15

没有余额条目的任何一天的有效余额等于最后余额。例如，1 月 2、3、4 日的 account1 余额为 5000 等。

我想从该数据中生成任何时期的日均值、期初余额和期末余额。我想出了以下查询并且它可以工作，但是当我针对完整数据集运行它时需要半个小时。我的方法是正确的还是有更有效的方法？

WITH cte_period
AS (
    SELECT '2020-01-01' date_from
        ,'2020-01-31' date_to
    FROM dual
    )
    ,cte_calendar
AS (
    SELECT rownum
        ,(
            SELECT to_date(date_from, 'YYYY-MM-DD')
            FROM cte_period
            ) + rownum - 1 AS balance_day
    FROM dual connect BY rownum <= (
            SELECT to_date(date_to, 'YYYY-MM-DD')
            FROM cte_period
            ) - (
            SELECT to_date(date_from, 'YYYY-MM-DD')
            FROM cte_period
            ) + 1
    )
    ,cte_balances
AS (
    SELECT 'account1' acc_no
        ,5000 balance
        ,to_date('2020-01-01', 'YYYY-MM-DD') sys_date
    FROM dual

    UNION ALL

    SELECT 'account1'
        ,6000
        ,to_date('2020-01-05', 'YYYY-MM-DD')
    FROM dual

    UNION ALL

    SELECT 'account2'
        ,3000
        ,to_date('2020-01-01', 'YYYY-MM-DD')
    FROM dual

    UNION ALL

    SELECT 'account1'
        ,3500
        ,to_date('2020-01-08', 'YYYY-MM-DD')
    FROM dual

    UNION ALL

    SELECT 'account2'
        ,7500
        ,to_date('2020-01-15', 'YYYY-MM-DD')
    FROM dual
    )
    ,cte_accounts
AS (
    SELECT DISTINCT acc_no
    FROM cte_balances
    )
SELECT t.acc_no
    ,(
        SELECT eff_bal
        FROM (
            SELECT cal.balance_day
                ,acc_nos.acc_no
                ,(
                    SELECT balance
                    FROM cte_balances bal
                    WHERE bal.sys_date <= cal.balance_day
                        AND acc_nos.acc_no = bal.acc_no
                    ORDER BY bal.sys_date DESC FETCH first 1 row ONLY
                    ) eff_bal
            FROM cte_calendar cal
            CROSS JOIN cte_accounts acc_nos
            ) t1
        WHERE balance_day = (
                SELECT to_date(date_from, 'YYYY-MM-DD')
                FROM cte_period
                )
            AND t.acc_no = t1.acc_no
        ) opening_bal
    ,(
        SELECT eff_bal
        FROM (
            SELECT cal.balance_day
                ,acc_nos.acc_no
                ,(
                    SELECT balance
                    FROM cte_balances bal
                    WHERE bal.sys_date <= cal.balance_day
                        AND acc_nos.acc_no = bal.acc_no
                    ORDER BY bal.sys_date DESC FETCH first 1 row ONLY
                    ) eff_bal
            FROM cte_calendar cal
            CROSS JOIN cte_accounts acc_nos
            ) t1
        WHERE balance_day = (
                SELECT to_date(date_to, 'YYYY-MM-DD')
                FROM cte_period
                )
            AND t.acc_no = t1.acc_no
        ) closing_bal
    ,round(avg(eff_bal), 2) avg_bal
FROM (
    SELECT cal.balance_day
        ,acc_nos.acc_no
        ,(
            SELECT balance
            FROM cte_balances bal
            WHERE bal.sys_date <= cal.balance_day
                AND acc_nos.acc_no = bal.acc_no
            ORDER BY bal.sys_date DESC FETCH first 1 row ONLY
            ) eff_bal
    FROM cte_calendar cal
    CROSS JOIN cte_accounts acc_nos
    ) t
GROUP BY acc_no
order by acc_no

预期结果 ACC_NO OPENING_BAL CLOSING_BAL AVG_BAL account1 5000 3500 3935.48 account2 3000 7500 5467.74

标签： sqloracle

解决方案

是的。您无需多次从同一张表中进行选择。像您一样生成日历，加入按帐户分区的数据并使用分析函数进行计算：

select acc_no, round(avg(bal), 2) av_bal,
       max(bal) keep (dense_rank first order by day) op_bal, 
       max(bal) keep (dense_rank last order by day) cl_bal
  from (
    select acc_no, day, 
           nvl(balance, lag(balance) ignore nulls over (partition by acc_no order by day)) bal
      from (
        select date_from + level - 1 as day
          from (select date '2020-01-01' date_from, date '2020-01-31' date_to from dual)
          connect by date_from + level - 1 <= date_to)
      left join cte_balances partition by (acc_no) on day = sys_date)
  group by acc_no

小提琴手

编辑：

有时每月的第一天没有余额条目，它应该采用最后可用的形式

我们必须以特殊的方式对待第一排。它是在 subquery中完成的data，在第一行和空余额的情况下，我运行相关子查询，该查询从最大上一个日期查找余额。

with 
  cte_calendar as (
    select level lvl, date_from + level - 1 as day
      from (select date '2020-01-01' date_from, date '2020-01-31' date_to from dual)
      connect by date_from + level - 1 <= date_to),
  data as (
    select lvl, day, acc_no, 
           case when balance is null and lvl = 1 
                then (select max(balance) keep (dense_rank last order by sys_date) 
                        from cte_balances a
                        where a.acc_no = b.acc_no and a.sys_date <= day) 
                else balance
           end bal
      from cte_calendar
      left join cte_balances b partition by (acc_no) on day = sys_date)
select acc_no, 
       max(bal) keep (dense_rank first order by day) op_bal, 
       max(bal) keep (dense_rank last order by day) cl_bal, 
       round(avg(bal), 2)
  from (
    select acc_no, day, 
           nvl(bal, lag(bal) ignore nulls over (partition by acc_no order by day)) bal
      from data)
  group by acc_no

小提琴手

虽然我还不明白

有三件事在这里并不明显，您应该知道了解查询：

  • 分区外连接。它是解决方案的主要部分，它为每个帐户生成整个期间。例如，您可以在此处阅读有关它们的信息，
  • lag() ignore nulls- 填充空余额值，从以前的非空值中获取它们，
  • max(bal) keep (dense_rank first order by day)取第一个日期的余额值作为期初余额。last- 期末余额的最后一行。

推荐阅读