partition 和partition by

partition

新建分区

partition by list (sex) --根据性别分区
(
partition l1 values('男')，
partition l2 values('女')，
partition l3 values(default)
);

查询，修改，删除分区

--查询分区
select * from person partition (p2);
select * from person1 partition (l2);
select * from person2 partition (SYS_P548);
select * from person3 partition (h2);
select * from person4 subpartition (p7_sp2);
select * from person5 subpartition (p11_sp5);

--删除表分区
--alter table 表名 drop partition 分区名;
alter table person drop partition p2;

--增加分区(ADD)： 
1、如果list分区有default或者range分区有maxvalue，则不能进行add partition操作
2、add partition的值必须大于所有分区的值。
3、若列表分区表中已经建立了default分区，则不能再增加分区。只能先删除default分区，再增加新分区。删除default分区前可以用交换分区进行备份。
alter table p_list add partition p_3 values('20121113');List分区也需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像range或者hash分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。

在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。

partition by

partition by的用法，需要结合over函数使用，放到from之前，over()函数写法over(partition by expr2 order by expr3)，根据expr2对结果进行分区，在各分区内按照expr3进行排序；

over函数不能单独使用，需要与row_number()，rank()和dense_rank，lag()和lead(),sum()等配合使用。

select *,rank()(partition by code order by score desc) from table t

分区partiton by 与 group by的区别

group by会将结果集按照指定字段进行聚合，结果集会缩减，在统计部门人数，平均工资时会用到；
partition by会对结果集按照指定字段分层排列，结果集不会缩减，如将公司所有人按照部门进行分区，会发现结果集中同一部门的人会连续排列。

partition by关键字是分析性函数的一部分，它和聚合函数（如group by）不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录，

partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组。

partition by 与group by不同之处在于前者返回的是分组里的每一条数据，并且可以对分组数据进行排序操作。后者只能返回聚合之后的组的数据统计值的记录。

dense_rank()，rank(),row_number()

rank():跳跃排序，有两个第一名时接下来是第三名

dense_rank():非跳跃排序，有两个第一名时接下来是第二名。

row_number():相同的数仅记一次