17.10 - Example: Cogroup Used for a Table Operator with Multiple Inputs - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Administration
Programming Reference
Publication ID
B035-1210-171K
Language
English (United States)

This example demonstrates the grouping operation used with multiple inputs to a table operator, called a cogroup. All of the inputs that satisfy the condition are grouped together in a single group. Cogroup matches the partition keys of multiple inputs, thus ensuring that the results are correct. Using a cogroup enhances the ability of table operators to work with multiple input streams and dimension tables.

Suppose that you have the following two 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

You run the following query:

SELECT cookie, cart_amt, adname, action
     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) as result1 ;

The inputs are grouped together similar to the following table.

Grouped and Nested Relations With the Cogroup on Cookie
Cookie WebLog AdLog
AAAA AAAA,$60, thankyou

AAAA,$140,thankyou

AAAA,champs,impression

AAAA,puppies,click

BBBB BBBB,$100,thankyou BBBB,apples,click
CCCC CCCC,$200,thankyou CCCC,baseball,impression

CCCC,apples,click

DDDD DDDD,$100,thankyou  

The output of the query is similar to the following.

adname attr_revenue
champs $40
puppies $160
apples $240
baseball $40

Each time you invoke the table operator, only the inputs that have the same partition key values participate in the invocation.

If there are no rows of the PARTITION BY ANY and PARTITION BY key inputs on an AMP and the query involves DIMENSION input, the table operator is not invoked even though the DIMENSION table has rows on that AMP.