Customers who Ordered All Types of Products
l Problem
There are some database tables on the order status as given below.
The Customers table which records customer IDs and account balances of customers:
The Orders table records IDs of orders and corresponding customer IDs:
The OrderDetails table records the detailed information about each order, including the product IDs and order quantities.
The Products table records all product information of the enterprise:
Please calculate the average account balance (acct_balance) for the customers who have ordered all types of products of the company as well as for other customers.
l Tip
General steps: Firstly, materialize the association between tables by taking advantage of an esProc feature - the pointer reference. In other words, update such fields as item_id, order_id and customer_id with the corresponding records, so that from any record in the resulting Orders table you can directly reference and retrieve the correponding records in the other associated tables. Secondly, group the Orders table by customer_id. Thirdly, count the different product types purchased by every customer and compare the result with the count of all types of products listed in the Product table so as to search for the customers with the counts of the types of products purchased by them equaling the number of records in the Products table. Those qualified customers are the ones who purchased all types of products; the rest of the custormers are those who didn’t purchase all types of products. Finally, calculate the average account balances for the two kinds of customers respectively.
1. Set *_id fields as the primary keys for the OrderDetails, Customers, and Products tables respectively;
2. Call the switch function to update the order_id field of the Orders table with the corresponding records in the OrderDetails table, update the customer_id field of the Orders table with the corresponding records in the Customers table, and update the item_id field of the OrderDetails table with the corresponding records in the Products table.
3. Group the Orders table by customer_id, count the different types of products purchased by each customers, and create a new table sequence.
4. Select the customers with the counts of the types of products purchased by them equaling the number of records in the Products table;
5. Calculate the average account balance for these customers.
6. Calculate the average account balance for other customers.
l Code
|
|
A |
|
|
1 |
=file("C:\\txt\\Orders.txt").import@t() |
The Orders table |
|
2 |
=file("C:\\txt\\OrderDetails.txt").import@t() |
The OrderDetails table |
|
3 |
=file("C:\\txt\\Customers.txt").import@t() |
The Customers table |
|
4 |
=file("C:\\txt\\Products2.txt").import@t() |
The Products table |
|
5 |
>A3.keys(customer_id), A2.keys(order_id), A4.keys(item_id) |
Set primary keys for tables |
|
6 |
>A1.switch(order_id,A2; customer_id,A3), A2.switch(item_id,A4) |
Replace the ID fields in the Orders table and the OrderDetails table with the corresponding records |
|
7 |
=A1.group(customer_id:customer;~:orders,~.id(order_id.item_id).len():items_cnt) |
Group the Orders table by customer_id, and count the types of products purchased by every customer |
|
8 |
=A7.select(items_cnt==A4.len()) |
Select the customers with the counts of the types of products purchased by them equaling to the number of records in the Products table |
|
9 |
=A8.avg(customer.acct_balance) |
The average account balances of these customers |
|
10 |
=(A7\A8).avg(customer.acct_balance) |
The average account balances of other customers |
l Result
The average account balance (acct_balance) for the customers who have ordered all types of products from the company:
The average account balance for the customers who have not ordered all products of the company:
