image

ZERO

????持續(xù)更新 請(qǐng)關(guān)注：https://zorkelvll.cn/blogs/zorkelvll/articles/2018/11/02/1541172528332

背景

??本文主要是針對(duì)在實(shí)際應(yīng)用場(chǎng)景中出現(xiàn)的“排序之后的數(shù)據(jù)根據(jù)某個(gè)列字段計(jì)算其前后兩個(gè)值的變化率,構(gòu)造出變化率這一列”的這樣一種需求，提供一種解決方案實(shí)踐，并結(jié)合具體的實(shí)例給出sql代碼！

描述

??SQL對(duì)排序之后的數(shù)據(jù)根據(jù)某個(gè)字段前后兩個(gè)計(jì)算變化率

??SQL對(duì)于根據(jù)某個(gè)表中的【根據(jù)字段S_INFO_WINDCODE分組，且組內(nèi)根據(jù)字段TRADE_DT升序之后的各組數(shù)據(jù)，對(duì)于各個(gè)組內(nèi)計(jì)算S_MARGIN_PURCHWITHBORROWMONEY的前后變化率】

??也即，對(duì)于表AShareMarginTrade（字段：TRADE_DT、S_INFO_WINDCODE、S_MARGIN_PURCHWITHBORROWMONEY）中的字段S_MARGIN_PURCHWITHBORROWMONEY數(shù)據(jù)，需要根據(jù)對(duì)于相同的S_INFO_WINDCODE下按照TRADE_DT升序排列分別計(jì)算各個(gè)S_INFO_WINDCODE的S_MARGIN_PURCHWITHBORROWMONEY前后兩條之間的變化率

場(chǎng)景

• 原始表

• 構(gòu)造邏輯：

??對(duì)于S_INFO_WINDCODE分組，組內(nèi)根據(jù)TRADE_DT順序排序，組內(nèi)分別計(jì)算后一個(gè)叫

• 目標(biāo)表：

• 解決思路：

（1）通過(guò)創(chuàng)建兩張表（也可以選擇為創(chuàng)建TEMPORARY臨時(shí)表）temp和temp2，同時(shí)在對(duì)兩張表初始化數(shù)據(jù)的時(shí)候分別是設(shè)置序號(hào)rank（起始值0和1，且數(shù)據(jù)保證是按照字段S_INFO_WINDCODE值相同和字段TRADE_DT升序排列，這樣的一個(gè)順序rank順序增加的）

（2）將兩張表temp和temp2進(jìn)行join操作，且條件是rank相等（其實(shí)是原表中剛好錯(cuò)位了的前后兩條被放在同一條記錄中了）和相同的t2(也即原有的S_INFO_WINDCODE字段值)，則可以計(jì)算出來(lái)某個(gè)S_INFO_WINDCODE某兩條相鄰的（TRADE_DT）記錄間的變化率

（3）將（2）中的計(jì)算結(jié)果以一個(gè)新的字段形式存儲(chǔ)在原表AShareMarginTrade中

具體實(shí)現(xiàn)的詳細(xì)SQL語(yǔ)句：

-- 如果存在則刪除臨時(shí)表temp 
DROP TABLE IF EXISTS `temp`;
-- 創(chuàng)建臨時(shí)表temp，且對(duì)其排序之后增加序號(hào)
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;

-- 如果存在則刪除臨時(shí)表temp2 
DROP TABLE IF EXISTS `temp2`;
-- 創(chuàng)建臨時(shí)表temp2，且對(duì)其排序之后增加序號(hào)（相比較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;

-- 計(jì)算變化率：將表temp與temp2根據(jù)rank及code相同的，聯(lián)表聯(lián)出來(lái)也即將原表中同一個(gè)code中date相鄰的兩個(gè)聯(lián)成同一行記錄中了，并且計(jì)算變化率
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 ;

-- 創(chuàng)建列名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 計(jì)算變化率 
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 計(jì)算變化率 
) AS tt
SET aa.S_MARGIN_PURCHWITHBORROWMONEY_VAR = tt.var
WHERE tt.next = aa.TRADE_DT AND tt.`code`=aa.S_INFO_WINDCODE;

-- 刪除臨時(shí)表 
DROP TABLE IF EXISTS `temp`;
DROP TABLE IF EXISTS `temp2`;