代码之家 › 专栏 › 技术社区 › Jeffrey Kemp

数据定义的Oracle分析函数窗口

analytic-functions oracle9i oracle sql

2

Jeffrey Kemp · 技术社区 · 14 年前

我有一个表,它表示从特定文本文件格式读取的数据的逐行转储。每行可代表“主”行或“细节”行,通过 rec_type 代码。我想写一个查询,在相关细节行旁边获取“主控”行。我想出了一个能完成这项工作的方法,但它似乎有点老土,我对更好的方法感兴趣,如果有的话。

CREATE TABLE mdtest
 (rec_seq  NUMBER        PRIMARY KEY
 ,rec_type VARCHAR2(3)   NOT NULL
 ,rec_data VARCHAR2(100) NOT NULL);

INSERT INTO mdtest VALUES (1, '100', 'Bill Jones');
INSERT INTO mdtest VALUES (2, '200', '20080115,100.25');
INSERT INTO mdtest VALUES (3, '100', 'John Smith');
INSERT INTO mdtest VALUES (4, '200', '20090701,80.95');
INSERT INTO mdtest VALUES (5, '200', '20091231,110.35');

期望结果:

SEQ_EMP  EMP_NAME    SEQ_DATA  EMP_DATA
=======  ==========  ========  ===============
      1  Bill Jones         2  20080115,100.25
      3  John Smith         4  20090701,80.95
      3  John Smith         5  20091231,110.35

假设:

记录按记录顺序处理
第一个记录类型是“A” 100 “
每一个 一百 “记录有1个或多个” 200 “以下记录

注意:这是针对Oracle9i的,但是我们今年应该升级到11gR1。

2 回复 | 直到 14 年前

1

2

Jeffrey Kemp 14 年前

以下是我目前为止的情况:

SELECT seq_emp 
      ,SUBSTR(emp_seq_name,10) emp_name 
      ,seq_data 
      ,emp_data 
FROM  (SELECT MAX(CASE WHEN rec_type = '100' THEN rec_seq END) 
              OVER (ORDER BY rec_seq 
                    ROWS BETWEEN UNBOUNDED PRECEDING 
                             AND CURRENT ROW) seq_emp 
             ,MAX(CASE 
                  WHEN rec_type = '100' 
                  THEN TO_CHAR(rec_seq,'fm00000000') || '|' || rec_data 
                  END) 
              OVER (ORDER BY rec_seq 
                    ROWS BETWEEN UNBOUNDED PRECEDING 
                             AND CURRENT ROW) emp_seq_name 
             ,rec_seq seq_data 
             ,rec_type 
             ,rec_data emp_data 
       FROM   mdtest) 
WHERE  rec_type = '200' 
ORDER BY seq_data;

如您所见,我使用的是max reporting分析函数,它有一个窗口,从集合的顶部向下到当前行,以获取当前“200”记录的相关“100”记录;然后在外部查询中,我将丢弃不需要的“100”记录。

为了得到emp_name,我必须将rec_seq与数据附加在一起,以便max函数仍然选择正确的头记录;然后在外部查询中,将rec_seq切掉。

我使用过其他分析函数和语法,包括first_value和keep语法,但这两种语法都不能简化这项工作;困难在于窗口是由rec_type的值定义的,而不是一些常量偏移量。

2

1

Nick Pierpoint 14 年前

为了简单起见,您认为是否值得在处理之前将每个记录类型加载到单独的导入表中?

create table mdtest100 as select * from mdtest where rec_type = 100;

create table mdtest200 as select * from mdtest where rec_type = 200;

with mdtest_detail as
    (
    select
        (select max(m.rec_seq) from mdtest100 m 
         where m.rec_seq < r200.rec_seq) master_rec_seq,
        r200.* 
    from 
        mdtest200 r200
    )
select
    m.rec_seq seq_emp,
    m.rec_data emp_name,
    d.rec_seq seq_data,
    d.rec_data emp_data
from
    mdtest_detail d
        inner join mdtest100 m on m.rec_seq = d.master_rec_seq
order by
    seq_emp,
    seq_data;


    SEQ_EMP  EMP_NAME    SEQ_DATA   EMP_DATA          
    1        Bill Jones  2          20080115,100.25          
    3        John Smith  4          20090701,80.95          
    3        John Smith  5          20091231,110.35

这可能适合于更易于维护的解决方案,并允许您分别解析和验证comm-separated emp_数据字段。

只是一个想法-如果你只是在寻找一个分析解决方案,那么很抱歉。