87 Group Aggregation
Group aggregation is query-result shaping over logic-frame extensions. A fold consumes the observed values of a unary path frame, and tuple construction chooses which labels are exposed in the result. When the result is displayed as a relation, that relation is the observed extension of the frame, not the primary denotation of the path expression.
The examples in this chapter compare Predica idioms with Tutorial D SUMMARIZE. They should be
read extensionally: the Predica expression and the Tutorial D expression are being compared by the
materialised relation shape they produce.
Aggregates in Tuple Results
s.supplier.(id, shipment.qty.Σsum)
has this materialised extension:
{
(id = 1, qty = 1300),
(id = 2, qty = 700),
(id = 3, qty = 200),
(id = 4, qty = 900),
(id = 5, qty = 0)
}
In the tuple constructor expression (id, shipment.qty.Σsum) there are two path tails.
Unless renamed, the attribute name to be used for a path tail is the final identifier
before any aggregation operator. The path expression still denotes a logic frame; the relation
display above is the observed extension of that frame.
Implicit Grouping by Receiver
Suppose the required result is the number of shipments per supplier. In Tutorial D this can be
written with SUMMARIZE on the shipments relation SP:
SUMMARIZE SP PER SP { S# } ADD COUNT(*) AS NUM_SHIPMENTS
In Predica, the direct object-centric expression is:
s.supplier.(id, shipment.Σcount as num_shipments)
In logic-frame display notation this means:
id=I:Integer, num_shipments=C:Integer
where
∃S:Supplier
s.supplier(S)
∧ S.id(I)
∧ C = |{ SP:Shipment | S.shipment(SP) }|
This expression includes suppliers having no shipments, with count zero. That is often the useful object-centric result, but it is not the same as summarising only the observed shipment extension. To match the Tutorial D expression above, filter to suppliers with at least one shipment:
s.supplier.[shipment.Σ ≠ ∅].(id, shipment.Σcount as num_shipments)
Explicit Grouping
Explicit grouping groups the observed extension by a key expression. The key expression is evaluated for each member of the receiver, and members with the same key become a group.
s.shipment.groupby(supplier.id)
In the relation view, this can be displayed as a grouped result with a nested relation display, much
like Tutorial D's GROUP. The nested display is not a claim that relations are first-class Predica
values. It is a way to show the grouped observed extension.
groupby does not compute aggregates. It restructures the observed extension so later folds can
aggregate each group.
| s.shipment | s.shipment.groupby(supplier.id) |
|---|---|
|
|
|
The following Predica expression:
s.shipment.groupby(supplier.id).(id, group.qty.Σsum as total_qty)
or, with an explicit group label:
s.shipment.group(g)by(supplier.id).(id, g.qty.Σsum as total_qty)
has the same observed extension as the Tutorial D expression:
SUMMARIZE SP PER SP { S# } ADD SUM(QTY) AS TOTAL_QTY
Group aggregation examples
The following examples are a comparison catalogue. The Predica column shows path expressions whose
observed extensions correspond to the Tutorial D results. Examples that collect nested results with
Σ should be read as producing set-valued result terms; any nested relation tables in the display are
relation-view renderings of those values, not a separate relation-valued denotational semantics.
| # | Description | Tutorial D (S, P, SP) | Predica |
|---|---|---|---|
| 1 | Total quantity shipped by supplier |
|
|
| 2 | Total quantity shipped by part |
|
|
| 3 | Number of shipments per supplier |
|
|
| 4 | Number of distinct parts shipped by supplier |
|
|
| 5 | Total quantity and number of shipments per supplier |
|
|
| 6 | Total quantity shipped per supplier–city |
|
|
| 7 | Total quantity and number of suppliers per part |
|
|
| 8 | Total weight of parts shipped per supplier |
|
|
| 9 | List of parts shipped by each supplier |
|
|
| 10 | Number of heavy parts shipped (weight > 15) per supplier |
|
|
Nested grouping examples
The following examples illustrate nested grouping, multi-level grouping, tuple-valued grouping keys, and grouping with filters:
| # | Description | Tutorial D (S, P, SP) | Predica |
|---|---|---|---|
| 11 | Total quantity shipped per supplier per part (two-level grouping) |
|
|
| 12 | For each supplier, aggregates grouped by part (nested grouping) |
|
|
| 13 | For each city, total quantity shipped and number of suppliers |
|
|
| 14 | For each part, list of suppliers and total quantity per supplier |
|
|
| 15 | For each supplier, total quantity of red parts shipped |
|
|
| 16 | For each supplier, number of parts shipped grouped by color |
|
|
| 17 | For each city, list of suppliers and their total shipments |
|
|
| 18 | For each part, total quantity shipped by suppliers in London |
|
|
The following examples illustrate deeply nested grouping, multi-attribute tuple keys, aggregates over aggregates, and multi-level summarization:
| # | Description | Tutorial D (S, P, SP) | Predica |
|---|---|---|---|
| 19 | For each supplier, for each part, total quantity (deep nested grouping) |
|
|
| 20 | For each city, for each supplier, total shipments (multi-level grouping) |
|
|
| 21 | For each supplier, total quantity per part color (grouping by derived attribute) |
|
|
| 22 | For each part, total quantity per supplier city (tuple-valued grouping key) |
|
|
| 23 | For each supplier, average quantity per part (aggregate over aggregates) |
|
|
| 24 | For each city, number of suppliers and total shipments (multi-aggregate grouping) |
|
|
| 25 | For each supplier, summary of heavy vs light parts shipped |
|
|
| 26 | For each part, summary of shipments grouped by supplier status |
|
|
| 27 | For each supplier, total shipments and average shipment size |
|
|