How can I limit the numbers of results being grouped in my Group By in Oracle?

Question

I've got a table of a parameters, values, and times at which those values were recorded.

I've got a procedure which takes in a time, and needs to get the average result of each parameters value in the window of time that is -15/+5 seconds around that time frame. On top of that, I want to make sure that I take the no more than 15 records before the passed in time, and no more than 5 records after it.

For example, maybe I'm recording values of some parameters every second. If I passed in the time 21:30:30, I'd want to get the values between 21:30:15 and 21:30:35. But if I was recording every half second, I'd actually have more parameters that fit in that time frame than I want, and that's where my need to limit my results comes in.

I've read this question and this article which seem pretty related to what I'm trying to do, but unfortunately I'm dealing with Oracle and not MySQL, so I can't use "limit".

I've currently got something that looks like this:

std_values as
(
    select
        V.ParameterId,
        V.NumericValue,
    from
        ValuesTable V 
    where
        V.ValueSource = pValueSource 
        and V.Time >= pSummaryTime - 15/86400
        and V.Time <= pSummaryTime + 5/86400
)
select
    ParameterId,
    avg(NumericValue) as NumericValue
from 
    std_values
group by 
    ParameterId

pValueSource is just something that lets me filter down which value types I'm looking at, and pSummaryTime is the input time that I'm basing my time frame around. The goal here is to get the 15 records before pSummaryTime that falls within that window, and the 5 after that falls within that window, and use those for the average. Currently I'm not limiting the number of "before" and "after" results though, so I'm ending up with the average of everything that falls into that time window. And without something like "limit", I'm not sure how to do this in Oracle.

Answer 1

Sounds like you want a moving window aggregate function. This is part of the Analytical functions feature of Oracle.

It's not my strong suit, and since you didn't include sample tables/data to build a test case, I'll just point you to the Oracle documentation, here: http://docs.oracle.com/cd/B14117_01/server.101/b10736/analysis.htm#i1006709

You probably want something like:

AVG(NumericValue) over (order by pSummaryTime RANGE BETWEEN 15 PRECEDING AND 5 FOLLOWING)

but, like I said, not my strong suit, and totally untested, but, I hope it gets the idea across.

Hope that helps.

Answer 2

Thanks to Mark Bobak's answer getting me on the right track, I ended up with this solution.

with 
values_before as
(
    select
        V.ParameterId,
        V.NumericValue,
        row_number() over (Partition by V.ParameterId order by V.Time desc) as RowNumber
    from
        ValuesTable V
    where
            V.ValueSource = pValueSource 
        and V.Time >= pSummaryTime - 15/86400
        and V.Time <= pSummaryTime
),
values_after as
(
    select
        V.ParameterId,
        V.NumericValue,
        row_number() over (Partition by V.ParameterId order by V.Time desc) as RowNumber
    from
        ValuesTable V
    where
            V.ValueSource = pValueSource 
        and V.Time <= pSummaryTime + 5/86400
        and V.Time > pSummaryTime
),
values_all as
(
    select * from values_before where RowNumber <= 15
    union all
    select * from values_after where RowNumber <= 5
)
select ParameterId, avg(NumericValue) from values_all group by ParameterId 

No doubt there's a better way to do this, but it at least seems to be giving the correct result. The key was using an analytical function to set the row number and order for the 15 before and 5 after, and then filtering my results down to just those.