Example: Using a Table Operator with Multiple PARTITION BY Inputs - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
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