优雅地求和AWS Athena中的多个列，不包括null

Question

我必须通过在AWS Athena中添加一些其他列来创建total_costs、total_proceeds和total_net_loss列。我还必须用zeros替换nulls，否则结果将为空。我尝试使用sum()函数，但它只适用于单列。下面的查询完成了我想要做的事情，但看起来非常不礼貌。有没有其他更优雅的方法来对Athena中的列求和？

SELECT loan_identifier,
monthly_reporting_period,
current_actual_upb, 
current_loan_delinquency_status, 
(COALESCE(foreclosure_costs,0) + COALESCE(property_preservation_and_repair_costs,0) + COALESCE(asset_recovery_costs,0) + COALESCE(miscellaneous_holding_expenses_and_credits,0) + COALESCE(associated_taxes_for_holding_property,0)) AS total_costs,
(COALESCE(net_sale_proceeds,0) + COALESCE(credit_enhancement_proceeds,0) + COALESCE(repurchase_make_whole_proceeds,0) + COALESCE(other_foreclosure_proceeds,0)) AS total_proceeds,
current_actual_upb + (current_actual_upb * ((current_interest_rate/100 - 0.0035)/12)*DATE_DIFF('day', last_paid_installement_date, disposition_date)/30) + (COALESCE(foreclosure_costs,0) + COALESCE(property_preservation_and_repair_costs,0) + COALESCE(asset_recovery_costs,0) + COALESCE(miscellaneous_holding_expenses_and_credits,0) + COALESCE(associated_taxes_for_holding_property,0)) - (COALESCE(net_sale_proceeds,0) + COALESCE(credit_enhancement_proceeds,0) + COALESCE(repurchase_make_whole_proceeds,0) + COALESCE(other_foreclosure_proceeds,0)) AS total_net_loss
FROM performance_data;

Answer 1

组合使用SUM和COALESCE，如下所示：

SELECT SUM(COALESCE(y, 0) + COALESCE(z, 0)) AS aggregate_sum_of_y_and_z
FROM (
VALUES (1, 2),
(NULL, 3),
(2, 1)) AS x (y, z)