Database Reference
In-Depth Information
customers who had not purchased anything, a
LEFT OUTER JOIN
is used to
include all customers. The customer's age and sales are stored in an array in the
cust_age_sales
table. The MADlib k-means function expects the coordinates to
be expressed as an array.
/* create an empty table to store the input for the k-means
analysis */
CREATE TABLE cust_age_sales (
customer_id integer,
coordinates float8[])
/* prepare the input for the k-means analysis */
INSERT INTO cust_age_sales (customer_id, coordinates[1],
coordinates[2])
SELECT d.customer_id,
d.customer_age,
CASE
WHEN s.sales IS NULL THEN 0.0
ELSE s.sales
END
FROM customer_demographics d
LEFT OUTER JOIN (SELECT r.customer_id,
SUM(r.item_quantity * r.item_price) AS sales
FROM orders_recent r
GROUP BY r.customer_id) s
ON d.customer_id = s.customer_id
/* examine the first 10 rows of the input */
SELECT * from cust_age_sales
order by customer_id
LIMIT 10
customer_id coordinates
1 {32,14.98}
2 {32,51.48}
3 {33,151.89}
4 {27,88.28}
5 {31,4.85}
6 {26,54}
7 {29,63}
8 {25,101.07}
