分区函数Partition By的基本用法
1.窗口函数
(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)窗口函数基本语法
<窗口函数> over ( partition by<用于分组的列名> order by <用于排序的列名>)
(3)窗口函数
专用窗口函数: rank(), dense_rank(), row_number()
聚合函数 : sum(), max(), min(), count(), avg() 等
2.窗口函数的使用
2.1 over函数的写法：
over(partition by type order by price desc)
--先对 type 中相同的进行分区，在 type 中相同的情况下对 price 进行排序
2.2 专用窗口函数 rank() 和 row_number() 以及 dense_rank()
SELECT *,rank() over(partition by type order by price desc) as mm from commodity;
SELECT *,row_number() over(partition by type order by price desc) as mm from commodity;
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
2.3 聚合函数作为窗口函数
(1) sum()
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)
我们来多看几种排序的结果是否符合上面的描述:
-- order by type
SELECT *,sum(price) over(partition by type order by type) as sum from commodity;
-- order by position
SELECT *,sum(price) over(partition by type order by position) as sum from commodity;
-- order by id
SELECT *,sum(price) over(partition by type order by id) as sum from commodity;
(2) max(), min(), avg(), count()
SELECT *,sum(price) over(partition by type order by price) as sum,
max(price) over(partition by type order by price) as max,
min(price) over(partition by type order by price) as min,
avg(price) over(partition by type order by price) as avg,
count(price) over(partition by type order by price) as count
from commodity;
我们可以看的到, 不管是sum(), avg() 还是min(), max(), count() 他们在窗口函数中，都是对自身记录以及位于自身记录之前的数据进行聚合，求和、求平均、最小值、最大值等。所以，聚合函数作为窗口函数的时候可以在每一行的数据里直观的看到，截止到本行数据统计数据是多少，也可以看出每一行数据对整体的影响。(注意 : 数据重复的除外,有点特殊)也就是说 sum(), max(), min(), avg(), count() 都是类似的。
2.4 rows 与 range
rows是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）
range是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内
通俗点来讲就是说:rows 取的时候是取当前行的前几行以及后几行,包括当前行在内一起进行计算的;而 range 不受行的限制,他跟当前行的值有关,当前行的值减去几,加上几,这个范围内的值都是要进行计算的数据,具体例子如下所示:
--在当前行往前1行，往后2行，一共4行范围内进行计算
rows between 1 preceding and 2 following
--在当前行的数值往前1个数值，往后2个数值，进行计算，范围不一定，因为可能会出现重复值
range between 1 preceding and 2 following
--rows
SELECT *,sum(price) over(partition by type order by price rows between 1 preceding and 2 following) as sum from commodity where type = '苹果';
第一行 8 ,前一行没有,后两行是 10,12 --> 8 + 10 + 12 = 30
第二行是 10 ,前一行 8,后两行 12,12 --> 8 + 10 + 12 + 12 = 42
第三行是 12 ,前一行 10,后两行 12 --> 10 + 12 + 12 = 34
第四行是 12 ,前一行 12,后两行没有 --> 12 + 12 = 24
--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 <= price <= 10 --> 8 + 10 = 18
第二行 10 ,往前一个数值 10-1 = 9,往后两个数值 10+2 = 12 --> 9 <= price <= 12 --> 10 + 12 + 12 = 34
第三行 12 ,往前一个数值 12-1 = 11,往后两个数值 12+2 = 14 --> 11 <= price <= 14 --> 12 + 12 = 24
第四行 12 ,往前一个数值 12-1 = 11,往后两个数值 12+2 = 14 --> 11 <= price <= 14 --> 12 + 12 = 24
2.5 unbound 和 current row
--在当前行往前1行，往后2行，一共4行范围内进行计算
rows between 1 preceding and 2 following
--在当前行的数值往前1个数值，往后2个数值，进行计算，范围不一定，因为可能会出现重复值
range between 1 preceding and 2 following
between … and … 后面的数字可以随着需求进行替换,当然也可以使用 unbound 和 current row ; 其中 unbounded 表示不做限制，current row 表示当前行
--按照分组内全部行求和，不做任何限制
rows between unbounded preceding and unbounded following
--从分组内排序的起始行到当前行
rows between unbounded preceding and current row
--按照分组内全部行求和，不做任何限制
range between unbounded preceding and unbounded following
--从分组内排序的起始行的值到当前行的值
range between unbounded preceding and current row
--rows between unbounded preceding and unbounded following
SELECT *,sum(price) over(partition by type order by price rows between unbounded preceding and unbounded following) as sum from commodity where type = '苹果';
--rows between unbounded preceding and current row
SELECT *,sum(price) over(partition by type order by price rows between unbounded preceding and current row) as sum from commodity where type = '苹果';
--range between unbounded preceding and unbounded following
SELECT *,sum(price) over(partition by type order by price range between unbounded preceding and unbounded following) as sum from commodity where type = '苹果';
--range between unbounded preceding and current row
SELECT *,sum(price) over(partition by type order by price range between unbounded preceding and current row) as sum from commodity where type = '苹果';
2.6 first_value(), last_valus(), lag(), lead()
first_value(字段) over(partition by … order by …) 求分组后的第一个值
last_value(字段) over(partition by … order by …) 求分组后的最后一个值
SELECT *,first_value(price) over(partition by type order by price) as mm from commodity;
SELECT *,last_value(price) over(partition by type order by price) as mm from commodity;
lag(expresstion,<offset>,<default>) over(partition by … order by …) 取出分组后前n行数据
lead(expresstion,<offset>,<default>) over(partition by … order by …) 取出分组后后n行数据
--取分组后的前两行数据/后两行数据, 默认值设置为 0
SELECT *,lag(price,2,0) over(partition by type order by price) as mm from commodity;
SELECT *,lead(price,2,0) over(partition by type order by price) as mm from commodity;
SELECT *,lag(price,1,0) over(partition by type order by price) as lag,lead(price,1,0) over(partition by type order by price) as lead from commodity;
--第一个参数:要取的字段
--第二个参数:取排序后的第几条记录
--第三个参数:缺省值,如果后面的记录取不到值就默认取值第三个参数的值,注意参数的类型要与第一个参数所取字段的类型一致哦,话默认为空
注:具体的sql输出结果下文放置了建表语句,可以执行一下,自己体验体验!!!
2.7 preceding 和 following
Hive函数, preceding：向前 following：向后，这两个窗口函数不仅可以实现滑窗求和（指定rows范围）或者指定范围内数据求和（指定range范围）,也可以用来计算移动平均值:
SELECT *,sum(price) over(partition by type order by price) as sum,avg(price) over(partition by type order by price) as avg,avg(price) over(partition by type order by price rows 2 preceding) as avg2 from commodity where type = '苹果';
3.参考文献
SQL高级功能：窗口函数、存储过程及经典排名问题、topN问题等
分区函数Partition By的用法
SQL：聚合类窗口函数的preceding和following参数用法
4.建表语句
-- ----------------------------
-- Table structure for commodity
-- ----------------------------
DROP TABLE IF EXISTS "public"."commodity";
CREATE TABLE "public"."commodity" (
"id" varchar(50) COLLATE "pg_catalog"."default" NOT NULL,
"position" varchar(50) COLLATE "pg_catalog"."default",
"type" varchar(50) COLLATE "pg_catalog"."default",
"price" numeric(10,2)
)
;
COMMENT ON COLUMN "public"."commodity"."id" IS '主键';
COMMENT ON COLUMN "public"."commodity"."position" IS '位置(商品放置的货架)';
COMMENT ON COLUMN "public"."commodity"."type" IS '类型';
COMMENT ON COLUMN "public"."commodity"."price" IS '价格';
-- ----------------------------
-- Records of commodity
-- ----------------------------
INSERT INTO "public"."commodity" VALUES ('1', '1-001', '苹果', 8.00);
INSERT INTO "public"."commodity" VALUES ('2', '2-002', '苹果', 10.00);
INSERT INTO "public"."commodity" VALUES ('3', '3-003', '苹果', 12.00);
INSERT INTO "public"."commodity" VALUES ('6', '1-001', '橘子', 5.00);
INSERT INTO "public"."commodity" VALUES ('7', '1-001', '橙子', 6.00);
INSERT INTO "public"."commodity" VALUES ('8', '3-003', '橙子', 8.00);
INSERT INTO "public"."commodity" VALUES ('10', '2-002', '菠萝', 10.00);
INSERT INTO "public"."commodity" VALUES ('9', '2-002', '香蕉', 5.00);
INSERT INTO "public"."commodity" VALUES ('4', '1-001', '苹果', 12.00);
INSERT INTO "public"."commodity" VALUES ('5', '1-001', '香蕉', 5.00);
-- ----------------------------
-- Primary Key structure for table commodity
-- ----------------------------
ALTER TABLE "public"."commodity" ADD CONSTRAINT "commodity_pkey" PRIMARY KEY ("id");
1753
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
