CODEWITHSUNDEEP
oracle-databaseplsql
Anand Kamal
Anand Kamal

Reputation: 43

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

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?

Upvotes: 4

Views: 286

Answers (4)

Sebas
Sebas

Reputation: 21542

Ok, this should work

ps: end and start are reserved words and I could not use them. I just added a letter, but this is still a clear result...

SELECT
    i.sstart,
    i.eend,
    i.dept_no,
    MIN(inventory.status) AS status
FROM (
    SELECT 
        DECODE(r, 1, 1, (LAG(max_range_item, 1) OVER(ORDER BY r) + 1)  ) AS sstart,
        max_range_item AS eend,
        dept_no
    FROM (
        SELECT
            rownum AS r,
            item_number AS max_range_item,
            dept_no
        FROM (
                SELECT 
                    item_number, 
                    dept_no, 
                    LEAD(dept_no, 1) OVER (ORDER BY item_number) AS next_dept_no
                FROM
                    inventory
                ORDER BY
                    item_number ASC
              ) i2
        WHERE
            dept_no != next_dept_no
        OR  next_dept_no IS NULL
        ORDER BY
            item_number ASC
        ) inv
    ) i
        JOIN inventory ON inventory.item_number BETWEEN i.sstart AND i.eend
GROUP BY
    i.sstart,
    i.eend,
    i.dept_no
ORDER BY
    i.sstart ASC

Fiddle: http://sqlfiddle.com/#!4/944ff3/17

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270653

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;

Upvotes: 1

krokodilko
krokodilko

Reputation: 36127

Another alternative (works on 11g)

WITH qry( item_number, dept_no, status, range_id ) as (
    select item_number, dept_no, status, 1 range_id
    from inventory where item_number = 1
    UNION ALL
    select src.item_number, src.dept_no, src.status,  
         case when src.dept_no <> qry.dept_no
              then qry.range_id + 1 else qry.range_id
         end
    from inventory src
    join qry on src.item_number = qry.item_number + 1
)
SELECT range_id, dept_no, min(item_number), max(item_number), min(status)
from qry
group by range_id, dept_no
order by 1
;

  RANGE_ID DEPT_NO MIN(ITEM_NUMBER) MAX(ITEM_NUMBER) MIN(STATUS)
---------- ------- ---------------- ---------------- -----------
         1 AAA                    1                3 MISSING     
         2 BBB                    4                5 OK          
         3 AAA                    6                7 OK

Upvotes: 1

ChrisProsser
ChrisProsser

Reputation: 13108

If this can be done using a database view I think it would be very complex. One option you could look into would be to try to create an inner query which is the same table joined to itself on item_number = item_number+1 and add a column that detects where a.item_number != b.item number to determine group boundaries and then use this group boundaries column as the group by in the outer query.

However, the problem is clearly better suited to a procedural approach looping through one row at a time. So I would recommend doing this as a pl/sql procedure instead if you can.

Upvotes: 0

Related Questions