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 |
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.
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 | |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |