SQL:如何获得忽略空值的 first_value 作为聚合？

Question

我有一个表用作员工、部门和公司设置的分层数据集。员工可以在任何部门工作，当他们这样做时，如果他们没有指定，他们将继承部门设置。更具体的设置胜出，我想编写一个查询来获取特定员工/部门对的设置。

settings 表有一个可以为空的employeeid 和也可以为null 的departmentid。如果两者都为空，则为公司范围设置的行。对“nvl(employeeid,0) 和 nvl(departmentid,0)”存在唯一约束。

设置示例数据集:

employeeid    departmentid    address        phone
null          null            123 Corp Dr.   800-555-1212
10            null            1 ABC Ave.     null
null          1               2 Dept Rd.     null
null          2               3 Dept Rd.     617-555-1212           

当我对员工 10 和部门 1 运行查询时，我应该得到一行:地址 = 1 ABC Ave，电话 = 800-555-1212

对于员工 10 和部门 2，我应该得到更新后的电话号码:地址 = 1 ABC Ave，电话 = 617-555-1212

到目前为止，我能做的最好的事情是在表格上使用 first_value，忽略空值，并按我添加的优先级排序。问题是 first_value over 不是聚合的，所以我需要一个单独的外部查询来选择特定的优先级。在我看来，这似乎是我应该能够聚合的东西。

select
    address,
    phone
from (
    select
        precedence,
        first_value(address ignore nulls) over (order by precedence) address,
        first_value(phone ignore nulls) over (order by precedence) phone
    from (
        select
            1 precedence,
            *
        from
            settings
        where
            settings.employeeid = ?
            and settings.departmentid is null
        union
        select
            2 precedence,
            *
        from
            settings
        where
            settings.departmentid = ?
            and settings.employeeid is null
        union
        select
            3 precedence,
            *
        from
            settings
        where
            settings.departmentid is null
            and settings.employeeid is null
    )
)
where
    precedence = 3

这得到了正确的答案，但我觉得好像应该有一种方法可以在中间查询中将 first_values 汇总为聚合并删除外部查询，并且可能只依赖联合的显式排序而不是引入一个优先列，尽管这不太重要。

我为此使用 Oracle 11。

Answer 1

您可以使用 keep dense rank 和单层内联 View 来做到这一点，它只需要访问一次表——而不是联合，您可以使用一个案例来决定基表中每个相关行的偏好级别:

select min(address) keep (dense_rank first
    order by case when address is null then 1 else 0 end, preference) as address,
  min(phone) keep (dense_rank first
    order by case when phone is null then 1 else 0 end, preference) as phone
from (
  select s.address, s.phone, case when s.employeeid is not null then 1
    when s.employeeid is null and s.departmentid is not null then 2
    else 3 end as preference
  from settings s
  where s.employeeid = 10
  or (s.employeeid is null and s.departmentid = 2)
  or (s.employeeid is null and s.departmentid is null)
);

ADDRESS      PHONE      
------------ ------------
1 ABC Ave.   617-555-1212

... 当然，更改 = 10 和 = 2 以绑定(bind)变量占位符。

传入员工 1 和部门 10 得到:

ADDRESS      PHONE      
------------ ------------
1 ABC Ave.   800-555-1212

您仍然可以改用 first_value:

first_value(address) over (order by case when address is null then 1 else 0 end,
  preference) as address,

...但是您必须使用 distinct 来删除重复项。