Example: Using a Table Operator with Multiple PARTITION BY Inputs - Teradata VantageCloud Lake

The following example shows one way that you can use table operator functionality with multiple PARTITION BY inputs.

Suppose that you have the following tables:

In this example, the operator attribute_sales returns the amount of sales revenue that is attributed to online ads. The inputs to the operator are sales information from the store’s web logs and logs from the ad server. Both the input tables are partitioned on the user’s browser cookie. The operator also accepts two custom clauses, Clicks and Impressions, which supply the percentages of sales to attribute to ad clicks and the views that lead to a purchase, respectively.

The syntax is as follows:

     SELECT adname, cart_amt
       FROM attribute_sales (
       ON (SELECT cookie, cart_amt FROM weblog WHERE page = 'thankyou' )
                     as W PARTITION BY cookie
       ON adlog as S PARTITION BY cookie
       USING clicks(.8)  impressions(.2)) AS D1(adname,attr_revenue) ;

The output looks similar to the following: