1.1 - 8.10 - BasketGenerator Example: Partition by tranid - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)

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;

Output

 tranid item1  item2   
 ------ ------ ------- 
   1001 butter eggs   
   1002 butter milk   
   1000 eggs   flour  
   1001 butter milk   
   1002 butter spinach
   1000 eggs   milk   
   1001 eggs   milk   
   1002 milk   spinach
   1000 eggs   spinach
   1500 butter eggs   
   1000 flour  milk   
   1500 butter flour  
   1000 flour  spinach
   1500 eggs   flour  
   1000 milk   spinach
    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

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.