前面的一篇文章介紹了左右值編碼，不知道大家注意到了沒有，如果資料龐大，每次更新都需要更新差不多全表，效率較低沒有更好的方式？今天我們就來研究下區間巢狀法。
區間巢狀法原理
如果節點區間[clft, crgt]與[plft, prgt]存在如下關係：plft <= clft and crgt >= prgt，則[clft, crgt]區間裡的點是[plft, prgt]的子節點。基於此假設我們就可以通過對區間的不斷的向下划來獲取新的區間。舉例：如果在區間[plft, prgt]中存在一個空白區間[lft1, rgt1]，如果要加入一個[plft,lft1]、[rgt1,prgt]同級的區間，只需插入節點：[(2*lft1+rgt1)/3, (rgt1+2*lft)/3]。在新增完節點後我們還留下[lft1,(2*lft1+rgt1)/3]和 [(rgt1+2*lft)/3,rgt1]兩個空餘的空間用來新增更多的子節點。
如果我們把區間放在二位平面上，把rgt當成是x軸，lft當做是y軸，納悶巢狀的區間數差不多是這樣的：
每個節點[lft, rgt]擁有的子節點都被包含在y >= lft & x <= rgt中。同時y >= clft & x <= crgt所在的空間也是y >= plft & x <= prgt的子集。另外由於新增的右區間都小於已有的左區間，所以新增的節點均在y=x這條直線以下。
區間巢狀法實現
瞭解了區間巢狀法的原理後，接下來我們就要考慮如何實現他，原則上初始的區間使用任何區間都是可以的，這裡我們使用的是[0,1]作為根區間。
首先，我們在XY平面上定義2個點。深度優先集合點和廣度有限集合點，針對點<x=1,y=1/2>的深度優先集合點為<x=1,y=1>，廣度優先集合點為<x=1/2,y=1/2>。接下來我們定義第一個子節點的位置為父節點和深度優先集合點的中間點。兄弟節點則為前一個子節點到廣度優先集合點的中間點，如上圖所示，節點1.2的位置為<x=3/4, y=5/8>。
另外仔細看我們可以看到點與點之間的關係。另外如果我們知道x和y的和，我們就能反推出x,y的值（具體的邏輯是什麼，我現在也還沒有搞懂，有知道的朋友可以幫忙解釋下）。
我們以節點<x=3/4, y=5/8>為例，我們可以得到他的和為11/8。
我們定義11為分子（numerator），8為分母（denominator），則x點的分子為：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE DEFINER = `root`@`localhost` FUNCTION `x_numer`(`numer` int,`denom` int) RETURNS int(11) BEGIN DECLARE ret_num INT; DECLARE ret_den INT; SET ret_num := numer+1; SET ret_den := denom*2; WHILE floor(ret_num/2) = ret_num/2 DO SET ret_num := ret_num/2; SET ret_den := ret_den/2; END WHILE; RETURN ret_num; END; |
x點的分母為：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE DEFINER = `root`@`localhost` FUNCTION `x_ denom`(`numer` int,`denom` int) RETURNS int(11) BEGIN DECLARE ret_num INT; DECLARE ret_den INT; SET ret_num := numer+1; SET ret_den := denom*2; WHILE floor(ret_num/2) = ret_num/2 DO SET ret_num := ret_num/2; SET ret_den := ret_den/2; END WHILE; RETURN ret_den; END; |
Y點的分子:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE DEFINER = `root`@`localhost` FUNCTION `y_numer`(`numer` int,`denom` int) RETURNS int(11) BEGIN DECLARE num INT; DECLARE den INT; SET num := x_numer(numer, denom); SET den := x_denom(numer, denom); WHILE den < denom DO SET num := num*2; SET den := den*2; END WHILE; SET num := (numer - num); WHILE floor(num/2) = num/2 DO SET num := num/2; SET den := den/2; END WHILE; RETURN num; END; |
Y 的分母：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE DEFINER = `root`@`localhost` FUNCTION `y_denom`(`numer` int,`denom` int) RETURNS int(11) BEGIN DECLARE num INT; DECLARE den INT; SET num := x_numer(numer, denom); SET den := x_denom(numer, denom); WHILE den < denom DO SET num := num*2; SET den := den*2; END WHILE; SET num := (numer - num); WHILE floor(num/2) = num/2 DO SET num := num/2; SET den := den/2; END WHILE; RETURN den; END; |
接下來我們來測試下，X與Y是否能解碼出來：
|
1 2 3 |
SELECT CONCAT(x_numer (11, 8),'/',x_denom (11, 8)) AS X, CONCAT(y_numer (11, 8),'/',y_denom (11, 8)) AS Y |
結果與節點1.2的位置為<x=3/4, y=5/8>完全一致。現在我們知道只需要一個分數即可表示平面上的一個點。
如有已經有分數11/8如何獲取該節點的父節點？(如果分子為3，則代表其為根節點)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE DEFINER = `root`@`localhost` FUNCTION `parent_numer`(`numer` int,`denom` int) RETURNS int(11) BEGIN DECLARE ret_num INT; DECLARE ret_den INT; IF numer=3 THEN RETURN denom/2; END IF; SET ret_num := (numer-1)/2; SET ret_den := denom/2; WHILE floor((ret_num-1)/4) = (ret_num-1)/4 DO SET ret_num := (ret_num+1)/2; SET ret_den := ret_den/2; END WHILE; RETURN ret_num; END; |
|
1 |
SELECT CONCAT(parent_numer(11,8),'/',parent_denom(11,8)) AS parent |
計算當前節點在同級所在的位置：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE DEFINER = `root`@`localhost` FUNCTION `parent_denom`(`numer` int,`denom` int) RETURNS int(11) BEGIN DECLARE ret_num INT; DECLARE ret_den INT; IF numer=3 THEN RETURN NULL; END IF; SET ret_num := (numer-1)/2; SET ret_den := denom/2; WHILE floor((ret_num-1)/4) = (ret_num-1)/4 DO SET ret_num := (ret_num+1)/2; SET ret_den := ret_den/2; END WHILE; RETURN ret_den; END; |
有了查詢父節點的方法及當前節點所在同級中的位置的方法，即可通過這兩個的組合，將節點的路徑給計算出來。
|
1 2 3 4 5 6 7 8 |
CREATE DEFINER = `root`@`localhost` FUNCTION `path`(`numer` int,`denom` int) RETURNS varchar(255) BEGIN IF numer is NULL THEN RETURN ''; END IF; RETURN path(parent_numer(numer, denom),parent_denom(numer, denom))|| ‘.’ || sibling_number(numer, denom); END; |
按照以上方法新增後進行測試，返回[Err] 1424 – Recursive stored functions and triggers are not allowed. 即MySQL的自定義函式不支援遞迴查詢。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE DEFINER = `root`@`localhost` FUNCTION `path`(`numer` int,`denom` int) RETURNS varchar(255) BEGIN DECLARE numer_temp INT; DECLARE denom_temp INT; DECLARE path_result VARCHAR(255); DECLARE path_temp VARCHAR(255); DECLARE sn VARCHAR(255); SET path_temp := ''; WHILE numer IS NOT NULL DO IF path_temp = '' THEN SET path_result := sibling_number(numer, denom); ELSE SET path_result := CONCAT(sibling_number(numer, denom),'.',path_temp); END IF; SET path_temp := path_result; SET numer_temp := parent_numer(numer, denom); SET denom_temp := parent_denom(numer, denom); SET numer := numer_temp; SET denom := denom_temp; END WHILE; RETURN path_result; END; |
SELECT path (11, 8) 的結果為 1.2
計算節點層級的方法：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE DEFINER = `root`@`localhost` FUNCTION `node_level`(`numer` int,`denom` int) RETURNS int(11) BEGIN DECLARE ret_num INT; DECLARE ret_den INT; DECLARE ret INT; SET ret =1; IF numer=3 THEN return 1; END IF; WHILE numer!=3 DO SET ret_num := parent_numer(numer, denom); SET ret_den := parent_denom(numer, denom); SET numer := ret_num; SET denom := ret_den; SET ret := ret + 1; END WHILE; RETURN ret; END; |
我們知道了如何將編碼過的節點轉成目錄形式，如何逆轉呢？以下是方法：
先新增2個輔助函式：
|
1 2 3 4 5 |
CREATE DEFINER = `root`@`localhost` FUNCTION `child_numer`(`num` int,`den` int,`child` int) RETURNS int(11) BEGIN RETURN num * power(2, child) + 3 - power(2, child); END; |
|
1 2 3 4 5 |
CREATE DEFINER = `root`@`localhost` FUNCTION `child_denom`(`num` int,`den` int,`child` int) RETURNS int(11) BEGIN RETURN den*power(2, child); END; |
再來編寫逆轉函式：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE DEFINER = `root`@`localhost` FUNCTION `path_numer`(`path` varchar(255)) RETURNS int(11) BEGIN DECLARE num INT; DECLARE den INT; DECLARE postfix VARCHAR(255); DECLARE sibling VARCHAR(255); SET num := 1; SET den := 1; SET postfix := CONCAT(path,'.'); WHILE length(postfix) > 1 DO SET sibling := SUBSTR(postfix, 1, instr(postfix,'.')-1); SET postfix := SUBSTR(postfix, instr(postfix,'.')+1); SET num := child_numer(num,den,sibling+0); SET den := child_denom(num,den,sibling+0); END WHILE; RETURN num; END; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE DEFINER = `root`@`localhost` FUNCTION `path_denom`(`path` varchar(255)) RETURNS int(11) BEGIN DECLARE num INT; DECLARE den INT; DECLARE postfix VARCHAR(255); DECLARE sibling VARCHAR(255); SET num := 1; SET den := 1; SET postfix := CONCAT(path,'.'); WHILE length(postfix) > 1 DO SET sibling := SUBSTR(postfix, 1, instr(postfix,'.')-1); SET postfix := SUBSTR(postfix, instr(postfix,'.')+1); SET num := child_numer(num,den,sibling+0); SET den := child_denom(num,den,sibling+0); END WHILE; RETURN den; END; |
select CONCAT(path_numer(‘2.1.3′),’/’,path_denom(‘2.1.3’)) 結果為51/64
參考資料：
- http://www.rampant-books.com/art_vadim_nested_sets_sql_trees.htm