ConceptDBISProofread

group-by(T, cols, agg)

The group-by operation partitions a table's rows by the values of the grouping columns and returns one row per group, with an aggregation function applied to each aggregated column.

$T = (C,D,R)$ is an arbitrary table with $k$ columns ( $|C| = k$ )
cols is a sequence $(g_1,...,g_m)$ , where each $g_j \in \{1,...,k\}$
agg is a sequence $((a_1,f_1), ..., (a_n, f_n))$ of pairs, where each $a_j \in \{1,...,k\}$ and each $f_j: \mathcal{P}(D_{a_j}) \rightarrow D'_{a_j}$ is a function

Result: a table $T' = (C', D', R')$ , where#

$C' = (c_{g_1}, ..., c_{g_m}, c_{a_1},...,c_{a_n})$
$D' = (D_{g_1}, ..., D_{g_m}, D'_{a_1},...,D'_{a_n})$
$R' = (r_{g_1}, ..., r_{g_m}, r'_{a_1},...,r'_{a_n}): r\in project(T, (g_1, ..., g_m), T)$ and $r'_{a_j} = f_j(\{r''_{a_j}: (r''_{1},..., r''_k) \in R\ |\ \forall j: r''_{g_j} = r_{g_j} \}$

TodoSuggested by Claude

The note has no H1 named after the note and no ## Sources; suggested: Slides 05, p. 24–29, DBIS_VL05 > Grouping.
Missing conditions from Slide 24: $\{g_1, \dots, g_m\} \cap \{a_1, \dots, a_n\} = \emptyset$ and $m \ge 1$ ; $\mathcal{P}(X)$ is meant as the set of multi-subsets of $X$ .
In $R'$ , the projection should be $\mathrm{project}(T, (g_1, \dots, g_m), \text{true})$ (distinct), not project(T, (g_1, ..., g_m), T); there is a stray ¨ before $\in R$ ; the index $j$ is used both for the aggregate and in “ $\forall j$ ” (use $i$ for the quantifier).
Missing: HAVING as a fourth argument $\varphi$ filtering result rows, WHERE vs. HAVING, the example group-by(movies, (2), ((3, max))) → (2023, 8.5), (2021, 8.0), (2022, 8.6), the hash-map implementation with cost $\sim n \cdot k$ , rows $\sim n/2$ (Slide 28), and the limit of GROUP BY (argmax per group needs a Subquery + join, Slide 29).
Structure (spec §5.3): the H1 would become # Grouping, the definition (now the H1 and ## Result: …) a > [!definition] callout, and the slide example above would fill ## Example.

Connections

Mentioned in inline links9

Ordering
Top- $k$ queries: ORDER BY … DESC LIMIT k gives the $k$ rows with the largest key; per-group maxima need Grouping instead. §
Relational Algebra
| GROUP BY … HAVING … | Grouping group-by (VL05) | §
Relational Model
The operations on tables (Relational Algebra: projection, selection, Cartesian Product, Join, Grouping) take tables and return a table, so they can be composed into query plans. SQL references columns by name $c_j$ ; the formal operations r… §
SQL Query Structure
| GROUP BY | 3 | partitions the surviving rows into groups and aggregates per group | Grouping | §
Subquery
Typical use: per-group values back to rows. Grouping aggregates per column, so “for each year, the movie with the highest score” cannot be written with GROUP BY alone. Compute the aggregate per group in a subquery and join it back to the o… §
VL05 – Joins and more SQL
The slide uses $j$ for both the aggregate index and the “for all” quantifier; here $i$ is used for the latter. See Grouping. §
Grouping — formal group-by operation, HAVING, hash-map implementation and cost estimate §
SQL and Query Planning
Grouping — group-by with aggregation functions and HAVING §
When SQL needs more than one clause. “Per group, a value” → Grouping; “per group, the row with the extreme value” (argmax) cannot be done by GROUP BY alone and needs a Subquery joined back to the table. WHERE filters rows before grouping,… §
SPARQL AggregationADIS
partitions the rows of $R$ into groups with equal values of $(g_1, \dots, g_m)$ and returns one row per group. It contains the values of the grouping variables and, for each (AGG(?x) AS ?y), the aggregate function AGG (COUNT, SUM, AVG, …)… §
Advanced SPARQLADIS
| GROUP BY + aggregate | one row per group | grouping variables + aggregates | GROUP BY (Grouping) | §

Search in these mentions →

Result: a table T′=(C′,D′,R′)T' = (C', D', R')T′=(C′,D′,R′), where#

Result: a table $T' = (C', D', R')$ , where#