I have the following 'sorted by row' lists (2nd column), in which every row produces an output (3rd column, and 4th column). This output has been found without using formulas and it represents a sequence of sql database language statements which have been verified and are correct. Just for completeness, these statements are related to evaluating all aggregations from a sequential list of values (2nd column) in order to not evaluate already evaluated groups. These aggregations can be evaluated in SQL with a simple statement, like for example:
group by cube(F1,F2,F3,F4)
which groups by all 2 raised to 4 combinations without repetitions of 4 elements taken 0,1,2,3,4 at a time, and more exactly:
F1,F2,F3,F4, (F1,F2), (F1,F3), (F1,F4), (F2,F3), (F2,F4), (F3,F4), (F1,F2,F3), (F1,F2,F4), (F1,F3,F4), (F2,F3,F4), (F1,F2,F3,F4), ()
where () means grouping by no effective combination (just evaluates the sql aggregation function).
If we consider one example (i.e. 4th example), we have that these 6 statements (which evaluate already evaluated groups, generating duplicates that are removed at the end of evaluation using UNION statement and so are not good from a performance point of view):
SELECT F1,F5, COUNT(*) FROM TAB GROUP BY CUBE(F1,F5)
UNION SELECT F1,F6, COUNT(*) FROM TAB GROUP BY CUBE(F1,F6)
UNION SELECT F1,F7, COUNT(*) FROM TAB GROUP BY CUBE(F1,F7)
UNION SELECT F4,F5, COUNT(*) FROM TAB GROUP BY CUBE(F4,F5)
UNION SELECT F4,F6, COUNT(*) FROM TAB GROUP BY CUBE(F4,F6)
UNION SELECT F4,F7, COUNT(*) FROM TAB GROUP BY CUBE(F4,F7)
are equivalent to the following ones (which not suffer from the performance problems, duplicates and multiple evaluation), represented in the 3rd and 4th columns:
SELECT F1,F5, COUNT(*) FROM TAB GROUP BY CUBE(F1,F5)
UNION ALL SELECT F1,F6, COUNT(*) FROM TAB GROUP BY GROUPING SETS ((F6)), GROUPING SETS (CUBE(F1))
UNION ALL SELECT F1,F7, COUNT(*) FROM TAB GROUP BY GROUPING SETS ((F7)), GROUPING SETS (CUBE(F1))
UNION ALL SELECT F4,F5, COUNT(*) FROM TAB GROUP BY GROUPING SETS ((F4)), GROUPING SETS (CUBE(F5))
UNION ALL SELECT F4,F6, COUNT(*) FROM TAB GROUP BY GROUPING SETS ((F4,F6))
UNION ALL SELECT F4,F7, COUNT(*) FROM TAB GROUP BY GROUPING SETS ((F4,F7))
Starting from the following examples, I would like to find a math algorithm to get the 3rd column values (or alternatively the 4th column values), given the ordered list of the 2nd column.
The examples are:
| Ordered list from cartesian product | 3rd column | 4th column | |
|---|---|---|---|
| 1st example: 1: F3,F7,F8 2: F4,F5,F6 |
F3,F4 | F3,F4 | |
| F3,F5 | F5 | F3 | |
| F3,F6 | F6 | F3 | |
| F7,F4 | F7 | F4 | |
| F7,F5 | F7,F5 | ||
| F7,F6 | F7,F6 | ||
| F8,F4 | F8 | F4 | |
| F8,F5 | F8,F5 | ||
| F8,F6 | F8,F6 | ||
| Ordered list from cartesian product | 3rd column | 4th column | |
| 2nd example: 1: F3,F4 2: F5,F6 3: F7,F8 |
F3,F5,F7 | F3,F5,F7 | |
| F3,F5,F8 | F8 | F3,F5 | |
| F3,F6,F7 | F6 | F3,F7 | |
| F3,F6,F8 | F6,F8 | F3 | |
| F4,F5,F7 | F4 | F5,F7 | |
| F4,F5,F8 | F4,F8 | F5 | |
| F4,F6,F7 | F4,F6 | F7 | |
| F4,F6,F8 | F4,F6,F8 | ||
| Ordered list from cartesian product | 3rd column | 4th column | |
| 3rd example: 1: F1,F2 2: F3,F4 3: F5,F6 4: F7,F8 |
F1,F3,F5,F7 | F1,F3,F5,F7 | |
| F1,F3,F5,F8 | F8 | F1,F3,F5 | |
| F1,F3,F6,F7 | F6 | F1,F3,F7 | |
| F1,F3,F6,F8 | F6,F8 | F1,F3 | |
| F1,F4,F5,F7 | F4 | F1,F5,F7 | |
| F1,F4,F5,F8 | F4,F8 | F1,F5 | |
| F1,F4,F6,F7 | F4,F6 | F1,F7 | |
| F1,F4,F6,F8 | F4,F6,F8 | F1 | |
| F2,F3,F5,F7 | F2 | F3,F5,F7 | |
| F2,F3,F5,F8 | F2,F8 | F3,F5 | |
| F2,F3,F6,F7 | F2,F6 | F3,F7 | |
| F2,F3,F6,F8 | F2,F6,F8 | F3 | |
| F2,F4,F5,F7 | F2,F4 | F5,F7 | |
| F2,F4,F5,F8 | F2,F4,F8 | F5 | |
| F2,F4,F6,F7 | F2,F4,F6 | F7 | |
| F2,F4,F6,F8 | F2,F4,F6,F8 | ||
| Ordered list from cartesian product | 3rd column | 4th column | |
| 4th example: 1: F1,F4 2: F5,F6,F7 |
F1,F5 | F1,F5 | |
| F1,F6 | F6 | F1 | |
| F1,F7 | F7 | F1 | |
| F4,F5 | F4 | F5 | |
| F4,F6 | F4,F6 | ||
| F4,F7 | F4,F7 | ||
| Ordered list from cartesian product | 3rd column | 4th column | |
| 5th example: 1: F1,F4 2: F5,F6,F7 3: F8,F9 |
F1,F5,F8 | F1,F5,F8 | |
| F1,F5,F9 | F9 | F1,F5 | |
| F1,F6,F8 | F6 | F1,F8 | |
| F1,F6,F9 | F6,F9 | F1 | |
| F1,F7,F8 | F7 | F1,F8 | |
| F1,F7,F9 | F7,F9 | F1 | |
| F4,F5,F8 | F4 | F5,F8 | |
| F4,F5,F9 | F4,F9 | F5 | |
| F4,F6,F8 | F4,F6 | F8 | |
| F4,F6,F9 | F4,F6,F9 | ||
| F4,F7,F8 | F4,F7 | F8 | |
| F4,F7,F9 | F4,F7,F9 | ||
| Ordered list from cartesian product | 3rd column | 4th column | |
| 6th example: 1: F1,F5 2: F2,F6,F9 3: F3,F7 4: F4,F8,F10 |
F1,F2,F3,F4 | F1,F2,F3,F4 | |
| F1,F2,F3,F8 | F8 | F1,F2,F3 | |
| F1,F2,F3,F10 | F10 | F1,F2,F3 | |
| F1,F2,F7,F4 | F7 | F1,F2,F4 | |
| F1,F2,F7,F8 | F7,F8 | F1,F2 | |
| F1,F2,F7,F10 | F7,F10 | F1,F2 | |
| F1,F6,F3,F4 | F6 | F1,F3,F4 | |
| F1,F6,F3,F8 | F6,F8 | F1,F3 | |
| F1,F6,F3,F10 | F6,F10 | F1,F3 | |
| F1,F6,F7,F4 | F6,F7 | F1,F4 | |
| F1,F6,F7,F8 | F6,F7,F8 | F1 | |
| F1,F6,F7,F10 | F6,F7,F10 | F1 | |
| F1,F9,F3,F4 | F9 | F1,F3,F4 | |
| F1,F9,F3,F8 | F9,F8 | F1,F3 | |
| F1,F9,F3,F10 | F9,F10 | F1,F3 | |
| F1,F9,F7,F4 | F9,F7 | F1,F4 | |
| F1,F9,F7,F8 | F9,F7,F8 | F1 | |
| F1,F9,F7,F10 | F9,F7,F10 | F1 | |
| F5,F2,F3,F4 | F5 | F2,F3,F4 | |
| F5,F2,F3,F8 | F5,F8 | F2,F3 | |
| F5,F2,F3,F10 | F5,F10 | F2,F3 | |
| F5,F2,F7,F4 | F5,F7 | F2,F4 | |
| F5,F2,F7,F8 | F5,F7,F8 | F2 | |
| F5,F2,F7,F10 | F5,F7,F10 | F2 | |
| F5,F6,F3,F4 | F5,F6 | F3,F4 | |
| F5,F6,F3,F8 | F5,F6,F8 | F3 | |
| F5,F6,F3,F10 | F5,F6,F10 | F3 | |
| F5,F6,F7,F4 | F5,F6,F7 | F4 | |
| F5,F6,F7,F8 | F5,F6,F7,F8 | ||
| F5,F6,F7,F10 | F5,F6,F7,F10 | ||
| F5,F9,F3,F4 | F5,F9 | F3,F4 | |
| F5,F9,F3,F8 | F5,F9,F8 | F3 | |
| F5,F9,F3,F10 | F5,F9,F10 | F3 | |
| F5,F9,F7,F4 | F5,F9,F7 | F4 | |
| F5,F9,F7,F8 | F5,F9,F7,F8 | ||
| F5,F9,F7,F10 | F5,F9,F7,F10 |
P.S. I posted a reduced version of this question on stackoverflow (with no answers), but I think this is a math problem.
Edit: Just to treat the problem as a math one, we can consider it like finding the correct use of the moltiplication of 2 math operators in the 3rd and 4th column, which meaning can be explained by the following table:
| 3rd column: operator GROUPING SETS(()) |
4rd column: operator GROUPING SETS (CUBE()) |
Equivalent: 2^3 = 8 tuples (3 = number of elements of the CUBE operator in the 4rd column). Note that 3rd column elements are always present |
|---|---|---|
| F1,F2 | F4,F5,F6 | (F1,F2),(F1,F2,F4), (F1,F2,F5), (F1,F2,F6), (F1,F2,F4,F5), (F1,F2,F4,F6), (F1,F2,F5,F6), (F1,F2,F4,F5,F6) |
So that, considering another example (i.e the 2nd example mentioned above), we have:
| Produced sets (note several incremental duplicates) |
Using operators in 3rd and 4th column |
Produced sets (note there are no duplicates). Order in a set is not important, (a,b)=(b,a) |
|
|---|---|---|---|
| CUBE(F3,F5,F7) | F3,F5,F7,(F3,F5),(F3,F7),(F5,F7),(F3,F5,F7),(), | GROUPING SETS(CUBE(F3,F5,F7)) | F3,F5,F7,(F3,F5),(F3,F7),(F5,F7),(F3,F5,F7),(), |
| CUBE(F3,F5,F8) | F3,F5,F8,(F3,F5),(F3,F8),(F5,F8),(F3,F5,F8),(), | GROUPING SETS(F8), GROUPING SETS(CUBE(F3,F5)) | F8,(F3,F8),(F5,F8),(F3,F5,F8) |
| CUBE(F3,F6,F7) | F3,F6,F7,(F3,F6),(F3,F7),(F6,F7),(F3,F6,F7),(), | GROUPING SETS(F6), GROUPING SETS(CUBE(F3,F7)) | F6,(F3,F6),(F6,F7),(F3,F6,F7) |
| CUBE(F3,F6,F8) | F3,F6,F8,(F3,F6),(F3,F8),(F6,F8),(F3,F6,F8),(), | GROUPING SETS(F6,F8), GROUPING SETS(CUBE(F3)) | (F6,F8),(F3,F6,F8) |
| CUBE(F4,F5,F7) | F4,F5,F7,(F4,F5),(F4,F7),(F5,F7),(F4,F5,F7),(), | GROUPING SETS(F4), GROUPING SETS(CUBE(F5,F7)) | F4,(F4,F5),(F4,F7),(F4,F5,F7) |
| CUBE(F4,F5,F8) | F4,F5,F8,(F4,F5),(F4,F8),(F5,F8),(F4,F5,F8),(), | GROUPING SETS(F4,F8), GROUPING SETS(CUBE(F5)) | (F4,F8),(F4,F5,F8) |
| CUBE(F4,F6,F7) | F4,F6,F7,(F4,F6),(F4,F7),(F6,F7),(F4,F6,F7),(), | GROUPING SETS(F4,F6), GROUPING SETS(CUBE(F7)) | (F4,F6),(F4,F6,F7) |
| CUBE(F4,F6,F8) | F4,F6,F8,(F4,F6),(F4,F8),(F6,F8),(F4,F6,F8),(), | GROUPING SETS(F4,F6,F8) | (F4,F6,F8) |
At the end we have 27 distinct sets and we produce the same distinct sets incrementing row by row.
