search

StarRocks / starrocks

The world's fastest open query engine for sub-second analytics both on and off the data lakehouse. With the flexibility to support nearly any scenario, StarRocks provides best-in-class performance for multi-dimensional analytics, real-time analytics, and ad-hoc queries. A Linux Foundation project.
https://starrocks.io
Apache License 2.0
8.87k stars 1.78k forks source link

Support the range window frame #41808

Open colagy opened 8 months ago

colagy commented 8 months ago

Enhancement

sum(val) OVER (ORDER BY logtime RANGE BETWEEN INTERVAL '10 minute' PRECEDING AND '10 minute' FOLLOWING)
jwengler094 commented 5 months ago

I would like to add +1 on this feature/enchancement request as it opens a range of very interesting use cases.

I am relatively new to Starrocks and looking to migrate from pre-computed / pre-defined Athena aggregations to more real-time / ad-hoc workloads.

Athena is using Presto which supports the window clause based on a (date) range (https://modern-sql.com/caniuse/over_range_between_(datetime)).

The below is an example from a window clause we are currently using in Athena:

w3 AS (
    PARTITION BY permaticker,
    slug
    ORDER BY calendardate RANGE BETWEEN INTERVAL '3' YEAR PRECEDING
        AND CURRENT ROW
),

What is the best alternative to do something like this in Starrocks? Doing a self join feels really unnatural, even though I know this is one of the strong points for Starrocks.