Oracle查询以填补分组记录之间的天差 [英] Oracle Query to Fill in Day Gaps Between Grouped Records
问题描述
不太确定从哪里开始查询，但是我有两个表TEST_A和TEST_B.
Not really sure where to start with the query, but I have two tables, TEST_A and TEST_B.
TEST_B包含特定ID的特定日期范围，而TEST_A包含具有提供的ASSIGNMENT值的ID的日期范围.
TEST_B contains a specific date range for a certain ID, while TEST_A contains the date ranges for the ID with a provided ASSIGNMENT value.
下面是用于创建和填充表格的DDL.
Below is the DDL to create and populate the tables.
CREATE TABLE TEST_A
(
ID VARCHAR2(5),
START_DATE DATE,
END_DATE DATE,
ASSIGNMENT VARCHAR2(25)
)
STORAGE (
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
CREATE TABLE TEST_B
(
ID VARCHAR2(5),
START_DATE DATE,
END_DATE DATE
)
STORAGE (
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
以及用于填充表格的脚本:
And the script to populate the tables:
INSERT INTO TEST_A(ID, START_DATE, END_DATE, ASSIGNMENT)
VALUES('A', TO_DATE('01/01/2010', 'MM/DD/YYYY'), TO_DATE('01/31/2010', 'MM/DD/YYYY'), 'Lot A');
INSERT INTO TEST_A(ID, START_DATE, END_DATE, ASSIGNMENT)
VALUES('A', TO_DATE('02/01/2010', 'MM/DD/YYYY'), TO_DATE('02/15/2010', 'MM/DD/YYYY'), 'Lot A');
INSERT INTO TEST_A(ID, START_DATE, END_DATE, ASSIGNMENT)
VALUES('A', TO_DATE('02/18/2010', 'MM/DD/YYYY'), TO_DATE('02/28/2010', 'MM/DD/YYYY'), 'Lot C');
INSERT INTO TEST_A(ID, START_DATE, END_DATE, ASSIGNMENT)
VALUES('A', TO_DATE('03/01/2010', 'MM/DD/YYYY'), TO_DATE('03/31/2010', 'MM/DD/YYYY'), 'Lot D');
INSERT INTO TEST_A(ID, START_DATE, END_DATE, ASSIGNMENT)
VALUES('B', TO_DATE('08/01/2010', 'MM/DD/YYYY'), TO_DATE('08/31/2010', 'MM/DD/YYYY'), 'Lot E');
INSERT INTO TEST_A(ID, START_DATE, END_DATE, ASSIGNMENT)
VALUES('B', TO_DATE('09/15/2010', 'MM/DD/YYYY'), TO_DATE('09/30/2010', 'MM/DD/YYYY'), 'Lot E');
INSERT INTO TEST_A(ID, START_DATE, END_DATE, ASSIGNMENT)
VALUES('C', TO_DATE('09/15/2010', 'MM/DD/YYYY'), TO_DATE('09/30/2010', 'MM/DD/YYYY'), 'Lot E');
INSERT INTO TEST_B(ID, START_DATE, END_DATE)
VALUES('A', TO_DATE('01/01/2010', 'MM/DD/YYYY'), TO_DATE('12/31/2099', 'MM/DD/YYYY'));
INSERT INTO TEST_B(ID, START_DATE, END_DATE)
VALUES('B', TO_DATE('08/01/2010', 'MM/DD/YYYY'), TO_DATE('12/31/2099', 'MM/DD/YYYY'));
INSERT INTO TEST_B(ID, START_DATE, END_DATE)
VALUES('C', TO_DATE('01/01/2010', 'MM/DD/YYYY'), TO_DATE('12/31/2099', 'MM/DD/YYYY'));
从数据中，我需要按任务"将TEST_A中的那些记录分组，并填写两者之间的缺失日间隔.每个ID的记录还应涵盖表TEST_B中提供的整个开始和结束日期.为了进一步说明，我需要的结果数据如下所示:
From the data, I would need to group those records from TEST_A by the Assignment, and fill in the missing day gaps in between. The records for each ID should also cover the whole start and end date as provided in the table TEST_B. To further explain, the resulting data I need would look like this:
ID START_DATE END_DATE ASSIGNMENT
A 01/01/2010 02/15/2010 Lot A
A 02/16/2010 02/17/2010 {NULL}
A 03/01/2010 03/31/2010 Lot D
A 04/01/2010 12/31/2099 {NULL}
B 08/01/2010 08/31/2010 Lot E
B 09/01/2010 09/14/2010 {NULL}
B 09/15/2010 09/30/2010 Lot E
B 10/01/2010 12/31/2099 {NULL}
C 01/01/2010 09/14/2010 {NULL}
C 09/15/2010 09/30/2010 Lot E
C 10/01/2010 12/31/2099 {NULL}
感谢您为此建立查询的任何帮助. LAG功能浮现在脑海，但我不确定如何正确注销它.谢谢.
Appreciate any help on building the query for this. LAG function comes into mind but I am unsure how to properly write it off. Thanks.
添加: 如果存储过程可以灵活地处理此问题，那么该解决方案仍然是可以接受的解决方案.
Addition: If a Stored Procedure will provide flexibility in handling this, then that would still be an accepted solution.
推荐答案
一种流水线函数方法(我在此处添加了另一个Lot A(id = A)行，只是为了显示结果).
a pipelined function approach (ive added another Lot A (id = A) row in here just to show the result).
create type test_typ as object(id varchar2(5),
start_date date,
end_date date,
assignment varchar2(25)
);
/
create type test_tab as table of test_typ;
/
create or replace function test_pipe
return test_tab pipelined is
begin
for r_row in (select a.id, a.start_date, a.end_date,
a.assignment,
lead(a.start_date, 1) over (partition by a.id order by a.start_date) next_start_date,
lead(a.id, 1) over (order by a.id, a.start_date) next_id,
lag(a.id, 1) over (order by a.id, a.start_date) prior_id,
b.start_date min_start, b.end_date max_end
from (select id, min(start_date) start_date, max(end_date) end_date, assignment
from (select id, start_date, end_date, assignment, rn, max(rn) over (partition by id order by start_date) r
from (select id, start_date, end_date, assignment,
case
when lag(end_date, 1) over (partition by id, assignment order by start_date) = start_date-1
then null
else row_number() over (order by start_date)
end rn
from test_a)
order by id, start_date)
group by id, assignment, r) a,
test_b b
where b.id = a.id
order by id, start_date)
loop
if ((r_row.prior_id != r_row.id or r_row.prior_id is null) and r_row.start_date > r_row.min_start)
then
pipe row(test_typ(r_row.id, r_row.min_start, r_row.start_date-1, null));
end if;
pipe row(test_typ(r_row.id, r_row.start_date, r_row.end_date, r_row.assignment));
if (r_row.next_start_date != r_row.end_date + 1)
then
pipe row(test_typ(r_row.id, r_row.end_date + 1, r_row.next_start_date-1, null));
elsif ((r_row.next_id != r_row.id or r_row.next_id is null) and r_row.end_date < r_row.max_end)
then
pipe row(test_typ(r_row.id, r_row.end_date + 1, r_row.max_end, null));
end if;
end loop;
end test_pipe;
带有数据:
SQL> select * from test_a order by 1, 2;
ID START_DAT END_DATE ASSIGNMENT
----- --------- --------- -------------------------
A 01-JAN-10 31-JAN-10 Lot A
A 01-FEB-10 15-FEB-10 Lot A
A 18-FEB-10 28-FEB-10 Lot C
A 01-MAR-10 31-MAR-10 Lot D
A 01-APR-10 30-MAY-10 Lot A
B 01-AUG-10 31-AUG-10 Lot E
B 15-SEP-10 30-SEP-10 Lot E
C 15-SEP-10 30-SEP-10 Lot E
SQL> select *
2 from table(test_pipe()) b;
ID START_DAT END_DATE ASSIGNMENT
----- --------- --------- -------------------------
A 01-JAN-10 15-FEB-10 Lot A
A 16-FEB-10 17-FEB-10
A 18-FEB-10 28-FEB-10 Lot C
A 01-MAR-10 31-MAR-10 Lot D
A 01-APR-10 30-MAY-10 Lot A
A 31-MAY-10 31-DEC-99
B 01-AUG-10 31-AUG-10 Lot E
B 01-SEP-10 14-SEP-10
B 15-SEP-10 30-SEP-10 Lot E
B 01-OCT-10 31-DEC-99
C 01-JAN-10 14-SEP-10
C 15-SEP-10 30-SEP-10 Lot E
C 01-OCT-10 31-DEC-99
13 rows selected.
SQL>
这篇关于Oracle查询以填补分组记录之间的天差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
