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, 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 | S11 |
| P1 | S0 |
| P2 | $S1 |
| P3 | $SO |
You can also make the table operator call using PARTITION BY ANY, which keeps the existing distribution of the rows on the AMPs:
SELECT pid, sid FROM closest_store ( ON phone_purchases PARTITION BY ANY, ON stores DIMENSION) AS D;