我一直在努力解决这个问题,但我相信有一种方法可以做到这一点。我有一张表,上面有在我们这里的住宿记录。每一行都有一个clientID、rowID、一个开始日期、一个结束日期、一个类型和前一个住宿的rowID。
像这样:
如果我正确理解您的任务,这可以通过connect by
查询(分层查询)然后是聚合来完成,这可以在相同的select
语句中完成。这样的:
alter session set nls_date_format = 'dd-mm-yyyy';
with
sample_data (clientid, stayid, previous_stay, start_date, end_date, type_) as (
select 1, 101, null, to_date('1-1-2010') , to_date('20-6-2010') , 'A' from dual
union all
select 1, 105, 101 , to_date('1-7-2010') , to_date('30-12-2020'), 'B' from dual
union all
select 1, 108, null, to_date('8-10-2012'), to_date('10-12-2012'), 'B' from dual
)
select clientid,
min(case connect_by_isleaf when 1 then stayid end) as stayid,
min(case level when 1 then stayid end) as first_stay,
min(start_date) as first_start_date,
max(end_date) as last_end_date,
count(distinct type_) as distinct_types
from sample_data
start with previous_stay is null
connect by prior stayid = previous_stay and prior clientid = clientid
group by clientid, connect_by_root(stayid)
;
CLIENTID STAYID FIRST_STAY FIRST_START_DATE LAST_END_DATE DISTINCT_TYPES
-------- ------ ---------- ---------- ------------- --------------
1 105 101 01-01-2010 30-12-2020 2
1 108 108 08-10-2012 10-12-2012 1
输出在两个地方与您的不同:STAYID 105的最后结束日期是2020年,而不是您显示的2010年,因为输入显示的是2020年。(可能在示例输入或示例输出中有错别字)。并且,对于STAYID 108,第一个STAYID是108,而不是NULL,正如您在输出中显示的那样;事实上,NULL不是输入中任何停留的STAYID,因此显示为"第一个停留"是没有意义的。
你只是想要聚合吗?
select clientid, max(stayid), min(stayid), min(startdate), max(enddate),
count(distinct type)
from t
group by clientid;