1.1 - 8.10 - OutlierFilter Example: OutlierMethod ('MAD-median'), ReplacementValue ('median') - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)

SQL Call

SELECT * FROM OutlierFilter (
 ON ville_pressuredata AS InputTable
 OUT TABLE OutputTable (of_output2)
 OUT TABLE OutliersTable (of_outlier2)
 USING
 TargetColumns ('pressure_mbar')
 ReplacementValue ('median')
 OutlierMethod ('MAD-median')
 MADScaleConstant (1.4826)
 MADThreshold (3)
 GroupByColumns ('city')
) AS dt ;

Output

The outlying values have been replaced with the median value for the group.

 message                            
 ---------------------------------- 
 Output tables created successfully

SELECT * FROM of_output2 ORDER BY 1, 2, 3;
 sn  city        period                     pressure_mbar 
 --- ----------- -------------------------- ------------- 
   1 ashville    2010-01-01 00:00:00.000000        1020.5
   2 ashville    2010-01-01 01:00:00.000000        1020.8
   3 ashville    2010-01-01 02:00:00.000000        1020.0
   4 ashville    2010-01-01 03:00:00.000000        1020.8
   5 ashville    2010-01-01 04:00:00.000000        1020.2
   6 ashville    2010-01-01 05:00:00.000000        1020.0
   7 ashville    2010-01-01 06:00:00.000000        1020.3
   8 ashville    2010-01-01 07:00:00.000000        1020.8
   9 ashville    2010-01-01 08:00:00.000000        1021.3
  10 ashville    2010-01-01 09:00:00.000000        1021.7
  11 ashville    2010-01-01 10:00:00.000000        1022.1
  12 ashville    2010-01-01 11:00:00.000000        1022.0
  13 ashville    2010-01-01 12:00:00.000000        1021.1
  14 ashville    2010-01-01 13:00:00.000000        1020.0
  15 ashville    2010-01-01 14:00:00.000000        1019.3
  16 ashville    2010-01-01 15:00:00.000000        1019.0
  17 ashville    2010-01-01 16:00:00.000000        1019.2
  18 ashville    2010-01-01 17:00:00.000000        1019.6
  19 ashville    2010-01-01 18:00:00.000000        1020.1
  20 ashville    2010-01-01 19:00:00.000000        1020.6
  21 ashville    2010-01-01 20:00:00.000000        1020.9
  22 ashville    2010-01-01 21:00:00.000000        1021.1
  23 ashville    2010-01-01 22:00:00.000000        1021.0
  24 ashville    2010-01-01 23:00:00.000000        1020.9
  25 greenville  2010-01-01 00:00:00.000000        1020.6
  26 greenville  2010-01-01 01:00:00.000000        1020.8
  27 greenville  2010-01-01 02:00:00.000000        1020.1
  28 greenville  2010-01-01 03:00:00.000000        1020.8
  29 greenville  2010-01-01 04:00:00.000000        1020.2
  30 greenville  2010-01-01 05:00:00.000000        1020.0
  31 greenville  2010-01-01 06:00:00.000000        1020.4
  32 greenville  2010-01-01 07:00:00.000000        1020.8
  33 greenville  2010-01-01 08:00:00.000000        1021.3
  34 greenville  2010-01-01 09:00:00.000000        1021.7
  35 greenville  2010-01-01 10:00:00.000000        1022.0
  36 greenville  2010-01-01 11:00:00.000000        1021.9
  37 greenville  2010-01-01 12:00:00.000000        1021.1
  38 greenville  2010-01-01 13:00:00.000000        1020.0
  39 greenville  2010-01-01 14:00:00.000000        1019.3
  40 greenville  2010-01-01 15:00:00.000000        1019.0
  41 greenville  2010-01-01 16:00:00.000000        1019.2
  42 greenville  2010-01-01 17:00:00.000000        1019.6
  43 greenville  2010-01-01 18:00:00.000000        1020.1
  44 greenville  2010-01-01 19:00:00.000000        1020.6
  45 greenville  2010-01-01 20:00:00.000000        1020.9
  46 greenville  2010-01-01 21:00:00.000000        1021.0
  47 greenville  2010-01-01 22:00:00.000000        1020.9
  48 greenville  2010-01-01 23:00:00.000000        1020.9
  49 brownsville 2010-01-01 00:00:00.000000        1020.5
  50 brownsville 2010-01-01 01:00:00.000000        1020.8
  51 brownsville 2010-01-01 02:00:00.000000        1020.0
  52 brownsville 2010-01-01 03:00:00.000000        1020.8
  53 brownsville 2010-01-01 04:00:00.000000        1020.2
  54 brownsville 2010-01-01 05:00:00.000000        1020.0
  55 brownsville 2010-01-01 06:00:00.000000        1020.3
  56 brownsville 2010-01-01 07:00:00.000000        1020.8
  57 brownsville 2010-01-01 08:00:00.000000        1021.2
  58 brownsville 2010-01-01 09:00:00.000000        1021.6
  59 brownsville 2010-01-01 10:00:00.000000        1022.0
  60 brownsville 2010-01-01 11:00:00.000000        1021.9
  61 brownsville 2010-01-01 12:00:00.000000        1021.0
  62 brownsville 2010-01-01 13:00:00.000000        1019.9
  63 brownsville 2010-01-01 14:00:00.000000        1019.2
  64 brownsville 2010-01-01 15:00:00.000000        1019.0
  65 brownsville 2010-01-01 16:00:00.000000        1019.2
  66 brownsville 2010-01-01 17:00:00.000000        1019.6
  67 brownsville 2010-01-01 18:00:00.000000        1020.0
  68 brownsville 2010-01-01 19:00:00.000000        1020.5
  69 brownsville 2010-01-01 20:00:00.000000        1020.8
  70 brownsville 2010-01-01 21:00:00.000000        1020.9
  71 brownsville 2010-01-01 22:00:00.000000        1020.9
  72 brownsville 2010-01-01 23:00:00.000000        1020.8
  73 nashville   2010-01-01 00:00:00.000000        1020.4
  74 nashville   2010-01-01 01:00:00.000000        1020.6
  75 nashville   2010-01-01 02:00:00.000000        1019.9
  76 nashville   2010-01-01 03:00:00.000000        1020.6
  77 nashville   2010-01-01 04:00:00.000000        1020.1
  78 nashville   2010-01-01 05:00:00.000000        1019.9
  79 nashville   2010-01-01 06:00:00.000000        1020.2
  80 nashville   2010-01-01 07:00:00.000000        1020.6
  81 nashville   2010-01-01 08:00:00.000000        1021.1
  82 nashville   2010-01-01 09:00:00.000000        1021.5
  83 nashville   2010-01-01 10:00:00.000000        1021.9
  84 nashville   2010-01-01 11:00:00.000000        1021.8
  85 nashville   2010-01-01 12:00:00.000000        1021.0
  86 nashville   2010-01-01 13:00:00.000000        1019.8
  87 nashville   2010-01-01 14:00:00.000000        1019.2
  88 nashville   2010-01-01 15:00:00.000000        1018.9
  89 nashville   2010-01-01 16:00:00.000000        1019.1
  90 nashville   2010-01-01 17:00:00.000000        1019.5
  91 nashville   2010-01-01 18:00:00.000000        1019.9
  92 nashville   2010-01-01 19:00:00.000000        1020.4
  93 nashville   2010-01-01 20:00:00.000000        1020.7
  94 nashville   2010-01-01 21:00:00.000000        1020.9
  95 nashville   2010-01-01 22:00:00.000000        1020.8
  96 nashville   2010-01-01 23:00:00.000000        1020.7
  97 knoxville   2010-01-01 00:00:00.000000        1020.4
  98 knoxville   2010-01-01 01:00:00.000000        1020.6
  99 knoxville   2010-01-01 02:00:00.000000        1019.9
 100 knoxville   2010-01-01 03:00:00.000000        1020.6
 101 knoxville   2010-01-01 04:00:00.000000        1020.0
 102 knoxville   2010-01-01 05:00:00.000000        1019.9
 103 knoxville   2010-01-01 06:00:00.000000        1020.2
 104 knoxville   2010-01-01 07:00:00.000000        1020.6
 105 knoxville   2010-01-01 08:00:00.000000        1021.1
 106 knoxville   2010-01-01 09:00:00.000000        1021.5
 107 knoxville   2010-01-01 10:00:00.000000        1021.9
 108 knoxville   2010-01-01 11:00:00.000000        1021.8
 109 knoxville   2010-01-01 12:00:00.000000        1021.0
 110 knoxville   2010-01-01 13:00:00.000000        1019.9
 111 knoxville   2010-01-01 14:00:00.000000        1019.2
 112 knoxville   2010-01-01 15:00:00.000000        1018.9
 113 knoxville   2010-01-01 16:00:00.000000        1019.2
 114 knoxville   2010-01-01 17:00:00.000000        1019.6
 115 knoxville   2010-01-01 18:00:00.000000        1020.0
 116 knoxville   2010-01-01 19:00:00.000000        1020.5
 117 knoxville   2010-01-01 20:00:00.000000        1020.8
 118 knoxville   2010-01-01 21:00:00.000000        1020.9
 119 knoxville   2010-01-01 22:00:00.000000        1020.9
 120 knoxville   2010-01-01 23:00:00.000000        1020.8


SELECT * FROM of_outlier2 ORDER BY 1, 2, 3;

 sn  city        period                     pressure_mbar 
 --- ----------- -------------------------- ------------- 
   2 ashville    2010-01-01 01:00:00.000000        9000.0
   4 ashville    2010-01-01 03:00:00.000000       10000.0
  26 greenville  2010-01-01 01:00:00.000000        9000.0
  28 greenville  2010-01-01 03:00:00.000000       10000.0
  50 brownsville 2010-01-01 01:00:00.000000        9000.0
  52 brownsville 2010-01-01 03:00:00.000000       10000.0
  74 nashville   2010-01-01 01:00:00.000000        9000.0
  76 nashville   2010-01-01 03:00:00.000000       10000.0
  98 knoxville   2010-01-01 01:00:00.000000        9000.0
 100 knoxville   2010-01-01 03:00:00.000000       10000.0

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.