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

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!
    
  • 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
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file