av一区二区在线观看_亚洲男人的天堂网站_日韩亚洲视频_在线成人免费_欧美日韩精品免费观看视频_久草视

您的位置:首頁技術(shù)文章
文章詳情頁

mysql - sql 左連接結(jié)果union右連接結(jié)果,導(dǎo)致重復(fù)性計(jì)算怎么解決?

瀏覽:162日期:2022-06-14 11:29:15

問題描述

滑動(dòng)計(jì)算場(chǎng)景簡(jiǎn)化

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

因?yàn)樵瓎栴}場(chǎng)景比較復(fù)雜,下面將其簡(jiǎn)化描述:(標(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)于原問題場(chǎng)景的模擬及描述,有興趣的可以看下,實(shí)現(xiàn)不正確,不夠好的地方望指正。

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

表 t3 :

abc1101252373474575317648753861197610

# 簡(jiǎn)單模擬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ì)算場(chǎng)景描述

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

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

模擬場(chǎng)景: 現(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ù)庫(kù)而不在于業(yè)務(wù)邏輯。而t所代表的計(jì)算時(shí)間相對(duì)較?。?00 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ì)于模擬場(chǎng)景簡(jiǎn)化的回答。問題下邊的滑動(dòng)計(jì)算場(chǎng)景模擬與滑動(dòng)計(jì)算場(chǎng)景描述中有更為詳盡的描述過程,有興趣的可以看下。

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

主站蜘蛛池模板: 老司机免费视频 | 91精品国产一区二区 | 国产欧美在线视频 | 成人不卡在线 | 久久网一区二区三区 | 国产一区二区三区高清 | 欧美午夜一区 | 亚洲午夜精品视频 | 91在线看 | 欧美日韩在线免费观看 | 亚洲成人高清 | av片在线观看 | 日本韩国电影免费观看 | 欧美高清视频在线观看 | 男女那个视频 | 国户精品久久久久久久久久久不卡 | 成人高清视频在线观看 | 亚洲中午字幕 | 久久久91精品国产一区二区三区 | 91短视频网址 | 91免费小视频 | 操操操av | 蜜桃av鲁一鲁一鲁一鲁 | 一级欧美 | 免费一区二区三区 | 国产免费看 | 日韩一区二区三区在线视频 | 国产午夜亚洲精品不卡 | 色综合久 | 北条麻妃一区二区三区在线观看 | 亚洲a网 | 久久久久国产 | 国产激情91久久精品导航 | 91就要激情 | 欧美精品国产一区二区 | 日韩色在线 | 日韩一二区 | 一区二区国产精品 | 精品蜜桃一区二区三区 | 91久久伊人 | 精品国产乱码久久久久久88av |