feat(sql): add twap() time-weighted average price aggregate function#6786
feat(sql): add twap() time-weighted average price aggregate function#6786bluestreak01 merged 3 commits intomasterfrom
Conversation
|
Important Review skippedAuto reviews are disabled on this repository. Please check the settings in the CodeRabbit UI or the You can disable this status message by setting the Use the checkbox below for a quick retry:
WalkthroughIntroduces 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
Estimated code review effort🎯 4 (Complex) | ⏱️ ~50 minutes 🚥 Pre-merge checks | ✅ 2 | ❌ 2❌ Failed checks (2 warnings)
✅ Passed checks (2 passed)
✏️ Tip: You can configure your own custom pre-merge checks in the settings. ✨ Finishing touches🧪 Generate unit tests (beta)
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. Comment |
|
@CodeRabbit please critically review |
|
Design & Architecture:
Correctness & Edge Cases:
Memory Management:
Concurrency & Performance:
Testing Coverage:
I'll challenge assumptions, question implementation choices, and look for potential issues that could surface in production. 🧠 Learnings used✅ Actions performedFull review triggered. |
There was a problem hiding this comment.
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 methodstestTwapWithSampleByFillPrevandtestTwapWithSampleByare in wrong alphabetical order.
testTwapWithSampleBy(shorter prefix) should precedetestTwapWithSampleByFillPrev.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: Prefernull::DOUBLEcast 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) tsAs 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
finalqualifier. CurrentlypriceFuncandtsFuncprecedeallocator,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.
...st/java/io/questdb/test/griffin/engine/functions/groupby/TwapGroupByFunctionFactoryTest.java
Show resolved
Hide resolved
|
@CodeRabbit your comments have been addressed in 7d04f3b what do you think? |
|
Tip For best results, initiate chat on the files or code changes. 🧩 Analysis chain🏁 Script executed: Length of output: 8993 🏁 Script executed: Length of output: 12002 🏁 Script executed: Length of output: 1214 🏁 Script executed: Length of output: 1886
✅ Fixed:
❌ Still missing: Field ordering in 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, This is the one remaining actionable item from the review. Can you reorder these fields? 🧠 Learnings used |
[PR Coverage check]😍 pass : 133 / 133 (100.00%) file detail
|
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]>
Summary
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.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 ingetDouble(), with per-buffer memoization to avoid redundant recomputation.Test plan
🤖 Generated with Claude Code