Redshift中排名窗口函数对NULL值的处理疑问

测试场景说明 #

建表语句

CREATE TABLE public.Employee
("ID" numeric(18,0),
Name VARCHAR(100),
RoleName VARCHAR(100),
Salary NUMERIC(18,0),
Salary2 numeric(20,0)
);

插入测试数据

INSERT INTO public.Employee("ID",NAME,ROLENAME,SALARY,SALARY2) VALUES
(1, 'Alex', 'SuperAdmin', null,null ),
(2,'Thomas', 'Admin', 80000,null ),
(3, 'Peter', 'SuperAdmin', 95000,20444 ),
(4, 'Sid', 'SuperAdmin', 70000,45477),
(5, 'Maria', 'Admin', 60000,7685 ),
(6, 'Andrew', 'Manager', 50000,686754 ),
(7, 'Fedric', 'SuperAdmin', null,46342 ),
(8, 'Anton', 'Supervisor', null,967564 ),
(9, 'Mandy', 'Manager', 30000,7563452 )

执行查询

select ID,(salary - salary2) as sum_val ,rank() over (order by sum_val desc) as total_profit
from public.employee

查询结果

id  sum_val total_profit
1       1
2       1
7       1
8       1
3   74556   5
5   52315   6
4   24523   7
6   -636754 8
9   -7533452    9

问题解答 #

1. 为何NULL值会自动排在排序结果的首位？ #

Redshift遵循默认的SQL排序逻辑：NULL被视为未知值。在**降序（DESC）排序时，系统会判定NULL比任何非NULL的数值都“大”，因此会排在结果集的最前面；如果是升序（ASC）**排序，NULL则会被判定为比任何非NULL数值都“小”，排在结果集的最后。

你查询中的sum_val由salary - salary2计算而来，只要其中任意一个字段为NULL，计算结果就是NULL。这些NULL值在order by sum_val desc的规则下，全部被当作最大的“值”，因此它们的排名都是1。

2. 是否可以在窗口函数中自定义NULL值的排序位置？ #

完全可以。Redshift支持在ORDER BY子句中通过NULLS FIRST或NULLS LAST关键字，明确指定NULL值的排序位置，这个规则在窗口函数的排序中同样适用。

示例1：让NULL值排在降序排序的末尾

修改查询语句，添加NULLS LAST：

select ID,(salary - salary2) as sum_val ,rank() over (order by sum_val desc NULLS LAST) as total_profit
from public.employee

此时NULL值会被放到结果的最后，非NULL的数值按降序排列在前。

示例2：让NULL值排在升序排序的首位

如果使用升序排序，想让NULL值排在前面，可添加NULLS FIRST：

select ID,(salary - salary2) as sum_val ,rank() over (order by sum_val asc NULLS FIRST) as total_profit
from public.employee

内容的提问来源于stack exchange，提问作者Whiteflames