Window function for average-CodePudding

I have this table timestamp_table and I'm using Presto SQL

timestamp  |  id
2021-01-01 10:00:00 | 2456

I would like to compute the number of unique IDs in the last 24 and 48 hours and I thought this could be achieved with window functions but I'm struggling. This is my proposed solution, but it needs work

SELECT COUNT(id) OVER (PARTITION BY timestamp ORDER BY timestamp RANGE BETWEEN INTERVAL '24' HOUR PRECEDING AND CURRENT ROW)

CodePudding user response：

You're probably having trouble due to the PARTITION BY clause, since the COUNT will only apply to rows within the same timestamp values.

Try something like this, as a starting point:

The fiddle

SELECT *
     , COUNT(id) OVER (ORDER BY timestamp RANGE BETWEEN INTERVAL '24' HOUR PRECEDING AND CURRENT ROW)
     , MIN(id)   OVER (ORDER BY timestamp RANGE BETWEEN INTERVAL '24' HOUR PRECEDING AND CURRENT ROW)
  FROM tbl
;

CodePudding user response：

I think that you can't get data for both time intervals by one table scan. Because row that is in last 24 hours must be in both groups: 24 hours and 48 hours. So you must do 2 request or union them.

select 'h24', count(distinct id)
from timestamp_table
where timestamp < current_timestamp and timestamp >= date_add(day, -1, current_timestamp)
union all
select 'h48', count(distinct id) 
from timestamp_table
where timestamp < current_timestamp and timestamp >= date_add(day, -2, current_timestamp)