mysql group by cube_GROUPING函数来排除group by cube数据问题

起因：group by cube的结果中出现两条维度值一样，汇总值不一样的数据。

SELECT * FROM(

SELECT NVL(T.REG,'0000') AS REGION_ID,

NVL(T.PROJECT_TYPE, '0000') AS PROJECT_TYPE_ID,

NVL(T.VOLTAGE_LEVEL, '0000') AS VOL_CLASSIFY_ID,

NVL(T.CURRENT_NODE, '0000') AS NODE_ID,

NVL(T.TOTAL_CONTROL_INFO, '0000') AS TOTAL_CONTROL_TYPE_ID,

SUM(T.CN) AS CN,

SUM(T.AMT) AMT

FROM TMP T

WHERE 1 = 1

GROUP BY CUBE(T.REG,

T.PROJECT_TYPE,

T.VOLTAGE_LEVEL,

T.CURRENT_NODE,

T.TOTAL_CONTROL_INFO)) T

WHERE 1=1

AND T.REGION_ID='0000'

AND T.PROJECT_TYPE_ID='0000'

AND T.VOL_CLASSIFY_ID='0000'

AND T.TOTAL_CONTROL_TYPE_ID='75'

AND T.NODE_ID='9';

使用GROUPING函数排除原因：

SELECT * FROM(

SELECT NVL(T.REG,'0000') AS REGION_ID,

NVL(T.PROJECT_TYPE, '0000') AS PROJECT_TYPE_ID,

NVL(T.VOLTAGE_LEVEL, '0000') AS VOL_CLASSIFY_ID,

NVL(T.CURRENT_NODE, '0000') AS NODE_ID,

NVL(T.TOTAL_CONTROL_INFO, '0000') AS TOTAL_CONTROL_TYPE_ID,

grouping(REG) AS REG,

grouping(PROJECT_TYPE) AS PROJECT_TYPE,

grouping(VOLTAGE_LEVEL) AS VOLTAGE_LEVEL,

grouping(TOTAL_CONTROL_INFO) AS TOTAL_CONTROL_INFO,

grouping(TOTAL_CONTROL_INFO) AS TOTAL_CONTROL_INFO,

SUM(T.CN) AS CN,

SUM(T.AMT) AMT

FROM TMP T

WHERE 1 = 1

GROUP BY CUBE(T.REG,

T.PROJECT_TYPE,

T.VOLTAGE_LEVEL,

T.CURRENT_NODE,

T.TOTAL_CONTROL_INFO)) T

WHERE 1=1

AND T.REGION_ID='0000'

AND T.PROJECT_TYPE_ID='0000'

AND T.VOL_CLASSIFY_ID='0000'

AND T.TOTAL_CONTROL_TYPE_ID='75'

AND T.NODE_ID='9';

通过GROUPING函数可以看出，一条数据使用了reg来进行汇总，一条则没有使用，这个时候就可以看出来，源表中存在reg为0000的脏数据。

解释：

如上结果中GROUPING函数返回值“0”和“1”的含义。

如果显示“1”表示CUBE函数对应的列(例如REG字段)是由于CUBE函数所产生的空值对应的信息，即对此列进行汇总计算后的结果。

如果显示“0”表示此行对应的这列参未与分组汇总活动。