Sqlite
聚合具有欄位約束的嵌套集模型
我有一個建立在嵌套集模型上的表,它基本上記錄了不同性質的事務(由欄位定義
idx
):pk lft rgt idx value 1 1 30 2783 20402.710000 2 2 3 2783 -13885.670000 3 4 5 131 78.547946 4 6 7 2783 -586.810000 5 8 9 1 0.013421 6 10 11 2783 -1777.630000 7 12 13 873 10791.237266 8 14 15 2783 -72.510000 9 16 17 1697 128.626280 10 18 19 2783 -600.430000 11 20 21 1168 6.949175 12 22 23 2783 -2479.080000 13 24 25 1758 853.502787 14 26 27 2783 -1000.580000 15 28 29 3794 3172.428304 16 31 40 2783 615.940000 17 32 33 2783 -615.940000 18 34 39 1 0.040390 19 35 36 1 -0.000152 20 37 38 1 -0.000300 21 41 42 2783 3012.050000
由於這些事務以特定方式嵌套,其中子節點的性質可能與其父節點的性質不同(在此過程中代表一種“轉換”),我很難為每個父節點獲取“餘額”節點本身的性質:
prnt_pk idx aggr 1 2783 0.000000 3 131 78.547946 5 1 0.013421 7 873 10791.237266 9 1697 128.626280 11 1168 6.949175 13 1758 853.502787 15 3794 3172.428304 16 2783 0.000000 18 1 0.039938 21 2783 3012.050000
給定原始表格,我怎樣才能實現這個期望的輸出?我已經嘗試按照這個問題中描述的方式做一些事情,但是我設法得到的只是一個父節點及其所有具有相同的子節點,
idx
在我的情況下我可以更輕鬆地完成SELECT * FROM table WHERE idx = 2783
或類似。如果有幫助,每個父節點都有一個正值,不允許有負值的節點有子節點。
這些是獲得所需結果的步驟:
- 交叉自連接以獲得可能的主鍵的每一個組合(這將平方記錄數);
- 應用嵌套集過濾器(在下面註釋)以刪除所有不相關的節點組合(例如,
pk
1 和 15);- 斷言只有相同性質的組合才會被提取和聚合,方法是等於
idx
from bothnode
andcrosst
;- 由於我們從 聚合
crosst.idx
,這意味著它將匯總所有節點的“餘額”,包括那些為負的(因此是孤兒);第三個過濾器確保我們只從最初的積極父母開始獲取總餘額。SELECT crosst.pk AS prnt_pk, crosst.idx, SUM(node.value) AS aggr FROM nsm_table AS node, nsm_table AS crosst WHERE 1=1 AND node.lft BETWEEN crosst.lft AND crosst.rgt --NSM filter for containers AND node.idx = crosst.idx AND crosst.value > 0 GROUP BY crosst.pk, crosst.idx HAVING SUM(node.value) >= 0
並且輸出與期望的完全一樣:
prnt_pk idx aggr 1 2783 0.000000 3 131 78.547946 5 1 0.013421 7 873 10791.237266 9 1697 128.626280 11 1168 6.949175 13 1758 853.502787 15 3794 3172.428304 16 2783 0.000000 18 1 0.039938 21 2783 3012.050000