Hi,
this is an interesting idea. But as far as I can see, by looking at
other SQL engines like Microsoft SQL Server:
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15
The range is a well-defined set of keywords and CURRENT_TIMESTAMP is not
listed there.
Regards,
Timo
On 01.11.21 08:22, 林挺滨 wrote:
Can anyone give me some advice or information about this feature?
林挺滨 <lintingbi...@gmail.com> 于2021年10月14日周四 下午1:33写道：
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.
