一句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

————下载必看————

1、资源通过互联网渠道公开获取,仅供阅读测试,请在下载后24小时内删除,谢谢合作!
2、本站解压密码统一为:yudouyudou
3、本站微信客服/失效补发:rsst58
4、若版权方认为本站侵权,请联系发邮件至(rsstime@qq.com)处理。
5、关于下载,播放声音字幕,解压等问题,请点击查看>>常见问题
时光屋 » 一句SQL更新整张表的涨跌幅、涨跌率

发表评论

至臻视听_发烧收藏

会员介绍 常见问题