SQL-如果日期连接则合并行 [英] Sql - Merging rows if date connects
本文介绍了SQL-如果日期连接则合并行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有带行的表:clientid,startdate和enddate.同一客户ID的日期不能重叠. 如果日期连接,我想为每个客户端合并行.
I have table with rows: clientid, startdate and enddate. Date cant overlap for same clientid. I would like to merge rows for every client if date connects.
表如下所示:
clientid startdate enddate
1 10.10.2017 12.10.2017
1 12.10.2017 13.10.2017
1 13.10.2017 17.10.2017
1 10.11.2017 17.11.2017
1 17.11.2017 23.11.2017
1 12.12.2017 14.12.2017
2 10.11.2017 15.11.2017
2 01.12.2017 02.12.2017
2 02.12.2017 05.12.2017
最终表应如下所示:
clientid startdate enddate
1 10.10.2017 17.10.2017
1 10.11.2017 23.11.2017
1 12.12.2017 14.12.2017
2 10.11.2017 15.11.2017
2 01.12.2017 05.12.2017
感谢您的帮助.
推荐答案
您可以对sum
聚合和lag
窗口函数使用这样的逻辑,如下所示:
You can use such a logic with sum
aggregate and lag
window functions as below :
select clientid, min(startdate) as startdate, max(enddate) as enddate
from
(
select tt.*, sum(grp) over (order by clientid, startdate) sm
from
(
with t(clientid, startdate, enddate) as
(
select 1, date'2017-10-10', date'2017-10-12' from dual union all
select 1, date'2017-10-12', date'2017-10-13' from dual union all
select 1, date'2017-10-13', date'2017-10-17' from dual union all
select 1, date'2017-11-10', date'2017-11-17' from dual union all
select 1, date'2017-11-17', date'2017-11-23' from dual union all
select 1, date'2017-12-12', date'2017-12-14' from dual union all
select 2, date'2017-11-10', date'2017-11-15' from dual union all
select 2, date'2017-12-01', date'2017-12-02' from dual union all
select 2, date'2017-12-02', date'2017-12-05' from dual
)
select clientid,
decode(nvl(lag(enddate) over
(order by enddate),startdate),startdate,0,1)
as grp, --> means prev. value equals or not
row_number() over (order by clientid, enddate) as rn, startdate, enddate
from t
) tt
order by rn
)
group by clientid, sm
order by clientid, enddate;
CLIENTID STARTDATE ENDDATE
---------- ---------- ----------
1 10.10.2017 17.10.2017
1 10.11.2017 23.11.2017
1 12.12.2017 14.12.2017
2 10.11.2017 15.11.2017
2 01.12.2017 05.12.2017
这篇关于SQL-如果日期连接则合并行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文