問題描述

滑動計算場景簡化

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

因為原問題場景比較復雜，下面將其簡化描述：(標題描述可能不太切題)

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

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

表 t1 :

ab11025374757

表 t2 :

ab317485361176

現(xiàn)在，按主鍵值相同的情況下進行計算，t1.b - t2.b。如果主鍵a的值在對方表中不存在，則字段b取0值。查詢得到如下表結構的數(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不能解決兩個問題：

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

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

怎么寫sql比較好？

下面的sql可以解決上面的轉0問題，但是仍然解決不了重復計算的問題。

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

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

滑動計算場景模擬

表 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滑動計算場景描述

表3中沒有代表主鍵的字段，一行代表一次消費記錄，c列可以代表消費時間戳，a列代表用戶號，b列代表消費金額。

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

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

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

比如，第一次計算的當前時間為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之間消費記錄的總金額，總次數(shù)，在現(xiàn)實中，4t所代表的時間跨度（24 hour）總是很大，如果按照上述sql去計算，則每次計算都要觸及大量的交易記錄，雖然計算的負擔在數(shù)據(jù)庫而不在于業(yè)務邏輯。而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;

現(xiàn)在，第一次計算采用上邊的所述的全量計算方案，而第二次計算如果能借助第一次計算的結果，加上第二次相對于第一次新增的消費記錄（即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;

問題解答

回答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

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

回答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的主鍵，計算；union之后是根據(jù)b(且不在a出現(xiàn))的主鍵，計算；

回答3：

首先請檢查一下你給的示例結果，主鍵a為3,4,5的計算結果應該是正負相反了。

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

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羅列下 結果的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