Filtering outliers from Oracle APEX activity logs

Last year I described a simple test case that described how to remove outliers from a fictional dataset using the STDDEV() analytical function .
http://www.grassroots-oracle.com/2017/06/removing-outliers-using-stddev.html

I want to follow this up with a practical case using one of my favourite data sets - the apex_workspace_activity_logs that record who opened what page, in what context, and how long it took to generate.

I've been keeping an eye on the performance of a particular page, after making a few performance adjustments to some conditions. Unfortunately, we had an unrelated anomaly that pushed average pages times quite high for a short period. Needless to say, this set of outliers transformed my beautiful performance indicating lines to a boxy bell curve.

Oracle APEX page performance data with extreme outlier

A great feature with Oracle JET is the ability to hide certain series, on click within the legend.
In this case I just wanted to ignore the MAX line for this post, which in this chart forms the secondary y-axis.

OracleJET Region Attributes - rescale

Our clients really enjoy this particular feature (so do I), so thanks to the JET team for building such a device, and the APEX team for integrating it.

This graph shows results where I modified the query to filter the outliers, on demand.

Performance graph with outlier removed

Looks like the adjustments to the conditions worked! The trend is downwards.

I tried a few variations to control the switch, but this seemed to perform the most predictably, although I'm not happy with the hardcoded number.

select [aggregate stuff]
from (
 select [all columns]
,case when :P23_IGNORE_OUTLIERS = 'Y' then
  -- only bother calculating when filtering them out
  stddev(elapsed_time) over (order by null)
else
  9999999
end as the_stddev
from [activity logs]
where [time/page is desired]
)
-- only when elapsed time less than 2 standard deviations gets 95% of your data
where (elapsed_time < 2*the_stddev )

I have a generic example of this on livesql.oracle.com I pay attention to these aggregates for our performance reports

• Median - what most users are experiencing
• Average - a typical user experience, influenced by extremes
• Moving average - general trend of visits, spread over a few days. An attempt to normalise local events
• Max - what's the worst some people are experiencing?

Here are some other activity log queries you may find interesting.

Happy graphing!

Removing Outliers using stddev()

A colleague asked me about removing outliers from reports and I knew just the function to give a go.

Some time ago I had to prove that some data we had conformed to a normal distribution. Remember those from high school?

A normal distrubition (bell curve)

That request was about 2007 and I ended up using stddev() and lpad() to produce a vertical shaped bell curve in SQL*Plus. That was pretty cool.

As for removing outliers, we can use this tool to exclude a small percentage of whatever data you have, so that it doesn't skew averages.

If you want to look at the code in context, I've created a test case on livesql.oracle.com

But as an inline reference, first I created a simple table, and populated it with a small set of random data between known range.
(I would like to add this is exactly what Tim Hall recently described here in regard to learning and asking questions.)

Importantly, I also inserted an outlier that was an order of magnitude higher than the rest.
My aim was to ignore this outlier in my SQL that calculated average.

create table outlier_demo  (a number, val number);

insert into outlier_demo  
select rownum -- we don't care  
  , round(dbms_random.value(20,50),1) -- some random numbers between 20 and 50  
from dual   
-- let's create 49 rows  
connect by level < 50

insert into outlier_demo values (0,500)

select * from outlier_demo;

A       VAL
---     ----
1       27.9
2       42.5
3       49.7
4       22.3
5       42.3
6       20.2
7       33.9
8       46.8
...
48      45.7
49      21.8
0       500

The average is higher than expected because of that outlier. I've included a column that calculates the average without the outlier, albiet hardcoded. Notice how close it is to the median? When considering usage statistics from APEX, I've often paid more attention to the median (most rows) than the average (affected by outliers).

select count(*) c   
   -- what's our average?   
  ,round(avg(val)) the_avg   
   -- what would the average be without the known outlier?   
  ,round(avg(case when val < 500 then val end)) no_outlier   
  ,round(stddev(val)) std   
   -- what's the median  
  ,round(median(val)) the_median  
from outlier_demo 

C    THE_AVG NO_OUTLIER    STD   THE_MEDIAN
---  ------- ----------  -----   ----------
50        46         37     66           36

We can take the same function and get the result for every row, that's one way we can use it to filter outlier data.

select a, val  
   -- what is 2 standard deviations away  
   -- provide result for each row  
   ,round(2*stddev(val) over (order by null)) local_std  
from outlier_demo

Re-written as a from clause, we can now filter any rows that have a value higher than x standard deviations from the norm.

select count(*) c   
  ,round(avg(val)) the_avg   
  ,round(avg(case when val < 500 then val end)) no_outlier   
  ,round(stddev(val)) new_stddev  
  ,round(avg(local_std)) filter  
from    
  (select a, val   
   -- what is 2 standard deviations away   
   -- provide result for each row   
   ,round(2*stddev(val) over (order by null)) local_std   
   from outlier_demo   
  )   
-- only average those values within two standard deviations   
where val < local_std;

C   THE_AVG NO_OUTLIER  NEW_STDDEV  FILTER
--  ------- ----------  ----------  ------
49       37        37           9      132

This could be re-written in a form that may be more recognisable to some.

select count(*) c
  ,avg(val) the_avg
  ,avg(case when val < 500 then val end) no_outlier
  ,stddev(val) std
from outlier_demo
where val < (select 2*stddev(val) from outlier_demo);

There are probably a number of variations on how to apply this solution, and possibly a few alternative methods.
It will probably depend on the SQL in your particular report.

This is not to say you should be ignoring outliers completely, but stddev() can help identify them where necessary. And it scales.

Thank Kate for this post.

ps - If you want to dive deeper, Jonathan Gennick (my patient book editor) has two in depth posts on the topic, with some diagrams to help understand the mathematical concepts.

Related post:
Filtering outliers from APEX workspace activity logs

Building simple test scenarios

When I talk to people learning about how to write queries in Oracle - particularly business analysts - a typical conversation would consist of basics such as single row functions, joins and aggregates. However when the subject of DDL is raised, such as CREATE TABLE, I often get the reaction - "Oh, I don't need to know that".

I beg to differ. Regularly I create little tables to test theories with; confirm behaviour; and demonstrate test cases - in fact my schema is littered with them. I think this is an important skill, particularly the latter. If you're having trouble getting some task done, it is often helpful to simplify the problem to the lowest common denominator - a basic test case. Colleagues, DBAs, Oracle support - they'll all ask for the same thing. Visit asktom.oracle.com, he requests and uses them all the time.

So I think one should fluent with creating and dropping small test tables. Take the example of demonstrating the handling of nulls by the AVG group function.

-- always good to have drop at the top so you can repeat the test from scratch
-- ... and clean up your schema!
drop table avg_test;
create table avg_test (a number);
insert into avg_test values (1);
insert into avg_test values (2);
insert into avg_test values (3);
insert into avg_test values (null);
select sum(a)
      ,avg(a)
      ,avg(nvl(a,0))
      ,sum(a)/count(a) my_avg
      ,sum(a)/count(*) my_Avg2
      ,count(a)
      ,count(*)
      ,count(1)
      ,count(null)
from avg_test;

    SUM(A)     AVG(A) AVG(NVL(A,0))     MY_AVG    MY_AVG2
---------- ---------- ------------- ---------- ----------
         6          2           1.5          2        1.5

  COUNT(A)   COUNT(*)   COUNT(1) COUNT(NULL)
---------- ---------- ---------- -----------
         3          4          4           0

So by creating a basic table with just one column, I can insert four rows for this particular case that allows me to demonstrate & confirm the behaviour of avg vs sum/count; and how count(*) differs from count of a particular field. This obviously harmonises with the oft forgot sentence in the documentation:
If you specify the asterisk (*), then this function returns all rows, including duplicates and nulls.

With this in mind, I can then relate back to my actual data and see how the following two queries can differ in behaviour just because of the expression I've used within the count function.

SELECT  count(*), daily_rate
FROM resources
GROUP by daily_rate
HAVING COUNT(*) > 1;

  COUNT(*) DAILY_RATE
---------- ----------
         2        100
         4
         2        120

SELECT  count(daily_rate), daily_rate
FROM resources
GROUP by daily_rate
HAVING COUNT(daily_rate) > 1;

COUNT(DAILY_RATE) DAILY_RATE
----------------- ----------
                2        100
                2        120

We've always got to consider nulls, even with aggregated functions!

In the context of a person learning how Oracle behaves, the simple process of creating small test tables can be quite valuable.