仅用一句SQL更新整张表的涨跌幅、涨跌率的解决
编程学习 2021-07-05 13:43www.dzhlxh.cn编程入门
这篇文章主要介绍了仅用一句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
到此这篇关于仅用一句SQL更新整张表的涨跌幅、涨跌率的文章就介绍到这了,更多相关SQL更新整张表内容请搜索狼蚁SEO以前的文章或继续浏览狼蚁网站SEO优化的相关文章希望大家以后多多支持狼蚁SEO！