Sql技巧:空值用序列之前的最近的非空值去补
有下表,表名p_detail
p_id | action | action_time | dept | transfer_num |
---|---|---|---|---|
10001 | question | 2023-01-01 00:10:14 | dept_1 | 1 |
10001 | reply | 2023-01-01 00:11:14 | 1 | |
10001 | transfer | 2023-01-01 00:12:14 | dept_2 | 2 |
10001 | reply | 2023-01-01 00:13:14 | 2 | |
10001 | reply | 2023-01-01 00:17:14 | 2 | |
10001 | reply | 2023-01-01 00:13:14 | 2 | |
10001 | transfer | 2023-01-01 00:13:14 | dept_3 | 3 |
10001 | reply | 2023-01-01 00:13:14 | 3 | |
10001 | transfer | 2023-01-01 00:13:14 | dept_2 | 4 |
10001 | reply | 2023-01-01 00:13:14 | 4 | |
10002 | question | 2023-01-01 00:12:20 | dept_3 | 1 |
10002 | transfer | 2023-01-01 00:13:20 | 1 | |
10002 | reply | 2023-01-01 00:18:20 | 1 |
该表已经按照p_id,action_time排序
目标:要将dept列的空值补全,规则是用上个非空值去补。比如第2行的空值应该补成dept_1,第5行的空值应该补成dept_2。
方法总结
目前已知5种方法,简要总结如下:
方法 | 简要说明 | 方法优势 | 方法劣势 |
---|---|---|---|
方法1 | 开窗函数直接取,需要忽略空值 | 效率最高、代码量最少 | 很多数据库语言不支持开窗函数里忽略空值 |
方法2 | 做分组的辅助列,再p_id,辅助列聚合开窗用max取 | 代码量少,可读性好,执行效率高,几乎所有数据库都支持 | |
方法3 | 在select后面取字段时,用子查询关联原表,并限制死只取1个值 | 效率比较高,代码量较少 | 很多数据库语言不支持 |
方法4 | 做分组的辅助列,将每个p_id对应的的dept合并为数组,然后再按辅助列的顺序将数组拆开。 | 适用大多数sql数据库,执行效率还可以 | 代码量有点多,可读性也差 |
方法5 | 自关联笛卡尔积,然后原表每一行扩展成多行后排序取最后一行 | 执行效率最低 |
方法1
开窗函数直接取,需要忽略空值
LAST_VALUE(dept IGNORE NULLS) OVER (