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;