SQL Grouping spitballing

This eventually became the separate module AggregateFunctions

Exploratory survey for Aggregation / Grouping in the Qwiratry language redesign (see SQL-Features-Plan.md §6, still TBR). Companion gap list: SQL-bits-missing.md (GROUP BY, WINDOW, Unique/DISTINCT). Research: SQL-AST-Qwiratry-Mapping.md.

Status: Spitballing / Outline.Scope: How other languages express grouping-shaped features, plus provisional prposed Qwiratry features.Non-scope: Backend SQL encoding, TaOP Relation APIs.

1. Grouping Survey

How existing languages and APIs express grouping, windows, and related aggregation features. Cells are representative idioms, not exhaustive catalogues. — means that family does not really own that column (or treats it as out of scope).

Rows mix SQL / query ASTs, host languages, dataframes, document/graph query surfaces, MapReduce-family systems, and algebraic analogies. Use this when designing Qwiratry operators for group-by, window, distinct, and aggregates (vs today’s ReduceOperator ⌿).

We don't need a column for HAVING, because we just do 𜸈/σ after the GROUP BY equivalent.

1.1 Column meanings

• GROUP BY — Partition a relation (or stream) into groups by key(s), then typically collapse each group. Classical SQL GROUP BY; dataframe groupby; RA γ. Proposed Qwiratry naming: Partition + Aggregation.

• WINDOW — Compute over a peer partition without collapsing rows (OVER (PARTITION BY … ORDER BY …); rolling / sliding frames). Not the same as GROUP BY. Proposed Qwiratry naming: Partition (row-preserving).

• DISTINCT / Unique — Eliminate duplicate rows or values (SELECT DISTINCT, .unique, RA δ). Not a fold to one accumulator.

• Aggregates — Per-group or global summary functions (COUNT / SUM / …). May appear with GROUP BY, as a global fold, or as window functions.

• Reduce with Partition — Whether the language has a reduce/fold-style combinator that runs per partition/key (MapReduce reduce, Spark reduceByKey, Kotlin groupingBy().reduce, …), as opposed to only a global reduce or only named aggregates (SUM) without a user fold.

Notes for Qwiratry design

• GROUP BY vs WINDOW: GROUP BY collapses; WINDOW keeps row cardinality. Both need partition keys; do not overload one operator for both. Proposed naming uses Partition for both, distinguished by whether Aggregation collapses the partition.

• HAVING vs σ: No HAVING column in the survey — use 𜸈/σ after the GROUP BY equivalent (SQL-Features-Plan.md §3.1).

• DISTINCT vs ⌿: ReduceOperator folds to one value; DISTINCT removes duplicates and yields a set-like relation. SQL-bits-missing’s “Unique” is the latter.

• Qwiratry today only has ⌿ (and σ / Π / sort). GROUP BY / WINDOW / DISTINCT / partitioned reduce are still —.

• Proposed Qwiratry Symbols (provisional): γ / ω / δ; ⌿ remains fold. Proposed Qwiratry Naming: Partition, Aggregation, Unique, Reduce-over-Partition (TBD). Not normative until Plan §6 is written.

• Reduce with Partition is the missing bridge between ⌿ and GROUP BY: many languages expose it explicitly (MapReduce, reduceByKey, groupingBy().reduce).

• Under the hood: SQL WITH (CTE) can materialise grouped pipelines; not required in the language surface for a first cut.

GROUP BY vs WINDOW vs Qwiratry Reduce (⌿)

Side-by-side of SQL’s two partition styles and today’s Qwiratry fold. Reduce is not a stand-in for either SQL feature; it only covers the “collapse the whole input with a combining function” corner.

Takeaways

1. GROUP BY ≈ many reduces (one fold per key), then emit group rows.

2. WINDOW ≈ annotate each row using neighbours in a partition; it does not replace GROUP BY and does not match ⌿.

3. ⌿ alone cannot express GROUP BY dept or SUM(…) OVER (PARTITION BY dept): it has no partition and collapses to a single value.

4. Designing γ / ω as separate operators (see Proposed Qwiratry Symbols) avoids overloading ⌿ for all three jobs; a partitioned reduce may still be the implementation bridge (or a fourth surface name).