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

 선택 t.a , sum(t.b) from (
        t1에서 t1.a, b를 선택합니다.
        모두 노동조합
        t2에서 t2.a, 0-b를 선택하세요.
    ) t t.a로 그룹화

<시간>

이 답변은 시뮬레이션 시나리오 단순화에 대한 답변일 뿐임을 말씀드립니다.
질문 아래의 슬라이딩 계산 시나리오 시뮬레이션과 슬라이딩 계산 시나리오 설명에는 더 자세한 설명 과정이 나와 있으니 관심 있는 분들은 읽어보세요.

Answer 2

(t1.a를 a로 선택, ifnull(t1.b, 0) - ifnull(t2.b, 0)을 b로 선택
t1에서 왼쪽으로 t1.a = t2.a에서 t2에 조인
여기서 t1.a in(t1 조합에서 a 선택, t2에서 a 선택))
노동 조합
(t2.a를 a로 선택, ifnull(t1.b, 0) - ifnull(t2.b, 0)을 b로 선택
t1에서 오른쪽으로 t1.a = t2.a에서 t2를 조인합니다.
여기서 t2.a는 (t1에서 선택));

Union 이전에는 a의 기본 키를 기준으로 계산됩니다.
Union 이후에는 b의 기본 키를 기준으로 계산됩니다(a에는 나타나지 않습니다).

Answer 3

먼저 제공하신 예시 결과를 확인하시기 바랍니다. 기본 키 a가 3, 4, 5인 계산 결과는 양수와 음수여야 합니다.

NVL 함수와 결합된 FULL JOIN 문을 사용하면 하나의 테이블 연결로 계산할 수 있습니다.

SELECT NVL(t1.a, t2.a), NVL(t1.b, 0) - NVL(t2.b, 0)
t1 전체 조인 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 왼쪽 조인 t2 on t1.a = t2.a
union
SELECT t1.a t1a,t1.b t1b,t2.a t2a,t2.b t2b FROM t1 오른쪽 조인 t2 on t1.a = t2.a
) eee
a로 정렬

Answer 5

첫 번째 조합은 결과에 a가 몇 개 있는지 나열한 다음 nvl을 수행하여 비어 있는지 확인합니다.
AA.A,nvl(BB. b,0)-nvl(cc .b,0)

(
t1에서
선택


t2에서
선택) AA ,
t1 BB,
t2 CC
여기서 AA.A=BB.A(+) 및 CC.A(+)=AA.a