Need to compare column value and identify outlier records which shows huge spike in data

rahul_vertica_infyrahul_vertica_infy
in General Discussion

Dear All,

Can you please help me develop query to solve below cases

Below is one sample data set

CUST_ID BATCH1 BATCH2 BATCH3 BATCH4 DATE
2573 265 265 265 265 9/18/2018
3205 261 261 261 261 9/18/2018
3211 375 375 375 375 9/18/2018
3292 207 207 207 207 9/18/2018
3755 203 8996 203 203 9/18/2018
3640 6244 216 216 216 9/18/2018

Here I need to identify all those cust_id where among (col2 ,col3 ,col4,col5 i.e Batch1 , Batch2 , Batch3 , Batch 3 , Batch 4 values has huge spike

So Here I am expecting output like below

CUST_ID BATCH1 BATCH2 BATCH3 BATCH4 DATE
3755 203 8996 203 203 9/18/2018
3640 6244 216 216 216 9/18/2018
I found DETECT_OUTLIERS function but it seems in my environment this function is not supported . In my current environment Vertica 8.1 version is there

Comments

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited September 2018

    Hi,

    The DETECT_OUTLIERS function is available in Vertica 8.1.

    See:

    https://www.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/AnalyzingData/MachineLearning/DataPreparation/DetectingOutliers.htm

    Example:

    dbadmin=> SELECT version();
               version
-------------------------------------
 Vertica Analytic Database v8.1.1-18
(1 row)

dbadmin=> SELECT * FROM test_nutty_values;
 cust_id | batch1 | batch2 | batch3 | batch4 |    DATE
---------+--------+--------+--------+--------+------------
    2573 |    265 |    265 |    265 |    265 | 2018-09-18
    3205 |    261 |    261 |    261 |    261 | 2018-09-18
    3211 |    375 |    375 |    375 |    375 | 2018-09-18
    3292 |    207 |    207 |    207 |    207 | 2018-09-18
    3755 |    203 |   8996 |    203 |    203 | 2018-09-18
    3640 |   6244 |    216 |    216 |    216 | 2018-09-18
(6 rows)

dbadmin=> SELECT DETECT_OUTLIERS('test_nutty_values_outliers', 'test_nutty_values', 'cust_id, batch1, batch2, batch3, batch4', 'robust_zscore' USING PARAMETERS outlier_threshold=4.0);
   DETECT_OUTLIERS
----------------------
 Detected 2 outliers

(1 row)

dbadmin=> SELECT * FROM test_nutty_values_outliers;
 cust_id | batch1 | batch2 | batch3 | batch4 |    DATE
---------+--------+--------+--------+--------+------------
    3640 |   6244 |    216 |    216 |    216 | 2018-09-18
    3755 |    203 |   8996 |    203 |    203 | 2018-09-18
(2 rows)

    Maybe have the DBA reinstall the Machine Learning package if you cannot access the function.

    Example:

    [dbadmin@vertica8 ~]$ admintools -t install_package -d test_db -P MachineLearning --force-reinstall
Installing package MachineLearning...
...Success!
  • rahul_vertica_infyrahul_vertica_infy

    Thanks Jim_Knicely for your quick help. But Still it does not solve the problem here.

    I was able to Use DETECT_OUTLIERS Function and Use same command suggested by you like below

    SELECT DETECT_OUTLIERS('fastmart_pos_prc.RLS_Refresh_Monitor_outliers', 'fastmart_pos_prc.RLS_Refresh_Monitor', 'cust_id, batch1, batch2, batch3, batch4', 'robust_zscore' USING PARAMETERS outlier_threshold=3.0);

    But this Time I keep 166 Rows in in My Main table fastmart_pos_prc.RLS_Refresh_Monitor and It fetches 30 Outlier Records ,but some of are not outlier for me. Look like this function works for all data sets ? But I wanted to analyze 1 row at a time .

    Let me share Sample Data again.

    fastmart_pos_prc.RLS_Refresh_Monitor

    cust_id BATCH1 BATCH2 BATCH3 BATCH4 DATE
    2573 265 265 265 265 2018-09-18
    3205 261 261 261 261 2018-09-18
    3211 375 375 375 375 2018-09-18
    3292 207 207 207 207 2018-09-18
    3318 236 236 236 236 2018-09-18
    3484 198 198 198 198 2018-09-18
    3645 151 151 151 151 2018-09-18
    3682 241 241 241 241 2018-09-18
    3688 227 227 227 227 2018-09-18
    3700 295 295 295 295 2018-09-18
    3719 221 221 221 221 2018-09-18
    3777 212 212 212 212 2018-09-18
    3783 144 144 144 144 2018-09-18
    3791 232 232 232 232 2018-09-18
    3804 231 231 231 231 2018-09-18
    3031 233 233 233 233 2018-09-18
    3174 248 248 248 248 2018-09-18
    3210 255 255 255 255 2018-09-18
    3412 240 240 240 240 2018-09-18
    3588 268 268 268 268 2018-09-18
    3648 233 233 233 233 2018-09-18
    3754 202 202 202 202 2018-09-18
    3771 209 209 209 209 2018-09-18
    3778 217 217 217 217 2018-09-18
    3810 182 182 182 182 2018-09-18
    3817 157 157 157 157 2018-09-18
    3818 159 159 159 159 2018-09-18
    9033 33 33 33 33 2018-09-18
    3243 235 235 235 235 2018-09-18
    3267 163 163 231 169 2018-09-18
    3293 202 202 202 202 2018-09-18
    3659 261 261 261 261 2018-09-18
    3701 248 248 248 248 2018-09-18
    3702 169 169 169 169 2018-09-18
    3742 212 212 212 212 2018-09-18
    3756 200 200 200 200 2018-09-18
    3762 227 227 227 227 2018-09-18
    3781 339 339 339 339 2018-09-18
    3811 181 181 181 181 2018-09-18
    3813 181 181 181 181 2018-09-18
    3826 162 162 162 162 2018-09-18
    1121 265 265 265 265 2018-09-18
    3587 188 188 188 188 2018-09-18
    3617 238 239 239 239 2018-09-18
    3668 203 203 203 203 2018-09-18
    3681 244 244 244 244 2018-09-18
    3687 240 240 240 240 2018-09-18
    3738 258 258 258 258 2018-09-18
    3741 269 269 269 269 2018-09-18
    3750 173 173 173 173 2018-09-18
    3770 221 221 221 221 2018-09-18
    3782 162 162 162 162 2018-09-18
    3784 141 141 141 141 2018-09-18
    3806 229 229 229 229 2018-09-18
    3812 183 183 183 183 2018-09-18
    3823 162 162 162 162 2018-09-18
    1885 282 282 282 282 2018-09-18
    1903 235 235 235 235 2018-09-18
    2572 288 288 288 288 2018-09-18
    3200 264 264 264 264 2018-09-18
    3202 292 292 292 292 2018-09-18
    3204 261 261 261 261 2018-09-18
    3230 215 215 215 215 2018-09-18
    3240 238 238 238 238 2018-09-18
    3291 204 204 204 204 2018-09-18
    3628 260 260 260 261 2018-09-18
    3640 6244 216 216 216 2018-09-18
    3666 232 232 232 232 2018-09-18
    3684 168 168 168 168 2018-09-18
    3726 166 166 166 166 2018-09-18
    3739 178 178 178 178 2018-09-18
    3749 232 232 232 232 2018-09-18
    3755 203 8996 203 203 2018-09-18
    3769 230 230 230 230 2018-09-18
    3775 215 215 215 215 2018-09-18
    3793 227 227 227 227 2018-09-18
    3794 221 221 221 221 2018-09-18
    3797 196 196 196 196 2018-09-18
    3805 231 231 231 231 2018-09-18
    3807 132 132 132 132 2018-09-18
    3819 164 164 164 164 2018-09-18
    3821 154 154 154 154 2018-09-18
    3822 164 164 164 164 2018-09-18
    4000 161 161 161 161 2018-09-18
    9007 31 31 31 31 2018-09-18
    1868 369 369 369 369 2018-09-18
    2541 279 279 279 279 2018-09-18
    2545 241 241 241 241 2018-09-18
    2577 234 234 234 234 2018-09-18
    3172 245 245 245 245 2018-09-18
    3177 230 230 230 230 2018-09-18
    3248 234 234 234 234 2018-09-18
    3585 250 250 250 250 2018-09-18
    3656 229 220 229 229 2018-09-18
    3664 227 227 227 227 2018-09-18
    3667 184 184 184 184 2018-09-18
    3744 250 250 250 250 2018-09-18
    3789 228 230 230 230 2018-09-18
    3795 227 227 227 227 2018-09-18
    3149 284 284 284 284 2018-09-18
    3244 260 260 260 260 2018-09-18
    3759 223 127 223 223 2018-09-18
    3788 214 214 214 214 2018-09-18
    1127 255 255 255 255 2018-09-18
    1432 334 334 334 334 2018-09-18
    2715 168 168 168 168 2018-09-18
    2736 221 221 221 221 2018-09-18
    3141 156 156 156 156 2018-09-18
    3147 291 291 291 291 2018-09-18
    3176 248 248 248 248 2018-09-18
    3236 222 222 222 222 2018-09-18
    3369 294 294 294 294 2018-09-18
    3618 232 232 232 232 2018-09-18
    3650 210 210 210 210 2018-09-18
    3731 218 218 218 218 2018-09-18
    3764 239 239 239 239 2018-09-18
    3765 166 166 166 166 2018-09-18
    3776 199 199 199 199 2018-09-18
    3786 214 214 214 214 2018-09-18
    3798 186 186 186 186 2018-09-18
    3820 162 162 162 162 2018-09-18
    3824 162 162 162 162 2018-09-18
    9001 216 216 216 216 2018-09-18
    9002 36 36 36 36 2018-09-18
    1120 280 280 280 280 2018-09-18
    1126 236 236 236 236 2018-09-18
    1429 249 249 249 249 2018-09-18
    1578 244 244 244 244 2018-09-18
    3144 294 294 294 294 2018-09-18
    3148 293 293 293 293 2018-09-18
    3178 263 263 263 263 2018-09-18
    3184 276 276 276 276 2018-09-18
    3208 255 255 255 255 2018-09-18
    3220 199 199 199 199 2018-09-18
    3233 316 277 316 316 2018-09-18
    3242 234 234 234 234 2018-09-18
    3246 265 265 265 265 2018-09-18
    3338 235 235 235 235 2018-09-18
    3370 225 225 225 225 2018-09-18
    3553 181 181 181 181 2018-09-18
    3624 242 242 242 242 2018-09-18
    3751 198 198 198 198 2018-09-18
    3790 228 228 228 228 2018-09-18
    3801 128 128 128 128 2018-09-18
    3814 182 182 182 182 2018-09-18
    9009 31 31 31 31 2018-09-18
    9017 31 31 31 31 2018-09-18
    9018 31 31 31 31 2018-09-18
    1434 229 229 229 229 2018-09-18
    1588 244 244 244 244 2018-09-18
    1652 170 170 170 170 2018-09-18
    2542 279 279 279 279 2018-09-18
    3146 295 295 295 295 2018-09-18
    3198 262 262 262 262 2018-09-18
    3216 233 233 233 233 2018-09-18
    3221 200 200 200 200 2018-09-18
    3241 248 248 248 248 2018-09-18
    3601 264 264 264 264 2018-09-18
    3642 211 211 211 211 2018-09-18
    3752 200 200 200 200 2018-09-18
    3757 156 156 156 156 2018-09-18
    3796 108 108 108 108 2018-09-18
    3799 137 137 137 137 2018-09-18
    3802 131 131 131 131 2018-09-18
    3808 179 179 179 179 2018-09-18
    3825 164 164 164 259 2018-09-18

    fastmart_pos_prc.RLS_Refresh_Monitor_outliers (Output Outlier Table)

    cust_id BATCH1 BATCH2 BATCH3 BATCH4 DATE
    1121 265 265 265 265 2018-09-18
    2573 265 265 265 265 2018-09-18
    1120 280 280 280 280 2018-09-18
    1126 236 236 236 236 2018-09-18
    1429 249 249 249 249 2018-09-18
    1578 244 244 244 244 2018-09-18
    9009 31 31 31 31 2018-09-18
    9017 31 31 31 31 2018-09-18
    9018 31 31 31 31 2018-09-18
    1868 369 369 369 369 2018-09-18
    2541 279 279 279 279 2018-09-18
    2545 241 241 241 241 2018-09-18
    2577 234 234 234 234 2018-09-18
    1434 229 229 229 229 2018-09-18
    1588 244 244 244 244 2018-09-18
    1652 170 170 170 170 2018-09-18
    2542 279 279 279 279 2018-09-18
    9033 33 33 33 33 2018-09-18
    1885 282 282 282 282 2018-09-18
    1903 235 235 235 235 2018-09-18
    2572 288 288 288 288 2018-09-18
    3640 6244 216 216 216 2018-09-18
    3755 203 8996 203 203 2018-09-18
    9007 31 31 31 31 2018-09-18
    1127 255 255 255 255 2018-09-18
    1432 334 334 334 334 2018-09-18
    2715 168 168 168 168 2018-09-18
    2736 221 221 221 221 2018-09-18
    9001 216 216 216 216 2018-09-18
    9002 36 36 36 36 2018-09-18

    Here I wanted to take 1 row based on Cust_id (Here cust_id is Unique key for us)
    9001 216 216 216 216 2018-09-18
    Here I have to see col2 col3 col4 col5 and here I did not see any spike ,so this is not outlier for me wherer as below records
    3640 6244 216 216 216 2018-09-18
    If I see col2,col3,col4,col5 here for cust_id 3640 this is consider as outlier .

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...