I'm going to develop my solution incrementally, decomposing each transformation into a view. This both helps explain what's being done, and helps in debugging and testing. It's essentially applying the principle of functional decomposition to database queries.
I'm also going to do it without using Oracle extensions, with SQL that ought to run on any modern RBDMS. So no keep, over, partition, just subqueries and group bys. (Inform me in the comments if it doesn't work on your RDBMS.)
First, the table, which since I'm uncreative, I'll call month_value. Since the id is not actually a unique id, I'll call it "eid". The other columns are "m"onth, "y"ear, and "v"alue:
create table month_value(
eid int not null, m int, y int, v int );
After inserting the data, for two eids, I have:
> select * from month_value;
+-----+------+------+------+
| eid | m | y | v |
+-----+------+------+------+
| 100 | 1 | 2008 | 80 |
| 100 | 2 | 2008 | 80 |
| 100 | 3 | 2008 | 90 |
| 100 | 4 | 2008 | 80 |
| 200 | 1 | 2008 | 80 |
| 200 | 2 | 2008 | 80 |
| 200 | 3 | 2008 | 90 |
| 200 | 4 | 2008 | 80 |
+-----+------+------+------+
8 rows in set (0.00 sec)
Next, we have one entity, the month, that's represented as two variables. That should really be one column (either a date or a datetime, or maybe even a foreign key to a table of dates), so we'll make it one column. We'll do that as a linear transform, such that it sorts the same as (y, m), and such that for any (y,m) tuple there is one and only value, and all values are consecutive:
> create view cm_abs_month as
select *, y * 12 + m as am from month_value;
That gives us:
> select * from cm_abs_month;
+-----+------+------+------+-------+
| eid | m | y | v | am |
+-----+------+------+------+-------+
| 100 | 1 | 2008 | 80 | 24097 |
| 100 | 2 | 2008 | 80 | 24098 |
| 100 | 3 | 2008 | 90 | 24099 |
| 100 | 4 | 2008 | 80 | 24100 |
| 200 | 1 | 2008 | 80 | 24097 |
| 200 | 2 | 2008 | 80 | 24098 |
| 200 | 3 | 2008 | 90 | 24099 |
| 200 | 4 | 2008 | 80 | 24100 |
+-----+------+------+------+-------+
8 rows in set (0.00 sec)
Now we'll use a self-join in a correlated subquery to find, for each row, the earliest successor month in which the value changes. We'll base this view on the previous view we created:
> create view cm_last_am as
select a.*,
( select min(b.am) from cm_abs_month b
where b.eid = a.eid and b.am > a.am and b.v <> a.v)
as last_am
from cm_abs_month a;
> select * from cm_last_am;
+-----+------+------+------+-------+---------+
| eid | m | y | v | am | last_am |
+-----+------+------+------+-------+---------+
| 100 | 1 | 2008 | 80 | 24097 | 24099 |
| 100 | 2 | 2008 | 80 | 24098 | 24099 |
| 100 | 3 | 2008 | 90 | 24099 | 24100 |
| 100 | 4 | 2008 | 80 | 24100 | NULL |
| 200 | 1 | 2008 | 80 | 24097 | 24099 |
| 200 | 2 | 2008 | 80 | 24098 | 24099 |
| 200 | 3 | 2008 | 90 | 24099 | 24100 |
| 200 | 4 | 2008 | 80 | 24100 | NULL |
+-----+------+------+------+-------+---------+
8 rows in set (0.01 sec)
last_am is now the "absolute month" of the first (earliest) month (after the month of the current row) in which the value, v, changes. It's null where there is no later month, for that eid, in the table.
Since last_am is the same for all months leading up to the change in v (which occurs at last_am), we can group on last_am and v (and eid, of course), and in any group, the min(am) is the absolute month of the first consecutive month that had that value:
> create view cm_result_data as
select eid, min(am) as am , last_am, v
from cm_last_am group by eid, last_am, v;
> select * from cm_result_data;
+-----+-------+---------+------+
| eid | am | last_am | v |
+-----+-------+---------+------+
| 100 | 24100 | NULL | 80 |
| 100 | 24097 | 24099 | 80 |
| 100 | 24099 | 24100 | 90 |
| 200 | 24100 | NULL | 80 |
| 200 | 24097 | 24099 | 80 |
| 200 | 24099 | 24100 | 90 |
+-----+-------+---------+------+
6 rows in set (0.00 sec)
Now this is the result set we want, which is why this view is called cm_result_data. All that's lacking is something to transform absolute months back to (y,m) tuples.
To do that, we'll just join to the table month_value.
There are only two problems:
1) we want the month before last_am in our output, and
2) we have nulls where there is no next month in our data; to met the OP's specification, those should be single month ranges.
EDIT: These could actually be longer ranges than one month, but in every case they mean we need to find the latest month for the eid, which is:
(select max(am) from cm_abs_month d where d.eid = a.eid )
Because the views decompose the problem, we could add in this "end cap" month earlier, by adding another view, but I'll just insert this into the coalesce. Which would be most efficient depends on how your RDBMS optimizes queries.
To get month before, we'll join (cm_result_data.last_am - 1 = cm_abs_month.am)
Wherever we have a null, the OP wants the "to" month to be the same as the "from" month, so we'll just use coalesce on that: coalesce( last_am, am). Since last eliminates any nulls, our joins don't need to be outer joins.
> select a.eid, b.m, b.y, c.m, c.y, a.v
from cm_result_data a
join cm_abs_month b
on ( a.eid = b.eid and a.am = b.am)
join cm_abs_month c
on ( a.eid = c.eid and
coalesce( a.last_am - 1,
(select max(am) from cm_abs_month d where d.eid = a.eid )
) = c.am)
order by 1, 3, 2, 5, 4;
+-----+------+------+------+------+------+
| eid | m | y | m | y | v |
+-----+------+------+------+------+------+
| 100 | 1 | 2008 | 2 | 2008 | 80 |
| 100 | 3 | 2008 | 3 | 2008 | 90 |
| 100 | 4 | 2008 | 4 | 2008 | 80 |
| 200 | 1 | 2008 | 2 | 2008 | 80 |
| 200 | 3 | 2008 | 3 | 2008 | 90 |
| 200 | 4 | 2008 | 4 | 2008 | 80 |
+-----+------+------+------+------+------+
By joining back we get the output the OP wants.
Not that we have to join back. As it happens, our absolute_month function is bi-directional, so we can just recalculate the year and offset month from it.
First, lets take care of adding the "end cap" month:
> create or replace view cm_capped_result as
select eid, am,
coalesce(
last_am - 1,
(select max(b.am) from cm_abs_month b where b.eid = a.eid)
) as last_am, v
from cm_result_data a;
And now we get the data, formatted per the OP:
select eid,
( (am - 1) % 12 ) + 1 as sm,
floor( ( am - 1 ) / 12 ) as sy,
( (last_am - 1) % 12 ) + 1 as em,
floor( ( last_am - 1 ) / 12 ) as ey, v
from cm_capped_result
order by 1, 3, 2, 5, 4;
+-----+------+------+------+------+------+
| eid | sm | sy | em | ey | v |
+-----+------+------+------+------+------+
| 100 | 1 | 2008 | 2 | 2008 | 80 |
| 100 | 3 | 2008 | 3 | 2008 | 90 |
| 100 | 4 | 2008 | 4 | 2008 | 80 |
| 200 | 1 | 2008 | 2 | 2008 | 80 |
| 200 | 3 | 2008 | 3 | 2008 | 90 |
| 200 | 4 | 2008 | 4 | 2008 | 80 |
+-----+------+------+------+------+------+
And there's the data the OP wants. All in SQL that should run on any RDBMS, and is decomposed into simple, easy to understand and easy to test views.
Is is better to rejoin or to recalculate? I'll leave that (it's a trick question) to the reader.
(If your RDBMS doesn't allow group bys in views, you'll have to join first and then group, or group and then pull in the month and year with correlated subqueries. This is left as an exercise for the reader.)
Jonathan Leffler asks in the comments,
What happens with your query if there
are gaps in the data (say there's an
entry for 2007-12 with value 80, and
another for 2007-10, but not one for
2007-11? The question isn't clear what
should happen there.
Well, you're exactly right, the OP doesn't specify. Perhaps there's an (unmentioned) pre-condition that there are no gaps. In the absence of a requirement, we shouldn't try to code around something that might not be there. But, the fact is, gaps make the "joining back" strategy fail; the "recalculate" strategy doesn't fail under those conditions. I'd say more, but that would reveal the trick in the trick question I alluded to above.