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

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