CFilter Example 1: Collaborative Filtering by Product - Teradata Vantage

Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
8.00
1.0
Published
May 2019
Language
English (United States)
Last Update
2019-11-22
dita:mapPath
blj1506016597986.ditamap
dita:ditavalPath
blj1506016597986.ditaval
dita:id
B700-4003
lifecycle
previous
Product Category
Teradata Vantageā„¢

Collaborative filtering by product is also called item-based collaborative filtering. In this example, JoinColumns = 'orderid'. The function tries to identify products that are often bought in the same transaction (as identified by the order_id).

Input

The InputTable has sales transaction data from an office supply chain store, in these columns:
Column Description
orderid Order (transaction) identifier
orderdate Order date
orderqty Quantity of product ordered
region Geographic region of store where order was placed
customer_segment Segment of customer who ordered product
prd_category Category of product ordered
product Product ordered
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 CFilter (
  ON sales_transaction AS InputTable
  OUT TABLE OutputTable (cfilter_output)
  USING
  TargetColumns ('product')
  JoinColumns ('orderid')
  PartitionColumns ('region')
) AS dt;

Output

This query returns the following table:

SELECT * FROM cfilter_output ORDER BY region, score;

Because all cntb values are equal, no z-scores appear.

cfilter_output
region col1_item1 col1_item2 cntb cnt1 cnt2 score support confidence lift z_score
Atlantic Paper Office Furnishings 1 4 5 0.05 0.0555555555555556 0.25 0.9  
Atlantic Office Furnishings Paper 1 5 4 0.05 0.0555555555555556 0.2 0.9  
Atlantic Binders and Binder Accessories Office Furnishings 1 3 5 0.0666666666666667 0.0555555555555556 0.333333333333333 1.2  
Atlantic Office Furnishings Binders and Binder Accessories 1 5 3 0.0666666666666667 0.0555555555555556 0.2 1.2  
Atlantic Computer Peripherals Office Furnishings 1 2 5 0.1 0.0555555555555556 0.5 1.8  
Atlantic Office Furnishings Computer Peripherals 1 5 2 0.1 0.0555555555555556 0.2 1.8  
Atlantic Labels Pens & Art Supplies 1 3 2 0.166666666666667 0.0555555555555556 0.333333333333333 3  
Atlantic Pens & Art Supplies Labels 1 2 3 0.166666666666667 0.0555555555555556 0.5 3  
... ... ... ... ... ... ... ... ... ... ...