Average account balance of customers who have ordered all products and other customers
Here are some tables in a database of enterprise order status. Firstly, the Customers table records customer IDs and account balances:
The Orders table records each order and its customers:
The OrderDetails table records detailed information for each order, including the ordered product ID and quantity:
The Products table records all product information of the enterprise:
Now we need to calculate the average acct_balance of customers who have ordered all of the company's products, as well as the average account balance of other customers.
Firstly, we need to use foreign key conversion to materialize the association between each table, by replacing fields such as item_id, order_id, and customer_id with corresponding records. This way, the resulting Orders table is associated with all other tables, and starting from any record in the Order table, we can directly extract its corresponding record from other tables. Then group the Orders table by customer and calculate the quantity of unique products purchased by each customer. Compare this quantity with the total product quantity in the Products table, select customers with equal number, which are the customers who have purchased all products. The difference set of all customers and the above set are the customers who have not purchased all products. Calculate their average account balances respectively.
| A | B | |
|---|---|---|
| 1 | =T("Customers.txt").keys(customer_id) | =T("Orders.txt") |
| 2 | =T("OrderDetails.txt").keys(order_id) | =T("Products.txt").keys(item_id) |
| 3 | >B1.switch(order_id,A2; customer_id,A1), A2.switch(item_id,B2) | =B1.groups(customer_id:customer; icount(order_id.item_id):items_cnt) |
| 4 | =all=B2.len(),B3.select(items_cnt==all) | |
| 5 | =A4.avg(customer.acct_balance) | =(B3\A4).avg(customer.acct_balance) |
https://try.esproc.com/splx?3jL
A1 reads the customer table and sets the primary key, B1 reads the order table, A2 reads the order details table and sets the primary key, B2 reads the product table and sets the primary key.
A3 uses the switch function to perform foreign key association, converting the order ID and customer ID of the order into corresponding records, and also converting the product names in the order details table into corresponding records.
B3 performs grouping and aggregation of order data, calculates the total number of product items ordered in each customer's order, and uses icount to only calculate the total number of different products during the aggregate calculation. The results are as follows:
A4 selects customer data for ordering all products:
A5 calculates the average account balance of customers who have ordered all products:
A6 calculates the average account balance of other customers:
