【PgSQL】- OVER(Partition By ... Order By ...)

写在前面

  此篇文章会通过一个案例，说明一下 PgSQL的OVER(Partition By … Order By …)的功能及用法。

---

---

一 、基本概念

Partition By是分组但不聚合。
Order By是排序。

---

二、具体步骤

1.环境说明

名称	说明
数据库版本	PostgreSQL 12.3

2.创建表/初试化数据

CREATE TABLE a_test
(
  a CHARACTER VARYING(255),
  b CHARACTER VARYING(255)
);

INSERT INTO a_test
VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 4),
(3, 3),
(5, 5),
(2, 2),
(3, 2);

3.查看表数据

SELECT * FROM a_test;

4.OVER(Partition By … Order By …)

SELECT 
	a,
	b,
	COUNT(1) OVER(PARTITION BY a ORDER BY a) AS count,
	ROW_NUMBER() OVER(PARTITION BY a ORDER BY a) AS rownum
FROM
	a_test;

---

三、总结

COUNT(1) OVER(PARTITION BY a ORDER BY a) AS count，是按a进行分组且组内按a进行升序，统计组内记录的条数。

ROW_NUMBER() OVER(PARTITION BY a ORDER BY a) AS rownum，是按a进行分组且组内按a进行升序，返回组内行编号。

---

写在后面

  如果本文内容对您有价值或者有启发的话，欢迎点赞、关注、评论和转发。您的反馈和陪伴将促进我们共同进步和成长。