BasketGenerator Example: Partition by tranid - 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).

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.