我已经编写了一系列两个相关的case语句(x4场景),由于原始数据的设置方式,我得到了每个惟一id的两个重复行。我将为ladder\u name和ladder\u value列生成一个单独的行,而实际上它们是相互关联的,只应组合生成。
我不知道如何调整case语句,使每个惟一id只能得到一行。
代码:
SELECT
# overall
sc.salesforce_id,
# first trigger
member_id_lh1,
ladder_config_id_lh1,
trigger_name_lh1,
trigger_record_id_lh1,
date_trigger_event_lh1,
ladder_name_lh1,
ladder_value_lh1,
ladder_change_lh1,
# second trigger
member_id_lh2,
trigger_name_lh2,
trigger_record_id_lh2,
date_trigger_event_lh2,
ladder_name_lh2,
ladder_value_lh2,
ladder_change_lh2,
days_spent,
rank,
# regions
#placeholder for now
#demographics
ar.`name` as alumni_region_name,
gender,
is_parent,
is_teacher,
first_generation_american,
first_generation_college_going,
lgbtq,
veteran,
person_of_color,
low_income_background,
registered_to_vote,
contact_in_ri_supported_region,
ethnicity,
# current ladder level
ladder_engagement as current_engagement_level,
ladder_advocacy as current_advocacy_level,
ladder_elected as current_elected_level,
ladder_policy as current_policy_level,
ladder_organizing as current_organizing_level,
ladder_collective as current_collective_level,
# Count - gain and loss
case
when (ladder_value_lh2 > ladder_value_lh1 and ladder_value_lh2 is not null) then 1
else -1
end as moved_up_down,
#referring ladder level
case
when ladder_value_lh2 > ladder_value_lh1 and ladder_value_lh2 is not null then ladder_name_lh2
else ladder_name_lh1
end as gain_moved_to_from_ladder_name,
case
when ladder_value_lh2 > ladder_value_lh1 and ladder_value_lh2 is not null then ladder_value_lh2
else ladder_value_lh1
end as gain_moved_to_from_ladder_level,
case
when ladder_value_lh2 < ladder_value_lh1 and ladder_value_lh2 is not null then ladder_name_lh2
else ladder_name_lh1
end as loss_moved_to_from_ladder_name,
case
when ladder_value_lh2 < ladder_value_lh1 and ladder_value_lh2 is not null then ladder_value_lh2
else ladder_value_lh1
end as loss_moved_to_from_ladder_level,
#referring trigger level
case
when ladder_value_lh2 > ladder_value_lh1 and ladder_value_lh2 is not null then trigger_name_lh2
else trigger_name_lh1
end as gain_moved_to_from_trigger_name,
case
when ladder_value_lh2 > ladder_value_lh1 and ladder_value_lh2 is not null then ladder_value_lh2
else ladder_value_lh1
end as gain_moved_to_from_trigger_level,
case
when ladder_value_lh2 < ladder_value_lh1 and ladder_value_lh2 is not null then trigger_name_lh2
else trigger_name_lh1
end as loss_moved_to_from_trigger_name,
case
when ladder_value_lh2 < ladder_value_lh1 and ladder_value_lh2 is not null then ladder_value_lh2
else ladder_value_lh1
end as loss_moved_to_from_trigger_level
from leeds_new.salesforce_contacts as sc
inner join cte_ladder_history_join_lh1_lh2_current_trigger_record on sc.salesforce_id = cte_ladder_history_join_lh1_lh2_current_trigger_record.member_id_lh1
inner join leeds_new.salesforce_alumni_regions_federated as ar on sc.alumni_region_id = ar.alumni_region_id;
结果:
1条答案
按热度按时间xn1cxnb41#
如果两行完全相同(看起来是这样),您可以使用
扣除条款。