CFilter Example: Filter by Product

CFilter Example: Filter by Product - Teradata Vantage

Machine Learning Engine Analytic Function Reference

Product

Teradata Vantage

Release Number

8.10

1.1

Published

October 2019

Language

English (United States)

Last Update

2019-12-31

dita:mapPath

ima1540829771750.ditamap

dita:ditavalPath

jsj1481748799576.ditaval

dita:id

B700-4003

lifecycle

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

              message              
-----------------------------------
 Output table created successfully
(1 row)

SELECT * FROM cfilter_output;

 region                col1_item1                     col1_item2                     cntb cnt1 cnt2 score                 support               confidence           lift                z_score             
 --------------------- ------------------------------ ------------------------------ ---- ---- ---- --------------------- --------------------- -------------------- ------------------- ------------------- 
 northwest territories binders and binder accessories labels                            2   44   16  0.005681818181818182  0.007434944237918215 0.045454545454545456  0.7642045454545454 0.12784297268860556
 northwest territories tables                         labels                            1   12   16  0.005208333333333333 0.0037174721189591076  0.08333333333333333  1.4010416666666667 -0.8522864845907044
 northwest territories computer peripherals           labels                            1   30   16 0.0020833333333333333 0.0037174721189591076  0.03333333333333333  0.5604166666666667 -0.8522864845907044
 nunavut               tables                         bookcases                         1    1    2                   0.5  0.017857142857142856                  1.0                28.0 -0.4588314677411239
 northwest territories binders and binder accessories tables                            2   44   12  0.007575757575757576  0.007434944237918215 0.045454545454545456   1.018939393939394 0.12784297268860556
 nunavut               labels                         rubber bands                      1    4    2                 0.125  0.017857142857142856                 0.25                 7.0 -0.4588314677411239
 nunavut               binders and binder accessories computer peripherals              1   10    6  0.016666666666666666  0.017857142857142856                  0.1  0.9333333333333333 -0.4588314677411239
 northwest territories labels                         binders and binder accessories    2   16   44  0.005681818181818182  0.007434944237918215                0.125  0.7642045454545454 0.12784297268860556
 northwest territories binders and binder accessories rubber bands                      3   44   10  0.020454545454545454  0.011152416356877323  0.06818181818181818   1.834090909090909  1.1079724299679155
 northwest territories labels                         paper                             2   16   40               0.00625  0.007434944237918215                0.125            0.840625 0.12784297268860556
 northwest territories computer peripherals           binders and binder accessories    3   30   44  0.006818181818181818  0.011152416356877323                  0.1  0.6113636363636363  1.1079724299679155
 atlantic              labels                         pens & art supplies               1    3    2   0.16666666666666666   0.05555555555555555   0.3333333333333333                 3.0                NULL
...

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.