僅用一句SQL更新整張表的漲跌幅、漲跌率

啥也不會的程式猿發表於2021-05-05
SQL

問題場景

各大平臺店鋪的三項評分（物流、服務、商品）變化情況；
商品每日價格的變化記錄；
股票的實時漲跌浮；

復現場景

表：主鍵ID，商品編號，記錄時的時間，記錄時的價格，建立時間。
問題：獲取每個商品每次的變化情況（漲跌幅、漲跌率）。

解決思路

1、要想高效率的更新漲跌，就肯定不能是逐條資料更新，要通過自連表建立起對應關係，將每一條資料關聯到上一次的價格資料。

2、由於資料庫非常龐大，所以可能存在很多垃圾資料，就比如說相關的欄位值為NULL或者非有效值的，這些資料要先排除掉。

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、然後在獲取每條資料的上一條資料，同樣也要先排除掉垃圾資料。

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、獲取到上一條資料後，獲取上條資料對應的商品價格。

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、獲取到上條資料以及對應的價格後，開始進行計算，獲取到最終的結果。

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

解決方案

-- 建立表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 '建立時間',
  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

相關文章