目录
- 窗口和开窗函数
- 表旋转
1.窗口和开窗函数
- 窗口
用户指定的一组行(分区)。 - OVER子句
OVER ( [PARTITION BY value_exp, ...[ n ] ] <ORDER BY_Clause> )
- 开窗函数
1)排名开窗函数,ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()
2)聚合开窗函数AVG()、SUM()等
IF DB_ID('tt') IS NOT NULL
DROP DATABASE tt
GO
CREATE DATABASE tt
GO
CREATE TABLE tt.dbo.voc
(
item varchar(20),
color varchar(20),
quantity int
)
INSERT INTO tt.dbo.voc
VALUES
('Chair', 'blue', 1), ('Chair', 'blue', 2),
('Chair', 'red', 3), ('yizi', 'red', 1),
('yizi', 'blue', 6)
GO
-- 先分区,按分区排序,再按分区内行按order by指定列排序
SELECT *
, ROW_NUMBER() OVER(PARTITION BY color ORDER BY color) AS row_num -- 编号
, RANK() OVER(ORDER BY quantity) AS rank_ -- 排名
, DENSE_RANK() OVER(ORDER BY quantity) AS dense_rank_ -- 排名
, NTILE(3) OVER(PARTITION BY color ORDER BY color) AS ntile_3 -- 分组,均分
FROM tt.dbo.voc
-- + CASE_Clause
SELECT *
, CASE NTILE(2) OVER(ORDER BY color DESC)
WHEN 1 THEN '组1'
WHEN 2 THEN '组2'
END AS '分组'
FROM tt.dbo.voc
-- ntile(2) 均分
delete from tt.dbo.voc
where item='yizi'
AND color='blue'
GO
insert into tt.dbo.voc
values
('yizi', 'yellow', 1), ('yizi', 'yellow', 1)
GO
select *
, NTILE(2) OVER(PARTITION BY color ORDER BY color) AS ntile_3 -- 分组
from tt.dbo.voc -- 见图,color中元素也被均分
-- 开窗聚合函数
select * from tt.dbo.voc
go
select item
, quantity
, AVG(quantity) OVER(PARTITION BY item) AS class_avg
, quantity - AVG(quantity) OVER(PARTITION BY item) AS class_diff
from tt.dbo.voc
注:利用OVER子句实现多种聚合计算,且语句中存在多个OVER时不影响查询效率。
-- 简化多种聚合计算
select item
, quantity
, AVG(quantity) OVER(PARTITION BY item) AS class_avg
, quantity - AVG(quantity) OVER(PARTITION BY item) AS class_diff
, AVG(quantity) OVER() AS all_avg
, quantity - AVG(quantity) OVER() AS vs_all_diff
, SUM(quantity) OVER() AS all_sum
from tt.dbo.voc
2.表旋转
-
PIVOT
行 -> 列SELECT <非旋转列> [第1个旋转列] AS <别名> ... [第n个旋转列] FROM (<SELECT生成源数据>) AS <源别名> PIVOT ( <聚合函数>(<被聚合列>) FOR [<包含将被转换为列标头的值的列>] IN ([第1个旋转后的列], ... [第n个旋转后的列]) ) AS <旋转表别名> [可选<ORDER BY_Clause>]
select * from tt.dbo.voc go select item , [blue] , [yellow] , [red] from tt.dbo.voc pivot ( sum(quantity) for color in ([blue], [yellow], [red]) ) AS pivot_color
- UNPIVOT
列 -> 行
select item,
[blue], [yellow], [red]
into tt.dbo.pivot_voc
from tt.dbo.voc
pivot
(
sum(quantity)
for color in ([blue], [yellow], [red])
) AS pivot_color
go
select * from tt.dbo.voc
go
select * from tt.dbo.pivot_voc
go
select item, color, quantity
from tt.dbo.pivot_voc
unpivot
(
quantity
for color in ([blue], [red], [yellow])
) AS voc