I need some help with how to compare values in the same column LogNum to find 'unusual' entries. For example, in my table below LogTbl we can see that on ID number 4 the LogNum entry jumps massively compared to the previous pattern of entries.

How can I compare these LogNum entries and identify/output any that have increased by say more than 5% from the previous entry, using LogDate to age the entries?

Thanks.

CodePudding user response：

There is no formal criteria for your words 'masssive' and 'unusual'. However I suppose that you may try to select the records where LogNum will be within the borders (LogNum >= MEAN(LogNum) - 2 * STDDEV(LogNum)) AND - LogNum <= MEAN(LogNum) 2 * STDDEV(LogNum))

CodePudding user response：

There's a wide interperetation of what your requirements are, one possible idea would be to identify a threshold using an average or standard deviation and filter rows that exceed the threshold.

with a as (
    select *, avg(lognum) over() threshold
  from t
)
select *
from a
where lognum > threshold

If you are interested in only differences between adjacent rows you could use lead, ie, find rows where the value is increases by >25% of the previous value

select Id, LogDate, Lognum
from (
    select *, Lead(lognum) over(order by logdate) nxt
    from t
)t
where nxt > lognum * 1.25