TD_Histogram Example | Histogram | Teradata Vantage - Examples: How to Use TD_Histogram - Analytics Database

Database Analytic Functions

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-06
dita:mapPath
gjn1627595495337.ditamap
dita:ditavalPath
ayr1485454803741.ditaval
dita:id
jmh1512506877710
Product Category
Teradata Vantageā„¢

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.

Multiple column support for TD_OrdinalEncoding, TD_OneHotEncoding, and TD_Histogram is available in release 17.20.03.07 and later. If you are using an older version, the TargetColumn argument accepts only one column.

Input Table cars_hist

sn model mpg cyl disp hp drat wt qsec vs am gear carb
1 Mazda RX4 21.0 6 160.0 110.0 3.9 2.62 16.46 S manual 4 4
2 Mazda RX4 Wag 21.0 6 160.0 110.0 3.9 2.875 17.02 S manual 4 4
3 Datsun 710 22.8 4 108.0 93.0 3.85 2.32 18.61 V manual 4 1
4 Hornet 4 Drive 21.4 6 258.0 110.0 3.08 3.215 19.44 V automatic 3 1
5 Hornet Sportabout 18.7 8 360.0 175.0 3.15 3.44 17.02 S automatic 3 2
6 Plymouth Valiant 18.1 6 225.0 105.0 2.76 3.46 20.22 V automatic 3 1
7 Duster 360 14.3 8 360.0 245.0 3.21 3.57 15.84 S automatic 3 4
8 Merc 240D 24.4 4 146.7 62.0 3.69 3.19 20.0 V automatic 4 2
9 Merc 230 22.8 4 140.8 95.0 3.92 3.15 22.9 V automatic 4 2
10 Merc 280 19.2 6 167.6 123.0 3.92 3.44 18.3 V automatic 4 4
11 Merc 280C 17.8 6 167.6 123.0 3.92 3.44 18.9 V automatic 4 4
12 Merc 450SE 16.4 8 275.8 180.0 3.07 4.07 17.4 S automatic 3 3
13 Merc 450SL 17.3 8 275.8 180.0 3.07 3.73 17.6 S automatic 3 3
14 Merc 450SLC 15.2 8 275.8 180.0 3.07 3.78 18.0 S automatic 3 3
15 Cadillac Fleetwood 10.4 8 472.0 205.0 2.93 5.25 17.98 S automatic 3 4
16 Lincoln Continental 10.4 8 460.0 215.0 3.0 5.424 17.82 S automatic 3 4
17 Chrysler Imperial 14.7 8 440.0 230.0 3.23 5.345 17.42 S automatic 3 4
18 Fiat 128 32.4 4 78.7 66.0 4.08 2.2 19.47 V manual 4 1
19 Honda Civic 30.4 4 75.7 52.0 4.93 1.615 18.52 V manual 4 2
20 Toyotal Corolla 33.9 4 71.1 65.0 4.22 1.835 19.9 V manual 4 1
21 Toyota Corona 21.5 4 120.1 97.0 3.7 2.465 20.01 V automatic 3 1
22 Dodge Challenger 15.5 8 318.0 150.0 2.76 3.52 16.87 S automatic 3 2
23 AMC Javelin 15.2 8 304.0 150.0 3.15 3.435 17.3 S automatic 3 2
24 Camaro Z28 13.3 8 350.0 245.0 3.73 3.84 15.41 S automatic 3 4
25 Pontiac Firebird 19.2 8 400.0 175.0 3.08 3.845 17.05 S automatic 3 2
26 Fiat X1-9 27.3 4 79.0 66.0 4.08 1.935 18.9 V manual 4 1
27 Porsche 914-2 26.0 4 120.3 91.0 4.43 2.14 16.7 S manual 5 2
28 Lotus Europa 30.4 4 95.1 113.0 3.77 1.513 16.9 V manual 5 2
29 Ford Pantera L 15.8 8 351.0 264.0 4.22 3.17 14.5 S manual 5 4
30 Ferrari Dino 19.7 6 145.0 175.0 3.62 2.77 15.5 S manual 5 6
31 Maserati Bora 15.0 8 301.0 335.0 3.54 3.57 14.6 S manual 5 8
32 Volvo 142E 21.4 4 121.0 109.0 4.11 2.78 18.6 V manual 4 2

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