时间戳

首页 > 解决方案 > 汇总来自不同表的金额

sql - 汇总来自不同表的金额

问题描述

我有一张t1这样的桌子：

store_id    industry_id    cust_id    amount     gender     age
1           100            1000       1.00       M          20
2           100            1000       2.05       M          20
3           100            1000       3.15       M          20
4           200            2000       5.00       F          30
5           200            2000       6.00       F          30

另一个t2看起来像这样的表：

store_id    industry_id    cust_id    amount   
10          100            1000       10.00   
20          200            2000       11.00

假设我们要构建一个表格，其中包含每个行业中给定客户的所有交易。换句话说，是这样的：

store_id.   industry_id.   cust_id.   amount
1           100            1000       1.00
2           100            1000       2.05
3           100            1000       3.15
4           200            2000       5.00
5           200            2000       6.00
10          100            1000       10.00
20          200            2000       11.00

我试图通过在下面的查询中使用 join 和 coalesce 语句来做到这一点，但它不起作用，因为每一行都有一个用于 中的amount列的条目t1，即，coalesce 语句没有任何 NULL 值使用。使用联接执行此操作的最佳方法是什么？

SELECT
a.store_id,
a.industry_id,
a.cust_id,
COALESCE(a.amount,b.amount,0) AS amount
FROM t1 a
LEFT JOIN (SELECT store_id AS store_id_2, industry_id AS industry_id_2, cust_id AS cust_id_2, amount FROM t2) b 
ON a.cust_id=b.cust_id_2 AND a.industry_id=b.industry_id_2;

此查询导致：

store_id    industry_id    cust_id    amount     
1           100            1000       1.00  
2           100            1000       2.05  
3           100            1000       3.15  
4           200            2000       5.00 
5           200            2000       6.00

标签： sqlhiveunionhiveqlfull-outer-join

解决方案

对于这个数据集union all似乎足够好：

select store_id, industry_id, cust_id, amount from t1
union all
select store_id, industry_id, cust_id, amount from t2

我推测同一个商店/行业/客户元组可能会出现在两个表中，并且您只需要结果中的一行以及相应金额的总和。如果是这样，您可能对以下内容感兴趣full join：

select
    coalesce(t1.store_id, t2.store_id) store_id,
    coalesce(t1.industry_id, t2.industry_id) industry_id,
    coalesce(t1.cust_id, t2.cust_id) cust_id,
    coalesce(t1.amount, 0) + coalesce(t2.amount, 0) amount
from t1
full join t2 
    on t2.store = t1.store and t2.industry = t1.industry and t2.cust_id = t1.cust_id

推荐阅读