开窗函数

orderitems表：

 

 

-- 查询订单号，次序，价格，和以订单号为分区 最大、最小、平均、和 价格
SELECT
	order_num,
	order_item,
	item_price,
	MAX (item_price) OVER (PARTITION BY order_num) AS maxx,
	MIN (item_price) OVER (PARTITION BY order_num) AS minn,
	AVG (item_price) OVER (PARTITION BY order_num) AS avgg,
	SUM (item_price) OVER (PARTITION BY order_num) AS summ
FROM
	orderitems

 

 

-- 查询每组订单的价格和
SELECT
	order_num,
	SUM (item_price) AS summ,
	MAX (item_price) AS maxx,
	MIN (item_price) AS minn,
	AVG (item_price) AS avgg

FROM
	orderitems AS avgg
GROUP BY
	order_num

-- 以订单号为分区 按照价格进行排序  rankk为排名
SELECT
	order_num,
	order_item,
	item_price,
	RANK () OVER (
		PARTITION BY order_num
		ORDER BY
			item_price DESC
	) AS rankk
FROM
	orderitems
ORDER BY
	order_num

如果有多个聚合函数，但是分组依据不同，此时只能使用开窗函数。
而GROUP BY要求聚合函数的分组依据一致。

 

--  排列每个价格在表中的序号 降序 数字连续 加上PARTITION BY order_num 就是先对编号进行分区，再排
SELECT
	order_num,
	item_price,
	ROW_NUMBER() OVER(PARTITION BY order_num ORDER BY item_price DESC) AS aaa
FROM	
	orderitems