TD_QQNorm Example | QQNorm | Teradata Vantage - Example: How to Use TD_QQNorm - Analytics Database

Every complete example in this document is available in a zip file that you can download. The zip file includes a SQL script file that creates the input tables for the examples. If you are reading this document on https://docs.teradata.com/, you can download the zip file from the attachment in the left sidebar.

TD_QQNorm Input

passenger survived pclass  name                                 gender   age    sibsp   parch    ticket       fare   	cabin       embarked 
 --------- -------- ------ ------------------------------------  ------  -------   -----   -----    --------     ------- 	----------- -------- 
        97        0      1 Goldschmidt; Mr. George B              male        71     0      0        PC 17754    34.6542	A5          	C       
       488        0      1 Kent; Mr. Edward Austin                male        58     0      0        11771       29.7	B37        	C       
       505        1      1 Maioni; Miss. Roberta                  female      16     0      0        110152      86.5 	B79             S       
       631        1      1 Barkworth; Mr. Algernon Henry Wilson   male        80     0      0        27042       30 	A23             S       
       873        0      1 Carlsson; Mr. Frans Olof               male        33     0      0        695         5 	B51 B53 B55     S   

From input table, create RankTable with this statement:

CREATE TABLE RankTable AS (
 SELECT age, fare,
 CAST (ROW_NUMBER() OVER (ORDER BY age ASC NULLS LAST) AS BIGINT)
 AS rank_age,
 CAST (ROW_NUMBER() OVER (ORDER BY fare ASC NULLS LAST) AS BIGINT)
 AS rank_fare
 FROM input_table AS dt
) WITH DATA;

 age fare      rank_age rank_fare 
 --- --------- -------- --------- 
  16 86.5        1         5
  33 5           2         1
  58 29.7        3         2
  71 34.6542     4         4
  80 30          5         3

TD_QQNorm SQL Call Using Column Numbers

SELECT * FROM TD_QQNorm (
  ON RankTable AS InputTable
  USING
  TargetColumns ('[0:1]')
  RankColumns ('[2:3]')
) AS dt;

TD_QQNorm SQL Call Using Column Names

SELECT * FROM TD_QQNorm (
  ON RankTable AS InputTable
  USING
  TargetColumns ('age', 'fare')
  RankColumns ('rank_age', 'rank_fare')
) AS dt;

TD_QQNorm SQL Call Using PARTITION BY ANY Clause

SELECT * FROM TD_QQNorm (
  ON RankTable AS InputTable
PARTITION BY ANY
  USING
  TargetColumns ('age', 'fare')
  RankColumns ('rank_age', 'rank_fare')
) AS dt;

TD_QQNorm Output

age        age_theoretical_quantiles  fare   fare_theoretical_quantiles 
 --------- -------------------------  -----  -----------------
 16         -1.17986882170049		 86.5    1.17986882170049
 33         -0.496788749686441		 5      -1.17986882170049
 58         -0.000000101006675468085   29.7    -0.496788749686441
 71          0.496788749686441         34.6542 0.496788749686441
 80          1.17986882170049          30      -0.000000101006675468085