累积添加上个月或上年的缺失数据

2021-12-24 00:00:00 sql oracle plsql

假设我有以下数据:

select 1 id, 'A' name, '2007' year, '04' month,  5 sales  from dual union all
select 2 id, 'A' name, '2007' year, '05' month,  2 sales  from dual union all
select 3 id, 'B' name, '2008' year, '12' month,  3 sales  from dual union all
select 4 id, 'B' name, '2009' year, '12' month, 56 sales  from dual union all
select 5 id, 'C' name, '2009' year, '08' month, 89 sales  from dual union all
select 13 id,'B' name, '2016' year, '01' month, 10 sales  from dual union all
select 14 id,'A' name, '2016' year, '02' month,  8 sales  from dual union all
select 15 id,'D' name, '2016' year, '03' month, 12 sales  from dual union all
select 16 id,'E' name, '2016' year, '04' month, 34 sales  from dual

我想将所有年份及其各自时期(月)的所有销售额相加.输出应如下所示:

I want to cumulatively add up all the sales across all years and their respective periods (months). The output should look like the following:

name    year    month   sale   opening bal   closing bal
 A      2007     04      5        0              5
 A      2007     05      2        5              7
 B      2008     12      3        12             15
 A      2008     04      0        5              5    -- to be generated
 A      2008     05      0        7              7    -- to be generated
 B      2009     12      56       15             71
 C      2009     08      89       71             160
 A      2009     04      0        5              5    -- to be generated
 A      2009     05      0        7              7    -- to be generated
 B      2016     01      10       278            288
 B      2016     12      0         71             71  -- to be generated
 A      2016     02      8        288            296
 A      2016     04      0         5              5   -- to be generated
 A      2016     05      0         7              7   -- to be generated
 D      2016     03      12       296            308
 E      2016     04      34       308            342
 C      2016     08      0        160            160  -- to be generated

期初余额是上个月的期末余额，如果进入下一年比期初余额是上年的期末余额.在接下来的几年里，它应该能够像这样工作.我有这部分工作.但是，我不知道如何解决 2008 年存在的 2009 年缺失的问题.例如，2009 年不存在密钥 A,2008,04 和 A,2008,05，代码应该能够添加它在 2009 年像上面一样.其他年份和月份也是如此.

The Opening balance is the closing balance of previous month, and if it goes into next year than the opening balance for next year is the closing balance of the previous year. It should be able to work like this for subsequent years. I've got this part working. However, I don't know how to get around ths missing in say 2009 that exists in 2008. For instance the key A,2008,04 and also A,2008,05 does not exist in 2009 and the code should be able to add it in 2009 like above. Same applies for other years and months.

我正在研究 Oracle 12c.

I'm working on Oracle 12c.

提前致谢.

推荐答案

这是我能得到的最接近您的结果，尽管我意识到这不是完全匹配.例如，您的期初余额看起来不正确(id = 3 的输出行的期初余额 12 是从哪里来的?).无论如何，希望以下内容能让您进行适当的修改:

This is the closest I can get to your result, although I realise it's not an exact match. For example, your opening balances don't look correct (where did the opening balance of 12 come from for the output row for id = 3?). Anyway, hopefully the following will enable you to amend as appropriate:

with sample_data as (select 1 id, 'A' name, '2007' year, '04' month,  5 sales  from dual union all
                     select 2 id, 'A' name, '2007' year, '05' month,  2 sales  from dual union all
                     select 3 id, 'B' name, '2008' year, '12' month,  3 sales  from dual union all
                     select 4 id, 'B' name, '2009' year, '12' month, 56 sales  from dual union all
                     select 5 id, 'C' name, '2009' year, '08' month, 89 sales  from dual union all
                     select 13 id, 'B' name, '2016' year, '01' month, 10 sales  from dual union all
                     select 14 id, 'A' name, '2016' year, '02' month,  8 sales  from dual union all
                     select 15 id, 'D' name, '2016' year, '03' month, 12 sales  from dual union all
                     select 16 id, 'E' name, '2016' year, '04' month, 34 sales  from dual),
             dts as (select distinct year
                     from   sample_data),
             res as (select sd.name,
                            dts.year,
                            sd.month,
                            nvl(sd.sales, 0) sales,
                            min(sd.year) over (partition by sd.name, sd.month) min_year_per_name_month,
                            sum(nvl(sd.sales, 0)) over (partition by name order by to_date(dts.year||'-'||sd.month, 'yyyy-mm')) - nvl(sd.sales, 0) as opening,
                            sum(nvl(sd.sales, 0)) over (partition by name order by to_date(dts.year||'-'||sd.month, 'yyyy-mm')) as closing
                     from   dts
                            left outer join sample_data sd partition by (sd.name, sd.month) on (sd.year = dts.year))
select name,
       year,
       month,
       sales,
       opening,
       closing
from   res
where  (opening != 0 or closing != 0)
and    year >= min_year_per_name_month
order by to_date(year||'-'||month, 'yyyy-mm'),
         name;

NAME YEAR MONTH      SALES    OPENING    CLOSING
---- ---- ----- ---------- ---------- ----------
A    2007 04             5          0          5
A    2007 05             2          5          7
A    2008 04             0          7          7
A    2008 05             0          7          7
B    2008 12             3          0          3
A    2009 04             0          7          7
A    2009 05             0          7          7
C    2009 08            89          0         89
B    2009 12            56          3         59
B    2016 01            10         59         69
A    2016 02             8          7         15
D    2016 03            12          0         12
A    2016 04             0         15         15
E    2016 04            34          0         34
A    2016 05             0         15         15
C    2016 08             0         89         89
B    2016 12             0         69         69

我使用了分区外连接来链接任何月份和表中的名称组合(在我的查询中，sample_data 子查询 - 您不需要该子查询，您只需使用您的表！)到同一个表中的任何年份，然后工作出期初/期末余额.然后我丢弃任何期初余额和期末余额为 0 的行.

I've used Partition Outer Join to link any month and name combination in the table (in my query, the sample_data subquery - you wouldn't need that subquery, you'd just use your table instead!) to any year in the same table, and then working out the opening / closing balances. I then discard any rows that have an opening and closing balance of 0.

相关文章