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

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顺序排序，组内分别计算后一个叫

• 目标表：

• 解决思路：

（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`;