聚合日期列的前后

Question

我有两个表:db。事务和数据库。我想将其合并以创建一个输出，该输出汇总了每个销售人员雇用日期之前和每个销售人员雇用日期之后的销售额。

select * from db.transactions
index  sales_rep   sales  trx_date
1         Tom        200   9/18/2020
2         Jerry      435   6/21/2020
3         Patrick   1400   4/30/2020
4         Tom        560   5/24/2020
5         Francis    240    1/2/2021

select * from db.salesman 
index   sales_rep     hire_date    
1        Tom            8/19/2020  
2        Jerry          1/28/2020  
3        Patrick         4/6/2020  
4        Francis         9/4/2020  

我想从db中汇总销售。每个销售代表入职前后的交易。

预期输出:

index   sales_rep     hire_date    agg_sales_before_hire_date    agg_sales_after_hire_date  
1        Tom            8/19/2020         1200                           5000
2        Jerry          1/28/2020          500                            900
3        Patrick         4/6/2020         5000                            300
4        Francis         9/4/2020         2900                           1500

对于单个销售代表，计算agg_sales_before_hire_date可能是:

select tx.sales_rep, tx.sum(sales)
from db.transactions tx
inner join db.salesman sm on sm.sales_rep = tx.sales_rep
where hire_date < '8/19/2020' and sales_rep = 'Tom'
group by tx.sales_rep

PostGRESQL。我也愿意将其应用到Tableau或Python中。

Answer 1

使用CROSS JOIN LATERAL

select 
sa.sales_rep, sa.hire_date,
l.agg_sales_before_hire_date, 
l.agg_sales_after_hire_date
from salesman sa
cross join lateral
(
select 
sum(tx.sales) filter (where tx.trx_date < sa.hire_date) agg_sales_before_hire_date,
sum(tx.sales) filter (where tx.trx_date >= sa.hire_date) agg_sales_after_hire_date
from transactions tx
where tx.sales_rep = sa.sales_rep     
) l;

Answer 2

使用条件聚合:

select tx.sales_rep, 
sum(case when tx.txn_date < sm.hire_date then sales else 0 end) as before_sales,
sum(case when tx.txn_date >= sm.hire_date then sales else 0 end) as after_sales
from db.transactions tx inner join
db.salesman sm
on sm.sales_rep = tx.sales_rep
group by tx.sales_rep;

编辑:

在Postgres中，您将使用filter作为逻辑:

select tx.sales_rep, 
sum(sales) filter (where tx.txn_date < sm.hire_date) as before_sales,
sum(sales) filter (where tx.txn_date >= sm.hire_date then sales) as after_sales