问题场景
各大平台店铺的三项评分(物流、服务、商品)变化情况;
商品每日价格的变化记录;
股票的实时涨跌浮;
复现场景
表:主键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
声明
本人博客的所有东西,部分源于网络书籍和视频,其他的是个人的理解感悟,经过自己整理撰写成博客。
本人博客均只用于个人学习、复习,不作为商业用途,如有侵权,请联系我修改或删除。
联系邮箱:itlaowen@163.com
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列:向量存储与相似性搜索在 .NET 中的实现
· 10年+ .NET Coder 心语 ── 封装的思维:从隐藏、稳定开始理解其本质意义
· 地球OL攻略 —— 某应届生求职总结
· 提示词工程——AI应用必不可少的技术
· Open-Sora 2.0 重磅开源!
· 周边上新:园子的第一款马克杯温暖上架