How can I summarize a range of rows with duplicate values in them in Oracle?

Question

I have an Oracle 11 database table that keeps track of the status of inventory items for each department.

ITEM_NUMBER     DEPT_NO       STATUS
-----------     ---------     ---------
1               AAA           OK
2               AAA           OK
3               AAA           MISSING
4               BBB           OK
5               BBB           OK
6               AAA           OK
7               AAA           OK

I would like to create a view that shows each range of items, by department, and the status. If any items in the range are MISSING, the status should be MISSING, otherwise, it should be OK.

In the above example, the view output should be:

START   END     DEPT_NO   STATUS
------  -----   -------   ------------
1       3       AAA       MISSING
4       5       BBB       OK
6       7       AAA       OK

This would be a simple thing to do if the departments only had one group of records - however, the same department can show up in different ranges, so doing GROUP BYs with MIN/MAX doesn't work because it ends up summarizing the two ranges for department AAA as one:

select dept_no, min(item_number), max(item_number), min(status)
from inventory
group by dept_no

START   END     DEPT_NO   STATUS
------  -----   -------   ------------
1       7       AAA       MISSING
4       5       BBB       OK

Can this be done using a database view or is it too complex?

Answer 1

You can solve this using window functions, but in to levels. The first is to use lag() to determine where a new "group" of departments begins. The second is a cumulative sum of this value. The cumulative sum works as a grouping id, which can then be used for aggregation.

select dept_no, min(item_number), max(item_number),
       (case when sum(case when status = 'MISSING' then 1 else 0 end) > 0
             then 'Missing'
             else 'Ok'
        end)
from (select i.*, sum(GroupStart) over (order by item_number) as Grouping
      from (select i.*,
                   (case when dept_no = lag(dept_no) over (order by item_number)
                         then 0 else 1
                    end) as GroupStart
            from inventory i
           ) i
     ) i
group by dept_no, grouping;