分类 标签 存档 社区 博客 友链 GitHub 订阅 搜索

MySQL 对排序之后的数据根据某个列字段计算其前后两个值的变化率

230 浏览

ZERO

    持续更新 请关注:https://zorkelvll.cn/blogs/zorkelvll/articles/2018/11/02/1541172528332

背景

   本文主要是针对在实际应用场景中出现的 “排序之后的数据根据某个列字段计算其前后两个值的变化率, 构造出变化率这一列” 的这样一种需求,提供一种解决方案实践,并结合具体的实例给出 sql 代码!

描述

  SQL 对排序之后的数据根据某个字段前后两个计算变化率

  SQL 对于根据某个表中的【根据字段 S_INFO_WINDCODE 分组,且组内根据字段 TRADE_DT 升序之后的各组数据,对于各个组内计算 S_MARGIN_PURCHWITHBORROWMONEY 的前后变化率】

   也即,对于表 AShareMarginTrade(字段:TRADE_DT、S_INFO_WINDCODE、S_MARGIN_PURCHWITHBORROWMONEY)中的字段 S_MARGIN_PURCHWITHBORROWMONEY 数据,需要根据对于相同的 S_INFO_WINDCODE 下按照 TRADE_DT 升序排列分别计算各个 S_INFO_WINDCODE 的 S_MARGIN_PURCHWITHBORROWMONEY 前后两条之间的变化率

场景

  • 原始表
S_INFO_WINDCODE TRADE_DT S_MARGIN_PURCHWITHBORROWMONEY
000001.SZ 20160815 320007905.0000
000001.SZ 20160812 277171367.0000
000001.SZ 20160816 209357556.0000
000002.SZ 20160812 1003339884.0000
000002.SZ 20160815 769999464.0000
  • 构造逻辑:

   对于 S_INFO_WINDCODE 分组,组内根据 TRADE_DT 顺序排序,组内分别计算后一个叫

  • 目标表:
S_INFO_WINDCODE TRADE_DT S_MARGIN_PURCHWITHBORROWMONEY S_MARGIN_PURCHWITHBORROWMONEY_VAR
000001.SZ 20160815 320007905.0000 0.15454893
000001.SZ 20160812 277171367.0000 NULL
000001.SZ 20160816 209357556.0000 -0.34577380
000002.SZ 20160812 1003339884.0000 NULL
000002.SZ 20160815 769999464.0000 -0.23256368
  • 解决思路:

(1)通过创建两张表(也可以选择为创建 TEMPORARY 临时表)temp 和 temp2,同时在对两张表初始化数据的时候分别是设置序号 rank(起始值 0 和 1,且数据保证是按照字段 S_INFO_WINDCODE 值相同和字段 TRADE_DT 升序排列,这样的一个顺序 rank 顺序增加的)

(2)将两张表 temp 和 temp2 进行 join 操作,且条件是 rank 相等(其实是原表中刚好错位了的前后两条被放在同一条记录中了)和相同的 t2(也即原有的 S_INFO_WINDCODE 字段值),则可以计算出来某个 S_INFO_WINDCODE 某两条相邻的(TRADE_DT)记录间的变化率

(3)将(2)中的计算结果以一个新的字段形式存储在原表 AShareMarginTrade 中

具体实现的详细 SQL 语句:

-- 如果存在则删除临时表temp 
DROP TABLE IF EXISTS `temp`;
-- 创建临时表temp,且对其排序之后增加序号
SELECT @rownum := 0;
CREATE TABLE IF NOT EXISTS
temp(t1 VARCHAR(8),t2 VARCHAR(20),t3 DECIMAL(20,4),rank INT,INDEX(rank))
SELECT mt.TRADE_DT t1,mt.S_INFO_WINDCODE t2,mt.S_MARGIN_PURCHWITHBORROWMONEY t3, @rownum := @rownum + 1 AS rank
FROM AShareMarginTrade mt
ORDER BY mt.S_INFO_WINDCODE,mt.TRADE_DT;

-- 如果存在则删除临时表temp2 
DROP TABLE IF EXISTS `temp2`;
-- 创建临时表temp2,且对其排序之后增加序号(相比较temp中的需要,全部都加了1)
SELECT @rownum := 1;
CREATE TABLE IF NOT EXISTS
temp2(t1 VARCHAR(8),t2 VARCHAR(20),t3 DECIMAL(20,4),rank INT,INDEX(rank))
SELECT mt.TRADE_DT t1,mt.S_INFO_WINDCODE t2,mt.S_MARGIN_PURCHWITHBORROWMONEY t3, @rownum := @rownum + 1 AS rank
FROM AShareMarginTrade mt
ORDER BY mt.S_INFO_WINDCODE,mt.TRADE_DT;

-- 计算变化率:将表temp与temp2根据rank及code相同的,联表联出来也即将原表中同一个code中date相邻的两个联成同一行记录中了,并且计算变化率
SELECT a.rank,a.t1 next,b.t1 pre,a.t2 `code`,(a.t3 - b.t3) / b.t3 as var
FROM temp a
LEFT JOIN temp2 b ON b.rank=a.rank AND b.t2=a.t2 AND b.t3 != 0
-- HAVING a.rank != 1 AND var IS NOT NULL AND a.t1 = '20161014'
HAVING a.rank != 1 AND var IS NOT NULL
ORDER BY var DESC ;

-- 创建列名S_MARGIN_PURCHWITHBORROWMONEY_VAR  
SET @dbname = DATABASE();  
SET @tablename = "AShareMarginTrade";  
SET @columnname = "S_MARGIN_PURCHWITHBORROWMONEY_VAR";  
SET @preparedStatement = (SELECT IF(  
  (  
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS  
    WHERE  
      (table_name = @tablename)  
      AND (table_schema = @dbname)  
      AND (column_name = @columnname)  
  ) > 0,  
  "SELECT 1",  
  CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " DECIMAL(20,8) DEFAULT NULL AFTER `S_MARGIN_PURCHWITHBORROWMONEY`;")  
)); 
-- ALTER TABLE `AShareMarginTrade` ADD `S_MARGIN_PURCHWITHBORROWMONEY_VAR` DECIMAL(20,8) DEFAULT NULL AFTER `S_MARGIN_PURCHWITHBORROWMONEY`;

UPDATE AShareMarginTrade aa,
(
-- start 计算变化率 
SELECT a.rank,a.t1 next,b.t1 pre,a.t2 `code`,(a.t3 - b.t3) / b.t3 as var
FROM temp a
LEFT JOIN temp2 b ON b.rank=a.rank AND b.t2=a.t2 AND b.t3 != 0
HAVING a.rank != 1 AND var IS NOT NULL
-- end 计算变化率 
) AS tt
SET aa.S_MARGIN_PURCHWITHBORROWMONEY_VAR = tt.var
WHERE tt.next = aa.TRADE_DT AND tt.`code`=aa.S_INFO_WINDCODE;

-- 删除临时表 
DROP TABLE IF EXISTS `temp`;
DROP TABLE IF EXISTS `temp2`;

评论  
留下你的脚步
推荐阅读