仅用一句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更新整张表内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！