sql - 带有 in group by 子句或 partition by 的 case 语句

Question

Customer   Decision      req_date       salary
   A       Approved     2017-06-13       1000
   A       Approved     2017-06-13       1000
   A       Pending      2017-06-13       500
   B       Pending      2017-10-23       800     
   B       final_stage  2017-10-20       400
   B       final_stage  2017-03-19       400

对于给定的客户 ID，

案例1：如果决定被批准，则保留该客户的所有批准记录并删除其他记录。

案例 2：如果客户没有任何已批准的决定，则根据最近的“req_date”保留特定客户的记录，并在最近的“req_date”的 5 天内记录，并根据最低工资选择记录

Customer   Decision      req_date       salary  
   A       Approved     2017-06-13       1000
   A       Approved     2017-05-13       1000
   B       final_stage  2017-10-20       400

标签： sql

Answer 1

必须分三个步骤过滤行。我将使用 cte 来计算聚合和两个查询的联合，用于批准和未批准的客户：

with cte as (
    select 
        customer, 
        bool_or(decision = 'Approved') as approved, 
        max(req_date) as last_date
    from my_table
    group by 1
)
select customer, decision, req_date, salary
from my_table
join cte using(customer)
where approved and decision = 'Approved'
union all (
    select distinct on(customer) customer, decision, req_date, salary
    from my_table
    join cte using(customer)
    where not approved 
    and req_date between last_date- '5day'::interval and last_date
    order by customer, salary
    )

DbFiddle。