Oracle -格式和结构数据/查询

Question

这是我对SQL (Oracle)的第一周挑战。我以前从未真正使用过SQL。只会尝试理解查询。目前，我试图通过服务器端的SQL (Oracle)外包一个查询，到目前为止，我已经在Excel (power query)中对其进行了转换，以获得相应的性能改进。这意味着，到目前为止，我已经在power查询中实现了步骤，试图使用SQL语句来实现它们。我已经在这里问了几个问题，也从你那里得到了很多帮助和好主意。我认为最有意义的是处理原始表或数据库的一个question.The结构中的所有内容，如下所示：

Name =值示例

• JDDate = 118001
• 列表项目
• START = 63000 ( 06:30:00)
• 完= 240000 (24:00)
• NR = 34000001
• 终端= MM01X11
• Dep = XX01
• 医生= 1000800001
• Typ =1
• Key1 = 99000000
• Key2 = 99000000
• 首先，我需要所有带有"Typ“=1和JDDate =>118000的值

其中"JDDate“>118000和"Typ”=1

• 然后，我需要基于正确的JDDate/格式的开始和结束之间的差异/时间步骤。不幸的是，这里有一些基于JDDate、START、END、Terminal的副本。

。

cte (staff_nr, start_datetime, end_datetime) as (
  select distinct ltrim(substr("NR", -6), '0'),
      date '1900-01-01'
        + floor("JDDate" / 1000) * interval '1' year
        + (mod("JDDate", 1000) -1) * interval '1' day
        + floor("START" / 10000) * interval '1' hour
        + floor(mod("START", 10000) / 100) * interval '1' minute
        + mod("START", 100) * interval '1' second,
      date '1900-01-01'
        + floor("JDDate" / 1000) * interval '1' year
        + (mod("JDDate", 1000) -1) * interval '1' day
        + case when "END" = 240000 then interval '1' day
               else floor("END" / 10000) * interval '1' hour
                  + floor(mod("END", 10000) / 100) * interval '1' minute
                  + mod("END", 100) * interval '1' second
          end
FROM "POOL0101"."9909KK"
WHERE   "JDDate" >118000
    AND "Typ" = 1

)
select staff_nr,
  to_char(start_datetime, 'YYYY-MM-DD HH24:MI:SS') as end_datetime,
  to_char(end_datetime, 'YYYY-MM-DD HH24:MI:SS') as end_datetime,
  end_datetime - start_datetime as diff_in_days
from cte

• 至少我在Key1和Key2上有一些过时的数据/副本。所以Key1每次都包含一个数字。Key2包含0或数字，这也是Key1中的数字。如果在Key1和Key2中是相同的数字，那么这两行都应该是删除。

有人能帮我把所有这些元素都放到SQL上吗？

有没有办法给你展示一个典型的数据库？

向约书亚问好

Answer 1

有没有办法给你展示一个典型的数据库？

您可以使用dbfiddle.uk创建测试表和查询。假设您有以下测试表(称为原始表)和数据：

表格

create table original (
  jddate number
, starttime timestamp
, endtime timestamp
, nr number
, terminal varchar2( 100 )
, dep varchar2( 100 )
, doc number
, typ number
, key1 number
, key2 number 
) ;

镶片

insert into original
select 118001
, trunc( sysdate ) + ( 6/24 ) + ( 30/(24*60 ) )
, trunc( sysdate ) + (86399/86400)
, 34000001, 'MM01X11', 'XX01', 1000800001, 1, 99000000, 99000000  from dual union all 
-- duplicate
select 118001
, trunc( sysdate ) + ( 6/24 ) + ( 30/(24*60 ) )
, trunc( sysdate ) + (86399/86400)
, 34000001, 'MM01X11', 'XX01', 1000800001, 1, 99000000, 99111111  from dual union all 
select 118001
, trunc( sysdate ) + ( 6/24 ) + ( 30/(24*60 ) )
, trunc( sysdate ) + (86399/86400)
, 34000001, 'MM01X11', 'XX01', 1000800001, 1, 99000000, 99111111  from dual union all 
--
select 118001
, trunc( sysdate ) + ( 6/24 ) + ( 30/(24*60 ) )
, trunc( sysdate ) + (86399/86400)
, 34000001, 'MM01X11', 'XX01', 1000800001, 2, 99000000, 99000000  from dual union all 
select 118001
, trunc( sysdate ) + ( 6/24 ) + ( 30/(24*60 ) )
, trunc( sysdate ) + (86399/86400)
, 34000001, 'MM01X11', 'XX01', 1000800001, 3, 99000000, 99000000  from dual union all 
select 118001
, trunc( sysdate ) + ( 6/24 ) + ( 30/(24*60 ) )
, trunc( sysdate ) + (86399/86400)
, 34000001, 'MM01X11', 'XX01', 1000800001, 4, 99000000, 99000000  from dual ;

选择

-- select * from original;    
JDDATE  STARTTIME                     ENDTIME                       NR        TERMINAL  DEP   DOC         TYP  KEY1      KEY2      
    118001  15-DEC-18 06.30.00.000000000  15-DEC-18 23.59.59.000000000  34000001  MM01X11   XX01  1000800001  1    99000000  99000000  
    118001  15-DEC-18 06.30.00.000000000  15-DEC-18 23.59.59.000000000  34000001  MM01X11   XX01  1000800001  1    99000000  99111111  
    118001  15-DEC-18 06.30.00.000000000  15-DEC-18 23.59.59.000000000  34000001  MM01X11   XX01  1000800001  1    99000000  99111111  
    118001  15-DEC-18 06.30.00.000000000  15-DEC-18 23.59.59.000000000  34000001  MM01X11   XX01  1000800001  2    99000000  99000000  
    118001  15-DEC-18 06.30.00.000000000  15-DEC-18 23.59.59.000000000  34000001  MM01X11   XX01  1000800001  3    99000000  99000000  
    118001  15-DEC-18 06.30.00.000000000  15-DEC-18 23.59.59.000000000  34000001  MM01X11   XX01  1000800001  4    99000000  99000000  

要求

{1}首先我需要所有带有"Typ“=1和JDDate =>118000的值 {2}那么，我需要基于正确的JDDate/格式的开始和结束之间的差异/时间步骤。不幸的是，这里有一些基于JDDate、START、END、Terminal的副本。 {3}，至少我在Key1和Key2上有一些过时的数据/副本。所以Key1每次都包含一个数字。Key2包含0或数字，这也是Key1中的数字。如果在Key1和Key2中是相同的数字，那么这两行都应该是删除。

示例查询-作为起点( WHERE子句将需要更多的工作.)

select distinct                    -- {2} remove duplicates
  jddate
, endtime - starttime as interval_ -- {2} 
, nr
, terminal
, dep
, doc
, typ
, key1
, key2
from original 
where typ = 1 and jddate > 118000  -- {1}
  and key1 <> key2                 -- {3}
; 

-- result
JDDATE  INTERVAL_            NR        TERMINAL  DEP   DOC         TYP  KEY1      KEY2      
118001  +00 17:29:59.000000  34000001  MM01X11   XX01  1000800001  1    99000000  99111111 

为了将JDDATE列中的值转换为Oracle，您可以使用前面获得的回答代码创建一个小函数。(你不必这么做，但它会消除你选择的一些“杂乱”)

--  https://stackoverflow.com/questions/53743601/sql-julien-date-cyyddd-to-date
/*
select date '1900-01-01'
  + floor(118001 / 1000) * interval '1' year
  + (mod(118001, 1000) - 1) * interval '1' day
from dual;
*/

-- this is far from perfect, needs range checking, exception handling etc
create or replace function cyyddd_to_date ( cyyddd number ) return date
is
begin
  return 
    date '1900-01-01' 
  + floor( cyyddd / 1000 ) * interval '1' year 
  + ( mod( cyyddd, 1000 ) - 1 ) * interval '1' day
  ;
end;
/

-- quick test
select 
  cyyddd_to_date( 118001 ) date_
, to_char( cyyddd_to_date( 118001 ), 'YYYY-MM-DD' ) datetime_
from dual; 

-- result
DATE_      DATETIME_   
01-JAN-18  2018-01-01

最终查询

select distinct                    -- {2} remove duplicates
  to_char( cyyddd_to_date( jddate ), 'YYYY-MM-DD' ) date_
, endtime - starttime  interval_   -- {2} 
, nr
, terminal
, dep
, doc
, typ
, key1
, key2
from original 
where typ = 1 and jddate > 118000  -- {1}
  and key1 <> key2                 -- {3}
; 

-- result
DATE_       INTERVAL_            NR        TERMINAL  DEP   DOC         TYP  KEY1      KEY2      
2018-01-01  +00 17:29:59.000000  34000001  MM01X11   XX01  1000800001  1    99000000  99111111 

用Oracle12c和Oracle11g进行测试，dbfiddle 这里。