仅用一句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;

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_aLEFT 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_bON tmp_a.goods_code = tmp_b.goods_code AND tmp_a.goods_date > tmp_b.goods_date GROUP BY tmp_a.id;

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_aLEFT 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_bON 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_cON tmp_ab.goods_code = tmp_c.goods_code AND tmp_c.goods_date = tmp_ab.last_date ORDER BY tmp_ab.id;

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_aLEFT 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_bON 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_cON tmp_ab.goods_code = tmp_c.goods_code AND tmp_c.goods_date = tmp_ab.last_date ORDER BY tmp_ab.id) AS tmp

问题场景

复现场景

解决思路

更多相关文章

随机推荐