• 售前

  • 售后

热门帖子
入门百科

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

[复制链接]
丁侦球 显示全部楼层 发表于 2021-8-14 15:16:29 |阅读模式 打印 上一主题 下一主题
问题场景

各大平台店肆的三项评分(物流、服务、商品)变化情况;
商品逐日价格的变化记录;
股票的及时涨跌浮;
复现场景

表:主键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、然后在获取每条数据的上一条数据,同样也要先排撤除垃圾数据。
  1. SELECT tmp_a.*, MAX(tmp_b.goods_date) AS last_date FROM
  2. ( 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
  3. LEFT JOIN
  4. ( 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
  5. ON tmp_a.goods_code = tmp_b.goods_code AND tmp_a.goods_date > tmp_b.goods_date GROUP BY tmp_a.id;
复制代码
4、获取到上一条数据后,获取上条数据对应的商品价格。
  1. SELECT tmp_ab.*,tmp_c.goods_price AS last_price FROM
  2. (
  3.         SELECT tmp_a.*, MAX(tmp_b.goods_date) AS last_date FROM
  4.         ( 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
  5.         LEFT JOIN
  6.         ( 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
  7.         ON tmp_a.goods_code = tmp_b.goods_code AND tmp_a.goods_date > tmp_b.goods_date GROUP BY tmp_a.id
  8. ) AS tmp_ab
  9. 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
  10. ON tmp_ab.goods_code = tmp_c.goods_code AND tmp_c.goods_date = tmp_ab.last_date ORDER BY tmp_ab.id;
复制代码
5、获取到上条数据以及对应的价格后,开始举行计算,获取到终极的效果。
  1. SELECT
  2.         *,
  3.         (CONVERT(goods_price, DECIMAL(10,2)) - CONVERT(last_price, DECIMAL(10,2))) AS '涨跌幅',
  4.         ROUND((CONVERT(goods_price, DECIMAL(10,2)) - CONVERT(last_price, DECIMAL(10,2)))/CONVERT(last_price, DECIMAL(10,2)), 2) AS '涨跌率'
  5. FROM (
  6.         SELECT tmp_ab.*,tmp_c.goods_price AS last_price FROM
  7.         (
  8.                 SELECT tmp_a.*, MAX(tmp_b.goods_date) AS last_date FROM
  9.                 ( 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
  10.                 LEFT JOIN
  11.                 ( 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
  12.                 ON tmp_a.goods_code = tmp_b.goods_code AND tmp_a.goods_date > tmp_b.goods_date GROUP BY tmp_a.id
  13.         ) AS tmp_ab
  14.         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
  15.         ON tmp_ab.goods_code = tmp_c.goods_code AND tmp_c.goods_date = tmp_ab.last_date ORDER BY tmp_ab.id
  16. ) AS tmp
复制代码
解决方案
  1. -- 创建表SQLCREATE 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;-- 获取涨跌浮SQLSELECT
  2.         *,
  3.         (CONVERT(goods_price, DECIMAL(10,2)) - CONVERT(last_price, DECIMAL(10,2))) AS '涨跌幅',
  4.         ROUND((CONVERT(goods_price, DECIMAL(10,2)) - CONVERT(last_price, DECIMAL(10,2)))/CONVERT(last_price, DECIMAL(10,2)), 2) AS '涨跌率'
  5. FROM (
  6.         SELECT tmp_ab.*,tmp_c.goods_price AS last_price FROM
  7.         (
  8.                 SELECT tmp_a.*, MAX(tmp_b.goods_date) AS last_date FROM
  9.                 ( 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
  10.                 LEFT JOIN
  11.                 ( 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
  12.                 ON tmp_a.goods_code = tmp_b.goods_code AND tmp_a.goods_date > tmp_b.goods_date GROUP BY tmp_a.id
  13.         ) AS tmp_ab
  14.         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
  15.         ON tmp_ab.goods_code = tmp_c.goods_code AND tmp_c.goods_date = tmp_ab.last_date ORDER BY tmp_ab.id
  16. ) AS tmp
复制代码
到此这篇关于仅用一句SQL更新整张表的涨跌幅、涨跌率的文章就先容到这了,更多相关SQL更新整张表内容请搜索草根技能分享从前的文章或继续欣赏下面的相关文章希望各人以后多多支持草根技能分享!

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作