Oracle中的Over()函数与ROLLUP的用法

Oracle中的Over()函数与ROLLUP的用法

Over()函数：

①先是简单的：用来计算两数相除之中分母的计算

SELECT
    supplierName as supplierName,
    TO_CHAR(ROUND(DECODE(SUM(amount) OVER(), 0,0, (amount / SUM(amount) OVER()))*100,2),'FM990.00') || '%' as percentage
FROM
		XXX
WHERE
		XXX

此处OVER()函数的作用意在优先算出分母SUM(amount)的合计，这样可以省去一层嵌套关系，在一层内直接得出结果。

OVER()中还可加“partition by”，其中“partition by”官方点的说法叫做"分区"，其实就是统计的范围条件。

注：此sql语句结尾处不要加“order by”，应为使用的分析函数的(partition by XXX order by sal)里已经有排序的语句了，如果再在句尾添加排序子句，一致倒罢了，不一致，结果就令人费解了。

②rank()结合over()

rank函数是分级函数，这个函数必须与 over 函数使用，否则会报一个"缺少窗口函数的错"。我测试sql如下：

select a.empno as 员工编号,

　　a.sal as 薪资,

　　a.job as 岗位,

　　rank() OVER(partition by a.job ORDER BY a.sal desc) as 岗位薪资等级

　　from scott.emp a;

ROLLUP：

ROLLUP分组函数可以理解为Group By分组函数封装后的精简用法，这里直接给出ROLLUP的求合计行代码。

SELECT
        DECODE( grouping ( city ), 1, '12', to_char(MAX( nums )) ) id,
        DECODE( grouping ( city ), 1, '合计', city ) city,
        SUM( roomNight ) roomNight,
        DECODE( grouping ( city ), 1, '100', TO_CHAR(MAX( roomNightPercent ),'fm990.00') ) || '%' roomNightPercent,
        TO_CHAR(SUM( price ),'FM9999999990.00') price,
        DECODE( grouping ( city ), 1, '100', TO_CHAR(MAX( pricePercent ),'fm990.00') ) || '%' salePercent,
        DECODE(SUM( refundRoomNights ),0,0,-SUM( refundRoomNights )) refundRoomNight,
        TO_CHAR(DECODE(SUM( refundAmount ),0,0,-SUM( refundAmount )),'FM9999999990.00') refundAmount,
        DECODE( grouping ( city ), 1, '100', TO_CHAR(MAX( refundAmountPercent ),'fm990.00') ) || '%' refundAmountPercent,
        TO_CHAR(ROUND( DECODE( SUM( roomNight ), 0, 0, SUM( price ) / SUM( roomNight ) ), 2 ),'FM990.00') avgPrice
        FROM
        (
        SELECT
        MAX( num ) nums,
        DECODE( MAX( num ), 11, '其他', city ) city,
        SUM( roomNight ) roomNight,
        DECODE(SUM( roomNight ), 0, 0,TO_CHAR( ROUND( SUM( nvl(roomNight,0) ) / SUM( SUM( roomNight ) ) over ( ) * 100, 2 ), 'Fm990.00' )) AS roomNightPercent,
        SUM( price ) price,
        DECODE(SUM( price ), 0, 0,TO_CHAR( ROUND( SUM( price ) / SUM( SUM( price ) ) over ( ) * 100, 2 ), 'Fm990.00' )) AS pricePercent,
        SUM( refundRoomNights ) refundRoomNights,
        SUM( refundAmount ) refundAmount,
        DECODE(SUM( refundAmount ), 0, 0,TO_CHAR( ROUND( SUM( refundAmount ) / SUM( SUM( refundAmount ) ) over ( ) * 100, 2 ), 'Fm990.00' )) AS refundAmountPercent,
        SUM( avgAmount ) avgAmount
        FROM
        (
        SELECT
        CASE
        WHEN ROWNUM
            <= 10 THEN
        to_char( ROWNUM ) ELSE '11'
        END AS num,
        MAX( city ) city,
        SUM( roomNight ) roomNight,
        SUM( price ) price,
        SUM( refundRoomNights ) refundRoomNights,
        SUM( refundAmount ) refundAmount,
        SUM( avgAmount ) avgAmount
        FROM
        (
        SELECT
        city city,
        SUM( roomNight ) roomNight,
        SUM( price ) price,
        SUM( refundRoomNights ) refundRoomNights,
        SUM( refundAmount ) refundAmount,
        SUM( avgAmount ) avgAmount
        FROM
        (
        SELECT
        CITY city,
        SUM( ROOM_NIGHTS ) roomNight,
        SUM( SETTLE_PRICE_TOTAL ) price,
        0 refundRoomNights,
        0 refundAmount,
        0 avgAmount
        FROM
        T_HOTEL_SALE
        WHERE
        SUPPLIER != 'GRCB'
        <if test="endDate != null and endDate != ''">
            AND SUBSTR(BOOK_DATE, 0, 10) BETWEEN #{startDate} AND #{endDate}
        </if>
        <if test="customer != null and customer != ''">
            AND MEMBERNAME = #{customer}
        </if>
        GROUP BY
        CITY UNION ALL
        SELECT
        CITY city,
        0 roomNight,
        0 price,
        SUM( ROOM_NIGHTS ) refundRoomNights,
        SUM( SETTLE_PRICE_TOTAL ) refundAmount,
        0 avgAmount
        FROM
        T_HOTEL_SALE
        WHERE
        SUPPLIER != 'GRCB'
        AND STATUS = '9'
        <if test="endDate != null and endDate != ''">
            AND SUBSTR(BOOK_DATE, 0, 10) BETWEEN #{startDate} AND #{endDate}
        </if>
        <if test="customer != null and customer != ''">
            AND MEMBERNAME = #{customer}
        </if>
        GROUP BY
        CITY
        )
        GROUP BY
        city
        ORDER BY
        price DESC
        )
        GROUP BY
        CASE
        WHEN ROWNUM &lt;= 10 THEN
        to_char( ROWNUM ) ELSE '11'
        END
        ORDER BY
        TO_NUMBER( num )
        )
        GROUP BY
        city
        ORDER BY
        TO_NUMBER( nums )
        )
        GROUP BY
        Rollup ( city, nums )
        HAVING
        grouping ( city ) = 1
        OR nums IS NOT NULL
        ORDER BY
        TO_NUMBER( nums )

这里sql是以 city, nums（城市，行号）来进行分组再求和的，

需要注意的是如果sum()搭配Rollup使用时要注意这个条件：

HAVING
	grouping ( city ) = 1
  OR nums IS NOT NULL

否则会导致统计数据翻倍

SELECT
	DECODE( grouping ( city ), 1, '合计', city ) city,
......

SELECT中再使用DECODE函数进行筛选赋值

grouping ( city ) 如果显示“1”表示GROUPING函数对应的列是由于ROLLUP函数所产生的空值对应的信息，即对此列进行汇总计算后的结果。
如果显示“0”表示此行对应的这列参未与ROLLUP函数分组汇总活动。

ROLLUP在数据统计和报表生成过程中带来极大的便利，而且效率比起来Group By + Union组合方法效率高得多。这也体现了Oracle在SQL统计分析上人性化、自动化、高效率的特点。