BasketGenerator Example 1: Partition by tranid - Teradata Vantage

Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
8.00
1.0
Published
May 2019
Language
English (United States)
Last Update
2019-11-22
dita:mapPath
blj1506016597986.ditamap
dita:ditavalPath
blj1506016597986.ditaval
dita:id
B700-4003
lifecycle
previous
Product Category
Teradata Vantageā„¢

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

grocery_transaction
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