Partition by tranid is recommended for use on a small number of transactions.
Input
The input table, grocery_transaction, contains data for five transactions or customers. The function outputs the different combinations of two items (basket size of 2) grouped by the transaction id (tranid).
tranid | period | storeid | region | item | sku | category |
---|---|---|---|---|---|---|
999 | 20100715 | 1 | west | milk | 1 | dairy |
999 | 20100715 | 1 | west | butter | 2 | dairy |
999 | 20100715 | 1 | west | eggs | 3 | dairy |
999 | 19990715 | 1 | west | flour | 4 | baking |
999 | 19990715 | 1 | west | spinach | 4 | produce |
1000 | 20100715 | 1 | west | milk | 1 | dairy |
1000 | 20100715 | 1 | west | eggs | 3 | dairy |
1000 | 19990715 | 1 | west | flour | 4 | baking |
1000 | 19990715 | 1 | west | spinach | 2 | produce |
1001 | 20100715 | 1 | west | milk | 1 | dairy |
1001 | 20100715 | 1 | west | butter | 2 | dairy |
1001 | 20100715 | 1 | west | eggs | 3 | dairy |
1002 | 20100715 | 1 | west | milk | 1 | dairy |
1002 | 20100715 | 1 | west | butter | 2 | dairy |
1002 | 20100715 | 1 | west | spinach | 3 | produce |
1500 | 20100715 | 3 | west | butter | 2 | dairy |
1500 | 20100715 | 3 | west | eggs | 3 | dairy |
1500 | 20100715 | 3 | west | flour | 4 | baking |
SQL Call
SELECT * FROM BasketGenerator ( ON grocery_transaction PARTITION BY tranid USING TargetColumns ('item') BasketSize (2) Accumulate ('tranid') OutputCombinations ('true') ) AS dt ORDER BY tranid;
Output
tranid | item1 | item2 |
---|---|---|
999 | butter | eggs |
999 | butter | flour |
999 | butter | milk |
999 | butter | spinach |
999 | eggs | flour |
999 | eggs | milk |
999 | eggs | spinach |
999 | flour | milk |
999 | flour | spinach |
999 | milk | spinach |
1000 | eggs | flour |
1000 | eggs | milk |
1000 | eggs | spinach |
1000 | flour | milk |
1000 | flour | spinach |
1000 | milk | spinach |
1001 | butter | eggs |
1001 | butter | milk |
1001 | eggs | milk |
1002 | butter | milk |
1002 | butter | spinach |
1002 | milk | spinach |
1500 | butter | eggs |
1500 | butter | flour |
1500 | eggs | flour |