rank - SQL: Ranking Sections separately of a Rollup over multiple columns -
i try rollup on multiple columns , apply ranking on each stage/section of rollup process. result should following:
| cola | colb | colc | rankingcriteria | ranking | |------|------|------|-----------------|---------| | - | - | - | 10 | 1 | |------|------|------|-----------------|---------| | | - | - | 10 | 1 | | b | - | - | 8 | 2 | |------|------|------|-----------------|---------| | | | - | 9 | 1 | | | b | - | 7 | 2 | | | c | - | 5 | 3 | | | d | - | 2 | 4 | |------|------|------|-----------------|---------| | b | | - | 8 | 1 | | b | c | - | 7 | 2 | | b | b | - | 2 | 3 | |------|------|------|-----------------|---------| | | | x | 7 | 1 | | | | y | 5 | 2 | | | | z | 4 | 3 | |------|------|------|-----------------|---------| | | b | y | 6 | 1 | |------|------|------|-----------------|---------| | | c | w | 10 | 1 | | | c | y | 10 | 1 | | | c | z | 8 | 2 | | | c | x | 6 | 3 | |------|------|------|-----------------|---------| | | d | y | 4 | 1 | |------|------|------|-----------------|---------| | b | | w | 10 | 1 | | b | | x | 8 | 2 | |------|------|------|-----------------|---------| | b | b | y | 6 | 1 | | b | b | z | 5 | 2 | | b | b | w | 4 | 3 | |------|------|------|-----------------|---------| | b | c | x | 6 | 1 | |------|------|------|-----------------|---------|
so can see each grouping set has it's own ranking.
the basic rollup-query simple ranking giving me headaches , running out of ideas on how achieve this.
select cola, colb, colc, rankingcriteria table group rollup(cola, colb, colc)
the problem cannot use normal rank() on (partition ...) because there no partition use that'd work on whole thing.
i think produce want:
select r.*, row_number() on (partition (case when colb null , colc null , cola not null 1 else 0 end), (case when colb null , colc null , cola not null null else end), (case when colb null , colc null , cola not null null else b end) order rankingcriteria desc) seqnum (select cola, colb, colc, rankingcriteria table group rollup(cola, colb, colc) ) r;
the way read logic partitioning , b works second group. why uses 3 case statements.
Comments
Post a Comment