postgreSQL count DISTINCT ONCE user_id多个属性

Question

你好，我无法在以下情况下获得好的结果：

我有一张这样的表格：

UserID | Label
-------- ------
1      | Private
1      | Public
2      | Private
3      | Hidden
4      | Public
5      | Hidden

如果用户分配了以下信息，我希望发生以下情况：

私有和隐藏的待遇相同：假设商业
公共：BtoC
公共和私人和/或隐藏：两者

所以最后我有一个count(DISTINCT UserID)

Business         3
BtoC             1
both             1

我尝试使用CASE WHEN但它不起作用，我当前的总查询如下所示：

SELECT gen_month,
count(DISTINCT cu.id) as leads,
a.label
FROM generate_series(DATE_TRUNC('month', CURRENT_DATE::date - 96*INTERVAL '1 month'), CURRENT_DATE::date, '1 month') m(gen_month)
LEFT OUTER JOIN company_user AS cu
ON (date_trunc('month', cu.creation_date) = date_trunc('month', gen_month))
LEFT JOIN user u
ON u.user_id = cu.id
LEFT join user_account_status as uas
on cu.id = uas.user_id
LEFT JOIN account as a
on uas.account_id = a.id
where gen_month >= DATE_TRUNC('month',NOW() - INTERVAL '5 months')
group by  m.gen_month, a.label
order by gen_month

所以我现在的主要问题是计数在每个属性中出现一次。 当user_id显示为公共和(私人或隐藏(然后计为(不同user_id(时，如何使用户 ID 在 CASE 条件下仅计数一次？

另外：它的mySQL mariaDB和postgreSQL。但首先我会对Postgres感到满意

Answer 1

这不会在总查询中实现，但要计算每个类别的用户数，您可以：

with the_table(UserID , Label) as(
select 1      ,'Private' union all
select 1      ,'Public' union all
select 2      ,'Private' union all
select 3      ,'Hidden' union all
select 4      ,'Public' union all
select 5     ,'Hidden'  
)
select result, count(*) from (
select UserID, case when min(Label) = 'Public' then 'BtoC' when max(Label) in('Private','Hidden') then 'Business' else 'both' end as result 
from the_table
group by UserID
) t
group by result

Answer 2

with 
my_table(user_id, label) as (values
(1,'Private'),
(1,'Public'),
(2,'Private'),
(3,'Hidden'),
(4,'Public'),
(5,'Hidden')),
t as (
select
user_id,
string_agg('{'||label||'}', '') as labels
from my_table
group by user_id),
tt as (
select
user_id,
labels,
case
when
position('{Public}' in labels) > 0 and (position('{Private}' in labels) > 0 or position('{Hidden}' in labels) > 0) then 'Both'
when
position('{Private}' in labels) > 0 or position('{Hidden}' in labels) > 0 then 'Business'
when
position('{Public}' in labels) > 0 then 'BtoC'
end as kind
from t)
select kind, count(*) from tt group by kind;

对于MariaDB，请使用GROUP_CONCAT()而不是PostgreSQLstring_agg()。

请注意，case语句按出现顺序检查条件，并返回第一个满足条件的值。

PS：使用PostgreSQL的数组，条件会更优雅。