I have a table (let's call it Data) with a set of object IDs, numeric values and dates. I would like to identify the objects whose values had a positive trend over the last X minutes (say, an hour).
Example data:
entity_id | value | date
1234 | 15 | 2014-01-02 11:30:00
5689 | 21 | 2014-01-02 11:31:00
1234 | 16 | 2014-01-02 11:31:00
I tried looking at similar questions, but didnt find anything that helps unfortunately...
If someone needs this in Mysql, this is the code that works for me.
datapoint | plays | status_time
1234 | 15 | 2014-01-02 11:30:00
5689 | 21 | 2014-01-02 11:31:00
1234 | 16 | 2014-01-02 11:31:00
select datapoint, 1.0*sum((x-xbar)*(y-ybar))/sum((x-xbar)*(x-xbar)) as Beta
from
(
select datapoint,
avg(plays) over(partition by datapoint) as ybar,
plays as y,
avg(TIME_TO_SEC(TIMEDIFF('2021-03-22 21:00:00', status_time))) over(partition by datapoint) as xbar,
TIME_TO_SEC(TIMEDIFF('2021-03-22 21:00:00', status_time)) as x
from aggregate_datapoints
where status_time BETWEEN'2021-03-22 21:00:00' and '2021-03-22 22:00:00'
and type = 'topContent') as calcs
group by datapoint
having 1.0*sum((x-xbar)*(y-ybar))/sum((x-xbar)*(x-xbar))>0