聚合SQL中的值

Question

Im试图使用下面子查询中的窗口函数，根据是(Y(、否(N(或无数据(N/D(聚合客户破产的输出值。例如，如果出现边缘情况，在一条记录中，Customer被归类为未破产(N(，但在同一CDate上的另一条记录中将其归类为无数据(N/D(，我应该得到最终的聚合输出值N/D，但它给了我N，因为我在这里所做的是将客户记录按IsBankrupt升序(asc(划分。它背后的逻辑应该被实现：

Y and Y = Y;
Y and N = Y;
N and N = N;
Y and N/D = Y;
N and N/D = N/D

with sample as (
select date('2020-12-32') as CDate, 123 as CustomerID, 'N/D' as IsBankrupt
union all
select date('2020-12-32') as CDate, 123 as CustomerID, 'N' as IsBankrupt)
select CDate, CustomerID, IsBankrupt, case when CustomerID = 123 then 'N/D' end as ExpectedResult
from
(
select CDate, CustomerID, IsBankrupt,
row_number() over (partition by CustomerID, CDate order by IsBankrupt asc) as flag
from sample
) from subsample
where flag = 1

输出：

客户ID22020-12-31123

Answer 1

我建议聚合：

select cdate, customerid,
(case when sum(case when IsBankrupt = 'Y' then 1 else 0 end) > 0
then 'Y'
when sum(case when IsBankrupt = 'N/D' then 1 else 0 end) > 0
then 'N/D'
else 'N'
end) as new_flag
from t
group by cdate, customerid;

如果您不喜欢嵌套的case表达式，您实际上可以根据值的顺序来执行此操作：

select cdate, customerid,
max(IsBankrupt) as new_flag
from t
group by cdate, customerid;