16.10 - Example 8: Multiple Partitioned Input Tables and Dimension Input Table - Teradata Database

Teradata Analytic Functions

prodname
Teradata Database
vrm_release
15.10
16.10
created_date
November 2017
category
Programming Reference
featnum
B035-1206-151K

An e-commerce store wants to count the advertising impressions that lead to a user clicking an online advertisement. The example counts the online advertisements that the user viewed and the television advertisements that the user might have viewed.

Input

nPath Multiple-Input Example 2 Input Table impressions
userid ts imp
1 2012-01-01 ad1
1 2012-01-02 ad1
1 2012-01-03 ad1
1 2012-01-04 ad1
1 2012-01-05 ad1
1 2012-01-06 ad1
1 2012-01-07 ad1
2 2012-01-08 ad2
2 2012-01-09 ad2
2 2012-01-10 ad2
2 2012-01-11 ad2
... ... ...
nPath Multiple-Input Example 2 Input Table clicks2
userid ts click
1 2012-01-01 ad1
2 2012-01-08 ad2
3 2012-01-16 ad3
4 2012-01-23 ad4
5 2012-02-01 ad5
6 2012-02-08 ad6
7 2012-02-14 ad7
8 2012-02-24 ad8
9 2012-03-02 ad9
10 2012-03-10 ad10
11 2012-03-18 ad11
12 2012-03-25 ad12
13 2012-03-30 ad13
14 2012-04-02 ad14
15 2012-04-06 ad15
nPath Multiple-Input Example 2 tv_spots
ts tv_imp
2012-01-01 ad2
2012-01-02 ad2
2012-01-03 ad3
2012-01-04 ad4
2012-01-05 ad5
2012-01-06 ad6
2012-01-07 ad7
2012-01-08 ad8
2012-01-09 ad9
2012-01-10 ad10
2012-01-11 ad11
2012-01-12 ad12
2012-01-13 ad13
2012-01-14 ad14
2012-01-15 ad15

SQL-MapReduce Call

The tables impressions and clicks have a user_id column, but the table tv_spots is only a record of television advertisements shown, which any user might have seen. Therefore, tv_spots must be a dimension table.

SELECT * FROM npath (
   ON impressions PARTITION BY userid ORDER BY ts
   ON clicks2 PARTITION BY userid ORDER BY ts
   ON tv_spots DIMENSION ORDER BY ts
   USING
   MODE (nonoverlapping)
   SYMBOLS (true as imp, true as click, true as tv_imp)
   PATTERN ('(imp|tv_imp)*.click')
   RESULT (COUNT(* of imp) as imp_cnt,
           COUNT (* of tv_imp) as tv_imp_cnt)
) as dt ORDER BY dt.imp_cnt;

Output

nPath Multiple-Input Example 2 Output Table
dt.imp_cnt tv_imp_cnt
18  
19 0
19 0
20 0
21 0
22 0
22 0
22 0
22 0
22 0
23 0
23 0
23 0
24 0
25 0