sql - Oracle - 格式化和结构数据/查询
问题描述
这是我挑战 SQL (Oracle) 的第一周。我以前从未真正使用过 SQL。只尝试理解查询。目前我正在尝试通过服务器端的 SQL (Oracle) 外包一个查询,到目前为止我已经在 Excel(电源查询)中进行了转换,以获得相应的性能改进。这意味着到目前为止我已经实现了电源查询中的步骤,并尝试使用 SQL 语句来实现它们。我已经在这里问了几个问题,也从你那里得到了很多帮助和好主意。我认为在一个问题中处理所有事情最有意义。原始表或数据库的结构如下所示:
名称 = 值示例
- JDDate = 118001
- 项目清单
- 开始 = 63000(对于 06:30:00)
- END = 240000(对于 24:00:00)
- NR = 34000001
- 端子 = MM01X11
- 部门 = XX01
- 文档 = 1000800001
- 典型值 = 1
- 键 1 = 99000000
密钥2 = 99000000
起初我需要“Typ”= 1 和 JDDate => 118000 的所有值
其中“JDDate”>118000 且“Typ”=1
- 然后我需要基于正确的 JDDate/ 格式的 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 中的一个数字。如果 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} 然后我需要基于正确的 JDDate/ 格式的 START 和 END 之间的差异/时间步长。不幸的是,这里有一些重复,基于 JDDate,START;结尾; 终端。:
{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 DATE,您可以使用之前获得的答案代码创建一个小函数。(您不必这样做,但它会从您的 SELECT 中删除一些“混乱”)例如
-- 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 。
推荐阅读
- reactjs - 使用表单数据回调请求以响应托管在 IIS 服务器上的应用程序
- python-3.x - 通过比较 pandas 中的两个数据框来计算数据转换百分比
- c++ - 用于包含不同元素的 2 个向量的单个迭代器 (c++)
- flutter - 无法从 Gradle 脚本中找到 signingReport 以在 android studio 中找到 SHA-1 指纹密钥
- javascript - Bootstrap 无法更改选择的选定值
- python - 计算特定形状的区域面积
- javascript - 使用 Python 调用网页上的 JavaScript 函数
- jenkins - 如何根据评论运行不同的管道?
- c - 立即发送到 TCP 套接字 bufferevent_write bufferevent libevent
- azure - 如何获取使用 Azure CLI 安装操作系统的 Azure VMS 列表?