分区函数partition by的基本用法【转载】

河外星系

本章将和大家分享分区函数partition by的基本用法（此处以MySQL为例）。废话不多说，下面我们直接进入主题。
一、建表语句

1. -- 创建商品表
2. CREATE TABLE commodity (
3.     id int NOT NULL PRIMARY KEY COMMENT '主键',
4.     position VARCHAR(50) COMMENT '位置(商品放置的货架)',
5.     type VARCHAR(50) COMMENT '类型',
6.     price DECIMAL(10, 2) COMMENT '价格'
7. ) COMMENT='商品表';
9. -- 初始化数据
10. INSERT INTO commodity VALUES (1, '1-001', '苹果', 8.00);
11. INSERT INTO commodity VALUES (2, '2-002', '苹果', 10.00);
12. INSERT INTO commodity VALUES (3, '3-003', '苹果', 12.00);
13. INSERT INTO commodity VALUES (6, '1-001', '橘子', 5.00);
14. INSERT INTO commodity VALUES (7, '1-001', '橙子', 6.00);
15. INSERT INTO commodity VALUES (8, '3-003', '橙子', 8.00);
16. INSERT INTO commodity VALUES (10, '2-002', '菠萝', 10.00);
17. INSERT INTO commodity VALUES (9, '2-002', '香蕉', 5.00);
18. INSERT INTO commodity VALUES (4, '1-001', '苹果', 12.00);
19. INSERT INTO commodity VALUES (5, '1-001', '香蕉', 5.00);

复制代码

二、窗口函数

1、partition by窗口函数 和 group by分组的区别

partition by关键字是分析性函数的一部分，它和聚合函数（如group by）不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录。
partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组。
partition by与group by不同之处在于前者返回的是分组里的每一条数据，并且可以对分组数据进行排序操作。后者只能返回聚合之后的组的数据统计值的记录。
partition by相比较于group by，能够在保留全部数据的基础上，只对其中某些字段做分组排序（类似excel中的操作），而group by则只保留参与分组的字段和聚合函数的结果；简单来说窗口函数对部分数据进行排序、计算等操作，group by对一组值进行聚合，即窗口函数是每一行都会保留，group by是从多行浓缩为少数行。
2、窗口函数基本语法

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

复制代码

3、窗口函数

1. 专用窗口函数：rank(), dense_rank(), row_number()
2. 聚合函数：sum(), max(), min(), count(), avg() 等

复制代码

三、窗口函数的使用

1、over函数的写法

1. -- 先对 type 中相同的进行分区，在 type 中相同的情况下对 price 进行排序
2. over(partition by type order by price desc)

复制代码

2、专用窗口函数 rank() 和 row_number() 以及 dense_rank()

1. SELECT *,rank() over(partition by type order by price desc) as  mm from commodity;
2. SELECT *,row_number() over(partition by type order by price desc) as  mm from commodity;
3. SELECT *,dense_rank() over(partition by type order by price desc) as  mm from commodity;

复制代码

从以上结果来看：
rank()函数：如果存在并列名次的行，会占用下一个名次的位置，比如苹果的组内排名【1,2,3,4】，但是由于有两个是并列的，所以显示的排名是【1,1,3,4】，其中 2 的位置还是被占用了。 
row_number()函数：不考虑并列的情况，此函数即使遇到了 price 相同的情况，还是会默认排出一个先后来。 
dense_rank()函数：如果存在并列名次的行，不会占用下一个名次的位置，例如：图片的最后显示的是【1,1,2,3】。  
3、聚合函数作为窗口函数

1）sum()

1. SELECT *,sum(price) over(partition by type order by price) as sum  from commodity;

复制代码

在进行求和的时候是这样的，当前行的 sum 值是组内当前行与其组内当前行之前所有行的和，例如红色圈出来的数据，橙子第一行是 6 ，第二行是两行的和 6 +8 = 14，同样的红色圈出来的 苹果的也是同样的道理。需要注意的是当在排序出现相同的时候，同样的都是 12 或者 同样的都是 5 无法进行区分，所以在计算的时候会把两个或多个相同值的都加进去，这样也就是 橙色圈出来的部分了 从 8 => 8+10 = 18 => 18+12+12 = 42 =>18+12+12 = 42 ，大概就是这个意思，下文会告诉大家如何解决这种问题 (rows between unbounded preceding and current row)。
我们来多看几种排序的结果是否符合上面的描述：

1. -- order by type
2. SELECT *,sum(price) over(partition by type order by type) as sum  from commodity;
3. -- order by position
4. SELECT *,sum(price) over(partition by type order by position) as sum  from commodity;
5. -- order by id
6. SELECT *,sum(price) over(partition by type order by id) as sum  from commodity;

复制代码

partition by 后面不指定 order by，如下所示：

1. SELECT *,sum(price) over(partition by type) as sum,avg(price) over(partition by type) as avg  from commodity;

复制代码

2）max(), min(), avg(), count()

1. SELECT *,sum(price) over(partition by type order by price) as sum,
2.          max(price) over(partition by type order by price) as max,
3.          min(price) over(partition by type order by price) as min,
4.          avg(price) over(partition by type order by price) as avg,
5.          count(price) over(partition by type order by price) as count
6. from commodity;

复制代码

我们可以看到，不管是sum(), avg() 还是min(), max(), count() 他们在窗口函数中，都是对自身记录以及位于自身记录之前的数据进行聚合，求和、求平均、最小值、最大值等。所以，聚合函数作为窗口函数的时候可以在每一行的数据里直观的看到，截止到本行数据统计数据是多少，也可以看出每一行数据对整体的影响。（注意：数据重复的除外，有点特殊）也就是说 sum(), max(), min(), avg(), count() 都是类似的。
4、rows 与 range

rows是物理窗口，即根据order by子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）。
range是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内。 
通俗点来讲就是说：rows 取的时候是取当前行的前几行以及后几行，包括当前行在内一起进行计算的；而 range 不受行的限制，他跟当前行的值有关，当前行的值减去几，加上几，这个范围内的值都是要进行计算的数据，具体例子如下所示：

1. -- 在当前行往前1行，往后2行，一共4行范围内进行计算
2. rows between 1 preceding and 2 following
4. -- 在当前行的数值往前1个数值，往后2个数值，进行计算，范围不一定，因为可能会出现重复值
5. range between 1 preceding and 2 following

复制代码

1. -- rows
2. SELECT *,sum(price) over(partition by type order by price rows between 1 preceding and 2 following) as sum from commodity where type = '苹果';
3. -- 第一行 8 ，前一行没有，后两行是10、12 => 8 + 10 + 12 = 30
4. -- 第二行是 10 ，前一行8，后两行是12、12 => 8 + 10 + 12 + 12 = 42
5. -- 第三行是 12 ，前一行10，后两行只有一个12 => 10 + 12 + 12 = 34
6. -- 第四行是 12 ，前一行12，后两行没有 => 12 + 12 = 24

复制代码

[code]-- range SELECT *,sum(price) over(partition by type order by price range between 1 preceding and 2 following) as sum from commodity where type = '苹果';-- 第一行 8 ，往前一个数值 8-1 = 7，往后两个数值 8+2 = 10 --> 7  9  11  11