SQL高级聚集特性

Markdown

SQLyog Ultimate 64

Authors

6s

参考 大学数据库university

排名（序号函数）

序号函数： ROW_NUMBER()、 RANK()、 DENSE_RANK()， 可以用来实现分组排序， 并添加序号

rank（）

-- Examp_1: 对每个系的教师按照薪资排序， 并给出排名；
SELECT id, dept_name, salary,
rank() over(PARTITION BY dept_name
ORDER BY salary DESC) AS 'Rank'
FROM instructor;

-- Examp_2: 对所有的教师按照薪资排序， 并给出排名；
SELECT id, dept_name,salary,
rank() over( ORDER BY salary DESC) AS 'Rank'
FROM instructor;

rank函数对所有在order by属性上相等的元组赋予相同的名次，不连号

dense_rank（）

-- Examp_2: 对所有的教师按照薪资排序， 并给出排名；
SELECT id, dept_name,salary,
dense_rank() over( ORDER BY salary DESC) AS 'Rank'
FROM instructor;

dense_rank函数对所有在order by属性上相等的元组赋予相同的名次，连号

row_number（）

-- Examp_2: 对所有的教师按照薪资排序， 并给出排名；
SELECT id, dept_name,salary,
row_number() over( ORDER BY salary DESC) AS 'Rank'
FROM instructor;

比较instructor原数据：

row_number函数对行进行排序，并且按行在排序顺序中所处位置给每行一个唯一的行号，具有相同排序值得不同行按照非确定的方式来得到不同的行号

percent_rank（）

-- Examp_2: 对所有的教师按照薪资排序， 并给出排名；
SELECT id, dept_name,salary,
percent_rank() over( ORDER BY salary DESC) AS 'Rank'
FROM instructor;

一个元组的percent_rank以分数的形式给出了该元组的排名。

-- Examp_1: 对每个系的教师按照薪资排序， 并给出排名；
SELECT id, dept_name, salary,
percent_rank() over(PARTITION BY dept_name
ORDER BY salary DESC) AS 'Rank'
FROM instructor;

举例：
Biology分区中有两个元组

元组排名	该元组的百分比排名
1	（1-1)/(2-1)=0
2	(2-1)/(2-1)=1

Comp.Sci分区中有三个元组

元组排名	该元组的百分比排名
1	(1-1)/(3-1)=0
2	(2-1)/(3-1)=0.5
3	(3-1)/(3-1)=1

-- Examp_2: 对所有的教师按照薪资排序， 并给出排名；
SELECT id, dept_name,salary,
percent_rank() over( ORDER BY salary DESC) AS 'Rank'
FROM instructor; 

如果分区中有n个元组且某元组的排名为r，则该元组的百分比排名定义为（r-1)/(n-1)(如果该分区中只有一个元组则定义为null)

即
假定对于某分区，n个元组，某元组排名为r, 则

元组排名	该元组的百分比排名
r	（r-1)/(n-1)

cume_dist( )

-- Examp_1: 查询小于等于当前薪资（ salary） 的比例
SELECT id, dept_name, salary,
cume_dist() over(PARTITION BY dept_name
ORDER BY salary DESC) AS 'Rank'
FROM instructor;

-- Examp_2: 对所有的教师按照薪资排序， 并给出排名；
SELECT id, dept_name,salary,
cume_dist() over( ORDER BY salary DESC) AS 'Rank'
FROM instructor; 

cume_dist（）（累积分布的简写）函数对于一个元组的定义是p/n, 其中p是分区中排序值小于或等于该元组排序值的元组数，并且n是分区中的元组数。

举例：
只有一个分区，元组数为15，n=15
所有元组的排序值从上到下依次1 2 3 3 5 6 7 7 9 10 11 12 13 14 15
(跳不跳数不影响，排序值肯定不连号）（结论推得，欢迎补充）

p	p/n
1	1/15 ~0.06666666666
2	2/15~0.13333333333
4	4/15~0.26666666666
4	4/15~0.26666666666
5	5/15~0.33333333333
6	6/15=0.4
8	8/15~0.53333333333
8	8/15~0.53333333333
…	…
15	15/15=1

> 有关排名的基本问题

如何处理多个元组在排序属性上取值相同的情况？

· rank函数对所有在order by属性上相等的元组赋予相同的名次，在排名中产生空挡（1134）
·dense_rank函数对所有在order by属性上相等的元组赋予相同的名次，不在排名中产生空挡（1123）
·row_number函数对行进行排序，每一行有唯一的行号（1234）

窗口函数

window

窗口查询在一定范围内的元组上计算聚集函数。该特性很有用，比如计算一个固定时间区间的聚集值，此时间区间被称为一个窗口。

命令格式

窗口函数名( expr ) over (
partition by …
Order by …
frame_clause
)

参数说明：
· expr 是字段表达式， 有些函数不需要参数；
· over 子句包含三个选项：
· 分区（ partition by）： 用于将数据行拆分成多个分区（ 组），
类似于 group by 分组。 如果省略了 partition by， 所有的数
据作为一个组进行计算；
· 排序（ORDER BY）： 用于指定分区内的排序方式；
· 窗口大小（frame_clause）： 用于在当前分区内指定一个计算窗
口， 也就是一个与当前行相关的数据子集

语法

<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>
rows/range子句<用于定义窗口大小> )

<窗口函数>可以放以下两种函数：
1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
2） 聚合函数，如sum. avg, count, max, min等

聚集函数+分窗

聚集函数（aggregate function）

是以值集（集合或多重集合）为输入并返回单个值的函数。
SQL提供了五个标准的固有聚集函数。
· 平均值：avg
· 最小值：min
· 最大值：max
· 总和：sum
· 计数：count

-- Examp_1: 查询每个教师的姓名， 所属系， 工资， 及所在系的累加工资；
SELECT NAME,dept_name,salary,
SUM(salary) over(PARTITION BY dept_name ORDER BY salary) AS v1
FROM instructor;

** 按系分区，区内按工资排序然后组内累加 **

--  Examp_2: 不使用 order by salary,默认把分组内的所有数据进行 sum操作；
SELECT NAME,dept_name,salary,
SUM(salary) over(PARTITION BY dept_name ) AS v1
FROM instructor;

** 按系分区，没有排名，区内累加**

-- Examp_3: 指定窗口大小；
SELECT id, dept_name,salary,
SUM(salary) over(ORDER BY salary ROWS BETWEEN 2 preceding AND current ROW) AS c1
FROM instructor;

rows or range

格式

[<ROWS or RANGE clause> BETWEEN <Start expr> AND <End expr>]

区分

·ROWS: 表示按照行的范围进行定义框架，根据order by子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关。

边界可取值（Start expr & End expr）	解释
Current Row	当前行
n preceding	前 n 行，n 为数字
unbounded preceding	开头
n following	后n行，n 为数字
unbounded following	结尾

以3为例

SUM(salary) over(ORDER BY salary ROWS BETWEEN 2 preceding AND current ROW) AS c1

按salary排序后，取前两行到当前行数据做和

行数	【sum】
1	NULL
2	NULL+46305.00=46305.00
3	NULL+46305.00+69457.50=115762.50
4	46305.00+69457.50+71772.75=187535.25

·RANGE：表示按照值的范围进行定义框架，根据order by子句排序后，指定当前行对应值的范围取值，行数不固定，只要行值在范围内，对应行都包含在内。适用于对日期、时间、数值排序分组

range取特定日期区间	说明
range interval 7-1 day preceding	最近7天的值
range between interval 1 day preceding and interval 1 day following	前后一天和当天的值

-- 求每个教师的薪资情况及对应+—10000薪资范围的人数
SELECT id, dept_name, salary,
COUNT(*) over(ORDER BY salary RANGE BETWEEN 10000 preceding AND 10000 following) AS '教师人数（~10000）'
FROM instructor;

薪资36305.00~56305.00的教师只有一个

分布函数- cume_dist()

定义

cume_dist（）（累积分布的简写）函数对于一个元组的定义是p/n, 其中p是分区中排序值小于或等于该元组排序值的元组数，并且n是分区中的元组数。

用途

计算：分组内小于、 等于当前 rank 值的行数 / 分组内总行数

例解

-- Examp_1： 查询小于等于当前薪资（ salary） 的比例；
SELECT id, dept_name,salary,
cume_dist() over(ORDER BY salary) AS r1,
cume_dist() over(PARTITION BY dept_name ORDER BY salary) AS r2
FROM instructor;

·按薪资进行排名，计算元组
·按系分区，按薪资排名，计算分区内元组

如 salary=79380.00， 全体教师中， 有 6个教师的工资<=79380.00，0.4*15=6
如salary=95917.50，全体教师中有10人工资<=95917.50，10/15=0.66666666
如salary=101430.00，Comp.Sci系教师工资都不超过101430.00，3/3=1

前后函数-lag 和 lead

用途

返回位于当前行的前 n 行（LAG(expr,n)）或后 n 行（LEAD(expr,n)）
的 expr 的值

例解

-- Examp_1： 查询前 1 名教师的工资和当前教师工资的差值；
SELECT id, dept_name, salary,
lag(salary,1) over(ORDER BY salary) AS r1
FROM instructor;

按薪资排序

-- Examp_2： 查询后 1 名教师的工资和当前教师工资的差值；
SELECT id, dept_name,salary,
lead(salary,1) over(ORDER BY salary) AS r1
FROM instructor;

上卷

上卷： with rollup
group by + with rollup： 表示在进行分组统计的基础上， 再次对每一小
组进行汇总统计。
————————————————————————————————-未完待续…