重新编码/转换SQL值为新的列从链接数据:为什么是CASE时返回多个值?

Question

我处理来自多个表的大量链接数据。因此，我在重复数据删除和以更有意义的方式将值重新编码到新列方面遇到了一些挑战。

我的核心数据集是一个以行表示的人级别记录列表。但是，关联的数据根据每个人预订活动的日期、他们是否出席以及他们是否是我们组织的成员，包括每个人的多行。通常会有多个预订。有可能失去会员身份并继续参加活动/取消/等，但我们感兴趣的是他们是否曾经是会员，如果不是，这是他们与我们组织有过的最高级别的联系。

简而言之:如果它们曾经是成员，则需要优先考虑。

select distinct 
a.ticketnumber
a.id
-- (many additional columns from multiple tables here)
case
when b.Went_Member >=1 then 'Member'
when b.Went_NonMember >=1 then 'Attended but not member'
when b.Going_NonMember >=1 then 'Going but not member'
when b.OptOut='1' then 'Opt Out'
when b.Cancelled >=1 then 'Cancelled'
when c.MemberStatus = '9' then 'Member'
when c.MemberStatus = '6' then 'Attended but not member'
when c.DateBooked > current_timestamp then 'Going but not member'
when c.OptOut='1' then 'Opt out'
when c.MemberStatus = '8' then 'Cancelled'
end [NewMemberStatus]
from table1 a
left join TableWithMemberStatus1 b on a.id = b.id
left join TableWithMemberStatus2 c on a.id = c.id
-- (further left joins to additional tables here)
order by a.ticketnumber

表b更准确，因为这些是我们的内部记录，而表c来自第三方。令人恼火的是，C中的数字与我们决定的顺序不同，所以我不能让它为每个ID选择最大值。

我的印象是CASE沿着WHEN语句的列表向下并返回第一个匹配的值，但这将产生多行。例如:

tbody> <<tr>

ID	NewMemberStatus
989898	空
989898	取消
777777	会员
111111	取消
111111	会员

在您的查询中，您显示了所有行(所有预订)，因为没有WHERE子句，也没有聚合。但是您只希望每个人有一个结果行。

您希望从内部表中获取一个人的最佳状态。如果在内部表中没有该人员的条目，则需要从第三方表中获取其最佳状态。通过按人聚合内部表和第三方表中的行，可以获得最佳状态。然后加入这个人。

我使用状态号，因为这些可以排序(我使用1表示最佳状态(成员)，因此我查找最小状态)。最后，我将找到的数字替换为相关文本(例如:"成员"为状态1)。

select
p.*,
case coalesce(i.best_status, tp.best_status)
when 1 then 'Member'
when 2 then 'Attended but not member'
when 3 then 'Going but not member'
when 4 then 'Opt out'
when 5 then 'Cancelled'
else 'unknown'
end as status
from person p
left join
(
select
person_id,
min(case when went_member >= 1 then 1
when went_nonmember >= 1 then 2
when going_nonmember >= 1 then 3
when optout = 1 then 4
when cancelled >= 1 then 5
end) as best_status
from internal_table
group by person_id
) i on i.person_id = p.person_id
left join
(
select
person_id,
min(case when MemberStatus = 9 then 1
when MemberStatus = 6 then 2
when DateBooked > current_timestamp then 3
when optout = 1 then 4
when memberstatus = 8 then 5
end) as best_status
from thirdparty_table
group by person_id
) tp on tp.person_id = p.person_id
order by p.person_id;