Oracle 12c-SQL查找乱序行 [英] Oracle 12c - sql to find out of order rows
问题描述
我有一个包含以下列的表格:
I have a table with following columns:
FILE_NAME VARCHAR2(30);
STATUS VARCHAR2(2);
DEPT_ID NUMBER;
DEPT_SUB_ID NUMBER;
CREATE_DATE DATE;
样本数据:
FILE_NAME STATUS DEPT_ID DEPT_SUB_ID CREATE_DATE
--------- ------- -------- ----------- ----------
TEST_20180806222127 C 1 10 07-AUG-18 01.04.47.821795000 AM
TEST_20180806221940 C 1 10 07-AUG-18 04.12.20.957400000 AM
TEST_20180806221733 C 1 10 07-AUG-18 03.35.27.809494000 AM
TEST_20180805202020 C 1 20 06-AUG-18 02.24.47.821795000 AM
TEST_20180805201640 C 1 20 06-AUG-18 00.42.20.957400000 AM
TEST_20180805201530 C 1 20 06-AUG-18 03.55.27.809494000 AM
FILE_NAME
包含:<TYPE>_<DATETIME>
我想为每个DEPT_ID
,DEPT_SUB_ID
编写查询,以确定基于FILE_NAME
和CREATE_DATE
字段中的<DATETIME>
乱序创建带有STATUS = 'C'
的文件.在此示例中,对于DEPT_SUB_ID = 10
,文件TEST_20180806222127
是在文件名DATE_TIME
之前的其他2个文件之前创建的,因此对于DEPT_SUB_ID = 10
,我只需要返回此文件名即可.对于DEPT_SUB_ID = 20
,结果应包含TEST_20180805201640
和TEST_20180805202020
,因为它们都是在TEST_20180805201530
之前创建的,这被认为是乱序的.
FILE_NAME
consists of: <TYPE>_<DATETIME>
I want to write a query for each DEPT_ID
, DEPT_SUB_ID
to determine which files with STATUS = 'C'
were created out of order based on the <DATETIME>
on FILE_NAME
and CREATE_DATE
field. In this example, for DEPT_SUB_ID = 10
, file TEST_20180806222127
was created before the other 2 based on the DATE_TIME
on the file name so I would need to return only this file name in result for DEPT_SUB_ID = 10
. For DEPT_SUB_ID = 20
, result should contain TEST_20180805201640
and TEST_20180805202020
since both were created before TEST_20180805201530
, which is considered out of order.
查询的预期结果将输出在运行顺序之前创建的所有file_name.
Expected results from query will output all file_name's which were created before it's order of run.
推荐答案
您可以为每行分配两个排名,一个基于文件名中嵌入时间戳的顺序,另一个基于创建日期的顺序:
You can assign two rankings to each row, one based on the order of the timestamp embedded int he file name, or other on the order of the creation date:
select yt.*,
row_number() over (partition by dept_id, dept_sub_id
order by to_date(substr(file_name, -14), 'YYYYMMDDHH24MISS')) as rn_file_name,
row_number() over (partition by dept_id, dept_sub_id
order by create_date) as rn_create_date
from your_table yt;
FILE_NAME S DEPT_ID DEPT_SUB_ID CREATE_DATE RN_FILE_NAME RN_CREATE_DATE
------------------- - ---------- ----------- ----------------------------- ------------ --------------
TEST_20180806221733 C 1 10 2018-08-07 03:35:27.809494000 1 2
TEST_20180806221940 C 1 10 2018-08-07 04:12:20.957400000 2 3
TEST_20180806222127 C 1 10 2018-08-07 01:04:47.821795000 3 1
TEST_20180805201530 C 1 20 2018-08-06 03:55:27.809494000 1 3
TEST_20180805201640 C 1 20 2018-08-06 00:42:20.957400000 2 1
TEST_20180805202020 C 1 20 2018-08-06 02:24:47.821795000 3 2
然后过滤以查看不匹配项:
Then filter to see the mismatches:
select file_name, status, dept_id, dept_sub_id, create_date
from (
select yt.*,
row_number() over (partition by dept_id, dept_sub_id
order by to_date(substr(file_name, -14), 'YYYYMMDDHH24MISS')) as rn_file_name,
row_number() over (partition by dept_id, dept_sub_id
order by create_date) as rn_create_date
from your_table yt
)
where rn_file_name > rn_create_date;
FILE_NAME S DEPT_ID DEPT_SUB_ID CREATE_DATE
------------------- - ---------- ----------- -----------------------------
TEST_20180806222127 C 1 10 2018-08-07 01:04:47.821795000
TEST_20180805201640 C 1 20 2018-08-06 00:42:20.957400000
TEST_20180805202020 C 1 20 2018-08-06 02:24:47.821795000
如果您不想一次看到所有ID,则可以在内部查询或外部查询中为特定ID或子ID添加过滤器.
And you can add a filter for a specific ID or sub-ID, either in the inner or outer query, if you don't want to see them all at once.
这篇关于Oracle 12c-SQL查找乱序行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!