问题场景
各大平台店铺的三项评分（物流、服务、商品）变化情况；
商品每日价格的变化记录；
股票的实时涨跌浮；
复现场景
表：主键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更新整张表内容请搜索<计算机技术网(www.ctvol.com)!!>以前的文章或继续浏览下面的相关文章希望大家以后多多支持<计算机技术网(www.ctvol.com)!!>！
需要了解更多数据库技术:仅用一句SQL更新整张表的涨跌幅、涨跌率的解决方案，都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
本文来自网络收集，不代表计算机技术网立场，如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址：https://www.ctvol.com/dtteaching/633136.html