I've had this problem since yesterday and I believe I finally solved it.
For each consecutive time period (month) that there is an amount, count how many buckets, up to six.
First thought was definitely Analytics. I toiled away on what became a very unwieldy query (took more than one page anyway). A whole bunch of LAGs with the same number of ever increasing CASE statements.
It took a good while to figure that out for some reason.
Once I had that figured, I needed to figure out which were consecutive. Frank Zhou is always solving puzzles with SQL and I remembered I had responded to one of his about a year ago. If you get a chance, please take a look at his site...he solves some pretty cool puzzles with SQL using the MODEL clause and analytics.
Anyway, his post, How to find the earliest start date and the latest end date for consecutive transactions in SQL was similar (and my response similar), so I found it to revisit my thinking at the time.
First, I use the LAG function to get the previous row's ID (unique) and call it PREV_ID. I use DIFF_ID in the PARTITION clause (window) and order by END_DATE; then add one to see if the months are consecutive. If that value matches the START_DATE of the current row, it's consecutive and I use LAG again to get the previous row's ID.
As you can see, I have 3 records with the PREV_ID populated.
As I am building it, I realize I keep nesting the queries, so in comes the WITH clause (when I first learned of that it was terribly difficult to search for, I didn't know it was also called subquery factoring clause...).
Much better. Note the START WITH and CONNECT BY PRIOR, I created my own heirarchical table to determine another window to PARTITION on (MIN_START_DATE of the consecutive records).
Now all I have to do is PIVOT the table (I chose not to use the new PIVOT feature) on DIFF_ID and add an analytic COUNT on my new window (MIN_START_DATE).
Table creation and data can be found here.
I have noticed that as well, my previous go at it had 15 million records.
How about the use of a virtual column that is indexed? Use the CASE/LAG statement to create it?
One of my colleagues suggested a PIPELINEd function, but it was procedural and I try to avoid that.
I do like yours as it is much more concise.
Re Laurent's query: Too sweet! I wouldn't have thought of the ROW_NUMBER - MONTHS_BETWEEN trick.
When I've done this sort of thing, I've usually flagged rows where a new bucket starts, instead of what you did (flagging rows that continue a bucket). I flag them with whatever unique ID I have handy (in your case I'd use a ROW_NUMBER).
Then in the next query/WITH clause I'd use MAX(flag) OVER (ORDER BY id RANGE UNBOUNDED PRECEDING) to get the latest row that started a bucket prior to this row.
I compared Laurent's to a full set of my data and it didn't match mine. I am going to look at it more closely later on and provide more feedback.
I was trying to figure how to flag the sliding window, but I couldn't figure out a way to do it. I was thinking something like a date, but couldn't quite get it.
I hadn't seen the RANGE UNBOUNDED clause...I'll look into that.