将行拆分为余额行||Oracle SQL

pn9klfpd 于 2023-06-22 发布在 Oracle

关注(0)|答案(3)|浏览(175)

我有一张table：xxtb_je_txn

CREATE TABLE XXTB_JE_TXN 
( "ENTITY" VARCHAR2(10 BYTE), 
"JE_HEADER_ID" VARCHAR2(1000 BYTE), 
"NET_CR" NUMBER, 
"NET_DR" NUMBER
);

Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('401','10101',0,30);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('302','10101',0,20);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('402','10101',0,50);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('301','10101',100,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('402','10102',50,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('301','10102',0,100);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('401','10102',30,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('302','10102',20,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('102','10103',0,400.44);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('101','10103',992.57,325.17);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('201','10103',0,266.96);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('102','10105',62.5,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('201','10105',0,17291);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('101','10105',17228.5,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('204','10104',200,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('101','10104',0,200);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('301','10106',70,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('302','10106',30,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('401','10106',0,60);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('402','10106',0,40);

它看起来像这样：
| 实体|JE_HEADER_ID| NET_CR| NET_DR|
| - -----|- -----|- -----|- -----|
| 四零一|10101| 0|三十|
| 三百零二|10101| 0|二十个|
| 四零二|10101| 0|五十|
| 三百零一|10101|一百|0|
| 四零二|10102|五十|0|
| 三百零一|10102| 0|一百|
| 四零一|10102|三十|0|
| 三百零二|10102|二十个|0|
| 一百零二|10103| 0|四百零四|
| 一百零一|10103|九百九十二点五七|三百二十五点一七|
| 二百零一|10103| 0|二百六十六点九六|
| 一百零二|10105|六十二点五|0|
| 二百零一|10105| 0| 17291|
| 一百零一|10105| 17228.5| 0|
| 二百零四|10104|两百|0|
| 一百零一|10104| 0|两百|
| 三百零一|10106|七十|0|
| 三百零二|10106|三十|0|
| 四零一|10106| 0|六十|
| 四零二|10106| 0|四十|
我想拆分行以创建余额条目，如：
| 实体|JE_HEADER_ID| NET_CR| NET_DR|
| - -----|- -----|- -----|- -----|
| 四零一|10101| 0|三十|
| 三百零二|10101| 0|二十个|
| 四零二|10101| 0|五十|
| 三百零一|10101|三十|0|
| 三百零一|10101|二十个|0|
| 三百零一|10101|五十|0|
| 四零二|10102|五十|0|
| 三百零一|10102| 0|五十|
| 三百零一|10102| 0|三十|
| 三百零一|10102| 0|二十个|
| 四零一|10102|三十|0|
| 三百零二|10102|二十个|0|
| 一百零二|10103| 0|四百零四|
| 一百零一|10103|四百零四|0|
| 一百零一|10103|二百六十六点九六|0|
| 二百零一|10103| 0|二百六十六点九六|
| 一百零二|10105|六十二点五|0|
| 二百零一|10105| 0| 17228.5|
| 二百零一|10105| 0|六十二点五|
| 一百零一|10105| 17228.5| 0|
| 二百零四|10104|两百|0|
| 一百零一|10104| 0|两百|
对于JE_HEADER_ID = 10106的行，实体之间没有net_cr和net_dr的明确关联。所以query应该忽略这些行并像这样标记它们。
| 实体|JE_HEADER_ID| NET_CR| NET_DR|错误|
| - -----|- -----|- -----|- -----|- -----|
| 三百零一|10106|七十|0|拒收|
| 三百零二|10106|三十|0|拒收|
| 四零一|10106| 0|六十|拒收|
| 四零二|10106| 0|四十|拒收|
如何做到这一点？
@Serg提供的查询很有帮助，但它不包括JE_HEADER_ID为10104的行，因为我在最初的问题中没有提到这些行。这个解决方案对最初的问题很有效。因此，我用所有必要的假设重新表述了这个问题（两组新的行，JE_HEADER_ID为10104和10106）。

with t as (
select ENTITY,JE_HEADER_ID, greatest(0, NET_CR-NET_DR)NET_CR, greatest(0, NET_DR-NET_CR) NET_DR
  , sum(greatest(0, NET_CR-NET_DR)) over(partition by JE_HEADER_ID) tot
from XXTB_JE_TXN 
)
select a.*
from t
cross join lateral (
  select t.ENTITY,t.JE_HEADER_ID,t2.NET_CR,t2.NET_DR
  from t t2
  where t2.JE_HEADER_ID = t.JE_HEADER_ID and t2.tot != t2.NET_CR and t2.tot != t2.NET_DR
) a
where t.tot = t.NET_CR or t.tot = t.NET_DR
  
union all
  
select ENTITY,JE_HEADER_ID,NET_DR,NET_CR
from t
where t.tot != t.NET_CR and t.tot != t.NET_DR
order by 2, 1;

oracle

来源：https://stackoverflow.com/questions/76502072/splitting-rows-to-balance-lines-oracle-sql

3条答案

按热度按时间

dba5bblo1#

您可以用途：

SELECT entity,
       je_header_id,
       SUM(net_cr) AS net_cr,
       SUM(net_dr) AS net_dr,
       MAX(note) AS note
FROM   (
  SELECT entity,
         je_header_id,
         CASE WHEN type = 'CR' THEN amount END AS net_cr,
         CASE WHEN type = 'DR' THEN amount END AS net_dr,
         CASE WHEN min_group_size = 1 THEN 'ACCEPT' ELSE 'REJECT' END AS note
  FROM   (
    SELECT je_header_id,
           COALESCE(
             e_cr,
             LEAD(e_cr) IGNORE NULLS OVER (
               PARTITION BY je_header_id
               ORDER BY total, amount
             )
           ) AS e_cr,
           COALESCE(
             e_dr,
             LEAD(e_dr) IGNORE NULLS OVER (
               PARTITION BY je_header_id
               ORDER BY total, amount
             )
           ) AS e_dr,
           LEAST(
             COUNT(net_cr) OVER (PARTITION BY je_header_id),
             COUNT(net_dr) OVER (PARTITION BY je_header_id)
           ) AS min_group_size,
           total - LAG(total, 1, 0) OVER (
               PARTITION BY je_header_id
               ORDER BY total, amount
           ) AS amount
    FROM   (
      SELECT je_header_id,
             ABS(net_cr - net_dr) AS amount,
             CASE
             WHEN net_cr > net_dr
             THEN net_cr - net_dr
             END AS net_cr,
             CASE
             WHEN net_dr > net_cr
             THEN net_dr - net_cr
             END AS net_dr,
             CASE WHEN net_cr > net_dr THEN entity END AS e_cr,
             CASE WHEN net_dr > net_cr THEN entity END AS e_dr,
             SUM(ABS(net_cr - net_dr)) OVER (
               PARTITION BY je_header_id, SIGN(net_cr - net_dr)
               ORDER BY ABS(net_cr - net_dr)
             ) AS total
      FROM   xxtb_je_txn
    )
  )
  UNPIVOT (
    entity FOR type IN (
      e_cr AS 'CR',
      e_dr AS 'DR'
    )
  )
  WHERE  amount > 0
)
GROUP BY
       je_header_id,
       entity,
       CASE note WHEN 'REJECT' THEN NULL ELSE ROWNUM END

对于您的示例数据，输出：
| 实体|JE_HEADER_ID| NET_CR| NET_DR|注意事项|
| - -----|- -----|- -----|- -----|- -----|
| 三百零一|10101|二十个|联系我们|接受|
| 三百零二|10101|联系我们|二十个|接受|
| 三百零一|10101|三十|联系我们|接受|
| 四零一|10101|联系我们|三十|接受|
| 三百零一|10101|五十|联系我们|接受|
| 四零二|10101|联系我们|五十|接受|
| 三百零二|10102|二十个|联系我们|接受|
| 三百零一|10102|联系我们|二十个|接受|
| 四零一|10102|三十|联系我们|接受|
| 三百零一|10102|联系我们|三十|接受|
| 四零二|10102|五十|联系我们|接受|
| 三百零一|10102|联系我们|五十|接受|
| 一百零一|10103|二百六十六点九六|联系我们|接受|
| 二百零一|10103|联系我们|二百六十六点九六|接受|
| 一百零一|10103|四百零四|联系我们|接受|
| 一百零二|10103|联系我们|四百零四|接受|
| 二百零四|10104|两百|联系我们|接受|
| 一百零一|10104|联系我们|两百|接受|
| 一百零二|10105|六十二点五|联系我们|接受|
| 二百零一|10105|联系我们|六十二点五|接受|
| 一百零一|10105| 17228.5|联系我们|接受|
| 二百零一|10105|联系我们|17228.5|接受|
| 三百零二|10106|三十|联系我们|拒收|
| 四零二|10106|联系我们|四十|拒收|
| 三百零一|10106|七十|联系我们|拒收|
| 四零一|10106|联系我们|六十|拒收|
fiddle

赞(0）回复(0）举报 2023-06-22

g2ieeal72#

我的尝试。测试你的输入，它显示正确的结果。它类似于匹配交易的查询，除了用于 rejected 行的额外逻辑：
dbfiddle demo

with t as (
  select ENTITY ent, JE_HEADER_ID id,          
         greatest(NET_CR-NET_DR, 0) cr, greatest(NET_DR-NET_CR, 0) dr,
         sum(greatest(NET_CR-NET_DR, 0)) 
           over (partition by JE_HEADER_ID order by NET_CR, rowid) scr, 
         sum(greatest(NET_DR-NET_CR, 0)) 
           over (partition by JE_HEADER_ID order by NET_DR, rowid) sdr
  from XXTB_JE_TXN),
c as (select ent, id, cr, scr-cr c1, scr c2 from t where cr > 0),
d as (select ent, id, dr, sdr-dr d1, sdr d2 from t where dr > 0),
m as (
  select cent, dent, id, cr, dr, val, cc, cd,
         max(case when cc > 1 and cd > 1 then 'rejected' end) 
             over (partition by id) status
  from (
    select c.ent cent, d.ent dent, c.id, cr, dr, --c1, c2, d1, d2,
           least(cr, c2-d1, dr, d2-c1) val,
           count(1) over (partition by c.id, c.ent) cc, 
           count(1) over (partition by c.id, d.ent) cd
    from c join d on c.id = d.id and c1 < d2 and d1 < c2) )
select t.ent, t.id, case status when 'rejected' then t.cr else m.val end cr, 
       0 dr, m.status 
from t join m on t.id = m.id and t.ent = m.cent 
where t.cr >= 0 and (cc < 2 or cd < 2)
union all
select t.ent, t.id, 0 cr, 
       case status when 'rejected' then t.dr else m.val end dr, m.status 
from t join m on t.id = m.id and t.ent = m.dent 
where t.dr >= 0 and (cc < 2 or cd < 2)
order by id, cr, dr

赞(0）回复(0）举报 2023-06-22

pcrecxhr3#

其中一个选择是使用MODEL Clause，它非常有用，可靠和快速。它的工作原理很像Excel工作表。因此，当您需要处理不同的行或列集或数据集中的单个单元格时，它可能是一个很好的选择。有了提供的数据，我创建了一个cte（网格）来准备数据集。

（代码在下面的注解后调整）*

WITH grid AS
    (   Select    JE_HEADER_ID, ENTITY, NET_CR, NET_DR,
                  GREATEST(Sum(NET_CR) Over(Partition  By JE_HEADER_ID),
                  Sum(NET_DR) Over(Partition  By JE_HEADER_ID)) "TOTAL",
                  Case When Count(*) Over(Partition  By JE_HEADER_ID) = 2 Then 'TXN'
                       When NET_CR = Sum(NET_CR) Over(Partition  By JE_HEADER_ID) And NET_DR != 0 Then 'TOT/TXN'
                       When NET_CR = Sum(NET_CR) Over(Partition  By JE_HEADER_ID) And NET_DR = 0  Then 'TOT'
                       When NET_DR = Sum(NET_DR) Over(Partition  By JE_HEADER_ID) And NET_CR != 0 Then 'TOT/TXN'
                       When NET_DR = Sum(NET_DR) Over(Partition  By JE_HEADER_ID) And NET_CR = 0  Then 'TOT' 
                  Else 'TXN'
                  End "AMOUNT_TYPE",
                  Row_Number() Over(Partition  By JE_HEADER_ID Order By JE_HEADER_ID, ENTITY) "RN",
                  Count(*) Over(Partition  By JE_HEADER_ID) "MAX_RN"
        From      XXTB_JE_TXN
        Order By  JE_HEADER_ID, ENTITY 
    )

...然后使用MODEL Clause得到结果...

--  M a i n   S Q L
SELECT  ENTITY, JE_HEADER_ID, NET_CR, NET_DR, ACCEPTED
FROM    (  Select  JE_HEADER_ID, IDX, RN, MAX_RN, AMOUNT_TYPE, ENTITY, Nvl(NET_CR, 0) "NET_CR", Nvl(NET_DR, 0) "NET_DR", Nvl(TOTAL, 0) "TOTAL", ACCEPTED
            From (  Select    0 "IDX", JE_HEADER_ID, RN, MAX_RN, ENTITY, NET_CR, NET_DR, TOTAL, AMOUNT_TYPE, 'YES' "ACCEPTED"
                    From      grid  
                 )
            MODEL     Partition By   (JE_HEADER_ID)
                      Dimension By  (IDX, RN, AMOUNT_TYPE)
                      Measures       (ENTITY, NET_CR, NET_DR, TOTAL, ACCEPTED, MAX_RN)
            RULES   ITERATE(6)
              (   NET_DR[1, ITERATION_NUMBER + 1, 'TXN'] =  CASE WHEN NET_CR[0, CV(RN), 'TXN'] != 0 THEN NET_CR[0, CV(RN), CV()] ELSE 0 END, 
                  --
                  NET_CR[1, ITERATION_NUMBER + 1, 'TXN'] =  CASE WHEN NET_DR[0, CV(RN), 'TXN'] != 0 THEN NET_DR[0, CV(RN), CV()] ELSE 0 END,
                  --
                  ACCEPTED[ANY, ANY, ANY] =  CASE   WHEN MAX_RN[CV(), CV(), CV()] = 2 And ( NET_CR[0, 1, 'TXN'] = NET_DR[0, 2, 'TXN'] OR NET_DR[0, 1, 'TXN'] = NET_CR[0, 2, 'TXN'] ) THEN 'YES'
                                                    WHEN Max(TOTAL)[IDX = 0, RN > 0, AMOUNT_TYPE Like '%TOT%'] > 0 Then 'YES' 
                                            ELSE 'NO' 
                                            END,
                  ENTITY[1, ANY, 'TXN'] =  ENTITY[0, CV(RN), 'TXN']
              )
        )
WHERE      Nvl(NET_CR, 0) + Nvl(NET_DR, 0) != 0  And AMOUNT_TYPE != 'TOT' And 
           TOTAL != CASE WHEN ACCEPTED = 'NO' THEN 0 ELSE TOTAL + 1 END And
           (  (NET_CR = 0 And TOTAL IN(0, TOTAL)) OR (NET_DR = 0 And TOTAL IN(0, TOTAL))  )
ORDER BY JE_HEADER_ID, RN, IDX

--      R e s u l t :
--  
--  ENTITY  JE_HEADER_ID  NET_CR  NET_DR  ACCEPTED
--  ------  ------------ ------- -------  --------
--  302     10101                0      20  YES
--  302     10101               20       0  YES
--  401     10101                0      30  YES
--  401     10101               30       0  YES
--  402     10101                0      50  YES
--  402     10101               50       0  YES
--  302     10102               20       0  YES
--  302     10102                0      20  YES
--  401     10102               30       0  YES
--  401     10102                0      30  YES
--  402     10102               50       0  YES
--  402     10102                0      50  YES
--  102     10103                0  400.44  YES
--  102     10103           400.44       0  YES
--  201     10103                0  266.96  YES
--  201     10103           266.96       0  YES
--  101     10104                0     200  YES
--  204     10104              200       0  YES
--  101     10105          17228.5       0  YES
--  101     10105                0 17228.5  YES
--  102     10105             62.5       0  YES
--  102     10105                0    62.5  YES
--  301     10106               70       0  NO
--  302     10106               30       0  NO
--  401     10106                0      60  NO
--  402     10106                0      40  NO

赞(0）回复(0）举报 2023-06-22

我来回答

将行拆分为余额行||Oracle SQL

3条答案

相关问题

热门标签

最新问答