महत SQL का उपयोग करते हुए एक पेड़ में मूल्यों की योग की गणना कैसे करें




सामाजिक मूल्यों की परिभाषा (8)

मुझे उपयोगकर्ताओं के एक पेड़ द्वारा अर्जित प्रत्येक स्तर पर अंक जोड़ना होगा। स्तर 1 उपयोगकर्ता के उपयोगकर्ताओं के अंक की संख्या 1 उपयोगकर्ता के नीचे स्तर है। स्तर 2 उपयोगकर्ताओं के स्तर 1 अंक है, उपयोगकर्ता के नीचे 2 स्तर, आदि ...

गणना एक गैर उत्पादन सर्वर पर महीने में एक बार होती है, प्रदर्शन के बारे में कोई चिंता नहीं।

एसक्यूएल की तरह इसे क्या करना चाहेंगे?

यदि आप भ्रमित हैं, चिंता न करें, मैं भी ठीक हूं!

उपयोगकर्ता तालिका:

ID    ParentID    Points
1     0           230
2     1           150
3     0           80
4     1           110
5     4           54
6     4           342

Tree:
0
|---\
1    3
| \
2  4---
    \  \
     5  6

उत्पादन होना चाहिए:

ID    Points    Level1     Level2
1     230       150+110    150+110+54+342
2     150
3     80
4     110       54+342
5     54
6     342

SQL सर्वर सिंटैक्स और कार्यों को प्राथमिकता से ...


यदि आप ओरेकल डीबीएमएस का प्रयोग कर रहे थे जो ओरेकल पेड़ के प्रश्नों को कनेक्ट द्वारा / स्टार्ट्स के साथ वाक्यविन्यास के साथ समर्थन करता है, क्योंकि यह बहुत सीधा होगा। SQL सर्वर के लिए मुझे लगता है कि आपको सामान्य तालिका अभिव्यक्ति उपयोगी लग सकती है


आपके पास विकल्पों की एक जोड़ी है:

  1. एक कर्सर और पुनरावर्ती उपयोगकर्ता-निर्धारित फ़ंक्शन कॉल का उपयोग करें (यह काफी धीमा है)
  2. एक कैश तालिका बनाएं, इसे ट्रिगर का उपयोग करके INSERT पर अपडेट करें (यह सबसे तेज़ समाधान है, लेकिन समस्या हो सकती है यदि आपके पास मुख्य तालिका में बहुत सारे अपडेट हैं)
  3. क्लाइंट-साइड पुनरावर्ती गणना करें (यदि आपके पास बहुत अधिक रिकॉर्ड नहीं हैं तो बेहतर)

आप नौकरी करने के लिए एक सरल पुनरावर्ती समारोह लिख सकते हैं। मेरा एमएसएसक्यूएल थोड़ी सी जंगली है, लेकिन यह इस तरह दिखता है:

CREATE FUNCTION CALC
(
@node integer,
)
returns 
(
@total integer
)
as
begin
    select @total = (select node_value from yourtable where node_id = @node);

    declare @children table (value integer);
    insert into @children   
    select calc(node_id) from yourtable where parent_id = @node;

    @current = @current + select sum(value) from @children;
    return
end

यदि आप किसी रिलेशनल डेटाबेस में संग्रहीत पेड़ों के साथ काम कर रहे हैं, तो मैं "नेस्टेड सेट" या "संशोधित प्रीऑर्डर ट्री ट्रवर्सल" को देखने का सुझाव देता हूं। एसक्यूएल उस रूप में सरल होगा:

SELECT id, 
       SUM(value) AS value 
FROM table 
WHERE left>left\_value\_of\_your\_node 
  AND right<$right\_value\_of\_your\_node;

... और हर नोड के लिए ऐसा करें जिसमें आप दिलचस्पी रखते हैं

शायद यह आपकी सहायता करेगा: http://www.dbazine.com/oracle/or-articles/tropashko4 या Google का उपयोग करें


पेड़ एसक्यूएल के साथ अच्छी तरह से काम नहीं करते हैं अगर आपके पास बहुत कुछ (बहुत ही कम) अभिलेख हैं, तो आप नीडिंत सेटों का उपयोग करने के लिए पेड़ के कार्यान्वयन को बदल सकते हैं, जो कि यह क्वेरी अविश्वसनीय रूप से आसान बना देगा

उदाहरण (यदि मुझे गलत नहीं है):

SELECT SUM(points) 
FROM users 
where left > x and right < y 

हालांकि, पेड़ पर होने वाले किसी भी परिवर्तन को बड़ी मात्रा में पंक्तियों को छूने की आवश्यकता होती है। क्लाइंट में पुनरावर्तीकरण करना बेहतर होगा।


निम्न तालिका:

Id   ParentId
1   NULL
11    1
12    1
110 11
111 11
112 11
120 12
121 12
122 12
123 12
124 12

और निम्न राशि तालिका:

Id     Val
110 500
111 50
112 5
120 3000
121 30000
122 300000

केवल पत्ते (अंतिम स्तर) आईडी का मान परिभाषित किया गया है। डेटा प्राप्त करने के लिए SQL क्वेरी यह दिखती है:

;WITH Data (Id, Val) AS
(
    select t.Id, SUM(v.val) as Val from dbo.TestTable t
    join dbo.Amount v on t.Id = v.Id
    group by t.Id
)

select cd.Id, ISNULL(SUM(cd.Val), 0) as Amount FROM
(
    -- level 3
    select t.Id, d.val from TestTable t
    left join Data d on d.id = t.Id

    UNION

    -- level 2
    select t.parentId as Id, sum(y.Val) from TestTable t
    left join Data y on y.id = t.Id
    where t.parentId is not null
    group by t.parentId

    UNION

    -- level 1
    select t.parentId as Id, sum(y.Val) from TestTable t
    join TestTable c on c.parentId = t.Id
    left join Data y on y.id = c.Id
    where t.parentId is not null
    group by t.parentId
) AS cd
group by id

यह आउटपुट में परिणाम है:

Id     Amount
1     333555
11   555
12   333000
110 500
111 50
112 5
120 3000
121 30000
122 300000
123 0
124 0

आशा है कि ये आपकी मदद करेगा।


मैं कहूंगा: एक संग्रहीत प्रक्रिया बना, शायद सबसे अच्छा प्रदर्शन है या अगर आपके पास अधिकतम स्तर हैं, तो आप उपकुंजी बना सकते हैं, लेकिन उनके पास बहुत ही कठोर प्रदर्शन होगा

(या आप एमएस एसक्यूएल सर्वर 2008 प्राप्त कर सकते हैं और नए पदानुक्रम कार्यों को प्राप्त कर सकते हैं ...;))


सामान्य रूप में एसक्यूएल, जैसे अन्य ने कहा, ऐसे संबंधों को अच्छी तरह से नहीं संभालता है। आमतौर पर, एक किराए की 'संबंध' तालिका की आवश्यकता होती है (आईडी, अभिभावकीय_आईडी, (आईडी, पैरेंट_आईडी) पर अद्वितीय कुंजी), जहां:

  • हर बार जब आप 'टेबल' में एक रिकॉर्ड जोड़ते हैं, तो आप:

    INSERT INTO relations (id, parent_id) VALUES ([current_id], [current_id]);

    INSERT INTO relations (id, parent_id) VALUES ([current_id], [current_parent_id]);

    INSERT INTO relations (id, parent_id) SELECT [current_id], parent_id FROM relations WHERE id = [current_parent_id];

  • चक्र से बचने के लिए तर्क है

  • सुनिश्चित करें कि 'रिश्तों' पर अद्यतन, विलोपन संग्रहित प्रक्रियाओं के साथ नियंत्रित किया जाता है

उस तालिका को देखते हुए, आप चाहते हैं:

SELECT rel.parent_id, SUM(tbl.points)
FROM table tbl INNER JOIN relations rel ON tbl.id=rel.id
WHERE rel.parent_id <> 0
GROUP BY rel.parent_id;




tree