問題描述

滑動(dòng)計(jì)算場景簡化

數(shù)據(jù)庫環(huán)境是MySQL

因?yàn)樵瓎栴}場景比較復(fù)雜，下面將其簡化描述：(標(biāo)題描述可能不太切題)

現(xiàn)在有兩張表t1和t2，表結(jié)構(gòu)相同，但是存儲(chǔ)的數(shù)據(jù)不同。主鍵a與字段b類型均為int型。示例如下。

其中，表t1和表t2存在部分主鍵a相同，但是字段b值不同的數(shù)據(jù)。也有主鍵值在表t1中存在，但是表t2中不存在，或者主鍵值在表t2中存在，但是表t1中不存在。

表 t1 :

ab11025374757

表 t2 :

ab317485361176

現(xiàn)在，按主鍵值相同的情況下進(jìn)行計(jì)算，t1.b - t2.b。如果主鍵a的值在對(duì)方表中不存在，則字段b取0值。查詢得到如下表結(jié)構(gòu)的數(shù)據(jù)。

at1.b - t2.b110253-104-1546-117-6

我所想到的sql語句如下：

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

但是，這條sql不能解決兩個(gè)問題：

如果主鍵a的值在對(duì)方表中不存在，則字段b取0值。

兩次join會(huì)導(dǎo)致主鍵值為3,4,5的數(shù)據(jù)重復(fù)計(jì)算

怎么寫sql比較好？

下面的sql可以解決上面的轉(zhuǎn)0問題，但是仍然解決不了重復(fù)計(jì)算的問題。

SELECT t1.a, ifnull(t1.b, 0) - ifnull(t2.b, 0) from t1 left join t2 on t1.a = t2.aunionSELECT 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

以上問題結(jié)束。以下是關(guān)于原問題場景的模擬及描述，有興趣的可以看下，實(shí)現(xiàn)不正確，不夠好的地方望指正。

滑動(dòng)計(jì)算場景模擬

表 t3 :

abc1101252373474575317648753861197610

# 簡單模擬sqlselect 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滑動(dòng)計(jì)算場景描述

表3中沒有代表主鍵的字段，一行代表一次消費(fèi)記錄，c列可以代表消費(fèi)時(shí)間戳，a列代表用戶號(hào)，b列代表消費(fèi)金額。

現(xiàn)實(shí)場景： 目前，想要每隔500毫秒就要計(jì)算一次當(dāng)前時(shí)間之前24小時(shí)內(nèi)的每位用戶交易的總金額，總次數(shù)。（甚至平均值、方差、標(biāo)準(zhǔn)差等，注意：方差、標(biāo)準(zhǔn)差這些統(tǒng)計(jì)方式不適用于滑動(dòng)計(jì)算，在此不考慮）

模擬場景： 現(xiàn)在假設(shè)每隔時(shí)間t只有一條交易，表3中c列的值代表每次遞增t，每隔t的時(shí)間計(jì)算一次當(dāng)前時(shí)間之前4t到當(dāng)前時(shí)間的總金額。如果每次都是完整的計(jì)算4t時(shí)間內(nèi)的總金額，總次數(shù)，可能消耗比較大。

之后，每次計(jì)算都需要將計(jì)算結(jié)果持久化到表4當(dāng)中（表4中用戶號(hào)唯一，如果存在該用戶，則更新總金額，總次數(shù)，否則，直接插入）。

比如，第一次計(jì)算的當(dāng)前時(shí)間為6，需要計(jì)算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 中 需要添加關(guān)于字段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;

第二次計(jì)算的當(dāng)前時(shí)間為7，需要計(jì)算c <= 7 and c > 7-4之間消費(fèi)記錄的總金額，總次數(shù)，在現(xiàn)實(shí)中，4t所代表的時(shí)間跨度（24 hour）總是很大，如果按照上述sql去計(jì)算，則每次計(jì)算都要觸及大量的交易記錄，雖然計(jì)算的負(fù)擔(dān)在數(shù)據(jù)庫而不在于業(yè)務(wù)邏輯。而t所代表的計(jì)算時(shí)間相對(duì)較小（500 ms），每隔t時(shí)間，新增的消費(fèi)記錄總是比較少。

select t3.a , sum(t3.b) , count(1) from t3 where 1=1 and (c <= 7 and c > 7-4) group by t3.a;# 查詢結(jié)果 ： (3,17,1),(4,15,2) ,(5,7,1 )# 持久化查詢結(jié)果。注意：表4 中 需要添加關(guān)于字段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;

現(xiàn)在，第一次計(jì)算采用上邊的所述的全量計(jì)算方案，而第二次計(jì)算如果能借助第一次計(jì)算的結(jié)果，加上第二次相對(duì)于第一次新增的消費(fèi)記錄（即c=7的記錄），再減去第二次相對(duì)于第一次所減少的消費(fèi)記錄(即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;# 查詢結(jié)果 ： (3, 24, 2),(4, 7, 1) ,(5, 7, 1)# 持久化查詢結(jié)果。注意：表4 中 需要添加關(guān)于字段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# 查詢結(jié)果 ： (4,8,1), (3,-7,-1)# 持久化查詢結(jié)果。注意：4 中 需要添加關(guān)于字段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;

問題解答

回答1：

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

說下這個(gè)答案僅僅是對(duì)于模擬場景簡化的回答。問題下邊的滑動(dòng)計(jì)算場景模擬與滑動(dòng)計(jì)算場景描述中有更為詳盡的描述過程，有興趣的可以看下。

回答2：

(select t1.a as a, ifnull(t1.b, 0) - ifnull(t2.b, 0) as bfrom t1 left join t2 on t1.a = t2.awhere 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 bfrom t1 right join t2 on t1.a = t2.awhere t2.a not in (select a from t1));

union之前是根據(jù)a的主鍵，計(jì)算；union之后是根據(jù)b(且不在a出現(xiàn))的主鍵，計(jì)算；

回答3：

首先請(qǐng)檢查一下你給的示例結(jié)果，主鍵a為3,4,5的計(jì)算結(jié)果應(yīng)該是正負(fù)相反了。

使用FULL JOIN語句和NVL函數(shù)結(jié)合，一次表連接就可以計(jì)算出來了。

SELECT NVL(t1.a, t2.a), NVL(t1.b, 0) - NVL(t2.b, 0)FROM t1 FULL JOIN t2 ON t1.a = t2.a回答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.aunion 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

回答5：

先union羅列下 結(jié)果的a有多少，再做減法就好，nvl判斷是否為空，空則置0:select AA.A,nvl(BB.b,0)-nvl(cc.b,0)from (select afrom t1union select afrom t2) AA,t1 BB,t2 CCwhere AA.A=BB.A(+) and CC.A(+)=AA.a