Powermultiset to find combinations
Part 2 of 2
If you haven’t figured it out, the reason for the xx column is to identify the set, which was produced by the powermultiset function. Starting from row 1 of the html table above, read it like this:
product 10 of department 1 is a member of the set (DEMO_PROD_TYP(1, 10))
product 24 of department 2 is a member of the set (DEMO_PROD_TYP(2, 22))
product 22 of department 2 is a member of the set (DEMO_PROD_TYP(2, 24))
product 22 of department 2 is a member of the set (DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24))
product 24 of department 2 is a member of the set (DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24))
product 25 of department 2 is a member of the set (DEMO_PROD_TYP(2, 25))
product 22 of department 2 is a member of the set (DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 25))
and so on
Unfortunately, we can not use GROUP BY on a nested table such as DEMO_PROD_TAB_TYP. Also, we have the groupings for the products, but we don’t have the unit_price of the products. We’ll take care of this problem in the next outward SELECT.
Getting the unit_price is no problem because we have the department and product from the lower level SELECT. In the below example, “SELECT unit_price … ” does the job.
As for the nested table column, we will change it into a string so that we can perform GROUP BY on it. I don’t want to address the XML functions, so I’ll let the code speak for itself. The result of the SELECT is shown in the HTML table below the code
SELECT DISTINCT department_t3, SUM(price) possible
FROM (SELECT department_t3, product_t3,
(SELECT unit_price
FROM demo
WHERE department = department_t3
AND product = product_t3) price,
CAST
((SELECT XMLELEMENT
("D",
XMLAGG(XMLELEMENT("E", e.product))).getclobval
()
AS "department"
FROM TABLE(xx) e) AS VARCHAR2(500)) myxml
FROM (SELECT VALUE(t2) xx, t3.department department_t3,
t3.product product_t3
FROM (SELECT department,
CAST
(COLLECT
(demo_prod_typ(department,
OID
FROM demo
GROUP BY department) t1,
TABLE(POWERMULTISET(t1.OID)) t2,
TABLE(VALUE(t2)) t3))
GROUP BY department_t3, myxml
ORDER BY 1, 2;
and this is some more normal web
| DEPART MENT_T3 | PRODUC T_T3 | PRICE | MYXML |
| 1 | 10 | 1200 | <D><E>10</E></D> |
| 2 | 22 | 99 | <D><E>22</E></D> |
| 2 | 24 | 101 | <D><E>24</E></D> |
| 2 | 22 | 99 | <D><E>22</E><E>24</E></D> |
| 2 | 24 | 101 | <D><E>22</E><E>24</E></D> |
| 2 | 25 | 150 | <D><E>25</E></D> |
| 2 | 22 | 99 | <D><E>22</E><E>25</E></D> |
| 2 | 25 | 150 | <D><E>22</E><E>25</E></D> |
| 2 | 24 | 101 | <D><E>24</E><E>25</E></D> |
| 2 | 25 | 150 | <D><E>24</E><E>25</E></D> |
| 2 | 22 | 99 | <D><E>22</E><E>24</E><E>25</E></D> |
| 2 | 24 | 101 | <D><E>22</E><E>24</E><E>25</E></D> |
| 2 | 25 | 150 | <D><E>22</E><E>24</E><E>25</E></D> |
| 2 | 23 | 50 | <D><E>23</E></D> |
| 2 | 22 | 99 | <D><E>22</E><E>23</E></D> |
| 2 | 23 | 50 | <D><E>22</E><E>23</E></D> |
| 2 | 24 | 101 | <D><E>24</E><E>23</E></D> |
| 2 | 23 | 50 | <D><E>24</E><E>23</E></D> |
| 2 | 22 | 99 | <D><E>22</E><E>24</E><E>23</E></D> |
| 2 | 24 | 101 | <D><E>22</E><E>24</E><E>23</E></D> |
| 2 | 23 | 50 | <D><E>22</E><E>24</E><E>23</E></D> |
| 2 | 25 | 150 | <D><E>25</E><E>23</E></D> |
| 2 | 23 | 50 | <D><E>25</E><E>23</E></D> |
| 2 | 22 | 99 | <D><E>22</E><E>25</E><E>23</E></D> |
| 2 | 25 | 150 | <D><E>22</E><E>25</E><E>23</E></D> |
| 2 | 23 | 50 | <D><E>22</E><E>25</E><E>23</E></D> |
| 2 | 24 | 101 | <D><E>24</E><E>25</E><E>23</E></D> |
| 2 | 25 | 150 | <D><E>24</E><E>25</E><E>23</E></D> |
| 2 | 23 | 50 | <D><E>24</E><E>25</E><E>23</E></D> |
| 2 | 22 | 99 | <D><E>22</E><E>24</E><E>25</E><E>23</E></D> |
| 2 | 24 | 101 | <D><E>22</E><E>24</E><E>25</E><E>23</E></D> |
| 2 | 25 | 150 | <D><E>22</E><E>24</E><E>25</E><E>23</E></D> |
| 2 | 23 | 50 | <D><E>22</E><E>24</E><E>25</E><E>23</E></D> |
| 3 | 30 | 1999 | <D><E>30</E></D> |
| 4 | 41 | 500 | <D><E>41</E></D> |
| 4 | 45 | 250 | <D><E>45</E></D> |
| 4 | 41 | 500 | <D><E>41</E><E>45</E></D> |
| 4 | 45 | 250 | <D><E>41</E><E>45</E></D> |
| 5 | 56 | 40 | <D><E>56</E></D> |
| 6 | 63 | 250 | <D><E>63</E></D> |
| 7 | 77 | 1200 | <D><E>77</E></D> |
| 7 | 73 | 50 | <D><E>73</E></D> |
| 7 | 77 | 1200 | <D><E>77</E><E>73</E></D> |
| 7 | 73 | 50 | <D><E>77</E><E>73</E></D> |
| 8 | 81 | 40 | <D><E>81</E></D> |
| 9 | 91 | 200 | <D><E>91</E></D> |
| 9 | 94 | 250 | <D><E>94</E></D> |
| 9 | 91 | 200 | <D><E>91</E><E>94</E></D> |
| 9 | 94 | 250 | <D><E>91</E><E>94</E></D> |
| 10 | 77 | 500 | <D><E>77</E></D> |
50 rows selected.
We only have one more step which is addressed by the outer most SELECT. The only part of this SELECT that might not be obvious is the DISTINCT. Imagine the powerset of {1,2,3}. It would be:
{1},{2},{3},{1,2},{1,3}{2,3}{1,2,3}, now, add together the elements of each group. The results are
1,2,3,3,4,5,6.
As you can see, 3 occurs twice. We don’t want to state twice that 3 is a possible result of adding the elements, hence DISTINCT.
SELECT DISTINCT department_t3, SUM(price) possible
FROM (SELECT department_t3, product_t3,
(SELECT unit_price
FROM demo
WHERE department = department_t3
AND product = product_t3) price,
CAST
((SELECT XMLELEMENT
("D",
XMLAGG(XMLELEMENT("E", e.product))).getclobval()
AS "department"
FROM TABLE(xx) e) AS VARCHAR2(500)) myxml
FROM (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,
TABLE(VALUE(t2)) t3))
GROUP BY department_t3, myxml
ORDER BY 1, 2;
The final result set looks like:
| DEPARTMENT_T3 | POSSIBLE |
| 1 | 1200 |
| 2 | 50 |
| 2 | 99 |
| 2 | 101 |
| 2 | 149 |
| 2 | 150 |
| 2 | 151 |
| 2 | 200 |
| 2 | 249 |
| 2 | 250 |
| 2 | 251 |
| 2 | 299 |
| 2 | 301 |
| 2 | 350 |
| 2 | 400 |
| 3 | 1999 |
| 4 | 250 |
| 4 | 500 |
| 4 | 750 |
| 5 | 40 |
| 6 | 250 |
| 7 | 50 |
| 7 | 1200 |
| 7 | 1250 |
| 8 | 40 |
| 9 | 200 |
| 9 | 250 |
| 9 | 450 |
| 10 | 500 |
29 rows selected.
No comments:
Post a Comment