Oracle—格式化和结构数据/查询

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

Name=值示例

JDDate=1118001
列出项目
START=63000(06:30:00(
END=240000(对于24:00:00(
NR=334000001
端子=MM01X11
Dep=XX01
DOC=100008001
类型=1
密钥1=99000000
Key2=99000000
起初，我需要"Typ"=1和JDDate=>118000 的所有值

WHERE"JDDate">118000AND"Typ"=1

然后我需要基于正确的JDDate/Format的START和END之间的差异/时间步长。不幸的是，这里有一些重复，基于JDDate，START；结束；终端。：

。

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中的一个数字。如果in Key1和Key2是相同的数字，则应删除这两行

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

有没有办法向您展示一个示例数据库？

致以最良好的问候约书亚

有没有办法向您展示一个示例数据库？

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

表

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} 然后我需要START和END之间的差异/时间步长以正确的JDDate/Format。不幸的是，这里有一些重复，基于JDDate，START；结束；终端。：
{3} 至少我在Key1和Key2.所以Key1每次都包含一个数字。Key2包含0或数字，该数字也是Key1中的数字。如果在键1中并且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列中的值转换为OracleDATEs，您可以使用前面得到的答案代码创建一个小函数。(你不必这样做，但它会从你的选择中消除一些"混乱"(例如

--  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

使用Oracle 12c和Oracle 11g进行了测试，请点击此处dbfiddle。

相关内容

最新更新

热门标签：