TD_Histogram Example | Histogram | Teradata Vantage - Examples: How to Use TD_Histogram - 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.

Input Table cars_hist

Example: TD_Histogram Call Using Sturges Method Type

SELECT * FROM TD_Histogram(
ON cars_hist as InputTable
USING
TargetColumn('hp','disp')
MethodType('STURGES')
) as dt ORDER BY 1,2,3,4,5,6;

TD_Histogram Output for Sturges Method Type

ColumnName Label MinValue MaxValue CountOfValues Bin_Percent
---------  ----- -------- -------- ------------- -----------  
disp       0     0.0      100.0     5            15.625
disp       1     100.0    200.0    11            34.375
disp       2     200.00   300.00    5            15.625
disp       3     300.00   400.00    7            21.875
disp       4     400.00   500.00    4            12.500
hp         0      50.00   100.00    9            28.125
hp         1     100.00   150.00    8            25.000
hp         2     150.00   200.00    8            25.000
hp         3     200.00   250.00    5            15.625
hp         4     250.00   300.00    1             3.125
hp         5     300.00   350.00    1             3.125

Example: TD_Histogram Call Using Scott Method Type

SELECT * FROM TD_Histogram(
ON cars_hist as InputTable
USING
TargetColumn('hp','disp')
MethodType('SCOTT')
) as dt ORDER BY 1,2,3,4,5,6;

TD_Histogram Output for Scott Method Type

ColumnName Label MinValue MaxValue CountOfValues Bin_Percent
---------  ----- -------- -------- ------------- -----------  
disp       0     0.0      100.0     5            15.625
disp       1     100.0    200.0    11            34.375
disp       2     200.00   300.00    5            15.625
disp       3     300.00   400.00    7            21.875
disp       4     400.00   500.00    4            12.500
hp         0       0.00   100.00    9            28.125
hp         1     100.00   200.00   16            50.000
hp         2     200.00   300.00    6            25.000
hp         3     300.00   400.00    1             3.125

Example: TD_Histogram Call Using Equal0Width Method Type

SELECT * FROM TD_Histogram(
ON cars_hist as InputTable
USING
TargetColumn('hp','disp')
MethodType('Equal-Width')
Nbins(3, 4)
) as dt ORDER BY 1,2,3,4,5,6;

TD_Histogram Output for Equal-Width Method Type

ColumnName Label  MinValue   MaxValue    CountOfValues Bin_Percent
---------- -----  ---------  --------    ------------- -----------
disp       0      71.100000  171.325000  16            50.000
disp       1     171.325000  271.550000   2             6.250
disp       2     271.550000  371.775000  10            31.250
disp       3     371.775000  472.000000   4            12.500
hp         0      52.000000  146.333333  17            53.125
hp         1     146.333333  240.666667  11            34.375
hp         2     240.666667  335.000000   4            12.500

Example: TD_Histogram Call Using Variable-Width Method Type

SELECT * FROM TD_Histogram(
ON cars_hist as InputTable
ON minmaxTable as MinMax Dimension
USING
TargetColumn('hp','disp')
MethodType('VARIABLE-WIDTH')
Nbins(3, 4)
) as dt ORDER BY 1,2,3,4,5,6;

TD_Histogram Output for Variable-Width Method Type

ColumnName  Label   MinValue   MaxValue    CountOfValues Bin_Percent
---------- -------  ---------  --------    ------------- -----------
disp       label_0     71.100  171.325     16            50.000
disp       label_1    171.325  271.550      2             6.250
disp       label_2    271.550  371.775     10            31.250
disp       label_3    371.775  472.000      4            12.500
hp         label_0     52.000  146.333     17            53.125
hp         label_1    146.333  240.666     11            34.375
hp         label_2    240.666  335.000      4            12.500