trino Compatibility

[colagy]

Trino (formerly known as PrestoSQL) indeed supports the RANGE BETWEEN clause in window functions. RANGE BETWEEN is used to define a window frame based on a range of values in the ordering column. This is different from ROWS BETWEEN, which is based on a physical number of rows.

Basic Usage of RANGE BETWEEN

RANGE BETWEEN is typically used in conjunction with the ORDER BY clause to define a window frame based on the range of values in the ordering column. Here are some common usage examples:

Example 1: Calculating Aggregations Over a Value Range

Suppose you have a table sales with columns date and amount, and you want to calculate the sales amount for each date along with the total sales amount for the previous 7 days.

SELECT
    date,
    amount,
    SUM(amount) OVER (
        ORDER BY date
        RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW
    ) AS total_amount_7_days
FROM
    sales;

In this example, RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW defines a window that includes the current row and all rows within the previous 7 days.