jesusjsc

Oracle多维分析函数
# Oracle多维分析函数 --- ## ROLLUP * GROUP BY ROLLUP (...
扫描右侧二维码阅读全文
27
2019/04

Oracle多维分析函数

Oracle多维分析函数


ROLLUP

  • GROUP BY ROLLUP (A, B, C)

    (a, b, c)
    (a, b)
    (a)
    ()
  • GROUP BY ROLLUP((A, B), C)

    (a, b, c)
    (a, b)
    ()

CUBE

  • GROUP BY CUBE(A, B, C)

    (a, b, c)
    (a, b)
    (a, c)
    (b, c)
    (a)
    (b)
    (c)
    ()
  • GROUP BY CUBE((A, B), C)

    (a, b, c)
    (a, b)
    (c)
    ()

GROUPING SETS

  • GROUP BY GROUPING SETS(A, B)

    (a)
    (b)
  • GROUP BY GROUPING SETS(A, B), GROUPING SETS(C, D)

    (a, c)
    (a, d)
    (b, c)
    (b, d)

GROUPING FUNCTION

  • 一个例子看懂GROUP_ID,GROUPING,GROUPING_ID
SELECT
    FACT_1_ID,
    FACT_2_ID,
    sum(SALES_VALUE) v,
    GROUP_ID() GROUP_ID,
    GROUPING(FACT_1_ID) F1G,
    GROUPING(FACT_2_ID) F2G,
    GROUPING_ID(FACT_1_ID, FACT_2_ID) GROUPING_ID
FROM DIMENSION_TAB
GROUP BY GROUPING SETS (FACT_1_ID, FACT_2_ID, FACT_1_ID, CUBE(FACT_1_ID, FACT_2_ID))

结果如下:

其中,

GROUP_ID()用来解决区分重复行的问题;

GROUPING(COL1)用来判断COL1列是否是NULL(理解成all),如果是则为1,否则为0;

GROUPING_ID(COL1, COL2)用来判断多列COL1,COL2中为NULL的汇总情况,以二进制转为十进制的数字来表示

FACT_1_IDFACT_2_IDVGROUP_IDF1GF2GGROUPING_ID
114946.970000
125051.730000
134966.420000
145136.890000
155158.920000
214288.290000
224399.20000
235114.920000
245049.530000
254570.820000
1NULL25260.930011
2NULL23422.760011
1NULL25260.932011
2NULL23422.762011
1NULL25260.931011
2NULL23422.761011
NULL19235.260102
NULL29450.930102
NULL310081.340102
NULL410186.420102
NULL59729.740102
NULL19235.261102
NULL29450.931102
NULL310081.341102
NULL410186.421102
NULL59729.741102
NULLNULL48683.690113

参考

https://oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets#grouping_sets

Last modification:May 27th, 2019 at 06:02 pm
If you think my article is useful to you, please feel free to appreciate

Leave a Comment