使用分区创建新列时，我在新列数据中得到了 ?

Question

我有两个表exam_table和emp_table。在这里，我想使用左外部连接连接两个表，并且我想使用右表中的列创建一个名为new_column的新列exam_completed_date

select id, exam_completed_date from exam_table;

id exam_completed_date 
0  12-01-2019
1  12-12-2019

select id, week_end_date from emp_table where id=0;

id week_end_date
0  11-29-2019
0  11-30-2019
0  12-31-2019
0  12-01-2019
0  12-02-2019
0  12-03-2019
0  12-04-2019

当我使用左外连接连接两个表时，我在数据中得到 ？

，如下所示，

select id, week_end_date, exam_completed_date ,
max(exam_completed_date) over (partition by id order by week_end_date) as newcolumn
from emp_table emp left outer join 
exam_table exam 
on (exam.id=emp.id and exam.exam_completed_date=emp.week_end_date)
where id=0

id week_end_date exam_completed_date  new_column
0  11-29-2019          ?                  ?
0  11-30-2019          ?                  ?
0  12-31-2019          ?                  ?
0  12-01-2019     12-01-2019           12-01-2019
0  12-02-2019          ?               12-01-2019
0  12-03-2019          ?               12-01-2019 
0  12-04-2019          ?               12-01-2019  

如何消除?并替换为 null？

Answer 1

你正在使用窗口函数，除非你指定，否则它永远不会返回?，你可能会收到null值，所以你可以用coalesce()替换：

coalesce(max(exam_completed_date) over (partition by id order by week_end_date), '')