根据余额将释放分配给反向释放

Assign Releases to Reverse Release based on the balances

提问人：Mani 提问时间：11/13/2023 最后编辑：Mani 更新时间：11/14/2023 访问量：49

问：

我有两个表格，需要根据可用的余额（释放）分配金额。 这里的版本仅适用于 20 个，因此请根据可用性进行分配

释放

PID的 relDate 释放金额
第1页 5 月 1 日 20

反向释放

PID的 revRelDate reverese发布
第1页 5 月 5 日 15
第1页 5 月 6 日 8

所需输出

PID的 relDate revRelDate 释放金额 reverese发布
第1页 5 月 1 日 5 月 5 日 20 15
第1页 5 月 1 日 5 月 6 日 20 5

尝试查询：

WITH RELEASE AS (
    SELECT 'P1' PID, '2023-05-01' TRANSACTION_DATE, 23 RELEASE 
    FROM DUAL

) ,
REV_RELEASE AS (     
    SELECT 'P1' PID, '2023-05-05' TRANSACTION_DATE, 15 REV_RELEASE
    FROM DUAL
    UNION ALL
    SELECT 'P1' PID, '2023-05-06' TRANSACTION_DATE,  8 REV_RELEASE
    FROM DUAL)
SELECT  *,CASE
        WHEN RUNNING_TOTAL >= 0
        THEN
            CASE
                WHEN LEAD(RUNNING_TOTAL, 1, 0) OVER (PARTITION BY PID ORDER BY TRANSACTION_DATE) >= 0
                THEN REV_RELEASE
                ELSE REV_RELEASE + RUNNING_TOTAL
            END
        ELSE 0
    END 
FROM (
            SELECT  a.RELEASE,b.*, SUM(RELEASE - REV_RELEASE) OVER (PARTITION BY a.PID ORDER BY b.TRANSACTION_DATE) AS RUNNING_TOTAL
              FROM
            RELEASE a FULL OUTER JOIN REV_RELEASE b
            ON a.PID=b.PID
)
SQL 预言机 oracle11g

评论

0赞 NickW 11/13/2023
请不要使用图像，将所有信息添加为可编辑的文本（格式为数据表），以便有人可以复制它
0赞 Mani 11/13/2023
@NickW-对不起，我现在更新了
0赞 NickW 11/13/2023
如果不是错别字，当 reverseRelease 为 5 时，您如何在所需的输出中获得 8？假设这是一个错别字，你想要的结果似乎是两个表之间的直接连接
0赞 MT0 11/13/2023
@NickW 只发布了 20 个。第一排有 15 个，第二排有 8 个，即剩余的 5 个释放加上 3 个多余的。rev_release

答：

1赞 MT0 11/13/2023 #1

您可以使用分析函数对金额进行合计，然后根据累计合计对表进行合计：JOIN

SELECT COALESCE(r.pid, rr.pid) AS pid,
       r.transaction_date AS rel_date,
       rr.transaction_date AS relRevDate,
       r.release AS releaseAmount,
       LEAST(
         rr.rev_release,
         rr.total_rev_release - r.total_release + r.release,
         r.total_release - rr.total_rev_release + rr.rev_release
       )
         AS rev_release
FROM   (
         SELECT pid,
                transaction_date,
                release,
                SUM(release)
                  OVER (PARTITION BY pid ORDER BY transaction_date, ROWNUM)
                  AS total_release
         FROM   RELEASE
       ) r
       FULL OUTER JOIN (
         SELECT pid,
                transaction_date,
                rev_release,
                SUM(rev_release)
                  OVER (PARTITION BY pid ORDER BY transaction_date, ROWNUM)
                  AS total_rev_release
         FROM   REV_RELEASE
       ) rr
       ON (   r.pid = rr.pid
          AND r.total_release - r.release < rr.total_rev_release
          AND r.total_release > rr.total_rev_release - rr.rev_release
          )

其中，对于示例数据：

CREATE TABLE RELEASE (pid, transaction_date, release) AS
  SELECT 'P1', DATE '2023-05-01',  5 FROM DUAL UNION ALL
  SELECT 'P1', DATE '2023-05-03', 20 FROM DUAL UNION ALL
  SELECT 'P1', DATE '2023-05-06',  4 FROM DUAL UNION ALL
  SELECT 'P1', DATE '2023-05-07',  4 FROM DUAL;

CREATE TABLE REV_RELEASE (pid, transaction_date, rev_release) AS  
  SELECT 'P1', DATE '2023-05-05', 15 FROM DUAL UNION ALL
  SELECT 'P1', DATE '2023-05-06',  8 FROM DUAL UNION ALL
  SELECT 'P1', DATE '2023-05-07',  9 FROM DUAL;

输出：

PID的 REL_DATE RELREVDATE 释放金额 REV_RELEASE
小一 2023-05-01 00:00:00 2023-05-05 00:00:00 5 5
小一 2023-05-03 00:00:00 2023-05-05 00:00:00 20 10
小一 2023-05-03 00:00:00 2023-05-06 00:00:00 20 8
小一 2023-05-03 00:00:00 2023-05-07 00:00:00 20 2
小一 2023-05-06 00:00:00 2023-05-07 00:00:00 4 6
小一 2023-05-07 00:00:00 2023-05-07 00:00:00 4 3

小提琴

评论

0赞 Mani 11/14/2023
@MT0- 如果同一 PID 的版本是多个，则无法正常工作。如果我在 fiddle 中更新发布表，然后运行相同的发布表，则发布没有正确拆分。-------------------------- CREATE TABLE RELEASE （pid， transaction_date， release） AS SELECT 'P1'， DATE '2023-05-01'， 5 FROM DUAL UNION SELECT 'P1'， DATE '2023-05-03'， 20 FROM DUAL
1赞 d r 11/13/2023 #2

您可以将分析函数 Sum（） Over（） 与 windowing 子句 （...之间的行 ...获取运行总数。但是，您需要嵌套的 Case 表达式来处理REV_RELEASE列的结果，因为表中可能rev_release行更多行......

WITH    --  S a m p l e   D a t a :
    release AS 
       ( Select 'P1' "PID", Date '2023-05-01' "TRANSACTION_DATE", 20 "RELEASE"  From Dual 
       ),
    rev_release AS 
      ( Select 'P1' "PID", Date '2023-05-05' "TRANSACTION_DATE", 15 "REV_RELEASE" From Dual Union All
        Select 'P1' "PID", Date '2023-05-06' "TRANSACTION_DATE",  8 "REV_RELEASE" From Dual 
      )

--  M a i n    S Q L :
Select      r.PID, r.TRANSACTION_DATE "REL_DATE", rr.TRANSACTION_DATE "REV_REL_DATE", 
            r.RELEASE "REL_AMOUNT",
            CASE WHEN 
                  Case  When Sum(rr.REV_RELEASE) Over(Partition By r.PID Order By r.PID, rr.TRANSACTION_DATE
                                                    Rows Between Unbounded Preceding And Current Row) > r.RELEASE
                        Then rr.REV_RELEASE - ( Sum(rr.REV_RELEASE) Over(Partition By r.PID Order By r.PID, rr.TRANSACTION_DATE
                                                                       Rows Between Unbounded Preceding And Current Row) - r.RELEASE 
                                              )
                  Else rr.REV_RELEASE 
                  End < 0 Then 0 
            ELSE
                  Case  When Sum(rr.REV_RELEASE) Over(Partition By r.PID Order By r.PID, rr.TRANSACTION_DATE
                                                    Rows Between Unbounded Preceding And Current Row) > r.RELEASE
                        Then rr.REV_RELEASE - ( Sum(rr.REV_RELEASE) Over(Partition By r.PID Order By r.PID, rr.TRANSACTION_DATE
                                                                       Rows Between Unbounded Preceding And Current Row) - r.RELEASE 
                                              )
                  Else rr.REV_RELEASE 
                  End
            END "REV_RELEASE"
From        release r
Inner Join  rev_release rr ON(rr.PID = r.PID)

/*    R e s u l t :
PID REL_DATE  REV_REL_DATE REL_AMOUNT REV_RELEASE
--- --------- ------------ ---------- -----------
P1  01-MAY-23 05-MAY-23            20          15
P1  01-MAY-23 06-MAY-23            20           5  */

...在示例数据中多一点行

WITH    --  S a m p l e   D a t a   2 :
    release AS 
       ( Select 'P1' "PID", Date '2023-05-01' "TRANSACTION_DATE", 20 "RELEASE"  From Dual Union All
         Select 'P2' "PID", Date '2023-05-01' "TRANSACTION_DATE", 20 "RELEASE"  From Dual
       ),
    rev_release AS 
      ( Select 'P1' "PID", Date '2023-05-05' "TRANSACTION_DATE", 15 "REV_RELEASE" From Dual Union All
        Select 'P1' "PID", Date '2023-05-06' "TRANSACTION_DATE",  8 "REV_RELEASE" From Dual Union All
        Select 'P1' "PID", Date '2023-05-07' "TRANSACTION_DATE",  8 "REV_RELEASE" From Dual Union All
        --
        Select 'P2' "PID", Date '2023-05-05' "TRANSACTION_DATE", 15 "REV_RELEASE" From Dual Union All
        Select 'P2' "PID", Date '2023-05-06' "TRANSACTION_DATE",  4 "REV_RELEASE" From Dual 
      )

/*    R e s u l t   2 :
PID REL_DATE  REV_REL_DATE REL_AMOUNT REV_RELEASE
--- --------- ------------ ---------- -----------
P1  01-MAY-23 05-MAY-23            20          15
P1  01-MAY-23 06-MAY-23            20           5
P1  01-MAY-23 07-MAY-23            20           0
P2  01-MAY-23 05-MAY-23            20          15
P2  01-MAY-23 06-MAY-23            20           4   */

...如果需要，可以筛选出 REV_RELEASE = 0 的行，然后对生成的数据集的其余部分执行任何操作。
此外，如果您在发布表中有行，而表中没有匹配的行rev_release并且您想显示发布表行，那么只需在主 sql 中将内部联接更改为左联接即可。

上一个：如何在 Snowflake 中选择每 n 个日期？

下一个：从金额大于零的行中为每个客户分配行序列