1.0 - 8.00 - Best-Match Mode Example 1: Input Values of Type DOUBLE PRECISION - Teradata Vantage

Teradata® Vantage Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.0
8.00
Release Date
May 2019
Content Type
Programming Reference
Publication ID
B700-4003-098K
Language
English (United States)

Input

The example creates input tables t1 and t2 from factory_7, the Hypothesis-Test Mode Example 1: Normality Tests without GroupByColumns input table.

The following statements create tables t1 and t2:

CREATE MULTISET TABLE t1 AS (
  SELECT product,
         COUNT (*) AS group_size,
         AVG (mttf) AS mean,
         STDDEV_SAMP (mttf) as sd,
         CASE
           WHEN MIN (mttf) > 0
           THEN AVG (
             LN (
               CASE
                 WHEN mttf > 0
                 THEN mttf
                 ELSE 1
               END
             )
           )
           ELSE 0
         END AS mean_of_ln,
         CASE
           WHEN MIN(mttf) > 0
           THEN STDDEV_SAMP ( 
             LN (
               CASE WHEN mttf > 0
               THEN mttf
               ELSE 1
               END
             )
           )
           ELSE -1
           END AS sd_of_ln,
         MAX(mttf) AS "maximum", MIN(mttf) AS "minimum"
  FROM factory_7
  WHERE mttf IS NOT NULL
GROUP BY product) WITH data;
CREATE MULTISET TABLE t2 AS (
  SELECT RANK() OVER (PARTITION BY product ORDER BY mttf) AS "rank", product, mttf
  FROM factory_7
  WHERE mttf IS NOT NULL
) WITH data;

SQL Call

SELECT * FROM DistributionMatchReduce (
  ON DistributionMatchMultiInput (
    ON t2 AS "input" PARTITION BY ANY
    ON t1 AS groupstats DIMENSION
    USING
    ValueColumn ('mttf')
    Tests ('KS', 'AD')
    GroupByColumns ('product')
    MinGroupSize (50)
  ) PARTITION BY product
  USING "Top" (2)
)AS dt;

Output

product group_size best_match_ks_top1 p_value_ks_top1 best_match_ad_top1 p_value_ad_top1
D 3000 UNIFORMCONTINUOUS:9500.6201171875,10499.7998046875 0.99645 BETA:10,1 2e-07
A 3000 GAMMA:4495.788908676626,2.2244538184136595 0.774728 GAMMA:4495.788908676626,2.2244538184136595 0.687165