僅用一句SQL更新整張表的漲跌幅、漲跌率的解決方案
問(wèn)題場(chǎng)景
各大平臺(tái)店鋪的三項(xiàng)評(píng)分（物流、服務(wù)、商品）變化情況；
商品每日價(jià)格的變化記錄；
股票的實(shí)時(shí)漲跌浮；
復(fù)現(xiàn)場(chǎng)景
表：主鍵ID，商品編號(hào)，記錄時(shí)的時(shí)間，記錄時(shí)的價(jià)格，創(chuàng)建時(shí)間。
問(wèn)題：獲取每個(gè)商品每次的變化情況（漲跌幅、漲跌率）。
解決思路
1、要想高效率的更新漲跌，就肯定不能是逐條數(shù)據(jù)更新，要通過(guò)自連表建立起對(duì)應(yīng)關(guān)系，將每一條數(shù)據(jù)關(guān)聯(lián)到上一次的價(jià)格數(shù)據(jù)。
2、由于數(shù)據(jù)庫(kù)非常龐大，所以可能存在很多垃圾數(shù)據(jù)，就比如說(shuō)相關(guān)的字段值為NULL或者非有效值的，這些數(shù)據(jù)要先排除掉。
SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL;
3、然后在獲取每條數(shù)據(jù)的上一條數(shù)據(jù)，同樣也要先排除掉垃圾數(shù)據(jù)。
SELECT tmp_a.*, MAX(tmp_b.goods_date) AS last_date FROM ( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_a LEFT JOIN ( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_b ON tmp_a.goods_code = tmp_b.goods_code AND tmp_a.goods_date > tmp_b.goods_date GROUP BY tmp_a.id;
4、獲取到上一條數(shù)據(jù)后，獲取上條數(shù)據(jù)對(duì)應(yīng)的商品價(jià)格。
SELECT tmp_ab.*,tmp_c.goods_price AS last_price FROM ( SELECT tmp_a.*, MAX(tmp_b.goods_date) AS last_date FROM ( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_a LEFT JOIN ( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_b ON tmp_a.goods_code = tmp_b.goods_code AND tmp_a.goods_date > tmp_b.goods_date GROUP BY tmp_a.id ) AS tmp_ab LEFT JOIN (SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_c ON tmp_ab.goods_code = tmp_c.goods_code AND tmp_c.goods_date = tmp_ab.last_date ORDER BY tmp_ab.id;
5、獲取到上條數(shù)據(jù)以及對(duì)應(yīng)的價(jià)格后，開(kāi)始進(jìn)行計(jì)算，獲取到最終的結(jié)果。
SELECT *, (CONVERT(goods_price, DECIMAL(10,2)) - CONVERT(last_price, DECIMAL(10,2))) AS '漲跌幅', ROUND((CONVERT(goods_price, DECIMAL(10,2)) - CONVERT(last_price, DECIMAL(10,2)))/CONVERT(last_price, DECIMAL(10,2)), 2) AS '漲跌率' FROM ( SELECT tmp_ab.*,tmp_c.goods_price AS last_price FROM ( SELECT tmp_a.*, MAX(tmp_b.goods_date) AS last_date FROM ( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_a LEFT JOIN ( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_b ON tmp_a.goods_code = tmp_b.goods_code AND tmp_a.goods_date > tmp_b.goods_date GROUP BY tmp_a.id ) AS tmp_ab LEFT JOIN (SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_c ON tmp_ab.goods_code = tmp_c.goods_code AND tmp_c.goods_date = tmp_ab.last_date ORDER BY tmp_ab.id ) AS tmp
解決方案
-- 創(chuàng)建表SQL CREATE TABLE `test_goods_price_change` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID', `goods_code` varchar(50) NOT NULL COMMENT '商品編碼', `goods_date` int(11) NOT NULL COMMENT '記錄時(shí)的時(shí)間', `goods_price` decimal(10,2) NOT NULL COMMENT '記錄時(shí)的價(jià)格', `created_at` int(11) NOT NULL COMMENT '創(chuàng)建時(shí)間', PRIMARY KEY (`id`) ) ENGINE=InnoDB CHARSET=utf8mb4; -- 獲取漲跌浮SQL SELECT *, (CONVERT(goods_price, DECIMAL(10,2)) - CONVERT(last_price, DECIMAL(10,2))) AS '漲跌幅', ROUND((CONVERT(goods_price, DECIMAL(10,2)) - CONVERT(last_price, DECIMAL(10,2)))/CONVERT(last_price, DECIMAL(10,2)), 2) AS '漲跌率' FROM ( SELECT tmp_ab.*,tmp_c.goods_price AS last_price FROM ( SELECT tmp_a.*, MAX(tmp_b.goods_date) AS last_date FROM ( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_a LEFT JOIN ( SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_b ON tmp_a.goods_code = tmp_b.goods_code AND tmp_a.goods_date > tmp_b.goods_date GROUP BY tmp_a.id ) AS tmp_ab LEFT JOIN (SELECT id,goods_code,goods_date,goods_price FROM test_goods_price_change WHERE goods_price IS NOT NULL AND goods_date IS NOT NULL ) AS tmp_c ON tmp_ab.goods_code = tmp_c.goods_code AND tmp_c.goods_date = tmp_ab.last_date ORDER BY tmp_ab.id ) AS tmp
到此這篇關(guān)于僅用一句SQL更新整張表的漲跌幅、漲跌率的文章就介紹到這了,更多相關(guān)SQL更新整張表內(nèi)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信