为来自不同表的两个独立操作运行多个子查询(相关)，并连接到一个表中

Question

我正在尝试使用两个子查询，但找不到正确的方法，我的以下查询不是正确的方法。请帮助我了解如何使用创建列revenue和spend的子查询。一个子查询有效，但如何同时包含这两个子查询。同样的逻辑也可以通过joins以更少的执行时间导出吗？

select country, location, postalcode, MAX(spend), MAX(revenue)
from (select a.*,
(select SUM(r.revenue)
from return r
where r.uid = a.uid and
r.dt >= a.dt - interval 10 minute and
r.dt <= a.dt + interval 10 minute and
r.cat_id in ('1200') and

) as revenue
(select SUM(spend)
from invest a
where r.uid = a.uid and
a.category = '433'
a.cat_id in ('0', '1200') and
a.event in ('begin')
) as spend
from invest a
where a.event in ('show1', 'show2', 'begin') and 
a.category = '433' and

) a
group by country, location, postalcode

同样的逻辑也可以通过joins以更少的执行时间导出吗？

**Invest Table**
dt                  user cat_id cat location  postal     event    spent
2020-11-01 22:12:25  1     0    A      US      X12        Show      2
2020-11-01 22:12:25  1     0    A      US      X12        Show      2 (duplicate also in table)
2020-11-01 22:12:25  1     6    A      US      X12        Mid      null
2020-11-01 22:13:20  2     0    B      UK      L23        Show      2
2020-11-01 22:15:24  2     3    B      UK      L23        End      null

**Revenue table**
dt                     user  cat_id   revenue
2020-11-01 22:14:45     1      6        null
2020-11-01 22:13:20     2      3        3

想要创建最终表格(通过汇总每个"邮政"区域的收入(：

location   postal   spend  revenue returns
UK          X12      2       0        0
US          L23      2       3        3/2=1.5  

Answer 1

其中一个子查询(支出(可以完全删除：

select country, location, postalcode,
SUM(case when a.event= 'begin' and a.cat_id in ('0', '1200') then spend end) as spend,
(select SUM(r.revenue)
from return r
where r.uid = a.uid and
r.dt >= a.dt - interval 10 minute and
r.dt <= a.dt + interval 10 minute and
r.pixel_id in ('1200') and -------------why is this and ?

) as revenue
from invest a
where a.event in ('show1', 'show2', 'begin') and 
a.category = '433' 

group by country, location, postalcode

可以加入第二个子查询：

select country, location, postalcode,
SUM(case when a.event= 'begin' and a.cat_id in ('0', '1200') then spend end) as spend,
b.revenue
from invest a 
left join                  
(select SUM(r.revenue) revenue, dt, uid
from return r
where
r.pixel_id in ('1200')
group by dt

) b ON b.uid = a.uid and
b.dt >= a.dt - interval 10 minute and
b.dt <= a.dt + interval 10 minute 
where a.event in ('show1', 'show2', 'begin') and 
a.category = '433' 

group by country, location, postalcode, b.revenue