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

【博文已迁移，持续更新请关注：https://zorkelvll.cn/blogs/zorkelvll/articles/2018/11/02/1541172528332】

 

场景1：

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