Postgresql 案例，如果在一个组中不能属于另一个组

Question

由于某种原因，我正在为本应是非常基本的事情而挣扎。我有一些用户的项目中包含多个操作。我需要根据项目中是否发生过一次操作，将项目分为两组。如果动作"；扫描的"；已经发生的项目是"；扫描的"；所有其他项目都是"；未屏蔽"；。

数据示例：

user_id    project_id    action
012081f     9a5a30e     modified
012081f     9a5a30e     loaded
012081f     9a5a30e     saved  
03011d2     246b52e     scanned
03011d2     246b52e     loaded 
03011d2     246b52e     saved 
04345a6     afb63ef     modified 
04345a6     afb63ef     modified
04345a6     afb63ef     scanned

我最终想要的是：

user_id    project_id   project_type    
012081f     9a5a30e     not_scanned       
03011d2     246b52e     scanned
04345a6     afb63ef     scanned

我尝试过这个案例，但因为同一个项目有很多行有不同的操作，所以project_id最终都在not_scanted和scaned组中

select 
user_id,
project_id,
case    
when action = 'scanned' then 'scanned'
else 'not_scanned'
end as project_type
from base_table
where user_id is not Null
group by 1,2,3

Answer 1

许多不同的选项来解决这个问题，有两个例子：

SELECT  user_id
,   project_id
,   COALESCE((ARRAY_AGG(DISTINCT action) FILTER(WHERE action = 'scanned'))[1], 'not_scanned')
FROM    base_table
GROUP BY
1,2;

另一种选择是使用DISTINCT ON:

SELECT  DISTINCT ON (user_id,project_id)
user_id
,   project_id
,   CASE WHEN action <> 'scanned' THEN 'not_scanned'
ELSE action
END AS action
FROM    base_table
ORDER BY
user_id
,   project_id
,   action <> 'scanned';

也许还有更多的方法可以解决这个问题，也许更聪明，也许更快。