《MYSQL》----字串的複雜函數，檢索的七-天-排-重

標籤：函數   連續   建表   使用方法   複雜   mes   timestamp   div   add   

《MYSQL》----字串的複雜函數，檢索的七-天-排-重

接到了一個新的需求，拿到需求的時候瞬間有點頭大，因為實在是有些棘手。

我們這個系統本身是個介面系統，總介面數大概在200個左右。外部會有很多使用者在不同的時間拿著不同參數去調我們的這些介面，使用者的調集記錄會寫在資料庫的一個日誌表裡。日誌表欄位大概有url（帶參數），使用者id，調用時間，返回結果等等等等。現在的需求是，查詢一定時間範圍內（12月）裡，同一個使用者在7天之內調用的介面url和時間。

麻煩的是介面url本身比較複雜。它的長度大約在200-500，裡面有幾個麻煩的參數：一個是時間戳記，時間戳記有可能是傳遞，也有可能是產生的，它鐵定不重複。另一個是數位簽章，它會根據參數和時間戳記通過演算法產生，也鐵定不重複。所以解決這個問題的第一步，是把url進行處理。去掉這兩個鐵定不重複的參數。
這個系統還有個讓人蛋疼的地方，就是它是兩個系統合二為一的。兩個系統的時間戳記和數位簽章參數名稱是不一樣的，也就是說有四個參數需要判斷是否存在+處理。這一步肯定是用函數做。建立函數如下：

CREATE FUNCTION ReplaceUrl (strUrl varchar(800))
RETURNS varchar(800)
BEGIN
DECLARE v_Tmp_1 varchar(800) default ‘‘;
DECLARE v_Tmp_2 varchar(800) default ‘‘;
DECLARE v_Tmp_3 varchar(800) default ‘‘;
DECLARE v_Tmp_4 varchar(800) default ‘‘;

SET v_Tmp_1 = if(locate(‘timeStamp‘,strUrl)>0,REPLACE(strUrl,SUBSTRING_INDEX(SUBSTRING_INDEX(strUrl,‘timeStamp‘,-1),‘&‘,1),‘‘),strUrl);
SET v_Tmp_2 = if(locate(‘time_stamp‘,v_Tmp_1)>0,REPLACE(v_Tmp_1,SUBSTRING_INDEX(SUBSTRING_INDEX(v_Tmp_1,‘time_stamp‘,-1),‘&‘,1),‘‘),v_Tmp_1);
SET v_Tmp_3 = if(locate(‘access_signature‘,v_Tmp_2)>0,REPLACE(v_Tmp_2,SUBSTRING_INDEX(SUBSTRING_INDEX(v_Tmp_2,‘access_signature‘,-1),‘&‘,1),‘‘),v_Tmp_2);
SET v_Tmp_4 = if(locate(‘accessSignature‘,v_Tmp_3)>0,REPLACE(v_Tmp_3,SUBSTRING_INDEX(SUBSTRING_INDEX(v_Tmp_3,‘accessSignature‘,-1),‘&‘,1),‘‘),v_Tmp_3);
RETURN v_Tmp_4;
END;

MYSQL中if函數使用方法是if(參數1，參數2，參數3)，如果參數1正確則執行參數2，不正確執行參數3。這裡先判斷url是否含有參數A，如果含有則執行替換，不含有則什麼都不處理。
替換的邏輯是REPLACE函數，REPLACE（參數1，參數2，參數3），參數1是等待執行的參數，參數2是被替換的欄位，參數3是替換為的欄位。我們把指定參數替換為空白就可以了。
裡面還有個參數是SUBSTRING_INDEX——按關鍵字截取字串，用法是substring_index（被截取欄位，關鍵字，關鍵字出現的次數），次數為正是往右數，次數為負是往左數。我們直接把次數設定為-1，也就是參數分隔字元&左邊的該參數全部被截取就可以了。

連續執行4次之後，url的四個隨機產生參數全部被清除，可以執行查重了。

第二步，排重。
難的不是排重，而是排重之後還要加上7天檢索的演算法。問題可以簡化為，有一張表，表裡只有3個欄位，id，name,time。現在要檢索重複的name，還要加上time必須在7天之內，否則就不算重複。
那麼答案是：

select t1.* from table_name t1 join table_name t2 on t1.name=t2.name and t1.id!=t2.id
where abs(t1.time-t2.time)<=7

這是一張表當兩張表關聯查詢，雖然我知道會很慢，目前也沒有更好的辦法能解決這個問題。

那麼，這個問題最終的解決語句是，建立函數——將範圍內的日誌調取出來建立表——把新表處理後的url執行md5——再查重

CREATE FUNCTION ReplaceUrl (strUrl varchar(800))
RETURNS varchar(800)
BEGIN
DECLARE v_Tmp_1 varchar(800) default ‘‘;
DECLARE v_Tmp_2 varchar(800) default ‘‘;
DECLARE v_Tmp_3 varchar(800) default ‘‘;
DECLARE v_Tmp_4 varchar(800) default ‘‘;

SET v_Tmp_1 = if(locate(‘timeStamp‘,strUrl)>0,REPLACE(strUrl,SUBSTRING_INDEX(SUBSTRING_INDEX(strUrl,‘timeStamp‘,-1),‘&‘,1),‘‘),strUrl);
SET v_Tmp_2 = if(locate(‘time_stamp‘,v_Tmp_1)>0,REPLACE(v_Tmp_1,SUBSTRING_INDEX(SUBSTRING_INDEX(v_Tmp_1,‘time_stamp‘,-1),‘&‘,1),‘‘),v_Tmp_1);
SET v_Tmp_3 = if(locate(‘access_signature‘,v_Tmp_2)>0,REPLACE(v_Tmp_2,SUBSTRING_INDEX(SUBSTRING_INDEX(v_Tmp_2,‘access_signature‘,-1),‘&‘,1),‘‘),v_Tmp_2);
SET v_Tmp_4 = if(locate(‘accessSignature‘,v_Tmp_3)>0,REPLACE(v_Tmp_3,SUBSTRING_INDEX(SUBSTRING_INDEX(v_Tmp_3,‘accessSignature‘,-1),‘&‘,1),‘‘),v_Tmp_3);
RETURN v_Tmp_4;
END;

CREATE TABLE api_table (
SELECT ReplaceUrl(T5)
as temp,T1,DATE(T6) as daytemp,T5,T6
FROM
`v2-api-log`WHERE DATE(t6) BETWEEN ‘2017-11-24‘ AND ‘2017-12-24‘)

alter table api_table add column md5temp varchar(50);
UPDATE api_table set md5temp = MD5(temp);
ALTER TABLE api_table ADD INDEX tempindex (md5temp);

create table api_repeat(
SELECT
t1.temp,t1.T1,t1.T6
FROM
api_table t1
JOIN api_table t2 ON t1.md5temp = t2.md5temp
AND t1.T1 = t2.T1
AND t1.T6 != t2.T6
WHERE
abs(t1.daytemp - t2.daytemp) <= 7
GROUP BY T6)

 

 

 

《MYSQL》----字串的複雜函數，檢索的七-天-排-重