Apache Spark / Basic: typed relational foundations / Lesson 6

Aggregations and completeness metrics

Summarize energy with an explicit population, denominator and missing-reading policy.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Distinguish row counts, known-value counts and aggregate filters.
Explain exact decimals, approximate distinct counts and distributed aggregation cost.

State the population before choosing SUM or AVG

A daily total over accepted readings differs from a daily total over all received rows, and an average over known measurements differs from treating missing values as zero. COUNT(*) counts rows, COUNT(kwh) counts non-NULL measurements and SUM ignores NULL inputs. An empty ungrouped input returns zero for count but NULL for sum. Decide whether the output should show zero production, unknown production or no reporting population. A completeness metric should compare observed accepted readings against a justified expected schedule rather than assuming every site produces the same number of readings every day.

Expose missingness beside the total

The fixture contains three readings for north, but only two known measurements. Their sum is five and their average is two and a half. Reporting only SUM would hide the missing observation. Decimal input keeps bounded arithmetic explicit; formatting the number as text should happen at the presentation boundary. The SQL uses one grouping key and a deterministic final order. It is a logical teaching calculation, not a Spark aggregation run, and it does not claim a particular number of shuffle partitions or tasks.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

WITH readings AS (SELECT * FROM VALUES
 ('north',CAST(2 AS DECIMAL(10,3))),
 ('north',CAST(3 AS DECIMAL(10,3))),
 ('north',CAST(NULL AS DECIMAL(10,3))) AS r(site_id,kwh))
SELECT site_id, COUNT(*) AS received, COUNT(kwh) AS known,
 SUM(kwh) AS total_kwh, AVG(kwh) AS mean_known_kwh
FROM readings GROUP BY site_id ORDER BY site_id;

Expected output

site_id | received | known | total_kwh | mean_known_kwh
north   | 3        | 2     | 5         | 2.5

The logical counts are received=3 and known=2; total_kwh=5 and mean_known_kwh=2.5. Display scale can differ.

Filter rows and groups at different stages

WHERE defines eligible source rows before aggregation; HAVING filters aggregate groups afterward. To select sites whose accepted energy exceeds a threshold, first restrict accepted readings and the event interval, then apply the total threshold after grouping. Filtering individual readings above that threshold asks a different question. Conditional aggregation can count invalid or late observations while preserving the denominator, but categories must be mutually exclusive or explicitly overlapping. Keep one outcome classification for each source row when reconciling input totals. Distinct site counts do not replace reading-level completeness checks.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

SELECT site_id, SUM(kwh) AS total_kwh
FROM curated_readings
WHERE quality = 'accepted'
GROUP BY site_id
HAVING SUM(kwh) > 100
ORDER BY site_id;

Assumes an explicitly prepared curated relation. Readings below 100 still contribute; the threshold applies to each site’s aggregate.

Aggregation can move data and retain key state

Spark can perform partial aggregation before shuffling grouped results, but the physical plan depends on the expression and engine decisions. High-cardinality grouping retains many keys and can pressure memory or spill to disk. groupBy on a unique reading identifier may provide little reduction. Exact countDistinct and approximate distinct methods answer different accuracy contracts; an approximate count must be labeled and evaluated against the tolerance. Do not use probabilistic counts as proof that no duplicate or missing readings exist. For those invariants, use explicit identity checks and a complete source reconciliation.

Numeric reproducibility has a representation boundary

Floating-point addition is not associative, so distributed aggregation order can change low-order bits. Use an appropriate decimal representation when the domain requires exact bounded decimal totals, while reviewing precision growth and overflow. Decimal arithmetic still needs units, rounding boundaries and invalid-value rules. A daily energy report should record whether corrections replace a reading or add an adjustment, because replay can otherwise double totals. Validate a small fixture with missing, zero, duplicate and boundary-scale values, then compare representative authorized runtime evidence. No Spark numeric or performance test is performed in this course.

Common errors and fixes

A total hides an incomplete reporting day

Why it happens: NULL measurements were ignored without a separate completeness measure.
How to fix it: Publish known and expected counts with the domain’s incomplete-state policy.
Prevent it next time: Test a site with all readings missing.

A group threshold removes small valid readings

Why it happens: WHERE was used for a condition intended for HAVING.
How to fix it: Apply row eligibility first and aggregate eligibility afterward.
Prevent it next time: Review each predicate against its intended grain.

Approximate distinct counts certify uniqueness

Why it happens: An estimation tool was treated as an invariant check.
How to fix it: Use exact key validation and source reconciliation for uniqueness requirements.
Prevent it next time: Label approximate metrics and their accepted error contract.

Check your understanding

1. What is the average of 2, 3 and NULL under ordinary AVG semantics?

Show worked solution

It is 2.5 over two known values, not 5/3. Publish the known-value denominator and missing count when completeness matters. Substituting zero would change the question and requires a separate documented domain rule.

2. Why can two floating-point totals differ slightly after repartitioning?