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 |