主页 > 知识库 > 仅用一句SQL更新整张表的涨跌幅、涨跌率的解决方案

仅用一句SQL更新整张表的涨跌幅、涨跌率的解决方案

热门标签:服务外包 铁路电话系统 呼叫中心市场需求 网站排名优化 AI电销 Linux服务器 百度竞价排名 地方门户网站

问题场景

各大平台店铺的三项评分(物流、服务、商品)变化情况;
商品每日价格的变化记录;
股票的实时涨跌浮;

复现场景

表:主键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更新整张表内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家!

您可能感兴趣的文章:
  • Mysql update多表联合更新的方法小结
  • mysql实现查询数据并根据条件更新到另一张表的方法示例
  • 如何使用MySQL一个表中的字段更新另一个表中字段
  • Mysql 根据一个表数据更新另一个表的某些字段(sql语句)
  • MySQL数据库同时查询更新同一张表的方法
  • mysql用一个表更新另一个表的方法

标签:黄山 兰州 衡水 湖南 仙桃 崇左 铜川 湘潭

巨人网络通讯声明:本文标题《仅用一句SQL更新整张表的涨跌幅、涨跌率的解决方案》,本文关键词  ;如发现本文内容存在版权问题,烦请提供相关信息告之我们,我们将及时沟通与处理。本站内容系统采集于网络,涉及言论、版权与本站无关。
  • 相关文章
  • 收缩
    • 微信客服
    • 微信二维码
    • 电话咨询

    • 400-1100-266