时间戳

首页 > 解决方案 > 从基于事件的小时表中查看 Oracle 小时数据

oracle - 从基于事件的小时表中查看 Oracle 小时数据

问题描述

嗨，我有一个表，其中包含基于事件的表中多方之间的每小时 LEND/RETURN 交易，如下所示：

FROM_CODE|TO_CODE|DAYTIME|TRAN_REASON|QTY

A, B, 08-OCT-2019 02:00, LEND, 100
A, B, 07-NOV-2019 04:00, LEND, 200
A, C, 20-JAN-2020 13:00, LEND, 400
B, A, 05-MAR-2020 20:00, RETURN, 150
C, A, 09-MAR-2020 23:00, RETURN, 200
C, A, 08-APR-2020 02:00, RETURN, 100

解释：

问题：

我如何从表格中创建一个视图，当我传入婴儿车日期时，它会给我如下结果，如下面的示例所示：

  1. 如果参数日期 = 05-MAR-2020 21:00 View 将按照前一小时向我返回交易：
DAYTIME|FROM_CODE|TO_CODE|TRAN_REASON|QTY

05-MAR-2020 21:00, A, B, LEND, 300
05-MAR-2020 21:00, B, A, RETURN, 150
05-MAR-2020 21:00, A, C, LEND, 400
  1. 如果参数日期 = 10-MAR-2020 00:00
DAYTIME|FROM_CODE|TO_CODE|TRAN_REASON|QTY

10-MAR-2020 00:00, A, B, LEND, 300
10-MAR-2020 00:00, B, A, RETURN, 150
10-MAR-2020 00:00, A, C, LEND, 400
10-MAR-2020 00:00, C, A, RETURN, 200
  1. 如果参数日期 = 20-JUN-2020 00:00
DAYTIME|FROM_CODE|TO_CODE|TRAN_REASON|QTY

20-JUN-2020 00:00, A, B, LEND, 300  
20-JUN-2020 00:00, B, A, RETURN, 150 
20-JUN-2020 00:00, A, C, LEND, 400 
20-JUN-2020 00:00, C, A, RETURN, 300

笔记：

  1. 参数日期与表中的白天不完全匹配
  2. 结果将显示前一小时的交易状态

拥有每小时数据视图的原因是因为目前我们依赖于只能“读取”静态数据以进行报告的遗留系统，无法使用有效的 from/until 方法:(感谢任何可以帮助我的人的帮助，非常感谢！

标签： oracledatedatetimeview

解决方案

您无法创建将输入变量作为列值返回的视图，但您有 2 个选项来获得所需的内容：

a) 使用简单查询：:dt这是您输入的绑定变量

with t(FROM_CODE,TO_CODE,DAYTIME,TRAN_REASON,QTY) as (
select 'A', 'B', to_date('08-OCT-2019 02:00','dd-mon-yyyy hh24:mi'), 'LEND', 100   from dual union all
select 'A', 'B', to_date('07-NOV-2019 04:00','dd-mon-yyyy hh24:mi'), 'LEND', 200   from dual union all
select 'A', 'C', to_date('20-JAN-2020 13:00','dd-mon-yyyy hh24:mi'), 'LEND', 400   from dual union all
select 'B', 'A', to_date('05-MAR-2020 20:00','dd-mon-yyyy hh24:mi'), 'RETURN', 150 from dual union all
select 'C', 'A', to_date('09-MAR-2020 23:00','dd-mon-yyyy hh24:mi'), 'RETURN', 200 from dual union all
select 'C', 'A', to_date('08-APR-2020 02:00','dd-mon-yyyy hh24:mi'), 'RETURN', 100 from dual 
)
select
   FROM_CODE,
   TO_CODE,
   :dt, 
   TRAN_REASON,
   sum(qty)
from t
where daytime<=:dt
group by
   FROM_CODE,
   TO_CODE,
   TRAN_REASON

b）使用具有相同查询的流水线函数：完整示例：

create table test_data as
with t(FROM_CODE,TO_CODE,DAYTIME,TRAN_REASON,QTY) as (
   select 'A', 'B', to_date('08-OCT-2019 02:00','dd-mon-yyyy hh24:mi'), 'LEND', 100   from dual union all
   select 'A', 'B', to_date('07-NOV-2019 04:00','dd-mon-yyyy hh24:mi'), 'LEND', 200   from dual union all
   select 'A', 'C', to_date('20-JAN-2020 13:00','dd-mon-yyyy hh24:mi'), 'LEND', 400   from dual union all
   select 'B', 'A', to_date('05-MAR-2020 20:00','dd-mon-yyyy hh24:mi'), 'RETURN', 150 from dual union all
   select 'C', 'A', to_date('09-MAR-2020 23:00','dd-mon-yyyy hh24:mi'), 'RETURN', 200 from dual union all
   select 'C', 'A', to_date('08-APR-2020 02:00','dd-mon-yyyy hh24:mi'), 'RETURN', 100 from dual 
)
select *
from t
/
create or replace package pkg_test as
   type t_test_data_row is record (
      DAYTIME      test_data.DAYTIME%type,
      FROM_CODE    test_data.FROM_CODE%type,
      TO_CODE      test_data.TO_CODE%type,
      TRAN_REASON  test_data.TRAN_REASON%type,
      QTY          test_data.QTY%type
   );
   type t_test_data_tab is table of t_test_data_row;

  function get_output (dt date) return t_test_data_tab pipelined;
end;
/
create or replace package body pkg_test as

  function get_output (dt date) return t_test_data_tab pipelined
  is
  begin
     for r in (
         select
            get_output.dt,
            FROM_CODE,
            TO_CODE,
            TRAN_REASON,
            sum(qty) as qty
         from test_data
         where daytime<=get_output.dt
         group by
            FROM_CODE,
            TO_CODE,
            TRAN_REASON
     )
     loop
        pipe row (r);
     end loop;
  end;
end;
/
select * 
from table(pkg_test.get_output(to_date('2020-03-05 21:00','yyyy-mm-dd hh24:mi')));

结果：

DAYTIME             F T TRAN_R        QTY
------------------- - - ------ ----------
2020-03-05 21:00:00 A B LEND          300
2020-03-05 21:00:00 B A RETURN        150
2020-03-05 21:00:00 A C LEND          400

推荐阅读