主页 / coding / 问题

问题[oracle19c](coding)

Martin Hope
Bob
Asked: 2025-04-07 14:57:10 +0800 CST
  • 5

我需要根据账户和交易日期对结果进行汇总。我得到以下结果：

 ACCOUNT_ID     TRANSACTION_DATE    AMOUNT
  581            05-SEP-23         309.32
  581            08-SEP-23         1863.76
  581            15-SEP-23         0.26
  581            21-SEP-23         23.17

with transaction_tbl 
 AS (

   select  581 ACCOUNT_ID,  TO_DATE('05-09-2023', 'DD-MM-YYYY') TRANSACTION_DATE,    309.32 AMOUNT from dual
union all
select  581 ACCOUNT_ID,  TO_DATE('08-09-2023', 'DD-MM-YYYY') TRANSACTION_DATE,    1863.76 AMOUNT from dual
union all
select  581 ACCOUNT_ID,  TO_DATE('15-09-2023', 'DD-MM-YYYY') TRANSACTION_DATE,    0.26 AMOUNT from dual
union all
select  581 ACCOUNT_ID,  TO_DATE('21-09-2023', 'DD-MM-YYYY') TRANSACTION_DATE,    23.17 AMOUNT from dual
)
SELECT
    ACCOUNT_ID,TRANSACTION_DATE,AMOUNT
FROM
    transaction_tbl
    --where TRANSACTION_DATE < asofdate ;

我有以下无法处理的案例。

当 TRANSACTION_DATE < asofdate TO_DATE('06-09-2023', 'DD-MM-YYYY') 预期结果时的情况；

581 05-SEP-23   309.32

当 TRANSACTION_DATE < asofdate TO_DATE('09-09-2023', 'DD-MM-YYYY') 预期结果时的情况；

581 08-SEP-23   2173.08

当 TRANSACTION_DATE < asofdate TO_DATE('18-09-2023', 'DD-MM-YYYY') 预期结果时的情况；

581 15-SEP-23   2173.34

当 TRANSACTION_DATE < asofdate TO_DATE('22-09-2023', 'DD-MM-YYYY') 预期结果时的情况；

581 21-SEP-23   2196.51

我的想法是，我需要根据作为交易存在的日期的先前数据重新计算结果。

oracle19c
Martin Hope
Bob
Asked: 2024-10-18 01:55:35 +0800 CST
  • 5

我正在尝试使用更新来计算权重。在特定情况下，我需要应用分组，因为负担需要按客户端 ID 分配。但这里的分组无法正常工作。必须使用更新

结果

在此处输入图片描述

预期结果：预期结果是根据客户ID和持股量按权重分配0.14/0.14/1/0.71

    CREATE TABLE VALUE_TBL 
     ( CLIEND_ID NUMBER(10),
       VALUE NUMBER,
       WEIGHT NUMBER,
       HOLDINGS VARCHAR(50)
     );

insert into value_tbl1(cliend_id,value,WEIGHT,HOLDINGS) values(1,10,33.33,'GOOGLE');
insert into value_tbl1(cliend_id,value,WEIGHT,HOLDINGS) values(1,10,33.33,'AAPL');
insert into value_tbl1(cliend_id,value,WEIGHT,HOLDINGS) values(2,30,100,'ABB');
insert into value_tbl1(cliend_id,value,WEIGHT,HOLDINGS) values(1,50,33.33,'TESLA');

我尝试了以下更新，但没有成功

  update value_tbl y
  set weight = value / 
  (select sum(value) 
  from value_tbl x 
  group by x.cliend_id)
oracle19c
Martin Hope
Bob
Asked: 2024-09-29 21:31:51 +0800 CST
  • 5

我正在尝试使用DBMS_JOB.SUBMITINSERT 语句来实现该过程，但无法成功地将数据从一个表传输到另一个表。

DECLARE
    l_job NUMBER;
 BEGIN
    DBMS_JOB.SUBMIT(
       job       => l_job,
       what      => 'BEGIN insert into customers_tbl1 select * from customers_tbl; 
 END;'--,
     --  next_date => SYSDATE--, -- Schedule for 1 hour from now
       --interval  => 'SYSDATE'   -- Reschedule every day
    );
   COMMIT; -- Important to commit the transaction to schedule the job
 END;

表格数据：

    CREATE TABLE customers_tbl 
    ( customer_id number(10),
      customer_name varchar2(50),
      city varchar2(50)
    );
    insert into customers_tbl(customer_id,customer_name,city) values(1,'Albert','Munich');
    insert into customers_tbl(customer_id,customer_name,city) values(2,'Alex','Stuttgart');
    insert into customers_tbl(customer_id,customer_name,city) values(3,'Sasuke','Tokyo');
    
    CREATE TABLE customers_tbl1 
    ( customer_id number(10),
      customer_name varchar2(50),
      city varchar2(50)
    );
oracle19c