CODEWITHSUNDEEP
sqloracle-databasetimeaggregation
user1503
user1503

Reputation: 70

Extract time points meeting some time interval criteria

I have searched around and didn't find the solution. (A useful reading SQL to find time elapsed from multiple overlapping intervals)

Here is my data: the rules are

"For each country, select the Date_ID in sequence with time interval equal or greater than 5 months"

My working environment is ORACLE SQL.

Thanks a lot.

Country       Date_ID
----------------------
USA           199003
USA           200004
USA           200005
USA           200009
USA           200010
UK            199307
UK            199308
UK            199408

Therefore the output should be

 Country    Date_ID
 --------------------
 USA        199003
 USA        200004
 USA        200009
 UK         199307
 UK         199408

Upvotes: 0

Views: 69

Answers (1)

user5683823
user5683823

Reputation:

Here is one way to solve this, which will work at least as far back as Oracle 10.2. It uses analytic functions and a hierarchical query.

The WITH clause is there just to build the sample data on the fly. You don't need it - remove it, and use your actual table and column names in the query. (In the WITH clause I declared the columns after the CTE name, which works only in Oracle 11.2 and higher, but the WITH clause is not part of the solution, so I wouldn't worry about that.)

with
  sample_data (country, date_id) as (
    select 'USA', 199003 from dual union all
    select 'USA', 200004 from dual union all
    select 'USA', 200005 from dual union all
    select 'USA', 200009 from dual union all
    select 'USA', 200010 from dual union all
    select 'UK' , 199307 from dual union all
    select 'UK' , 199308 from dual union all
    select 'UK' , 199408 from dual
  )
select country, date_id
from   (
         select country, date_id,
                row_number() over (partition by country order by dt) as rn,
                count(*) over (partition by country order by dt
                  range between current row 
                            and interval '4' month following) as ct
         from   (
                  select country, date_id, 
                         to_date(to_char(date_id, 'fm999999'), 'yyyymm') as dt
                  from   sample_data
                )
       )
start with rn = 1
connect by country = prior country and rn = prior rn + prior ct
;

COUNTRY    DATE_ID
------- ----------
UK          199307
UK          199408
USA         199003
USA         200004
USA         200009

For comparison, here is a match_recognize solution, which requires Oracle 12.1 or higher:

select country, date_id
from   (
         select country, date_id, 
                to_date(to_char(date_id, 'fm999999'), 'yyyymm') dt
         from   sample_data
       )
match_recognize(
  partition by country
  order by     date_id
  all rows per match
  pattern      (a {- b* -})
  define b as  dt < add_months(a.dt, 5)
);

Upvotes: 1

Related Questions