Approximate Cardinality Example | Teradata Vantage - Approximate Cardinality Example - Teradata Vantage

This example calculates the number of distinct values for each specified column with a 1% error rate (accuracy).

Input

The input table, cracker, has more than 3000 rows of price and advertisement information for U.S. cracker brands Sunshine, Keebler, Nabisco, and a private label (such as a store brand). Input column names have these meanings:

SQL Call

SELECT * FROM ApproxCardinalityReduce(
	ON ApproxCardinalityMap(
		ON cracker AS InputTable
		USING
		TargetColumns('sn', 'id', 
          'dispsunshine', 'dispkeebler', 'dispnabisco', 'dispprivate',
          'featsunshine', 'featkeebler','featnabisco', 'featprivate', 
          'pricesunshine', 'pricekeebler', 'pricenabisco', 'priceprivate', 
          'choice')
		ErrorRate(1)
	) PARTITION BY column_name
) AS dt ORDER BY column_name;

Output

 column_name   cnt  method    
 ------------- ---- --------- 
 choice           4 nearExact
 dispkeebler      2 nearExact
 dispnabisco      2 nearExact
 dispprivate      2 nearExact
 dispsunshine     2 nearExact
 featkeebler      2 nearExact
 featnabisco      2 nearExact
 featprivate      2 nearExact
 featsunshine     2 nearExact
 id             136 nearExact
 pricekeebler    29 nearExact
 pricenabisco    42 nearExact
 priceprivate    39 nearExact
 pricesunshine   27 nearExact
 sn            3292 nearExact

Download a zip file of all examples and a SQL script file that creates their input tables.