問題場景
各大平臺店鋪的三項評分（物流、服務(wù)、商品）變化情況；
商品每日價格的變化記錄；
股票的實時漲跌浮；
復(fù)現(xiàn)場景
表：主鍵ID，商品編號，記錄時的時間，記錄時的價格，創(chuàng)建時間。
問題：獲取每個商品每次的變化情況（漲跌幅、漲跌率）。
解決思路
1、要想高效率的更新漲跌，就肯定不能是逐條數(shù)據(jù)更新，要通過自連表建立起對應(yīng)關(guān)系，將每一條數(shù)據(jù)關(guān)聯(lián)到上一次的價格數(shù)據(jù)。
2、由于數(shù)據(jù)庫非常龐大，所以可能存在很多垃圾數(shù)據(jù)，就比如說相關(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ù)對應(yīng)的商品價格。
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ù)以及對應(yīng)的價格后，開始進行計算，獲取到最終的結(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 '記錄時的時間',
`goods_price` decimal(10,2) NOT NULL COMMENT '記錄時的價格',
`created_at` int(11) NOT NULL COMMENT '創(chuàng)建時間',
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)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- Mysql update多表聯(lián)合更新的方法小結(jié)
- mysql實現(xiàn)查詢數(shù)據(jù)并根據(jù)條件更新到另一張表的方法示例
- 如何使用MySQL一個表中的字段更新另一個表中字段
- Mysql 根據(jù)一個表數(shù)據(jù)更新另一個表的某些字段(sql語句)
- MySQL數(shù)據(jù)庫同時查詢更新同一張表的方法
- mysql用一個表更新另一個表的方法
