Total children values based on parent
Use a recursive CTE:
WITH RECURSIVE cte AS (
SELECT parent, child, point AS total
FROM tbl1
UNION ALL
SELECT c.parent, t.child, c.total + t.point
FROM cte c
JOIN tbl1 t ON t.parent = c.child
)
SELECT *
FROM cte
R - Calculate totals using a parent-child relation table
It looks like you need to use recursion here:
sum_children <- function(ID, data)
{
result <- 0;
children <- which(data$PARENT_ID == ID)
if(length(children) == 0)
return(data$VALUE[which(data$ID == ID)])
else
for(i in seq_along(children))
result <- result + sum_children(data$ID[children[i]], data)
return(result)
}
This function will take an ID whose child sums you wish to calculate and return the result.
sum_children(60, df)
#> [1] 1706
It will work to any depth. For example, each node with a value in your example is ultimately descended from ID 5. Therefore the child sum of 5 should be the same as the total of VALUE
sum(df$VALUE, na.rm = T)
#> [1] 104553
sum_children(5, df)
#> [1] 104553
And in fact, you can quickly get the result for all descendants for each ID by doing:
df$child_sum <- sapply(df$ID, sum_children, df)
df
#> ID PARENT_ID VALUE child_sum
#> 1 5 NA NA 104553
#> 2 10 5 NA 15
#> 3 20 10 14 14
#> 4 30 10 1 1
#> 5 40 5 4329 4329
#> 6 50 5 98503 98503
#> 7 60 5 NA 1706
#> 8 70 60 272 272
#> 9 80 60 667 667
#> 10 90 60 712 712
#> 11 100 60 55 55
LAG All Previous Rows - Parent Child Database Find All Total Children Count From Each Parent
Here is solution that works if you don't care for id order. Unfortunately, I don't know how to make it ordered as you did in desired output. If nothing I hope it helps you come to solution as data is good.
WITH cte as (
SELECT p.Id as 'PID', c.Id as 'CID' FROM nodes p left join nodes c on p.id = c.ParentId
UNION ALL
SELECT c.PID,p.ID FROM cte c JOIN nodes p ON c.CID=p.ParentId
)
SELECT PID as id, count(*) as 'Children Count' FROM cte where CID IS NOT NULL GROUP BY PID
UNION ALL
SELECT PID, 0 FROM cte WHERE CID IS NULL GROUP BY PID
ORDER BY PID ASC
Parent value as sum of all children values within nested javascript object
So in fact you wanna do sth like this:
every elem asks his childs for its values, these do the same and give back their totalValues plus their own value.
function sumUp(object){
object.totalValue = 0;
for(child of object.children){
object.totalValue += sumUp(child);
}
return object.totalValue + object.value;
}
Start like this:
const totalofall = sumUp(names);
console.log(names); //your expected result.
Working example:
http://jsbin.com/laxiveyoki/edit?console
How to get the cumulative sum of children up its parents?
To get totals for tree nodes you need to generate hierarchy tree for every node in a subquery like this
SELECT
d.*,
v.volume,
(
WITH RECURSIVE cte AS (
SELECT
dd.id AS branch_id,
dd.id
FROM dimensionvalue dd
WHERE dd.id = d.id
UNION ALL
SELECT
cte.branch_id,
dd.id
FROM dimensionvalue dd
JOIN cte ON dd.dimensionvalueid = cte.id
)
SELECT SUM(v.volume)
FROM cte
JOIN valuation v ON v.dimensionvalueid = cte.id
GROUP BY cte.branch_id
) AS totals
FROM dimensionvalue d
LEFT JOIN valuation v ON v.dimensionvalueid = d.id
ORDER BY d.name;
If you really need all those "decoration" columns that you generate in your query for each tree node than you can combine your recursive CTE hierarchy with subquery for totals calculation like this
WITH RECURSIVE hierarchy AS (
SELECT
d.id,
d.name,
d.dimensionvalueid,
0 AS level,
CAST(d.id AS varchar(50)) AS order_sequence
FROM dimensionvalue d
WHERE d.dimensionvalueid IS NULL
UNION ALL
SELECT
e.id,
e.name,
e.dimensionvalueid,
hierarchy.level + 1 AS level,
CAST(hierarchy.order_sequence || '_' || CAST(hierarchy.id AS VARCHAR(50)) AS VARCHAR(50)) AS order_sequence
FROM hierarchy
JOIN dimensionvalue e ON e.dimensionvalueid = hierarchy.id
)
SELECT
RIGHT('-----------', h.level * 5) || h.name || ' ' AS parent_child_tree,
h.*,
v.volume,
(
WITH RECURSIVE cte AS (
SELECT
dd.id AS branch_id,
dd.id
FROM dimensionvalue dd
WHERE dd.id = h.id
UNION ALL
SELECT
cte.branch_id,
dd.id
FROM dimensionvalue dd
JOIN cte ON dd.dimensionvalueid = cte.id
)
SELECT SUM(v.volume)
FROM cte
JOIN valuation v ON v.dimensionvalueid = cte.id
GROUP BY cte.branch_id
) AS totals
FROM hierarchy h
LEFT JOIN valuation v ON v.dimensionvalueid = h.id
ORDER BY h.name
You can check a working demo here
Find the depth of children and their children emanating from a parent element in Pandas dataframe
This is a graph problem.
Here is how your graph looks like:
If I understand correctly, you want to know the length of all possible paths from a given node to the end.
For instance, taking a subset of the graph. There are 3 possible paths from 65, of lengths 1 (to 66), 4 (to 80), and 4 (to 81).
You can use networkx to compute the graph, then determine the terminal nodes as those that do not have children using set operations. Starting fro each terminal node, go back recursively and count the edges to each given node saving the distance in a dictionary (defaultdict):
# set up graph
import networkx as nx
G = nx.from_pandas_edgelist(df, source='parent_id', target='child_id',
create_using=nx.DiGraph)
# compute paths
from collections import defaultdict
successors = defaultdict(list)
def get_parent(n, num=0):
for p in G.predecessors(n):
successors[p].append(num+1)
get_parent(p, num+1)
parents = set(df['parent_id'])
children = set(df['child_id'])
terminal = children-parents
for t in terminal:
get_parent(t)
print(dict(successors))
output:
{33: [1, 11, 10, 14, 13, 14, 13, 7, 6],
32: [2, 12, 11, 15, 14, 15, 14, 8, 7],
17: [3, 1, 13, 12, 16, 15, 16, 15, 5, 9, 8],
16: [4, 2, 14, 13, 17, 16, 17, 16, 6, 10, 9],
65: [1, 4, 4],
64: [2, 5, 5],
63: [3, 6, 6],
59: [4, 7, 7],
57: [5, 8, 8, 1],
56: [6, 9, 9, 2],
44: [7, 10, 10, 3],
43: [8, 11, 11, 4],
42: [9, 8, 12, 11, 12, 11, 5, 4],
41: [10, 9, 13, 12, 13, 12, 6, 5],
11: [1],
10: [2],
79: [1, 1],
78: [2, 2],
67: [3, 3],
21: [1],
20: [2],
19: [3],
18: [4]}
Finally, to merge to your original dataframe:
df.merge(pd.Series(successors, name='paths'),
left_on='parent_id', right_index=True)
output:
child_id parent_id paths
0 11 10 [2]
1 12 11 [1]
2 17 16 [4, 2, 14, 13, 17, 16, 17, 16, 6, 10, 9]
3 18 17 [3, 1, 13, 12, 16, 15, 16, 15, 5, 9, 8]
8 32 17 [3, 1, 13, 12, 16, 15, 16, 15, 5, 9, 8]
11 35 17 [3, 1, 13, 12, 16, 15, 16, 15, 5, 9, 8]
4 19 18 [4]
5 20 19 [3]
6 21 20 [2]
7 22 21 [1]
9 33 32 [2, 12, 11, 15, 14, 15, 14, 8, 7]
10 34 33 [1, 11, 10, 14, 13, 14, 13, 7, 6]
12 41 33 [1, 11, 10, 14, 13, 14, 13, 7, 6]
13 42 41 [10, 9, 13, 12, 13, 12, 6, 5]
14 43 42 [9, 8, 12, 11, 12, 11, 5, 4]
16 44 42 [9, 8, 12, 11, 12, 11, 5, 4]
15 44 43 [8, 11, 11, 4]
17 56 44 [7, 10, 10, 3]
18 57 56 [6, 9, 9, 2]
19 58 57 [5, 8, 8, 1]
20 59 57 [5, 8, 8, 1]
21 63 59 [4, 7, 7]
22 64 63 [3, 6, 6]
23 65 64 [2, 5, 5]
24 66 65 [1, 4, 4]
25 67 65 [1, 4, 4]
26 78 67 [3, 3]
27 79 78 [2, 2]
28 80 79 [1, 1]
29 81 79 [1, 1]
result for your duplicated question
graph:
output:
child_id parent_id paths
0 11 10 [2, 3]
5 20 10 [2, 3]
1 12 11 [1]
2 17 16 [3]
3 18 17 [2]
4 19 18 [1]
6 21 20 [2]
7 22 21 [1]
8 32 13 [4, 8]
9 33 32 [3, 7]
10 34 33 [2, 6]
12 41 33 [2, 6]
11 35 34 [1]
13 42 41 [5]
14 43 42 [4]
15 44 43 [3]
16 56 44 [2]
17 57 56 [1]
Recursive CTE - Compute Parent Value based on child values
This was a bit tricky, anyhow common table expression do miracles.
The idea I'm using is first to select all the leafs (records with prices only) then go step by step upwards each step I take the price multiplied by the coef, and then again till then end. after that, I will use a sum with a group by to get the final result,
My result match your expected output.
;with leafs as (
select *,ins.Coef*ins.price [total] from Instances ins where price is not null
union all
select ins.*,leafs.total*ins.Coef [total] from leafs
inner join Instances ins on ins.ID=leafs.ParentID
)
select ID,sum(total) Totalprice from leafs
group by ID
order by ID
The result of the query above is as below:-
ID Totalprice
1 187.5
2 12.5
3 50
4 10
5 15
6 12
7 13
Hope this helps.
Related Topics
Select Newest Record Group by Username in SQL Server 2008
Remove Duplicate Records Except the First Record in SQL
How to Represent a Many-To-Many Relationship in a Relational Database
What Does "Select Count(1) from Table_Name" on Any Database Tables Mean
Postgresql - Using Subqueries with Alter Sequence Expressions
SQL Server Procedure Declare a List
Delete Top-N' Rows from a Table with Some Sorting(Order by 'Column')
Managing Hierarchies in SQL: Mptt/Nested Sets VS Adjacency Lists VS Storing Paths
Why Does No Database Fully Support Ansi or Iso SQL Standards
MySQL Myisam Table Performance Problem Revisited
What Are the Differences Between T-Sql, SQL Server and SQL
Pass String Variable Without Quotes in Query Vba
Suppress Output of Variables Substitution in SQLplus
How to Force a SQL Server 2008 Database to Go Offline
Select Count of Rows in Another Table in a Postgres Select Statement
What Is the Optimal Way to Compare Dates in Microsoft SQL Server