Example: Using a Multiple Input Table Operator with DIMENSION Input - 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™

In this example, the table operator closest_store finds the closest retail store when a purchase is made over a cell phone. The operator takes the following input data sets:

  • The phone_purchases table, which contains entries for purchases made over a cell phone, along with normalized spatial coordinates of the phone when the online purchase was made.
  • The stores table, which contains the location of all the retail stores and their associated normalized spatial coordinates. The stores table is a smaller fact table that is provided as DIMENSION input.
    pid x_coordinate y_coordinate
    P0 2 1
    P1 1 5
    P2 3 2
    P3 0 4
sid x_coordinate y_coordinate
SO 1 4
S1 2 3

The SELECT syntax is as follows:

     SELECT pid, sid
       FROM closest_store (
       ON phone_purchases PARTITION BY pid,
       ON stores DIMENSION) AS D;

The output looks similar to the following:

  pid ---   sid ---
  P0   S1
  P1   S0
  P2   $S1
  P3   $SO

You can also make the table operator call using PARTITION BY ANY, which would keep the existing distribution of the rows on the AMPs. The SELECT syntax is as follows:

     SELECT pid, sid
       FROM closest_store (
       ON phone_purchases PARTITION BY ANY,
       ON stores DIMENSION) AS D;