In our scenario, it is often necessary to calculate the user's aggregated indicators in the most recent period of time. For example, if I need to calculate the user's recharge amount in the most recent day, I can do it through the following SQL code. --------------------------------------------------------------------- CREATE TEMPORARY VIEW purchase as select user_id, purchase_price, __ts__ from raw_purchase;
CREATE TEMPORARY VIEW purchase_expire as select user_id, 0 as purchase_price, SESSION_ROWTIME (__ts__, INTERVAL '1' DAY + INTERVAL '1' SECOND) as __ts__ from purchase as T GROUP BY SESSION (T.__ts__, INTERVAL '1' DAY + INTERVAL '1' SECOND), user_id, __ts__; CREATE TEMPORARY VIEW total_purchase as select * from purchase union all select * from purchase_expire; select user_id, SUM(purchase_price) OVER ( PARTITION BY user_id ORDER BY __ts__ RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW ) from total_purchase; ----------------------------------------- If the "Over Aggregation CURRENT_TIMESTAMP" is supported, the above code can be replaced by the following simple code，and the simple code is easier to understand. ----------------------------------------- select user_id, SUM(purchase_price) OVER ( PARTITION BY user_id ORDER BY __ts__ RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT_TIMESTAMP ) from raw_purchase; ----------------------------------------- I have seen the implementation of the RowTimeRangeBoundedPrecedingFunction class. It is very simple to add support for CURRENT_TIMESTAMP.
