1.1 - 8.10 - CFilter Example: Filter by Product - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)

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.