Powermultiset to find combinations
Part 1 of 2
This article consists of a statement of the problem to be solved, and then walks through the steps of solving it. There could be mistakes. There could be a better way to do this. Hopefully the reader can find some benefit regardless of the imperfections.
Statement of the Problem
Each department has it’s own products. Each product has a price.
The SQL query in this example answers the question:
Given that a customer can purchase, from a single department, any number of products, but not more than one of a specific product, what are the possible dollar amounts that the customer could potentially pay.
For example, Department 1 has 3 products:
product 1 cost $3
product 2 cost $5
product 3 cost $2
A customer of Department 1 could purchase one of the following 7 combinations.
1. product 1 - cost = $3
2. product 2 - cost = $5
3. product 3 - cost = $2
4. product 1 and product 2 - total cost = $8
5. product 1 and product 3 - total cost = $5
6. product 2 and product 3 - total cost = $7
7. product 1 and product 2 and product 3 - total cost = $10
So, given this data the answer would be (2,3,5,8,7 and 10)
CREATE TABLE DEMO
(
DEPARTMENT NUMBER(3) NOT NULL,
PRODUCT NUMBER NOT NULL,
UNIT_PRICE NUMBER(8,2) NOT NULL
);
SET DEFINE OFF;
Insert into DEMO (DEPARTMENT, PRODUCT, UNIT_PRICE) Values (1, 10, 1200);
Insert into DEMO (DEPARTMENT, PRODUCT, UNIT_PRICE) Values (2, 22, 99);
Insert into DEMO (DEPARTMENT, PRODUCT, UNIT_PRICE) Values (2, 23, 50);
Insert into DEMO (DEPARTMENT, PRODUCT, UNIT_PRICE) Values (2, 24, 101);
Insert into DEMO (DEPARTMENT, PRODUCT, UNIT_PRICE) Values (2, 25, 150);
Insert into DEMO (DEPARTMENT, PRODUCT, UNIT_PRICE) Values (3, 30, 1999);
Insert into DEMO (DEPARTMENT, PRODUCT, UNIT_PRICE) Values (4, 41, 500);
Insert into DEMO (DEPARTMENT, PRODUCT, UNIT_PRICE) Values (4, 45, 250);
Insert into DEMO (DEPARTMENT, PRODUCT, UNIT_PRICE) Values (5, 56, 40);
Insert into DEMO (DEPARTMENT, PRODUCT, UNIT_PRICE) Values (6, 63, 250);
Insert into DEMO (DEPARTMENT, PRODUCT, UNIT_PRICE) Values (7, 77, 1200);
Insert into DEMO (DEPARTMENT, PRODUCT, UNIT_PRICE) Values (7, 73, 50);
Insert into DEMO (DEPARTMENT, PRODUCT, UNIT_PRICE) Values (8, 81, 40);
Insert into DEMO (DEPARTMENT, PRODUCT, UNIT_PRICE) Values (9, 91, 200);
Insert into DEMO (DEPARTMENT, PRODUCT, UNIT_PRICE) Values (9, 94, 250);
Insert into DEMO (DEPARTMENT, PRODUCT, UNIT_PRICE) Values (10, 77, 500);
COMMIT;
CREATE OR REPLACE type demo_prod_typ as object(
department integer,
product integer
)
/
CREATE OR REPLACE type demo_prod_tab_typ as table of demo_prod_typ
/
In the HTML tables in this example were generated from SQLPlus using the set mark html on command. I used SQLPLUS because it has some useful pretty printing features. Specifically, when the output column is a nested table, SQLPlus will display the entire structure as a string. When you encounter examples of output data that look like this “DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(1, 10))”, you should understand that this is not the actual data. Instead, this is SQLPlus’s way of telling you that - the content of this column is a nested table named DEMO_PRD_TAB and that table contains a single object named DEMO_PROD_TYP and the object contains 2 variables which have the values of 1 and 10. If you look at the HTML table’s header for this column it will say “OID(DEPARTMENT, PRODUCT). This tells you that 1 represents a department and 10 represents a product.
The final solution is shown at the bottom of this article. You might want to take a look so that you can understand the context of the sub queries as I work my way from the inner most select to the outer select.
SELECT department, CAST
( COLLECT ( demo_prod_typ ( department
, product ) ) AS demo_prod_tab_typ )
OID
FROM demo
GROUP BY department
The innermost sub-query is shown above. In order to get the desired final result set, we will need to use the powermultiset function. This function operates on a table of objects, so we know that we first need to create these collections. We will have one collection per each grouping of department. COLLECT is a aggregating function just like MAX and MIN in so much as they produce a single result per group. The COLLECT will result in a single nested table of demo_product_typ per department. However, the table that contains these objects will be system defined. The name of the table will be something like SYSTPL16fhcAkBaDgQAEKXAp4Gg. The powermultiset function will not be able use these ’system named’ nested tables unless we first CAST them to a type that powermultiset “knows” about. The result of this SELECT is shown below.
| DEPAR TMENT | OID(DEPARTMENT, PRODUCT) |
| 1 | DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(1, 10)) |
| 2 | DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2,22),DEMO_PROD_TYP(2,24),DEMO_PROD_TYP(2,25),DEMO_PROD_TYP(2,23)) |
| 3 | DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(3, 30)) |
| 4 | DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(4, 41), DEMO_PROD_TYP(4, 45)) |
| 5 | DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(5, 56)) |
| 6 | DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(6, 63)) |
| 7 | DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(7, 77), DEMO_PROD_TYP(7, 73)) |
| 8 | DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(8, 81)) |
| 9 | DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(9, 91), DEMO_PROD_TYP(9, 94)) |
| 10 | DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(10, 77)) |
10 rows selected.
SELECT VALUE(t2) xx, t3.department department_t3, t3.product product_t3
FROM (SELECT department,
CAST
(COLLECT(demo_prod_typ(department, product)) AS demo_prod_tab_typ)
OID
FROM demo
GROUP BY department) t1,
TABLE(POWERMULTISET(t1.OID)) t2;
Now, having constructed and identified our nested tables, we are ready for POWERMULTISET to do it’s magic. But first, let’s talk about the JOIN in the above SELECT statement. You can see that the outer select is joining 3 tables; the first table is defined by a sub query and named t1. The second and third tables are the results of a TABLE functions and named t2 and t3 respectively. Unless you are already familiar with this type of join, you might falsely conclude that this is a 3 way Cartesian join because no join criteria have been defined. Instead, this is called a left correlated join and this specific join does goes like this:
For each row returned in the sub query known as t1, pass this row’s demo_prod_tab_typ (OID) to the POWERMULTISET function. Then, join each row from the current t1 to each row returned by the POWERMULTISET function. Then, for each row that results from the T1,T2 join, join to that the VALUE of t2. The value of t2 will always be a single row, and the output shown in the HTML table below might tell you why we are joining row.
| XX(DEPARTMENT, PRODUCT) | DEPART MENT_T3 | PRODU CT_T3 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(1, 10)) | 1 | 10 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22)) | 2 | 22 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 24)) | 2 | 24 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24)) | 2 | 22 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24)) | 2 | 24 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 25)) | 2 | 25 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 25)) | 2 | 22 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 25)) | 2 | 25 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 25)) | 2 | 24 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 25)) | 2 | 25 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24), | 2 | 22 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24), | 2 | 24 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24), | 2 | 25 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 23)) | 2 | 23 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 23)) | 2 | 22 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 23)) | 2 | 23 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 23)) | 2 | 24 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 23)) | 2 | 23 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24), | 2 | 22 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24), | 2 | 24 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24), | 2 | 23 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 25), DEMO_PROD_TYP(2, 23)) | 2 | 25 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 25), DEMO_PROD_TYP(2, 23)) | 2 | 23 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 25), | 2 | 22 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 25), | 2 | 25 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 25), | 2 | 23 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 25), | 2 | 24 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 25), | 2 | 25 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 25), | 2 | 23 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2,22), DEMO_PROD_TYP(2,24),DEMO_PROD_TYP(2,25),DEMO_PROD_TYP(2,23)) | 2 | 22 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 25), DEMO_PROD_TYP(2,23)) | 2 | 24 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 25), DEMO_PROD_TYP(2,23)) | 2 | 25 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 25), DEMO_PROD_TYP(2,23)) | 2 | 23 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(3, 30)) | 3 | 30 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(4, 41)) | 4 | 41 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(4, 45)) | 4 | 45 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(4, 41), DEMO_PROD_TYP(4, 45)) | 4 | 41 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(4, 41), DEMO_PROD_TYP(4, 45)) | 4 | 45 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(5, 56)) | 5 | 56 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(6, 63)) | 6 | 63 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(7, 77)) | 7 | 77 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(7, 73)) | 7 | 73 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(7, 77), DEMO_PROD_TYP(7, 73)) | 7 | 77 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(7, 77), DEMO_PROD_TYP(7, 73)) | 7 | 73 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(8, 81)) | 8 | 81 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(9, 91)) | 9 | 91 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(9, 94)) | 9 | 94 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(9, 91), DEMO_PROD_TYP(9, 94)) | 9 | 91 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(9, 91), DEMO_PROD_TYP(9, 94)) | 9 | 94 |
| DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(10, 77)) | 10 | 77 |
50 rows selected.
continued in part 2 posting
No comments:
Post a Comment