1.0 - 8.00 - FPGrowth Example - Teradata Vantage

Teradata® Vantage Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.0
8.00
Release Date
May 2019
Content Type
Programming Reference
Publication ID
B700-4003-098K
Language
English (United States)

Input

The InputTable has sales transaction data of an office supply chain store by different geographic regions and customer segments. The column product specifies the items that are purchased by a customer in a given transaction (column orderid).

InputTable: sales_transaction
orderid orderdate orderqty region customer_segment prd_category product
3 2010-10-13 00:00:00 6 Nunavut Small Business Office Supplies Storage & Organization
293 2012-10-01 00:00:00 49 Nunavut Consumer Office Supplies Appliances
293 2012-10-01 00:00:00 27 Nunavut Consumer Office Supplies Binders and Binder Accessories
483 2011-07-10 00:00:00 30 Nunavut Corporate Technology Telephones and Communication
515 2010-08-28 00:00:00 19 Nunavut Consumer Office Supplies Appliances
515 2010-08-28 00:00:00 21 Nunavut Consumer Furniture Office Furnishings
613 2011-06-17 00:00:00 12 Nunavut Corporate Office Supplies Binders and Binder Accessories
613 2011-06-17 00:00:00 22 Nunavut Corporate Office Supplies Storage & Organization
643 2011-03-24 00:00:00 21 Nunavut Corporate Office Supplies Storage & Organization
678 2010-02-26 00:00:00 44 Nunavut Home Office Office Supplies Paper
807 2010-11-23 00:00:00 45 Nunavut Home Office Office Supplies Paper
807 2010-11-23 00:00:00 32 Nunavut Home Office Office Supplies Rubber Bands
868 2012-06-08 00:00:00 32 Nunavut Home Office Office Supplies Appliances
... ... ... ... ... ... ...

SQL Call

SELECT * FROM FPGrowth (
  ON sales_transaction AS InputTable
  OUT TABLE OutputRuleTable (fpgrowth_out_rule)
  OUT TABLE OutputPatternTable (fpgrowth_out_pattern)
  USING
  TargetColumns ('product')
  TransactionIDColumns ('orderid')
  PartitionColumns ('region')
  MinSupport (0.01)
  MinConfidence (0.0)
  MaxPatternLength (4)
  ConsequenceCountRange ('1-1')
  PatternsOrRules ('both')
) AS dt;

Output

output_information
Patterns are kept in pattern table specified in the argument OutputPatternTable
Rules are kept in rule table specified in the argument OutputRuleTable

This query returns the following table:

SELECT * FROM fpgrowth_out_pattern ORDER BY region, pattern_product;
fpgrowth_out_pattern
region pattern_product length_of_pattern count support
Atlantic Appliances, Chairs & Chairmats 2 1 0.0555555555555556
Atlantic Chairs & Chairmats, Computer Peripherals 2 1 0.0555555555555556
Atlantic Labels, Pens & Art Supplies 2 1 0.0555555555555556
Atlantic Office Furnishings, Binder s and Binder Accessories 2 1 0.0555555555555556
Atlantic Office Furnishings, Computer Peripherals 2 1 0.0555555555555556
Atlantic Office Furnishings, Paper 2 1 0.0555555555555556
Atlantic Office Furnishings, Paper, Storage & Organization 3 1 0.0555555555555556
Atlantic Office Furnishings, Storage & Organization 2 1 0.0555555555555556
Atlantic Paper, Labels 2 1 0.0555555555555556
Atlantic Paper, Storage & Organization 2 1 0.0555555555555556
Northwest Territories Binder s and Binder Accessories, Computer Peripherals 2 3 0.0111524163568773
Northwest Territories Binder s and Binder Accessories, Office Furnishings 2 4 0.0148698884758364
Northwest Territories Binder s and Binder Accessories, Office Machines 2 3 0.0111524163568773
Northwest Territories Binder s and Binder Accessories, Rubber Bands 2 3 0.0111524163568773
Northwest Territories Binder s and Binder Accessories, Telephones and Communication 2 4 0.0148698884758364
Northwest Territories Computer Peripherals, Chairs & Chairmats 2 3 0.0111524163568773
... ... ... ... ...

This query returns the following table:

SELECT * FROM fpgrowth_out_rule ORDER BY region, score;
fpgrowth_out_rule
region antecedent_product consequence_product count_of_antecedent count_of_consequence cntb cnt_antecedent cnt_consequence score support confidence lift conviction leverage coverage chi_square z_score
Atlantic Paper Office Furnishings 1 1 1 4 5 0.05 0.0555555555555556 0.25 0.9 0.962962962962963 -0.00617283950617285 0.222222222222222 0.0197802197802198  
Atlantic Office Furnishings Paper 1 1 1 5 4 0.05 0.0555555555555556 0.2 0.9 0.972222222222222 -0.00617283950617285 0.277777777777778 0.0197802197802198  
Atlantic Binders and Binder Accessories Office Furnishings 1 1 1 3 5 0.0666666666666667 0.0555555555555556 0.333333333333333 1.2 1.08333333333333 0.00925925925925925 0.166666666666667 0.0553846153846154  
Atlantic Office Furnishings Binders and Binder Accessories 1 1 1 5 3 0.0666666666666667 0.0555555555555556 0.2 1.2 1.04166666666667 0.00925925925925925 0.277777777777778 0.0553846153846154  
Atlantic Labels Paper 1 1 1 3 4 0.0833333333333333 0.0555555555555556 0.333333333333333 1.5 1.16666666666667 0.0185185185185185 0.166666666666667 0.257142857142857  

The output tables contain only those rows that conform to MinSupport of 0.01. The rest of the rows that violate the condition are deleted.