Bob's questions

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

 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

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

我正在尝试使用更新来计算权重。在特定情况下，我需要应用分组，因为负担需要按客户端 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)

我正在尝试使用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)
    );

是否可以通过循环的一次迭代从表中获得以下结果：每次从每个人 cd_number 中获取一个数字及其日期。我尝试使用循环，但无法按照我需要的方式迭代它。SQL问题详细除了使用循环之外还有其他方法来检索特定结果吗？

table result; 
             cd_number  date_begin  date_end
                 1        03-APR-17   03-JUN-19
                 1        25-FEB-19   03-JUN-20
                 1        13-MAR-20   30-JUN-23
                 2        02-NOV-17   30-APR-18
                 2        25-FEB-19   11-OCT-19
                 3        31-DEC-16   30-OCT-17
                 3        21-OCT-21   16-FEB-22
                 3        19-OCT-22   30-JUN-23

           
             Example table with results
             CREATE TABLE tbl28 (cd_Number int, date_begin date, date_end date );

  insert into tbl28 (cd_Number,date_begin,date_end)
  SELECT 1,  '03-APR-17', '03-JUN-19'   FROM DUAL UNION ALL
  SELECT 1,  '25-FEB-19', '03-JUN-19'   FROM DUAL UNION ALL
  SELECT 1,  '13-MAR-20', '30-JUN-23'   FROM DUAL UNION ALL
  SELECT 2,  '02-NOV-17', '30-APR-18'   FROM DUAL UNION ALL
  SELECT 2,  '25-FEB-19', '11-OCT-19'   FROM DUAL UNION ALL
  SELECT 3,  '31-DEC-16', '30-OCT-17'   FROM DUAL UNION ALL
  SELECT 3,  '21-OCT-21', '16-FEB-22'   FROM DUAL UNION ALL
  SELECT 3,  '19-OCT-22', '30-JUN-23'   FROM DUAL;
 
      expected result;
   first iteration    1       03-APR-17   03-JUN-19
                      2       02-NOV-17   30-APR-18
                      3       31-DEC-16   30-OCT-17
   second iteration  
                      1       25-FEB-19   03-JUN-20   
                      2       25-FEB-19   11-OCT-19 
                      3       21-OCT-21   16-FEB-2 2
 and in the third iteration 
                      1       13-MAR-20   30-JUN-23
                      3       19-OCT-22   30-JUN-23   

数据库中有以下结构。l_family_id 始终作为参数返回，但 l_account 和 l_product_id 每次 2 之一为 null。如何克服这个问题，以便我在选择“可搜索帐户”或“产品 ID”时不会出现错误。

              such as

            select product_id from tbl27 
                where family_id = l_family_id
                and account  = null
                and product_id = l_product_id
                 
               and   
                  
            select product_id from tbl27 
                where family_id = l_family_id
                and account  = l_product_id
                and product_id = null 


             
            family_id       family   account    product_id

               1            family1    101      10
               1            family1    101      20
               1            family1    103      30
               1            family1    103      40
               2            family2    201      110
               2            family2    201      120
               2            family2    204      130
               2            family2    204      140
            




 CREATE TABLE tbl27 (family_id int, family VARCHAR2(30), account int, product_id 
    int);

      insert into tbl27 (family_id,family,account, product_id)
      SELECT 1,  'family1', 101, 10    FROM DUAL UNION ALL
      SELECT 1,  'family1', 101, 20    FROM DUAL UNION ALL
      SELECT 1,  'family1', 103, 30    FROM DUAL UNION ALL
      SELECT 1,  'family1', 103, 40    FROM DUAL UNION ALL
      SELECT 2,  'family2', 201, 110   FROM DUAL UNION ALL
      SELECT 2,  'family2', 201, 120   FROM DUAL UNION ALL
      SELECT 2,  'family2', 204, 130   FROM DUAL UNION ALL
      SELECT 2,  'family2', 204, 140   FROM DUAL;


        Expected result;

            select product_id from tbl27 
            where family_id = 1
            and account  = 101
            and product_id = null 

            product_id
                10
                20

   
                 
            

           

是否可以从一列中获取结果并将其转移到两列中。以第一个和第二个结果作为开始和结束，其他人都以同样的方式。我使用了pivot但没有得到任何实际结果。

当前结果

 rn old_date_row 

 1   01-JUN-18
 2   null 
 3   null 
 4   null
 5   22-JUN-18
 6   null
 7   null
 8   null
 9   25-JUN-19
 10  null
 11  null
 12  25-JUN-20

预期结果新表

    start_date      end_date
   01-JUN-18       22-JUN-18    
   25-JUN-19       25-JUN-20