sql - 查找从三个表的左连接获得的三列的总和

Question

我有 4 张桌子：

accounts_info
----------------------------------------------
user_id | user_type | user_page | device_type 


hostel_billing (can have duplicate user_id)
-------------------------
user_id | billing_charges

academic_billing (can have duplicate user_id)
-------------------------
user_id | academic_charges

misc_billing (can have duplicate user_id)
-------------------------
user_id | misc_charges

我想创建一个从以上 4 个表中提取数据的新表：

user_expenses 
---------------------------------------------------------------
user_id | user_type | user_page | device_type | total_charges

total_charges 将是total_charges = misc_charges + academic_charges + billing_charges. 计算总费用是我无法弄清楚的。

到目前为止，我已经能够通过此查询获得每个 user_id 的 misc_charges、academic_charges 和 billing_charges 的总和，但无法弄清楚如何总结从分别加入 hostel_billing、misc_billing 和 Academic_billing 获得的三列 hostel_charges、misc_charges 和 Academic_charges：

with user_info as (
select 
   user_id, user_type, user_page, device_type 
from accounts_info 
),

academics as (
select user_id, sum(academic_charges) as academic from academic_billing group by 1
),

hostels as (
select user_id, sum(hostel_charges) as hostel from hostel_billing group by 1
),

misc as (
select user_id, sum(misc_charges) as misc from misc_billing group by 1
)

select 
     ui.user_id, 
     ui.user_page, 
     ui.user_type, 
     ui.device_type, 
     sum(a.academic_charges),
     sum(m.misc_charges), 
     sum(h.hostel_charges)
from user info ui left join misc m on ui.user_id=m.user_id
left join hostels h on ui.user_id=h.user_id
left join academics a on ui.user_id=a.user_id
group by 1,2,3,4

标签： sqlhivebigdata

Answer 1

应该很简单，你可以试试：

with user_info as (
select 
   user_id, user_type, user_page, device_type 
from accounts_info 
),

academics as (
select user_id, sum(academic_charges) as academic from academic_billing group by 1
),

hostels as (
select user_id, sum(hostel_charges) as hostel from hostel_billing group by 1
),

misc as (
select user_id, sum(misc_charges) as misc from misc_billing group by 1
)

select 
     ui.user_id, 
     ui.user_page, 
     ui.user_type, 
     ui.device_type, 
     sum(a.academic_charges) + sum(m.misc_charges) + sum(h.hostel_charges) as total_charges
from user info ui left join misc m on ui.user_id=m.user_id
left join hostels h on ui.user_id=h.user_id
left join academics a on ui.user_id=a.user_id
group by 1,2,3,4