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

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

作者: 发布: 2021年7月20日 39阅读 0评论

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

 更新时间：2021年05月06日 09:38:02   作者：啥也不会的程序猿  
这篇文章主要介绍了仅用一句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更新整张表内容请搜索华域联盟以前的文章或继续浏览下面的相关文章希望大家以后多多支持华域联盟！

您可能感兴趣的文章:

相关文章

最新评论

本文由 华域联盟 原创撰写：华域联盟 » 仅用一句SQL更新整张表的涨跌幅、涨跌率的解决方案

转载请保留出处和原文链接：https://www.cnhackhy.com/3877.htm

本文来自网络，不代表华域联盟立场，转载请注明出处。
0赞

作者:

mybatis调用sqlserver存储过程返回结果集的方法

mybatis调用sqlserver存储过程返回结果集的方法

jdbc使用PreparedStatement批量插入数据的方法

jdbc使用PreparedStatement批量插入数据的方法

相关推荐

发表回复

联系我们

联系我们

2551209778

在线咨询: QQ交谈

邮箱: [email protected]

工作时间：周一至周五，9:00-17:30，节假日休息

关注微信
微信扫一扫关注我们

微信扫一扫关注我们

关注微博