O'Reilly logo

The Art of SQL by Peter Robson, Stephane Faroult

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Aggregating Values from Trees

Now that you know how to deal with trees, let's look at how you can aggregate values held in tree structures. Most cases for the aggregation of values held in hierarchical structures fall into two categories: aggregation of values stored in leaf nodes and propagation of percentages across various levels in the tree.

Aggregation of Values Stored in Leaf Nodes

In a more realistic example than the one used to illustrate the Vandamme and Highlanders queries, nodes carry information—especially the leaf nodes. For instance, regiments should hold the number of their soldiers, from which we can derive the strength of every fighting unit.

Modeling head counts

If we take the same example we used previously, restricting it to a subset of the French Third Corps of General Vandamme and only descending to the level of brigades, a reasonably correct representation (as far as we can be correct) would be the tables described in the following subsections.

UNITS. Each row in the units table describes the various levels of aggregation (army corps, division, brigade) as in tables adjacency_model, materialized_path_models, or nested_sets_model, but without any attribute to specify how each unit relates to a larger unit:

 ID NAME COMMANDER -- -------------------------- ----------------------------------------------- 1 III Corps Général de Division Dominique Vandamme 2 8th Infantry Division Général de Division Baron Etienne-Nicolas Lefol 3 1st Brigade Général de Brigade Billard ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required