CODEWITHSUNDEEP
sqloracle-databasegaps-and-islands
Brian Badge
Brian Badge

Reputation: 45

Oracle SQL Find the largest island with the most ids

I have data in oracle with sequential numbers representing months with data for various IDs. In the example Start and End are numbers that represent each month sequentially (Jan13 is 205, Feb13 is 206, March13 207 etc) I need to identify the largest time period with the most IDs that have sequential months of data.

My table looks like this:

TAB1
╔════════╦═══════╗
║   ID   ║ MONTH ║
╠════════╬═══════╣
║ 111118 ║   210 ║
║ 111118 ║   211 ║
║ 111118 ║   212 ║
║ 111118 ║   213 ║
║ 111118 ║   214 ║
║ 111118 ║   215 ║
║ 111118 ║   216 ║
║ 111118 ║   217 ║
║ 111118 ║   218 ║
║ 111118 ║   219 ║
║ 111118 ║   220 ║
║ 111118 ║   221 ║
║ 111118 ║   222 ║
║ 111118 ║   223 ║
║ 111118 ║   224 ║
║ 111118 ║   225 ║
║ 111126 ║   200 ║
║ 111126 ║   201 ║
║ 111126 ║   205 ║
║ 111126 ║   206 ║
║ 111126 ║   207 ║
║ 111126 ║   208 ║
║ 111126 ║   209 ║
║ 111126 ║   210 ║
║ 111126 ║   211 ║
║ 111126 ║   212 ║
║ 111126 ║   213 ║
║ 111126 ║   214 ║
║ 111126 ║   215 ║
║ 111126 ║   216 ║
║ 111126 ║   217 ║
║ 111126 ║   218 ║
║ 111126 ║   219 ║
║ 111126 ║   220 ║
║ 111126 ║   221 ║
║ 111126 ║   222 ║
║ 111126 ║   223 ║
║ 111126 ║   224 ║
║ 111126 ║   225 ║
║ 111127 ║   211 ║
║ 111127 ║   212 ║
║ 111127 ║   213 ║
║ 111127 ║   214 ║
║ 111127 ║   215 ║
║ 111127 ║   216 ║
║ 111127 ║   217 ║
║ 111127 ║   218 ║
║ 111127 ║   219 ║
║ 111127 ║   220 ║
╚════════╩═══════╝

I did an island analysis with resulsts like these:

╔════════╦═══════╦═════╗
║   ID   ║ START ║ END ║
╠════════╬═══════╬═════╣
║ 111118 ║   210 ║ 225 ║
║ 111126 ║   205 ║ 225 ║
║ 111126 ║   200 ║ 201 ║
║ 111127 ║   211 ║ 220 ║
╚════════╩═══════╩═════╝

For this example the best time periods to look at would be either 

 205-225 which includes  111118, 111126 or
 211-220 which includes 111118, 111126, 111127

Any suggestions on how to accomplish this?

Upvotes: 0

Views: 111

Answers (1)

vav
vav

Reputation: 4694

You need to find a way to group sequential records together. Here is one approach that I like: 

select id, month, 
month - row_number() over (partition by id order by month) as grp
from yourtable

As a result, your grp field will be the same for the sequential values in month column.

After that all you need to do is to sum up:

select id, min(month), max(month), count(grp)
from (query above)
group by id, grp

Upvotes: 2

Related Questions