Example: How to Use TD_CFilter - Analytics Database

Input Table

The following table contains grocery_transaction data.

DROP TABLE grocery_transaction;
CREATE TABLE grocery_transaction (tranid int, period varchar(20), storeid int, region varchar(20), item varchar(20), sku int, category varchar(20)) Primary index(tranid);
insert into grocery_transaction values(999,'20100715',1,'west','milk',1,'dairy');
insert into grocery_transaction values(999,'20100715',1,'west','butter',2,'dairy');
insert into grocery_transaction values(999,'20100715',1,'west','eggs',3,'dairy');
insert into grocery_transaction values(999,'19990715',1,'west','flour',4,'baking');
insert into grocery_transaction values(999,'19990715',1,'west','spinach',4,'produce');
insert into grocery_transaction values(1000,'20100715',1,'west','milk',1,'dairy');
insert into grocery_transaction values(1000,'20100715',1,'west','eggs',3,'dairy');
insert into grocery_transaction values(1000,'19990715',1,'west','flour',4,'baking');
insert into grocery_transaction values(1000,'19990715',1,'west','spinach',2,'produce');
insert into grocery_transaction values(1001,'20100715',1,'west','milk',1,'dairy');
insert into grocery_transaction values(1001,'20100715',1,'west','butter',2,'dairy');
insert into grocery_transaction values(1001,'20100715',1,'west','eggs',3,'dairy');
insert into grocery_transaction values(1002,'20100715',1,'west','milk',1,'dairy');
insert into grocery_transaction values(1002,'20100715',1,'west','butter',2,'dairy');
insert into grocery_transaction values(1002,'20100715',1,'west','spinach',3,'produce');
insert into grocery_transaction values(1500,'20100715',3,'west','butter',2,'dairy');
insert into grocery_transaction values(1500,'20100715',3,'west','eggs',3,'dairy');
insert into grocery_transaction values(1500,'20100715',3,'west','flour',4,'baking');

SELECT Statement

SELECT * from grocery_transaction order by 1;

Result:

Query 1 - Use Case without PartitionColumns

SELECT * FROM TD_CFilter (
  ON grocery_transaction AS InputTable
  USING
  TargetColumn ('item')
  TransactionIDColumns ('tranid')
  MaxDistinctItems(100)
) AS dt Order By 1,2;

Result:

Query 2 - Use Case with PartitionColumns

SELECT * FROM TD_CFilter (
  ON grocery_transaction AS InputTable
  USING
  TargetColumn ('item')
  TransactionIDColumns ('tranid')
  PartitionColumns ('storeid')
  MaxDistinctItems(100)
) AS dt Order By 1,2,3;

Result: