Oracle SQL-获取连续记录的日期范围

askTom 2017-06-15

224

问题描述

嗨，汤姆，
我已经多次访问您的网站，并从您的意见和解决方案中学到了很多东西。

我正在寻找按时间顺序显示的SQL在哪个国家 “汤姆” 居住。

以下是我的详细信息:

--create table.
create table t02(
fname varchar2(10),
city varchar2(10),
country varchar2(5),
start_date date,
end_date date);
/
insert into t02 values('Tom','Boston','US','01-JAN-2001', '31-JUL-2001');
insert into t02 values('Tom','Nashua','US','01-AUG-2001', '31-DEC-2001');
insert into t02 values('Tom','New York','US','01-JAN-2002', '31-DEC-2002');
insert into t02 values('Tom','Mumbai','IND','01-JAN-2003', '31-DEC-2003');
insert into t02 values('Tom','Dallas','US','01-JAN-2004', '31-DEC-2004');
insert into t02 values('Tom','Austin','US','01-JAN-2005', '31-DEC-2006');
/
commit;

/
select * from t02
order by start_date

/

fname   City          Country        start_Date   End_date
------- ------        ---------     -----------   ---------
Tom     Boston  US       01-JAN-01   31-JUL-01
Tom     Nashua  US       01-AUG-01   31-DEC-01
Tom     New York     US       01-JAN-02   31-DEC-02
Tom     Mumbai  IND       01-JAN-03   31-DEC-03
Tom     Dallas  US       01-JAN-04   31-DEC-04
Tom     Austin  US       01-JAN-05   31-DEC-06


/复制

我想让sql显示汤姆在哪个国家居住的时间顺序

例如:

我期望从sql输出如下:

fname   Country       start_Date     End_date
------- --------      -----------   ---------
Tom     US       01-JAN-01     31-DEC-02
Tom     IND       01-JAN-03     31-DEC-03
Tom     US       01-JAN-04     31-DEC-06复制

提前谢谢。

专家解答

塔比比托桑去营救!

您可以使用它将连续的行分组在一起。要在这里做到这一点，你需要计算两件事:

-每个fname的行号，按 (开始) 日期排序
-每个fname和国家/地区的行号也按 (开始) 日期排序

例如:

select row_number() over (partition by fname order by start_date) rn, 
       row_number() over (partition by fname, country order by start_date) crn,
       t.*
from   t02 t;

RN  CRN  FNAME  CITY      COUNTRY  START_DATE            END_DATE              
1   1    Tom    Boston    US       01-JAN-2001 00:00:00  31-JUL-2001 00:00:00  
2   2    Tom    Nashua    US       01-AUG-2001 00:00:00  31-DEC-2001 00:00:00  
3   3    Tom    New York  US       01-JAN-2002 00:00:00  31-DEC-2002 00:00:00  
4   1    Tom    Mumbai    IND      01-JAN-2003 00:00:00  31-DEC-2003 00:00:00  
5   4    Tom    Dallas    US       01-JAN-2004 00:00:00  31-DEC-2004 00:00:00  
6   5    Tom    Austin    US       01-JAN-2005 00:00:00  31-DEC-2006 00:00:00 复制

连续的行是这些值之间的差相同的行。所以你可以通过从另一个中减去一个来对它们进行分组。

这样做，你就有一个简单的组按名称，国家和组。日期是每个的最小开始和最大结束:

with rws as (
  select row_number() over (partition by fname order by start_date) - 
           row_number() over (partition by fname, country order by start_date) grp, 
         t.*
  from   t02 t
)
  select fname, country, 
         min(start_date) st, 
         max(end_date) en 
  from   rws r
  group  by fname, country, grp
  order  by min(start_date) ;

FNAME  COUNTRY  ST                    EN                    
Tom    US       01-JAN-2001 00:00:00  31-DEC-2002 00:00:00  
Tom    IND      01-JAN-2003 00:00:00  31-DEC-2003 00:00:00  
Tom    US       01-JAN-2004 00:00:00  31-DEC-2006 00:00:00  复制