Skip to content

feat(sql): add twap() time-weighted average price aggregate function#6786

Merged
bluestreak01 merged 3 commits intomasterfrom
vi_twap
Feb 17, 2026
Merged

feat(sql): add twap() time-weighted average price aggregate function#6786
bluestreak01 merged 3 commits intomasterfrom
vi_twap

Conversation

@bluestreak01
Copy link
Copy Markdown
Member

Summary

  • Add twap(price, timestamp) aggregate function that computes the time-weighted average price using step-function integration: each price is held constant until the next observation, and the TWAP is the area under this step function divided by the total time span.
  • Supports parallel GROUP BY execution via per-worker native buffers that are merge-sorted during the merge phase.
  • Falls back to a simple arithmetic mean when all observations share the same timestamp.
  • Supports SAMPLE BY with FILL modes.

Design

Workers process non-adjacent page frames via work-stealing, so an incremental weighted-sum approach would incorrectly bridge gaps between frames. Instead, each worker records all (timestamp, price) observations in a native buffer. During the merge phase, two sorted per-worker buffers are combined with a merge-sort step. The final TWAP is computed from the fully sorted buffer in getDouble(), with per-buffer memoization to avoid redundant recomputation.

Test plan

  • Sequential tests: basic TWAP, single row, no rows, all NULLs, some NULLs, null timestamps, same-timestamp fallback, GROUP BY, SAMPLE BY, SAMPLE BY FILL(prev)
  • Parallel tests (4 workers, assertMemoryLeak): chunky data, constant price, single group, small dataset, sparse nulls, many groups (zipf), null dest/src merge paths, all-null prices, no GROUP BY

🤖 Generated with Claude Code

@bluestreak01 bluestreak01 added New feature Feature requests SQL Issues or changes relating to SQL execution labels Feb 16, 2026
@coderabbitai
Copy link
Copy Markdown

coderabbitai bot commented Feb 16, 2026

Important

Review skipped

Auto reviews are disabled on this repository. Please check the settings in the CodeRabbit UI or the .coderabbit.yaml file in this repository. To trigger a single review, invoke the @coderabbitai review command.

You can disable this status message by setting the reviews.review_status to false in the CodeRabbit configuration file.

Use the checkbox below for a quick retry:

  • 🔍 Trigger review

Walkthrough

Introduces a TWAP (time-weighted average price) group-by function implementation enabling parallel aggregation. Adds TwapGroupByFunction for core computation with per-worker buffer management and merge logic, TwapGroupByFunctionFactory for function registration, and comprehensive test suite with 20 test cases covering edge cases and parallel execution scenarios.

Changes

Cohort / File(s) Summary
TWAP Core Implementation
core/src/main/java/io/questdb/griffin/engine/functions/groupby/TwapGroupByFunction.java, core/src/main/java/io/questdb/griffin/engine/functions/groupby/TwapGroupByFunctionFactory.java
New TWAP group-by function with parallel aggregation support. Core implementation handles per-worker observation buffering, timestamp-ordered storage, merge-sort merge logic, and TWAP calculation via weighted price averaging. Factory registers function with "twap(DN)" signature and enables group-by mode.
TWAP Test Suite
core/src/test/java/io/questdb/test/griffin/engine/functions/groupby/TwapGroupByFunctionFactoryTest.java
Comprehensive test coverage including null handling, edge cases (all-null, single row, no rows, same timestamp), basic TWAP calculations, parallel execution with multiple groups, merge scenarios, and group-by/sample-by variants with deterministic assertions.

Estimated code review effort

🎯 4 (Complex) | ⏱️ ~50 minutes

🚥 Pre-merge checks | ✅ 2 | ❌ 2

❌ Failed checks (2 warnings)

Check name Status Explanation Resolution
Docstring Coverage ⚠️ Warning Docstring coverage is 7.50% which is insufficient. The required threshold is 80.00%. Write docstrings for the functions missing them to satisfy the coverage threshold.
Merge Conflict Detection ⚠️ Warning ⚠️ Unable to check for merge conflicts: Failed to fetch base branch: From https://github.com/questdb/questdb
! [rejected] master -> master (non-fast-forward)
+ 8dc0b7e...2e4afb2 master -> origin/master (forced update)
✅ Passed checks (2 passed)
Check name Status Explanation
Title check ✅ Passed The PR title accurately describes the main change: introducing a new twap() aggregate function for computing time-weighted average price, which directly matches the core changeset across all three modified files.
Description check ✅ Passed The PR description is comprehensive and directly related to the changeset, covering the function's purpose, parallel execution design, fallback behavior, and test plan that align with the implementation details across all three files.

✏️ Tip: You can configure your own custom pre-merge checks in the settings.

✨ Finishing touches
🧪 Generate unit tests (beta)
  • Create PR with unit tests
  • Post copyable unit tests in a comment
  • Commit unit tests in branch vi_twap

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

Comment @coderabbitai help to get the list of available commands and usage tips.

@bluestreak01
Copy link
Copy Markdown
Member Author

@CodeRabbit please critically review

@coderabbitai
Copy link
Copy Markdown

coderabbitai bot commented Feb 16, 2026

@bluestreak01: I'll conduct a thorough, critical review of this TWAP implementation. I'll focus on:

Design & Architecture:

  • The choice of storing all observations in native buffers vs. incremental computation
  • Handling of non-adjacent page frames in parallel execution
  • Merge-sort strategy for combining worker buffers

Correctness & Edge Cases:

  • Step-function integration logic and numerical stability
  • Boundary conditions (single row, same timestamps, sparse data)
  • NULL handling in both price and timestamp columns
  • Time span calculation edge cases (zero duration, overflow)

Memory Management:

  • Native buffer allocation/deallocation patterns
  • Memory leak prevention in error paths
  • Buffer growth strategy under load

Concurrency & Performance:

  • Thread-safety of worker buffer operations
  • Merge phase correctness with multiple workers
  • Memoization effectiveness and invalidation

Testing Coverage:

  • Parallel execution scenarios with work-stealing
  • Stress cases (many groups, large datasets)
  • FILL mode interactions

I'll challenge assumptions, question implementation choices, and look for potential issues that could surface in production.


🧠 Learnings used
Learnt from: jerrinot
Repo: questdb/questdb PR: 0
File: :0-0
Timestamp: 2026-02-04T11:18:36.492Z
Learning: For PRs by jerrinot, always use a thorough and critical review mode: question design decisions, challenge assumptions, scrutinize implementations for edge cases and potential issues, and avoid superficial validation. They prefer hard-core reviews that push back on the code.
✅ Actions performed

Full review triggered.

Copy link
Copy Markdown

@coderabbitai coderabbitai bot left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Actionable comments posted: 1

🤖 Fix all issues with AI agents
Verify each finding against the current code and only fix it if needed.


In
`@core/src/main/java/io/questdb/griffin/engine/functions/groupby/TwapGroupByFunction.java`:
- Around line 77-90: The instance fields in TwapGroupByFunction are not
alphabetically ordered; reorder the instance field declarations (allocator,
cachedPtr, cachedValue, valueIndex, priceFunc, tsFunc) so they follow
alphabetical order while keeping static fields (ENTRY_SIZE, INITIAL_CAPACITY)
first; update the field block in the TwapGroupByFunction class to list these
instance fields alphabetically to comply with the coding guideline.

In
`@core/src/test/java/io/questdb/test/griffin/engine/functions/groupby/TwapGroupByFunctionFactoryTest.java`:
- Around line 223-244: The test method testTwapParallelMergeValidDestNullSrc
incorrectly uses long_sequence(1_000_000) so the predicate x > 1_000_000 never
produces null prices; update the test SQL to use long_sequence(2_000_000)
(matching testTwapParallelMergeNullDestValidSrc) so rows with x > 1_000_000
produce nulls and the "valid dest, null src" merge path is exercised, and update
the inline comment if needed to reflect the two-million-row sequence.
- Around line 413-463: The two test methods are out of alphabetical order; move
the method testTwapWithSampleBy so it appears before
testTwapWithSampleByFillPrev (both are public tests in
TwapGroupByFunctionFactoryTest) to comply with the class member sorting
rule—i.e., cut the entire testTwapWithSampleBy method (signature and body) and
insert it above the testTwapWithSampleByFillPrev method, preserving imports and
annotations.
- Around line 123-149: In the test method testTwapParallelAllNullPrices update
the SQL that creates tbl to use the preferred cast syntax by replacing
"CAST(null AS DOUBLE) price" with "null::DOUBLE price" in the multi-line string;
locate the SQL inside the execute(...) call within
TwapGroupByFunctionFactoryTest and make the single-token substitution so the
test follows the expr::TYPE casting guideline.
🧹 Nitpick comments (3)
🤖 Fix all nitpicks with AI agents
Verify each finding against the current code and only fix it if needed.


In
`@core/src/main/java/io/questdb/griffin/engine/functions/groupby/TwapGroupByFunction.java`:
- Around line 77-90: The instance fields in TwapGroupByFunction are not
alphabetically ordered; reorder the instance field declarations (allocator,
cachedPtr, cachedValue, valueIndex, priceFunc, tsFunc) so they follow
alphabetical order while keeping static fields (ENTRY_SIZE, INITIAL_CAPACITY)
first; update the field block in the TwapGroupByFunction class to list these
instance fields alphabetically to comply with the coding guideline.

In
`@core/src/test/java/io/questdb/test/griffin/engine/functions/groupby/TwapGroupByFunctionFactoryTest.java`:
- Around line 413-463: The two test methods are out of alphabetical order; move
the method testTwapWithSampleBy so it appears before
testTwapWithSampleByFillPrev (both are public tests in
TwapGroupByFunctionFactoryTest) to comply with the class member sorting
rule—i.e., cut the entire testTwapWithSampleBy method (signature and body) and
insert it above the testTwapWithSampleByFillPrev method, preserving imports and
annotations.
- Around line 123-149: In the test method testTwapParallelAllNullPrices update
the SQL that creates tbl to use the preferred cast syntax by replacing
"CAST(null AS DOUBLE) price" with "null::DOUBLE price" in the multi-line string;
locate the SQL inside the execute(...) call within
TwapGroupByFunctionFactoryTest and make the single-token substitution so the
test follows the expr::TYPE casting guideline.
core/src/test/java/io/questdb/test/griffin/engine/functions/groupby/TwapGroupByFunctionFactoryTest.java (2)

413-463: Test methods testTwapWithSampleByFillPrev and testTwapWithSampleBy are in wrong alphabetical order.

testTwapWithSampleBy (shorter prefix) should precede testTwapWithSampleByFillPrev.

Swap order
     `@Test`
-    public void testTwapWithSampleByFillPrev() throws Exception {
+    public void testTwapWithSampleBy() throws Exception {
         assertMemoryLeak(() -> {
-            execute("CREATE TABLE tbl (price DOUBLE, ts TIMESTAMP) TIMESTAMP(ts) PARTITION BY DAY");
+            execute("CREATE TABLE tbl (price DOUBLE, ts TIMESTAMP) TIMESTAMP(ts) PARTITION BY DAY");
             execute("""
                     INSERT INTO tbl VALUES
                     (10.0, '2024-01-01T00:00:00.000000Z'),
                     (20.0, '2024-01-01T00:00:10.000000Z'),
                     (30.0, '2024-01-01T00:00:30.000000Z'),
-                    (100.0, '2024-01-01T00:02:00.000000Z'),
-                    (200.0, '2024-01-01T00:02:20.000000Z')
+                    (100.0, '2024-01-01T00:01:00.000000Z'),
+                    (200.0, '2024-01-01T00:01:20.000000Z')
                     """);
             assertSql(
                     """
                     ts\ttwap
                     2024-01-01T00:00:00.000000Z\t16.666666666666668
-                    2024-01-01T00:01:00.000000Z\t16.666666666666668
-                    2024-01-01T00:02:00.000000Z\t100.0
+                    2024-01-01T00:01:00.000000Z\t100.0
                     """,
-                    "SELECT ts, twap(price, ts) FROM tbl SAMPLE BY 1m FILL(prev)"
+                    "SELECT ts, twap(price, ts) FROM tbl SAMPLE BY 1m"
             );
         });
     }

     `@Test`
-    public void testTwapWithSampleBy() throws Exception {
+    public void testTwapWithSampleByFillPrev() throws Exception {
         assertMemoryLeak(() -> {
             execute("CREATE TABLE tbl (price DOUBLE, ts TIMESTAMP) TIMESTAMP(ts) PARTITION BY DAY");
             execute("""
                     INSERT INTO tbl VALUES
                     (10.0, '2024-01-01T00:00:00.000000Z'),
                     (20.0, '2024-01-01T00:00:10.000000Z'),
                     (30.0, '2024-01-01T00:00:30.000000Z'),
-                    (100.0, '2024-01-01T00:01:00.000000Z'),
-                    (200.0, '2024-01-01T00:01:20.000000Z')
+                    (100.0, '2024-01-01T00:02:00.000000Z'),
+                    (200.0, '2024-01-01T00:02:20.000000Z')
                     """);
             assertSql(
                     """
                     ts\ttwap
                     2024-01-01T00:00:00.000000Z\t16.666666666666668
-                    2024-01-01T00:01:00.000000Z\t100.0
+                    2024-01-01T00:01:00.000000Z\t16.666666666666668
+                    2024-01-01T00:02:00.000000Z\t100.0
                     """,
-                    "SELECT ts, twap(price, ts) FROM tbl SAMPLE BY 1m"
+                    "SELECT ts, twap(price, ts) FROM tbl SAMPLE BY 1m FILL(prev)"
             );
         });
     }

As per coding guidelines, "Java class members must be grouped by kind (static vs. instance) and visibility, and sorted alphabetically. When adding new methods or fields, insert them in the correct alphabetical position among existing members of the same kind."

🤖 Prompt for AI Agents
Verify each finding against the current code and only fix it if needed.

In
`@core/src/test/java/io/questdb/test/griffin/engine/functions/groupby/TwapGroupByFunctionFactoryTest.java`
around lines 413 - 463, The two test methods are out of alphabetical order; move
the method testTwapWithSampleBy so it appears before
testTwapWithSampleByFillPrev (both are public tests in
TwapGroupByFunctionFactoryTest) to comply with the class member sorting
rule—i.e., cut the entire testTwapWithSampleBy method (signature and body) and
insert it above the testTwapWithSampleByFillPrev method, preserving imports and
annotations.

123-149: Prefer null::DOUBLE cast syntax per coding guidelines.

Proposed fix
             SELECT
                 rnd_symbol('A','B','C','D','E') sym,
-                CAST(null AS DOUBLE) price,
+                null::DOUBLE price,
                 timestamp_sequence(0, 1000) ts

As per coding guidelines, "Always prefer the expr::TYPE syntax for casts over CAST(expr, type) in QuestDB SQL".

🤖 Prompt for AI Agents
Verify each finding against the current code and only fix it if needed.

In
`@core/src/test/java/io/questdb/test/griffin/engine/functions/groupby/TwapGroupByFunctionFactoryTest.java`
around lines 123 - 149, In the test method testTwapParallelAllNullPrices update
the SQL that creates tbl to use the preferred cast syntax by replacing
"CAST(null AS DOUBLE) price" with "null::DOUBLE price" in the multi-line string;
locate the SQL inside the execute(...) call within
TwapGroupByFunctionFactoryTest and make the single-token substitution so the
test follows the expr::TYPE casting guideline.
core/src/main/java/io/questdb/griffin/engine/functions/groupby/TwapGroupByFunction.java (1)

77-90: Instance fields should be sorted alphabetically.

Per coding guidelines, instance fields should be sorted alphabetically regardless of final qualifier. Currently priceFunc and tsFunc precede allocator, cachedPtr, cachedValue.

Proposed reordering
 public class TwapGroupByFunction extends DoubleFunction implements GroupByFunction, BinaryFunction {
     private static final long ENTRY_SIZE = 16;
     private static final long INITIAL_CAPACITY = 16;
+    private GroupByAllocator allocator;
+    private long cachedPtr;
+    private double cachedValue;
     private final Function priceFunc;
     private final Function tsFunc;
-    private GroupByAllocator allocator;
-    private long cachedPtr;
-    private double cachedValue;
     private int valueIndex;

As per coding guidelines, "Java class members must be grouped by kind (static vs. instance) and visibility, and sorted alphabetically."

🤖 Prompt for AI Agents
Verify each finding against the current code and only fix it if needed.

In
`@core/src/main/java/io/questdb/griffin/engine/functions/groupby/TwapGroupByFunction.java`
around lines 77 - 90, The instance fields in TwapGroupByFunction are not
alphabetically ordered; reorder the instance field declarations (allocator,
cachedPtr, cachedValue, valueIndex, priceFunc, tsFunc) so they follow
alphabetical order while keeping static fields (ENTRY_SIZE, INITIAL_CAPACITY)
first; update the field block in the TwapGroupByFunction class to list these
instance fields alphabetically to comply with the coding guideline.

@bluestreak01
Copy link
Copy Markdown
Member Author

@CodeRabbit your comments have been addressed in 7d04f3b what do you think?

@coderabbitai
Copy link
Copy Markdown

coderabbitai bot commented Feb 16, 2026

Tip

For best results, initiate chat on the files or code changes.

🧩 Analysis chain

🏁 Script executed:

#!/bin/bash
# Fetch the specific commit diff to see what changes were made
git show 7d04f3bce484fad3eaf27a6cef83872c1eea9017

Length of output: 8993


🏁 Script executed:

#!/bin/bash
# Fetch review comments from the PR to see what issues were raised
gh pr view 6786 --repo questdb/questdb --json reviews,comments --jq '.reviews[] | select(.author.login == "coderabbitai") | {state: .state, body: .body, submittedAt: .submittedAt}'

Length of output: 12002


🏁 Script executed:

#!/bin/bash
# Check the current state of testTwapParallelMergeValidDestNullSrc
rg -n -A 20 'testTwapParallelMergeValidDestNullSrc' core/src/test/java/io/questdb/test/griffin/engine/functions/groupby/TwapGroupByFunctionFactoryTest.java

Length of output: 1214


🏁 Script executed:

#!/bin/bash
# Check testTwapParallelSparseNulls to see if it also had the issue
rg -n -B 5 -A 20 'testTwapParallelSparseNulls' core/src/test/java/io/questdb/test/griffin/engine/functions/groupby/TwapGroupByFunctionFactoryTest.java

# Also check the field ordering issue in the main file
echo "=== Checking field ordering in TwapGroupByFunction ==="
rg -n -A 15 'public class TwapGroupByFunction' core/src/main/java/io/questdb/griffin/engine/functions/groupby/TwapGroupByFunction.java

Length of output: 1886


@bluestreak01: Good progress—you've addressed 3 of 4 issues from my review:

✅ Fixed:

  1. Test method ordering (line 413-463): testTwapWithSampleBy now correctly precedes testTwapWithSampleByFillPrev alphabetically.
  2. Cast syntax (line 129): Changed to preferred null::DOUBLE syntax.
  3. Null test bug (line 232): You fixed the testTwapParallelMergeValidDestNullSrc issue, though differently than I suggested. My recommendation was long_sequence(2_000_000) with x > 1_000_000. Your solution—long_sequence(1_000_000) with x > 500_000—achieves the same coverage (second half null) with fewer rows. Both are valid; yours is more efficient.

❌ Still missing:

Field ordering in TwapGroupByFunction.java (lines 80-85): The instance fields are still not alphabetically ordered. Per coding guidelines, they should be:

private static final long ENTRY_SIZE = 16;
private static final long INITIAL_CAPACITY = 16;
private GroupByAllocator allocator;        // 'a' comes first
private long cachedPtr;                     // then 'c'
private double cachedValue;                 // then 'c'
private final Function priceFunc;           // then 'p'
private final Function tsFunc;              // then 't'
private int valueIndex;                     // then 'v'

Currently, priceFunc and tsFunc appear before allocator, which violates the alphabetical sorting requirement (the final modifier doesn't exempt them from alphabetical ordering).

This is the one remaining actionable item from the review. Can you reorder these fields?


🧠 Learnings used
Learnt from: jerrinot
Repo: questdb/questdb PR: 0
File: :0-0
Timestamp: 2026-02-04T11:18:36.492Z
Learning: For PRs by jerrinot, always use a thorough and critical review mode: question design decisions, challenge assumptions, scrutinize implementations for edge cases and potential issues, and avoid superficial validation. They prefer hard-core reviews that push back on the code.

Learnt from: javier
Repo: questdb/questdb PR: 0
File: :0-0
Timestamp: 2026-02-06T18:22:28.130Z
Learning: In questdb/questdb, for PRs requested by javier, provide line-cited, push-back style reviews with concrete follow-ups (tests, diffs, and performance considerations).

Learnt from: javier
Repo: questdb/questdb PR: 0
File: :0-0
Timestamp: 2026-02-06T18:22:28.130Z
Learning: For questdb/questdb, javier expects a rigorous, push-back style review; always include concrete line-cited findings and propose targeted tests/fixes when reviewing javier’s PRs.

Learnt from: javier
Repo: questdb/questdb PR: 0
File: :0-0
Timestamp: 2026-02-06T18:22:28.130Z
Learning: In questdb/questdb, javier prefers hard‑core, critical reviews that challenge design choices and edge cases; respond with deep, push-back style analyses for javier’s requests.

@glasstiger
Copy link
Copy Markdown
Contributor

[PR Coverage check]

😍 pass : 133 / 133 (100.00%)

file detail

path covered line new line coverage
🔵 io/questdb/griffin/engine/functions/groupby/TwapGroupByFunction.java 129 129 100.00%
🔵 io/questdb/griffin/engine/functions/groupby/TwapGroupByFunctionFactory.java 4 4 100.00%

@bluestreak01 bluestreak01 merged commit 101e554 into master Feb 17, 2026
44 checks passed
@bluestreak01 bluestreak01 deleted the vi_twap branch February 17, 2026 19:31
bluestreak01 added a commit to questdb/documentation that referenced this pull request Feb 25, 2026
Document the new twap(price, timestamp) time-weighted average price
aggregate function introduced in questdb/questdb#6786.

- Reference entry in aggregation functions with formula, parameters,
  NULL handling, and demo dataset examples
- Cookbook recipe with TWAP-vs-VWAP comparison table, step-function
  diagram, hourly SAMPLE BY, tick-level VWAP comparison, and
  execution quality analysis
- Cross-references from finance functions page and VWAP recipe
- Sidebar and cookbook index entries

Co-Authored-By: Claude Opus 4.6 <[email protected]>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

New feature Feature requests SQL Issues or changes relating to SQL execution

Projects

None yet

Development

Successfully merging this pull request may close these issues.

2 participants