SQL OVER（Partition by）- 处理空值

Question

I have a following scenario:

Table Employees:

First Name | Last Name | Department | Salary
-----------|-----------|------------|---------
John       | Doe       | Finance    | 20
John       | Doe       | R&D        | 20
John       | null      | Finance    | 20
John       | long      | Finance    | 20

对于每个(名字，姓氏)，我希望只有1行，除非姓氏为空，那么我只想要1行(名字，null)。

对于上面的例子，结果如下：

First Name | Last Name | Department | Salary
-----------|-----------|------------|---------
John       | null      | Finance    | 20

但如果我没有那个记录，结果应该是：

First Name | Last Name | Department | Salary
-----------|-----------|------------|---------
John       | Doe       | R&D        | 20
John       | long      | Finance    | 20

我猜这个答案涉及到一些 Partition By，但是我不确定具体在哪里。

目前我得出了以下结论：

SELECT FirstName,LastName, DEPARTMENT,Salary,RK FROM 
(
select * from 
    SELECT EXT.*, 
    ROW_NUMBER() OVER(PARTITION BY EXT.FirstName,EXT.LastName 
    ORDER BY rownum ASC) AS RK
      FROM Employees EXT   
)
WHERE RK = 1 ;

感谢您！

- Nati

---

为什么你想要来自研发部门的John Doe的记录而不是来自财务部门的记录？这是任意的吗？ - Conrad Frix

Answer 1

您的问题在于 PARTITION 子句。您希望获取所有有姓氏的人的名字，除非该名字至少有一个姓氏为 NULL，则只获取该名字下的 NULL 姓氏。

解决方法是使用 RANK()，而不是 ROW_NUMBER()。RANK() 不会创建连续的列表；相反，具有相等值的行将获得相同的排名。
参考链接：http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions141.htm#SQLRF00690

select firstname, lastname, department, salary, rk 
  from ( select a.*
              , rank() over ( partition by firstname
                                  order by case when lastname is null then 0 
                                                else 1 
                                           end
                                           ) as rnk
           from employees a
                )
where rnk = 1

这是通过使姓氏的存在变得相关而不是姓氏本身来实现的。

还有两个要点：

1. 你使用了没有括号的嵌套选择。这样做是行不通的。
2. 按ROWNUM排序没有意义。根据定义，rownum返回语句返回的顺序中的行，这意味着行将始终按ROWNUM的顺序排列。

- Ben

---

1. 我按行号排序，因为我不关心顺序。
2. 正如你所说 - 你想要每个有姓氏的名字，除非至少有一个该名字的姓氏为空，那么你只想要那些有空姓氏的名字。 但是我想要每个（名字，姓氏）对都独立，而这个答案并没有做到。无论如何，谢谢。

- Nati

@Nati，你在问题中没有提到这一点... 你可以简单地在外部查询中添加一个DISTINCT，它就会起作用。 - Ben

它仍然无法完成任务，因为它会区分整行，而不是 fname、lname 的“夫妻对”。 - Nati

Answer 2

类似这样的东西：

SQL> create table person
  2  (
  3    fname varchar2(10),
  4    lname varchar2(10),
  5     dept  varchar2(10),
  6     sal   number
  7  );

Table created.

SQL> insert into person values ('John', 'Doe', 'Finance', 20);

1 row created.

SQL> insert into person values ('John', 'Doe', 'R&D', 20);

1 row created.

SQL> insert into person values ('John', '', 'Finance', 20);

1 row created.

SQL> insert into person values ('John', 'Long', 'Finance', 20);

1 row created.

SQL> insert into person values ('Paul', 'Doe', 'R&D', 30);

1 row created.

SQL> insert into person values ('Paul', 'Doe', 'Finance', 30);

1 row created.

SQL> insert into person values ('Paul', 'Long', 'Finance', 30);

1 row created.

SQL> select fname, lname, dept, sal
  2    from (select fname, lname, dept, sal,has_null,
  3                  row_number() over(partition by fname,
  4                                                  case when has_null = 'N' then lname else null end
  5                                    order by lname desc nulls first) rn
  6             from (select fname, lname,
  7                           nvl(max(case  when lname is null then 'Y'
  8                               end) over(partition by fname), 'N') has_null, dept, sal
  9                      from person))
 10   where rn = 1;

FNAME      LNAME      DEPT              SAL
---------- ---------- ---------- ----------
John                  Finance            20
Paul       Doe        R&D                30
Paul       Long       Finance            30

- DazzaL

---

选择 fname，lname，dept，sal 从
(选择 fname，lname，dept，sal， first_value(lname) over(partition by fname order by lname nulls first) null_domain， row_number() over (partition by fname,lname order by fname) r from person ) where ((null_domain is null and lname is null) or null_domain is not null) and r=1;相同的答案-更好的性能。 - Nati

Answer 3

这个查询可以达到同样的效果，但是性能更好。

SELECT fname, 
       lname, 
       dept, 
       sal 
FROM   (SELECT fname, 
               lname, 
               dept, 
               sal, 
               First_value(lname) 
                 OVER( 
                   partition BY fname 
                   ORDER BY lname nulls first) null_domain, 
               Row_number() 
                 OVER ( 
                   partition BY fname, lname 
                   ORDER BY fname)             r 
        FROM   person) 
    WHERE  ( ( null_domain IS NULL 
           AND lname IS NULL ) 
          OR null_domain IS NOT NULL ) 
        AND r = 1; 

- Nati