Example 1: Sparse Input - Teradata VantageCloud Lake

InputTable (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 query

SELECT * from grocery_transaction order by 1;

Output:

tranid	period	storeid	region	item	sku	category
999	20100715	1	west	eggs	3	dairy
999	19990715	1	west	spinach	4	produce
999	19990715	1	west	flour	4	baking
999	20100715	1	west	butter	2	dairy
999	20100715	1	west	milk	1	dairy
1000	19990715	1	west	flour	4	baking
1000	19990715	1	west	spinach	2	produce
1000	20100715	1	west	eggs	3	dairy
1000	20100715	1	west	milk	1	dairy
1001	20100715	1	west	eggs	3	dairy
1001	20100715	1	west	butter	2	dairy
1001	20100715	1	west	milk	1	dairy
1002	20100715	1	west	spinach	3	produce
1002	20100715	1	west	butter	2	dairy
1002	20100715	1	west	milk	1	dairy
1500	20100715	3	west	flour	4	baking
1500	20100715	3	west	eggs	3	dairy
1500	20100715	3	west	butter	2	dairy

Query 1

SELECT * FROM TD_Apriori(
ON grocery_transaction as InputTable
USING
TargetColumn('item')
IDColumn('tranid')
MaxLen(2)  
PatternsOrRules('Rules')
) as dt;

Output:

Antecedent	Consequence	Ante_Item_Cnt	Conse_Item_Cnt	cntb	Cnt_Antecedent	Cnt_Consequence	score
butter	eggs	1	1	3	4	4	0.5625
butter	flour	1	1	2	4	3	0.3333
butter	milk	1	1	3	4	4	0.5625
butter	spinach	1	1	2	4	3	0.3333
eggs	butter	1	1	3	4	4	0.5625
eggs	flour	1	1	3	4	3	0.75
eggs	milk	1	1	3	4	4	0.5625
eggs	spinach	1	1	2	4	3	0.333
flour	butter	1	1	2	3	4	0.3333
flour	eggs	1	1	3	3	4	0.75
flour	milk	1	1	2	3	4	0.333
flour	spinach	1	1	2	3	3	0.4444
milk	butter	1	1	3	4	4	0.5625
milk	eggs	1	1	3	4	4	0.5625
milk	flour	1	1	2	4	3	0.3333
milk	spinach	1	1	3	4	3	0.75
spinach	butter	1	1	2	3	4	0.3333
spinach	eggs	1	1	3	3	4	0.3333
spinach	flour	1	1	3	3	3	0.444
spinach	milk	1	1	3	3	4	0.75

support	confidence	lift	Conviction	Leverage	Coverage	Chi_Square
0.6	0.75	0.9375	0.8	-0.04	0.8	0.3125
4.000000000000000E-01	5.000000000000000E-01	8.333333333333330E-01	0.8	-0.08	0.8	0.83333
6.000000000000000E-01	7.500000000000000E-01	9.375000000000000E-01	0.8	-0.04	0.8	0.3125
4.000000000000000E-01	5.000000000000000E-01	8.333333333333330E-01	0.8	-0.08	0.8	0.83333
6.000000000000000E-01	7.500000000000000E-01	9.375000000000000E-01	0.8	-0.04	0.8	0.3125
6.000000000000000E-01	7.500000000000000E-01	1.25000000000000E 000	1.6	0.12	0.8	1.875
6.000000000000000E-01	7.500000000000000E-01	9.375000000000000E-01	0.8	-0.04	0.8	0.3125
4.000000000000000E-01	5.000000000000000E-01	8.333333333333330E-01	0.8	-0.08	0.8	0.83333
4.000000000000000E-01	6.666666666666670E-01	8.333333333333330E-01	0.6	-0.08	0.6	0.83333
6.000000000000000E-01	1.00000000000000E 000	1.25000000000000E 000	0	0.12	0.6	1.875
4.000000000000000E-01	6.666666666666670E-01	8.333333333333330E-01	0.6	-0.08	0.6	0.8333
4.000000000000000E-01	6.666666666666670E-01	1.11111111111111E 000	1.2	0.04	0.6	0.31888
6.000000000000000E-01	7.500000000000000E-01	9.375000000000000E-01	0.8	-0.04	0.8	0.3125
6.000000000000000E-01	7.500000000000000E-01	9.375000000000000E-01	0.8	-0.04	0.8	0.3125
4.000000000000000E-01	5.000000000000000E-01	8.333333333333330E-01	0.8	-0.08	0.8	0.8333
6.000000000000000E-01	7.500000000000000E-01	1.25000000000000E 000	1.6	0.12	0.8	1.875
4.000000000000000E-01	6.666666666666670E-01	8.333333333333330E-01	0.6	-0.08	0.6	0.8333
4.000000000000000E-01	6.666666666666670E-01	8.333333333333330E-01	0.6	-0.08	0.6	0.8333
4.000000000000000E-01	6.666666666666670E-01	1.11111111111111E 000	1.2	0.04	0.6	0.1388
6.000000000000000E-01	1.00000000000000E 000	1.25000000000000E 000	0	0.12	0.6	1.875

Query 2

SELECT * FROM TD_Apriori(
ON grocery_transaction as InputTable
USING
TargetColumn('item')
IDColumn('tranid')
MaxLen(2)  
PatternsOrRules('Patterns')
) as dt;

Output:

Itemset	Support
butter,eggs	0.6
butter,flour	0.4
butter,milk	0.6
butter,spinach	0.4
eggs,flour	0.6
eggs,milk	0.6
eggs,spinach	0.4
flour,spinach	0.4
milk,flour	0.4
milk,spinach	0.6