SQL编程学习05-SQL高级处理

一 窗口函数

窗口函数语法

<窗口函数> over (partition by <用于分组的列名>
				    order by <用于排序的列名>)

<窗口函数>的位置可以放置以下两种函数：

1. 专用窗口函数，比如rank，dense_rank，row_number等
2. 聚合函数，如sum，avg，count，max，min等

窗口函数功能

• 同时具有分组（可省略不写，即可不进行分组）和排序的功能
• 不减少原表的行数，所以经常用来在每组内排名

二 WITH ROLLUP

ROLLUP是GROUP BY子句的扩展，在group分组字段的基础上再进行统计数据。 ROLLUP选项允许包含表示小计的额外行，通常称为超级聚合行，以及总计行。

三 练习题

5.1 查询当前商品以及之前商品中的最高价格，作为当前商品的 最大价格

-- 使用with rollup 小计
SELECT regist_date
	   ,sale_price
	   ,SUM(sale_price) AS sum_price 
  FROM product
 GROUP BY regist_date, sale_price WITH ROLLUP
 ORDER BY regist_date IS NULL DESC, regist_date asc;

结果如下：

-- use 窗口函数
SELECT regist_date
	   ,product_name
	   ,sale_price
	   ,sum(sale_price) over (PARTITION by regist_date 
								  ORDER BY regist_date IS NULL DESC,regist_date) 
								  as day_sum_price
	FROM product;

结果如下：

5.2 按照登记日期（regist_date）升序进行排列的各日期的销售单价（sale_price）的总额

将登记日期为NULL 的“运动 T 恤”记录排在第 1 位（也就是将其看作比其他日期都早）

SELECT regist_date
	   ,sale_price
	   ,SUM(sale_price) AS sum_price 
  FROM product
 GROUP BY regist_date, sale_price WITH ROLLUP
 ORDER BY regist_date IS NOT NULL, regist_date;

结果如下：

5.3 思考

PARTITION BY是用来分组，即将数据分成多个窗口。如果不使用这个关键字，则是把整个查询结果作为一个窗口。
例如：

SELECT product_name
       ,product_type
       ,sale_price
       ,RANK() OVER (# PARTITION BY product_type
                         ORDER BY sale_price) AS ranking_part
	   ,RANK() OVER (PARTITION BY product_type
                         ORDER BY sale_price) AS ranking_no_part
  FROM product; 

– 思考二
根据SQL语句的执行顺序和窗口函数的作用（有选择的去某一部分数据进行汇总、计算和排序），窗口函数可以在SELECT 或order by 子句中使用。

执行顺序：FROM 》WHERE》GROUP BY》HAVE》SELECT》ORDER BY

如何在order 子句使用窗口函数呢？
将使用了窗口函数的查询结果看成视图，在其基础上进行查询，order by子句就可以使用窗口函数新增的列名了。