---
title: "One Swing, Many Frames: How We Aggregated Video into a Summary with Pixeltable"
date: "2026-06-21"
author: "Pierre Brunelle"
tags:
  - UDA
  - Video AI
  - Use Case
  - Computer Vision
  - Aggregations
  - PixelGolf
  - Sports Analytics
  - Python
  - User-Defined Aggregates
description: "While building PixelGolf, a private practice swing journal, we learned that Pixeltable has several tools that sound like they solve the same problem. They do not. Here is the one problem we had, the wrong paths we tried, and what actually worked: folding dozens of frame rows into one swing summary with @pxt.uda."
url: "https://pixeltable.com/blog/one-swing-many-frames-pixelgolf-uda-aggregation"
---

# One Swing, Many Frames: How We Aggregated Video into a Summary with Pixeltable

**Summary:** When a golfer uploads a swing video to PixelGolf, we need one summary from dozens of frames: swing phases, tempo ratio, spine angle change, head stability. MediaPipe runs per frame. The math runs per frame. The product shows one card per swing. That shape, many child rows folded into one parent row, was the hardest part of our Pixeltable schema. Not pose detection. Not LLM coaching. The fold. If you are building sports video, sensor bursts, document chunks, or session events, this post is a map of which Pixeltable primitive to reach for.

 
*PixelGolf is a private project we are building internally, not a public repo yet. This post shares the aggregation patterns from that work.*

 
## The one problem

 
**User story:** Upload one video, get one swing analysis (phases, metrics, coaching).

 
**Data shape:**

 
```
swings (1 row per upload)
 └── swing_frames (view: ~50-100 rows per video)
 └── frame_metrics (computed per frame)
 └── ??? → swings.swing_analysis (1 JSON blob)
 └── coaching_feedback, fault_tags, swing_score (cascade)
```

 
Everything below is a different answer to that single arrow.

 
## Wrong tool #1: @pxt.udf on the parent table

 
A `@pxt.udf` is **row-local**: one input row, one output value. It runs once per row in whatever table it is attached to.

 
We initially thought: put `compile_swing_analysis()` on `swings` as a computed column. Problem: when Pixeltable evaluates that column for a swing row, it only sees that swing row. It does not automatically gather the view's frame rows.

 
```python
# This mental model is wrong:
swings.add_computed_column(
 swing_analysis=compile_swing_analysis(???) # where do the frames come from?
)
```

 
**Verdict:** Not a Pixeltable bug. Wrong primitive. UDFs are for `pose(frame) → landmarks`, not `frames[] → summary`. See [Python UDFs in Pixeltable](/blog/python-udfs-pixeltable) for row-local transforms.

 
## Wrong tool #2: @pxt.udf + secret collect() inside

 
The escape hatch: write a UDF that looks row-local but secretly queries the child table:

 
```python
@pxt.udf
def analyze_swing(swing_uuid: str) -> dict:
 frames = pxt.get_table('swing_frames').where(...).collect()
 return compile_swing_analysis([r['frame_metrics'] for r in frames])
```

 
This works. We had something like it early on. We removed it because:

 

 - **Lineage breaks.** Pixeltable's [dependency graph](/blog/dependency-graph-magic-computed-columns) does not know the parent column depends on `swing_frames`.

 - **Recompute is opaque.** Change the metrics UDF? The parent may not invalidate correctly.

 - **Ordering is yours to enforce.** Frame order matters for tempo and phases; easy to get wrong outside the query planner.

 - **Upload becomes a lie.** You think you declared the pipeline in `schema.py`, but the real logic hides inside a function body.

 

 
**Verdict:** Same one problem, workable hack, bad long-term tradeoff. We do not recommend it.

 
## Red herring: @pxt.query and retrieval_udf

 
These are for **lookups**: find swings similar to X, fetch session by id, tool-calling templates. Point queries, not folds.

 
If you are aggregating N rows into 1, `@pxt.query` is the wrong drawer. We mention it only because the word "query" sounds general-purpose. It is not. See [reusable query patterns](/blog/reusable-query-patterns-pxt-query) for when queries fit.

 
## Right primitive: @pxt.uda (User Defined Aggregator)

 
Pixeltable's aggregator is the SQL-shaped answer: `update()` called per row in a group, `value()` called once at the end. See [Beyond AVG(): custom aggregations with UDA](/blog/beyond-avg-custom-aggregations-uda) for the full API tour.

 
```python
import config
import pixeltable as pxt
from udfs.swing_analysis import compile_swing_analysis

@pxt.uda(requires_order_by=True, allows_window=True)
class SwingAnalysisAgg(pxt.Aggregator):
 def __init__(self) -> None:
 self._metrics: list[dict | None] = []

 def update(self, frame_metrics: dict | None) -> None:
 self._metrics.append(frame_metrics)

 def value(self) -> dict | None:
 return compile_swing_analysis(self._metrics, fps=config.FRAME_FPS)
```

 
This is the right place for `compile_swing_analysis()`. The fold logic lives in one declarative class, not a Python loop in a router.

 
**Gotcha we hit:** with `requires_order_by=True`, the ordering expression must be the **first positional argument**, not `order_by=pos`:

 
```python
# Correct:
SwingAnalysisAgg(swing_frames.pos, swing_frames.frame_metrics)

# Wrong (raises at call time):
SwingAnalysisAgg(swing_frames.frame_metrics, order_by=swing_frames.pos)
```

 
**Another gotcha:** do not add custom `__init__` params like `fps=` unless you are sure Pixeltable binds them to init only. We passed `fps=4.0` and it leaked into `update()`. We moved `fps` into `value()` via `config.FRAME_FPS` instead.

 
## How we ship it today: group_by + UDA in a query

 
The UDA defines *how* to fold. A `group_by` query defines *which rows* to fold:

 
```python
swing_frames.where(swing_frames.uuid == swing_uuid).group_by(
 swing_frames.uuid
).select(
 analysis=SwingAnalysisAgg(
 swing_frames.pos,
 swing_frames.frame_metrics,
 )
).collect()
```

 
One result row per swing. Ordered frames. Same JSON we would get from hand-rolled Python.

 
Our upload pipeline still has three steps:

 

 - **Materialize frames**: force `pose` and `frame_metrics` to compute on the view

 - **Run the UDA query**: aggregate into analysis JSON

 - **`update(swing_analysis=…, cascade=True)`**: write to parent and trigger LLM columns

 

 
Steps 2-3 live in our upload pipeline module (`finalize_swing_analysis` and `run_upload_pipeline`). Step 1 is still required; the UDA does not replace frame extraction, only the manual list assembly.

 
```python
def run_upload_pipeline(swing_uuid: UUID) -> dict | None:
 materialize_swing_frames(swing_uuid) # step 1
 return finalize_swing_analysis(swing_uuid) # steps 2-3: UDA query + update(cascade=True)
```

 
## The finish line we could not reach: parent computed column

 
What we wanted: insert-only upload, no pipeline module:

 
```python
swings.add_computed_column(
 swing_analysis=SwingAnalysisAgg(
 swing_frames.pos,
 swing_frames.frame_metrics,
 group_by=swings,
 ),
 stored=True,
)
```

 
Insert swing → view materializes → parent column fills → `coaching_feedback` cascades. No `run_upload_pipeline()`. Lineage visible in the catalog.

 
We tried it. Pixeltable raised `NotFoundError: Table was dropped` during `add_computed_column`. Query-time `group_by` on the view works; declarative parent rollup does not (yet). That is the remaining gap: not "aggregation is impossible," but "persist aggregated child data onto parent declaratively."

 
## Decision guide

 
| You want to… | Reach for… |
| --- | --- |
| Transform one column on one row | @pxt.udf |
| Look up rows by key / power tool calling | @pxt.query, retrieval_udf |
| Fold many rows into one value (sum, list, custom JSON) | @pxt.uda + group_by |
| Fold child view rows onto parent at insert time | Parent computed column + window UDA (blocked for us; worth retrying on newer Pixeltable) |
| Fold child rows imperatively at upload | UDA query + update() (what we ship in PixelGolf today) |

 
## What we would tell our past selves

 

 - **It is one problem.** Do not read six Pixeltable features as six bugs.

 - **Start with UDA**, not UDF-on-parent.

 - **Never `collect()` inside a UDF** if you care about lineage.

 - **Order matters** for sports and video: use `requires_order_by=True` and test with reversed frames.

 - **Ship the query + update pattern** while waiting for declarative parent rollup.

 

 
Pixeltable fits our core loop: video → frames → pose → metrics → LLM coaching. The rough edge is cross-table aggregation ergonomics, not the multimodal pipeline itself. For a full video pipeline walkthrough, see [Build a Complete Video Intelligence Pipeline in 20 Minutes](/blog/video-intelligence-pipeline-tutorial). For a UGC gate that folds transcript plus visual signals with a parent UDF instead of a UDA, see [SafeStream](/blog/safestream-ugc-video-moderation).

 
## About PixelGolf

 
PixelGolf is a practice swing journal we are building as a private project: upload a range clip, get body-motion analysis and one coaching fix per swing. The repo is not public yet; this post documents the Pixeltable aggregation patterns we landed on while building it.

 
If you have a cleaner parent-rollup pattern, we would love to hear about it on [Discord](https://discord.gg/QPyqFYx2UN) or in a [Pixeltable discussion](https://github.com/pixeltable/pixeltable/discussions).