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

image

ZERO

背景

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