添加行，运行计数，运行总和以查询结果

I have a table with the following ddl.

我有一个包含以下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.

基于fy，fp，基金和类型的分区，我想写一个查询来保持从fp开始的运行计数（fp虽然它是一个varchar，它代表一个月中的数字.iE 2等于2月和3因此，如果仔细查看数据，您会注意到在FY 15中最长期限为04，所以我必须在报告中添加另外10个期间以使我的报告完整14个时期。这是预期的输出。

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.

但我最终得到了一堆空值和一些奇怪的结果。

1 个解决方案

#1

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.

这可能不是最有效的解决方案，但它易于理解和维护。首先（在最深嵌套的子查询中），我们为FY，FUND和TYPE的每个组合找到最小FP。然后我们使用CONNECT BY查询来填充所有FY，FUND，TYPE组合的所有FP（最高硬限制为14）。然后我们左外连接到LEDGER表中的原始数据。到目前为止，我们将数据加密了。在最终查询（连接）中，我们还添加了累积和的列 - 在我们将数据加密后，该部分很容易。

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.

TYPE是Oracle关键字，因此最好不要将其用作列名。最好不要使用双引号表和列名（由于这个原因，我不得不在大家处使用大写）。我还确保从varchar2转换为number并返回到varchar2 - 我们不应该依赖隐式转换。

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