Example: Using a Table Operator with Multiple PARTITION BY Inputs - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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:

WebLog
cookie cart_amount page
AAAA $60 Thankyou
AAAA $140 Thankyou
BBBB $100 Thankyou
CCCC   Intro
CCCC $200 Thankyou
DDDD $100 Thankyou
AdLog
cookie ad_name action
AAAA Champs Impression
AAAA Puppies Click
BBBB Apples Click
CCCC Baseball Impression
CCCC Apples Click

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:

  ad_name --------   attr_revenue ------------
  Champs   $40
  Puppies   $160
  Apples   $240
  Baseball   $40