SQL sever 基础学习8 （高级处理）

一.窗口函数

<窗口函数> over ([partition by <列清单>]
            order by <排序用清单>)

注：窗口函数大体可以分为两种

1. 能够作为窗口函数的聚合函数（sum, avg, count, max, min ）

2.rank, dense_rank, row_number 等专用窗口函数

1.rank函数

--升序例
select product_name, product_type, sale_price, 
        rank () over (partition by product_type
                            order by sale_price asc) as ranking
    from product;
--降序例
select product_name, product_type, sale_price, 
        rank () over (partition by product_type
                            order by sale_price desc) as ranking
    from product;

注：partition by能够设定排序的对象范围， order by能够按照那一列，何种顺序进行排序

注2：通过partition by分组后的记录集合称为窗口，也是表示范围

由于partition by可以省略，当省略后再排序发现结果不在分组，或者可以说整个结果就是一组按照升序或降序排序的结果。

2.专用窗口函数的种类

函数	描述
rank	计算排序时，如果遇到相同位次的记录，会跳过之后的位次，如有三条记录并列，则排序表示为1，2，2，2，5
dense_rank	计算排序时，如果遇到相同位次的记录，也不会跳过之后的为此，如1，2，2，2，3
row_number	赋予唯一的连续位次，如1，2，3，4，5

注：窗口函数不能在where 或者group by子句中使用，且窗口函数只能在select子句中使用

3.作为窗口函数使用的聚合函数

注：聚合开窗函数只能使用PARTITION BY子句或都不带任何语句，ORDER BY不能与聚合开窗函数一同使用。

<窗口函数> over ([partition by <列清单>])
--例
select product_id, product_name, sale_price, 
        sum (sale_price) over (partition by product_type) as current_sum
    from product;

二. GROUPING 运算符

1.rollup——同时得到合计与小计

--例1
select '合计' as product_type, sum(sale_price) 
    from product
union all
select product_type, sum(sale_price)
    from product
group by product_type;
--例2
select product_type, sum(sale_price) as sum_price
    from product
group by rollup(product_type);
--例3：在合计的行中插入'合计' 的字符串
select case when grouping(product_type) =1
            then '商品种类 合计'
            else product_type end as product_type,
        case when grouping(regist_date) =1
            then '登记日期 合计'
            else cast(regist_date as char(20)) end as regist_date,
        sum(sale_price) as sum_price
    from product
    group by rollup(product_type, regist_date);

以此计算出不同聚合键组合的汇总结果

又如：

select peoduct_type, regist_date, SUM(sale_price) as sum_price
    from product
    group by rollup(peoduct_type, regist_date);

结果中会出现各个分组的总计，分组内的小计和分组三个模块。

2.使用grouping函数分辨null值

grouping函数在其参数列的值为null时返回1，其他值返回0；

select grouping(product_type) as product_type,
            grouping(regist_date) as regist_date, sum(sale_price) as sum_price
    from product
    group by rollup(product_type, regist_date);

3.cube

select case when grouping(product_type) =1
            then '商品种类 合计'
            else product_type end as product_type,
        case when grouping(regist_date) =1
            then '登记日期 小计'
            else cast(regist_date as char(20)) end as regist_date,
        sum(sale_price) as sum_price
    from product
    group by cube(product_type, regist_date);

cube就是将group by子句中的聚合键的所有可能组合汇总到一个结果中，即商品种类合计再与各个登记日期小计进行组合追加结果

4.grouping sets ：从rollup与cube的结果中取出部分记录。

也即去除聚合键各自的结果而去除使用两个聚合见得到的结果

select case when grouping(product_type) =1
            then '商品种类 合计'
            else product_type end as product_type,
        case when grouping(regist_date) =1
            then '登记日期 小计'
            else cast(regist_date as char(20)) end as regist_date,
        sum(sale_price) as sum_price
    from product
    group by  grouping sets(product_type, regist_date);