英文:
Summarize rows with continued date range
问题
以下是翻译好的部分：
Oracle 19c 版本
不确定是否可以编写查询以实现我所要求的内容，但在向专家确认之前，我不想排除这种可能性！
我尝试在 dbfiddle 和 sqlfiddle 中创建此表格和数据，但出现了错误。因此，以下是脚本。
create table xxtest (
person_number number,
full_name varchar2(100),
grade varchar2(10),
st_date date,
end_date date
);
insert into xxtest values (1,'John Doe','E02',to_date('2023-01-01','YYYY-MM-DD'),to_date('2023-01-23','YYYY-MM-DD'));
insert into xxtest values (1,'John Doe','E02',to_date('2023-01-30','YYYY-MM-DD'),to_date('2023-02-28','YYYY-MM-DD'));
insert into xxtest values (1,'John Doe','E02',to_date('2023-03-01','YYYY-MM-DD'),to_date('2023-03-12','YYYY-MM-DD'));
insert into xxtest values (1,'John Doe','E02',to_date('2023-03-15','YYYY-MM-DD'),to_date('2023-03-31','YYYY-MM-DD'));
insert into xxtest values (1,'John Doe','E02',to_date('2023-04-01','YYYY-MM-DD'),to_date('2023-12-31','YYYY-MM-DD'));
insert into xxtest values (1,'John Doe','N01',to_date('2023-01-24','YYYY-MM-DD'),to_date('2023-01-29','YYYY-MM-DD'));
select a.*, a.end_date-a.st_date days
from xxtest a;
因此，数据看起来像这样：
预期的输出如下所示。按照个人、姓名和等级，如果日期没有间断，那么这些行应该被汇总，并具有 st_date 和 end_date 之间天数的总和。
我已将上述行进行了颜色编码，并在下面的图像中显示为汇总。
一如既往，非常感谢您的帮助！
英文:
Oracle 19c version
Not sure if it is possible to write a query to achieve what I am asking but didn't want to rule out without checking with the pros here!
I tried to create this table & data in dbfiddle & sqlfiddle but getting errors. So here are the scripts.
create table xxtest (
person_number number,
full_name varchar2(100),
grade varchar2(10),
st_date date,
end_date date
);
insert into xxtest values (1,'John Doe','E02',to_date('2023-01-01','YYYY-MM-DD'),to_date('2023-01-23','YYYY-MM-DD'));
insert into xxtest values (1,'John Doe','E02',to_date('2023-01-30','YYYY-MM-DD'),to_date('2023-02-28','YYYY-MM-DD'));
insert into xxtest values (1,'John Doe','E02',to_date('2023-03-01','YYYY-MM-DD'),to_date('2023-03-12','YYYY-MM-DD'));
insert into xxtest values (1,'John Doe','E02',to_date('2023-03-15','YYYY-MM-DD'),to_date('2023-03-31','YYYY-MM-DD'));
insert into xxtest values (1,'John Doe','E02',to_date('2023-04-01','YYYY-MM-DD'),to_date('2023-12-31','YYYY-MM-DD'));
insert into xxtest values (1,'John Doe','N01',to_date('2023-01-24','YYYY-MM-DD'),to_date('2023-01-29','YYYY-MM-DD'));
select a.*, a.end_date-a.st_date days
from xxtest a;
So the data looks like this:
Expected output is shown below. By person, name & grade, if there is no break in the dates, then those rows should be summarized and have the sum of the days between the st_date & end_date.
I have color coded the above rows and shown as rolled up in below image.
As always, truly appreciate your help!
答案1
得分: 3
SELECT *
FROM xxtest a
MATCH_RECOGNIZE (
PARTITION BY person_number, full_name, grade
ORDER BY st_date
MEASURES
FIRST(st_date) AS dt_start,
LAST(end_date) AS dt_end,
SUM(end_date - st_date) AS days
PATTERN (st nxt*)
DEFINE
nxt AS st_date = PREV(end_date) + 1
);
DBFiddle: https://dbfiddle.uk/ov7LzObI
PERSON_NUMBER FULL_NAME GRADE DT_START DT_END DAYS
1 John Doe E02 01-JAN-23 23-JAN-23 22
1 John Doe E02 30-JAN-23 12-MAR-23 40
1 John Doe E02 15-MAR-23 31-DEC-23 290
1 John Doe N01 24-JAN-23 29-JAN-23 5
英文:
select *
from xxtest a
match_recognize(
partition by person_number,full_name,grade
order by st_date
MEASURES
first(st_date) as dt_start,
last(end_date) as dt_end,
sum(end_date-st_date) as days
PATTERN (st nxt*)
DEFINE
nxt AS st_date = prev(end_date)+1
);
DBFiddle: https://dbfiddle.uk/ov7LzObI
PERSON_NUMBER FULL_NAME GRADE DT_START DT_END DAYS
1 John Doe E02 01-JAN-23 23-JAN-23 22
1 John Doe E02 30-JAN-23 12-MAR-23 40
1 John Doe E02 15-MAR-23 31-DEC-23 290
1 John Doe N01 24-JAN-23 29-JAN-23 5
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论