mysql - sql 左连接结果union右连接结果，导致重复性计算怎么解决？

Question

滑动计算场景简化

数据库环境是MySQL

因为原问题场景比较复杂，下面将其简化描述：(标题描述可能不太切题)

现在有两张表t1和t2，表结构相同，但是存储的数据不同。主键a与字段b类型均为int型。示例如下。

其中，表t1和表t2存在部分主键a相同，但是字段b值不同的数据。也有主键值在表t1中存在，但是表t2中不存在，或者主键值在表t2中存在，但是表t1中不存在。

• 表 t1 :

a	b
1	10
2	5
3	7
4	7
5	7

• 表 t2 :

a	b
3	17
4	8
5	3
6	11
7	6

现在，按主键值相同的情况下进行计算，t1.b - t2.b。如果主键a的值在对方表中不存在，则字段b取0值。查询得到如下表结构的数据。

a	t1.b - t2.b
1	10
2	5
3	-10
4	-1
5	4
6	-11
7	-6

我所想到的sql语句如下：

SELECT t1.a, t1.b - t2.b from t1 left  join t2 on t1.a = t2.a
union
SELECT t2.a, t1.b - t2.b from t1 right join t2 on t1.a = t2.a;

但是，这条sql不能解决两个问题：

• 如果主键a的值在对方表中不存在，则字段b取0值。

• 两次join会导致主键值为3,4,5的数据重复计算

怎么写sql比较好？

---

下面的sql可以解决上面的转0问题，但是仍然解决不了重复计算的问题。

SELECT t1.a, ifnull(t1.b, 0) - ifnull(t2.b, 0) from t1 left  join t2 on t1.a = t2.a
union
SELECT t2.a, ifnull(t1.b, 0) - ifnull(t2.b, 0) from t1 right join t2 on t1.a = t2.a;

---

select fa,fb from(
  SELECT t1.a as fa , ifnull(t1.b, 0) - ifnull(t2.b, 0) as fb from t1 left  join t2 on t1.a = t2.a
    union
    SELECT t2.a as fa,0-t2.b  as fb from t2 where t2.a not in (select t1.a from t1 )
) t order by t.fa

---

以上问题结束。以下是关于原问题场景的模拟及描述，有兴趣的可以看下，实现不正确，不够好的地方望指正。

滑动计算场景模拟

• 表 t3 :

a	b	c
1	10	1
2	5	2
3	7	3
4	7	4
5	7	5
3	17	6
4	8	7
5	3	8
6	11	9
7	6	10

# 简单模拟sql
select t.a , sum(t.b) from (
    select t3.a , b from t3 where c <=3
    union all
    select t3.a , 0-b from t3 where c > 7
) t group by t.a

滑动计算场景描述

表3中没有代表主键的字段，一行代表一次消费记录，c列可以代表消费时间戳，a列代表用户号，b列代表消费金额。

现实场景： 目前，想要每隔500毫秒就要计算一次当前时间之前24小时内的每位用户交易的总金额，总次数。（甚至平均值、方差、标准差等，注意：方差、标准差这些统计方式不适用于滑动计算，在此不考虑）

模拟场景： 现在假设每隔时间t只有一条交易，表3中c列的值代表每次递增t，每隔t的时间计算一次当前时间之前4t到当前时间的总金额。如果每次都是完整的计算4t时间内的总金额，总次数，可能消耗比较大。

之后，每次计算都需要将计算结果持久化到表4当中（表4中用户号唯一，如果存在该用户，则更新总金额，总次数，否则，直接插入）。

---

比如，第一次计算的当前时间为6，需要计算c <= 6 and c > 6-4的sql示例为:

select t3.a , sum(t3.b) , count(1) from t3 where 1=1 and (c <= 6 and c > 6-4) group by t3.a;
# 注意 表4 中 需要添加关于字段a的唯一约束
insert into t4 (a,sumb,cnt) values (3,    24,    2)  on duplicate key update sumb = 24 , cnt = 2;
insert into t4 (a,sumb,cnt) values (4,    7,    1)  on duplicate key update sumb = 7  , cnt = 1;
insert into t4 (a,sumb,cnt) values (5,    7,    1)  on duplicate key update sumb = 7  , cnt = 1;

第二次计算的当前时间为7，需要计算c <= 7 and c > 7-4之间消费记录的总金额，总次数，在现实中，4t所代表的时间跨度（24 hour）总是很大，如果按照上述sql去计算，则每次计算都要触及大量的交易记录，虽然计算的负担在数据库而不在于业务逻辑。而t所代表的计算时间相对较小（500 ms），每隔t时间，新增的消费记录总是比较少。

select t3.a , sum(t3.b) , count(1) from t3 where 1=1 and (c <= 7 and c > 7-4) group by t3.a;
# 查询结果 ： (3,17,1),(4,15,2) ,(5,7,1 )
# 持久化查询结果。注意：表4 中 需要添加关于字段a的唯一约束
insert into t4 (a,sumb,cnt) values (3,17,1)  on duplicate key update sumb = 17 , cnt = 1;
insert into t4 (a,sumb,cnt) values (4,15,2)  on duplicate key update sumb = 15 , cnt = 2;
insert into t4 (a,sumb,cnt) values (5,7,1 )  on duplicate key update sumb = 7  , cnt = 1;

---

现在，第一次计算采用上边的所述的全量计算方案，而第二次计算如果能借助第一次计算的结果，加上第二次相对于第一次新增的消费记录（即c=7的记录），再减去第二次相对于第一次所减少的消费记录(即c=3的记录)。(在此，不再介绍如何找出新增或减少的记录)

select t3.a , sum(t3.b) , count(1) from t3 where 1=1 and (c <= 6 and c > 6-4) group by t3.a;
# 查询结果 ：  (3,    24,    2),(4,    7,    1) ,(5,    7,    1)
# 持久化查询结果。注意：表4 中 需要添加关于字段a的唯一约束
insert into t4 (a,sumb,cnt) values (3,24,2) on duplicate key update sumb = 24, cnt = 2;
insert into t4 (a,sumb,cnt) values (4,7,1) on duplicate key update sumb = 7, cnt = 1;
insert into t4 (a,sumb,cnt) values (5,7,1) on duplicate key update sumb = 7, cnt = 1;

select t.a , t.sumb, t.cnt from (
    select t3.a as a , sum(t3.b) as sumb, count(1) as cnt from t3 where 1=1 and c=7 group by t3.a
    union all 
    select t3.a as a,0-sum(t3.b) as sumb,0-count(1) as cnt from t3 where 1=1 and c=3 group by t3.a
) t
# 查询结果 ： (4,8,1), (3,-7,-1)
# 持久化查询结果。注意：4 中 需要添加关于字段a的唯一约束
insert into t4 (a,sumb,cnt) values (4,8,1) on duplicate key update sumb = sumb+8,cnt = cnt+1;
insert into t4 (a,sumb,cnt) values (3,-7,-1) on duplicate key update sumb = sumb-7,cnt = cnt-1;

---

Answer 1

    select t.a , sum(t.b) from (
        select t1.a , b from t1 
        union all
        select t2.a , 0-b from t2
    ) t group by t.a

---

说下这个答案仅仅是对于模拟场景简化的回答。
问题下边的滑动计算场景模拟与滑动计算场景描述中有更为详尽的描述过程，有兴趣的可以看下。

Answer 2

(select t1.a as a, ifnull(t1.b, 0) - ifnull(t2.b, 0) as b
from t1 left join t2 on t1.a = t2.a
where t1.a in (select a from t1 union select a  from t2))
union
(select t2.a as a, ifnull(t1.b, 0) - ifnull(t2.b, 0) as b
from t1 right join t2 on t1.a = t2.a
where t2.a  not in (select a from t1));

union之前是根据a的主键，计算；
union之后是根据b(且不在a出现)的主键，计算；

Answer 3

首先请检查一下你给的示例结果，主键a为3,4,5的计算结果应该是正负相反了。

使用FULL JOIN语句和NVL函数结合，一次表连接就可以计算出来了。

SELECT NVL(t1.a, t2.a), NVL(t1.b, 0) - NVL(t2.b, 0)
FROM t1 FULL JOIN t2 ON t1.a = t2.a

Answer 4

select ifnull(t1a,t2a) a, ifnull(t1b,0)-ifnull(t2b,0) b from (
SELECT t1.a t1a,t1.b t1b,t2.a t2a,t2.b t2b FROM t1 left join t2 on t1.a = t2.a
union
SELECT t1.a t1a,t1.b t1b,t2.a t2a,t2.b t2b FROM t1 right join t2 on t1.a = t2.a
) eee
order by a

Answer 5

先union罗列下 结果的a有多少，再做减法就好，nvl判断是否为空，空则置0:
select AA.A,nvl(BB.b,0)-nvl(cc.b,0)
from
(select a
from
t1
union
select a
from
t2) AA,
t1 BB,
t2 CC
where AA.A=BB.A(+) and CC.A(+)=AA.a