oracle - 如何在 Oracle 中汇总具有重复值的一系列行？

Question

我有一个 Oracle 11 数据库表，用于跟踪每个部门的库存项目状态。

ITEM_NUMBER     DEPT_NO       STATUS-----------     ---------     ---------1               AAA           OK2               AAA           OK3               AAA           MISSING4               BBB           OK5               BBB           OK6               AAA           OK7               AAA           OK

我想创建一个 View ，按部门和状态显示每个项目范围。如果范围内的任何项目是 MISSING，状态应该是 MISSING，否则，它应该是 OK。

在上面的例子中， View 输出应该是:

START   END     DEPT_NO   STATUS------  -----   -------   ------------1       3       AAA       MISSING4       5       BBB       OK6       7       AAA       OK

如果部门只有一组记录，这将是一件简单的事情 - 然而，同一个部门可以出现在不同的范围内，所以用 MIN/MAX 进行 GROUP BY 是行不通的，因为它最终会汇总部门 AAA 的两个范围作为一个:

select dept_no, min(item_number), max(item_number), min(status)from inventorygroup by dept_noSTART   END     DEPT_NO   STATUS------  -----   -------   ------------1       7       AAA       MISSING4       5       BBB       OK

这可以使用数据库 View 来完成还是太复杂了？

Answer 1

您可以使用窗口函数解决此问题，但要分层次。第一种是使用 lag() 来确定新“组”部门的开始位置。第二个是这个值的累加和。累积总和用作分组 ID，然后可用于聚合。

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;