TD_OutlierFilterFit

This function is used to identify outliers in a dataset. Outliers are values that differ significantly from other values in the dataset. Outlier’s can significantly skew statistical output and so disproportionately effect mathematical average and deviations. TD_OutlierFilterFit is used to identify outliers and apply some simple handling techniques to the output table produced by the function, namely delete, replace with null or use the median value for the group as a replacement value.

The example here creates an input table with 2 outlier values, amount = 12 and distance_km = 45. The table is provided as input to the function using the default ‘PERCENTILE’ as the outlier method (Carlings modification and Tukey’s fences can also be used) to apply to the target columns (amount, distance_km). The lower and upper bounds are set for the percentile, 5 and 95.

TD_OutlierFilterFit is useful in Teradata because it allows you to detect and handle outliers directly inside the database, avoiding the need to move large volumes of data to external analytics tools. This improves data quality before reporting, machine learning, and statistical analysis.

Syntax

TD_OutlierFilterFit(
  ON {table|view|(query)} AS InputTable
  [OUT [PERMANENT|VOLATILE] TABLE OutputTable(output_table_name)]
  USING
  TargetColumns({'target_column'|target_column_range}[,...])
  [GroupColumns('group_column')]
  [OutlierMethod({'percentile'|'tukey'|'carling'})]
  [LowerPercentile(min_value)]
  [UpperPercentile(max_value)]
  [IQRMultiplier(k)]
  [ReplacementValue({'delete'|'null'|'median'|replacement_value})]
  [RemoveTail({'both'|'upper'|'lower'})]
  [PercentileMethod({'PercentileCont'|'PercentileDISC'})]
)

TD_OutlierFilterFit • Database Engine 20 In-Database Analytic Functions • Reader • Teradata Developers Portal

1. Create Sample Data

The example begins by creating a small dataset containing typical transaction values. Two records with unusually high and low values are included to act as clear outliers.

CREATE MULTISET TABLE demo_outlier_input AS (
    SELECT 1 AS id,
           CAST(98.00 AS DECIMAL(18,2)) AS amount,
           CAST(10.10 AS DECIMAL(18,2)) AS distance_km
    ...
    -- Outliers
    SELECT 11, 450.00, 45.00
    UNION ALL
    SELECT 12, 12.00, 0.20
) WITH DATA;

2. Fit Outlier Thresholds

TD_OutlierFilterFit analyses the selected columns and calculates the lower and upper bounds used to identify outliers. This example uses the PERCENTILE method with 5% and 95% thresholds.

SELECT *
FROM TD_OutlierFilterFit(
    ON demo_outlier_input AS InputTable
    OUT TABLE FitTable(demo_outlier_fit)
    USING
        TargetColumns('amount', 'distance_km')
        OutlierMethod('PERCENTILE')
        LowerPercentile(0.05)
        UpperPercentile(0.95)
) AS dt;

3. Apply the Transformation

TD_OutlierFilterTransform applies the fitted thresholds to the original data and removes rows containing outlier values.

CREATE MULTISET TABLE demo_outlier_filtered AS (
    SELECT *
    FROM TD_OutlierFilterTransform(
        ON demo_outlier_input AS InputTable PARTITION BY ANY
        ON demo_outlier_fit AS FitTable DIMENSION
    ) AS dt
) WITH DATA;

4. Review the Output

The filtered table can be queried to verify that the outlier records have been excluded from the results.

SELECT *
FROM demo_outlier_filtered
ORDER BY id;

5. Validate the Results

Comparing row counts before and after the transformation confirms how many records were removed by the filtering process.

SELECT 'input_row_count' AS metric,
       COUNT(*) AS cnt
FROM demo_outlier_input

UNION ALL

SELECT 'filtered_row_count',
       COUNT(*)
FROM demo_outlier_filtered;

6. Identify Removed Rows

A final comparison between the source and output tables shows exactly which records were classified as outliers and removed.

SELECT i.*
FROM demo_outlier_input AS i
LEFT JOIN demo_outlier_filtered AS f
    ON i.id = f.id
WHERE f.id IS NULL
ORDER BY i.id;

TD_OutlierFilterFit.sql