当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Adding rows, running count, running sum to query results

问题描述:

I have a table with the following ddl.

 CREATE TABLE "LEDGER"

("FY" NUMBER,

"FP" VARCHAR2(20 BYTE),

"FUND" VARCHAR2(20 BYTE),

"TYPE" VARCHAR2(2 BYTE),

"AMT" NUMBER

)

The table contains the following data.

REM INSERTING into LEDGER

SET DEFINE OFF;

Insert into LEDGER (FY,FP,FUND,TYPE,AMT) values (15,'03','A','03',1);

Insert into LEDGER (FY,FP,FUND,TYPE,AMT) values (15,'04','A','03',2);

Insert into LEDGER (FY,FP,FUND,TYPE,AMT) values (16,'04','A','03',3);

Insert into LEDGER (FY,FP,FUND,TYPE,AMT) values (12,'05','A','04',6);

based on the partition of fy,fp,fund and type I would like to write a query to keep a running count from the beginning of fp(fp though it is a varchar it represents a number in the month. i.E 2 equals february and 3 equals march etc.) to a hard number of 14. So taking a closer look at the data you will notice that in FY 15 the max period is 04 so i must add another 10 periods to my report to get my report to have the full 14 periods. here is the expected output.

here is what i tried, but I'm just simply stumbling all together on this.

WITH fy_range AS

(

SELECT MIN (fy) AS min_fy

, MAX (fy) AS max_fy

FROM ledger

),all_fys AS

(

SELECT min_fy + LEVEL - 1 AS fy

FROM fy_range

CONNECT BY LEVEL <= max_fy + 1 - min_fy

)

,all_fps AS

(

SELECT TO_CHAR (LEVEL, 'FM00') AS fp

FROM dual

CONNECT BY LEVEL <= 14

)

SELECT

FUND

,G.TYPE

,G.FY

,G.FP

,LAST_VALUE(G.AMT ignore nulls) OVER (PARTITION BY G.FUND ORDER BY Y.FY P.FP ) AS AMT

FROM all_fys y

CROSS JOIN all_fps p

LEFT OUTER JOIN LEDGER G PARTITION BY(FUND)

ON g.fy = y.fy

AND g.fp = p.fp;

but I end up with a bunch of nulls and some strange results.

网友答案:

This may not be the most efficient solution, but it is easy to understand and maintain. First (in the most deeply nested subquery) we find the min FP for each combination of FY, FUND and TYPE. Then we use a CONNECT BY query to fill all the FP for all FY, FUND, TYPE combinations (up to the hard upper limit of 14). Then we left-outer-join to the original data in the LEDGER table. So far we densified the data. In the final query (the join) we also add the column for the cumulative sum - that part is easy after we densified the data.

TYPE is an Oracle keyword, so it is probably best not to use it as a column name. It is also best not to use double-quoted table and column names (I had to use upper case everywhere because of that). I also made sure to convert from varchar2 to number and back to varchar2 - we shouldn't rely on implicit conversions.

select S.FY, to_char(S.FP, 'FM09') as FP, S.FUND, S.TYPE,
       sum(L.AMT) over (partition by S.FY, S.FUND, S.TYPE order by S.FP) as CUMULATIVE_AMT
from   (
         select FY, MIN_FP + level - 1 as FP, FUND, TYPE
         from   (
                  select   FY, min(to_number(FP)) as MIN_FP, FUND, TYPE
                  from     LEDGER
                  group by FY, FUND, TYPE
                )
         connect by level <= 15 - MIN_FP
             and prior FY   = FY
             and prior FUND = FUND
             and prior TYPE = TYPE
             and prior sys_guid() is not null
       ) S left outer join LEDGER L 
           on S.FY = L.FY and S.FP = L.FP and S.FUND = L.FUND and S.TYPE = L.TYPE
;

Output:

FY FP  FUND TYPE CUMULATIVE_AMT
--- --- ---- ---- --------------
 12 05  A    04                6
 12 06  A    04                6
 12 07  A    04                6
 12 08  A    04                6
 12 09  A    04                6
 12 10  A    04                6
 12 11  A    04                6
 12 12  A    04                6
 12 13  A    04                6
 12 14  A    04                6
 15 03  A    03                1
 15 04  A    03                3
 15 05  A    03                3
 15 06  A    03                3
 15 07  A    03                3
 15 08  A    03                3
 15 09  A    03                3
 15 10  A    03                3
 15 11  A    03                3
 15 12  A    03                3
 15 13  A    03                3
 15 14  A    03                3
 16 04  A    03                3
 16 05  A    03                3
 16 06  A    03                3
 16 07  A    03                3
 16 08  A    03                3
 16 09  A    03                3
 16 10  A    03                3
 16 11  A    03                3
 16 12  A    03                3
 16 13  A    03                3
 16 14  A    03                3
分享给朋友:
您可能感兴趣的文章:
随机阅读: