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

当前位置:

首页 > 数据库 > T-SQL >

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

这篇文章主要介绍了仅用一句SQL更新整张表的涨跌幅、涨跌率的解决方案,本文给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下

问题场景

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

复现场景

表：主键ID，商品编号，记录时的时间，记录时的价格，创建时间。
问题：获取每个商品每次的变化情况（涨跌幅、涨跌率）。

解决思路

1、要想高效率的更新涨跌，就肯定不能是逐条数据更新，要通过自连表建立起对应关系，将每一条数据关联到上一次的价格数据。

2、由于数据库非常庞大，所以可能存在很多垃圾数据，就比如说相关的字段值为NULL或者非有效值的，这些数据要先排除掉。

1	`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更新整张表的涨跌幅、涨跌率的文章就介绍到这了

原文链接：https://www.cnblogs.com/laowenBlog/p/14732305.html

栏目列表