一、模拟数据
CREATE TABLE empsalary ( depname VARCHAR(100), empno INT, salary DECIMAL(10, 2), enroll_date DATE );复制
INSERT INTO empsalary (depname, empno, salary, enroll_date) VALUES ('development', 1, 5200, '2020-02-01'), ('sales', 2, 5000, '2020-02-01'), ('human resources', 3, 3500, '2020-03-01'), ('development', 4, 4800, '2021-01-01'), ('sales', 5, 4800, '2021-02-01'), ('human resources', 6, 3900, '2021-02-01'), ('development', 7, 4200, '2021-03-01'), ('development', 8, 6000, '2022-01-01'), ('sales', 9, 4500, '2022-01-01'), ('development', 10, 5200, '2022-02-01');复制
二、窗口函数
t1=# SELECT depname, empno, salary, t1-# ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC) AS row_number t1-# FROM empsalary; depname | empno | salary | row_number -----------------+-------+---------+------------ development | 8 | 6000.00 | 1 development | 1 | 5200.00 | 2 development | 10 | 5200.00 | 3 development | 4 | 4800.00 | 4 development | 7 | 4200.00 | 5 human resources | 6 | 3900.00 | 1 human resources | 3 | 3500.00 | 2 sales | 2 | 5000.00 | 1 sales | 5 | 4800.00 | 2 sales | 9 | 4500.00 | 3 (10 rows)复制
这个查询会根据部门(depname)对员工进行分组，并在每个部门内根据薪水(salary)降序排序。ROW_NUMBER()函数为每个分组内的行分配一个唯一的序号（row_number），序号按薪水从高到低排序。
t1=# SELECT depname, empno, salary, t1-# AVG(salary) OVER (PARTITION BY depname) AS avg_salary t1-# FROM empsalary; depname | empno | salary | avg_salary -----------------+-------+---------+----------------------- development | 1 | 5200.00 | 5080.0000000000000000 development | 4 | 4800.00 | 5080.0000000000000000 development | 7 | 4200.00 | 5080.0000000000000000 development | 8 | 6000.00 | 5080.0000000000000000 development | 10 | 5200.00 | 5080.0000000000000000 human resources | 6 | 3900.00 | 3700.0000000000000000 human resources | 3 | 3500.00 | 3700.0000000000000000 sales | 2 | 5000.00 | 4766.6666666666666667 sales | 9 | 4500.00 | 4766.6666666666666667 sales | 5 | 4800.00 | 4766.6666666666666667 (10 rows)复制
这个查询计算每个部门的平均薪水(avg_salary)。通过PARTITION BY depname，AVG(salary)函数在每个部门内独立计算平均值，而不是在整个表上计算一个总的平均值。每行都会显示其所在部门的平均薪水。
t1=# SELECT empno, salary, t1-# LAG(salary) OVER (ORDER BY salary) AS prev_salary, t1-# LEAD(salary) OVER (ORDER BY salary) AS next_salary t1-# FROM empsalary; empno | salary | prev_salary | next_salary -------+---------+-------------+------------- 3 | 3500.00 | | 3900.00 6 | 3900.00 | 3500.00 | 4200.00 7 | 4200.00 | 3900.00 | 4500.00 9 | 4500.00 | 4200.00 | 4800.00 4 | 4800.00 | 4500.00 | 4800.00 5 | 4800.00 | 4800.00 | 5000.00 2 | 5000.00 | 4800.00 | 5200.00 10 | 5200.00 | 5000.00 | 5200.00 1 | 5200.00 | 5200.00 | 6000.00 8 | 6000.00 | 5200.00 | (10 rows)复制
这个查询展示了如何使用LEAD()和LAG()函数来访问当前行前后的薪水值。LAG(salary)返回当前行前一行的薪水(prev_salary)，而LEAD(salary)返回当前行后一行的薪水(next_salary)。通过ORDER BY salary，数据按薪水升序排序。
t1=# SELECT depname, empno, salary, t1-# RANK() OVER (PARTITION BY depname ORDER BY salary DESC) AS rank, t1-# PERCENT_RANK() OVER (PARTITION BY depname ORDER BY salary DESC) AS percent_rank t1-# FROM empsalary; depname | empno | salary | rank | percent_rank -----------------+-------+---------+------+-------------- development | 8 | 6000.00 | 1 | 0 development | 1 | 5200.00 | 2 | 0.25 development | 10 | 5200.00 | 2 | 0.25 development | 4 | 4800.00 | 4 | 0.75 development | 7 | 4200.00 | 5 | 1 human resources | 6 | 3900.00 | 1 | 0 human resources | 3 | 3500.00 | 2 | 1 sales | 2 | 5000.00 | 1 | 0 sales | 5 | 4800.00 | 2 | 0.5 sales | 9 | 4500.00 | 3 | 1 (10 rows)复制
PARTITION BY depname确保窗口函数在每个部门内部独立计算。
ORDER BY salary DESC基于薪水降序排序，以便排名和百分比排名是基于薪水从高到低。
RANK()函数为每个部门内的员工基于薪水进行排名。如果两个员工的薪水相同，他们将分享相同的排名，而下一个排名将跳过。
PERCENT_RANK()函数计算每个员工的薪水在其所在部门中超过了多少百分比的员工。结果是一个0到1之间的值，其中0表示最低（或并列最低）薪水，而1表示最高薪水。 这个查询为每个部门的每个员工提供了一个排名和一个百分比排名，可以让你快速了解员工的薪水在其所在部门的相对位置。这种类型的查询非常有用，特别是在进行薪酬分析和决策时。
三、继承
PostgreSQL数据库中的继承是一种实现表之间关系的机制，它允许表结构从一个（父）表继承到另一个（子）表。这种机制是从面向对象数据库继承而来的，为数据库设计提供了新的可能性。
- 结构共享：子表自动拥有父表的所有列，这意味着你在父表中定义的字段会自动出现在子表中。
- 数据独立：虽然子表继承了父表的结构，但它们存储的数据是独立的。你可以在子表中添加额外的字段或约束，以满足特定的需求。
- 查询和操作：在查询父表时，默认情况下，查询结果会包含所有子表中的数据。如果你只想查询父表中的数据，可以使用
ONLY关键字。同样，更新和删除操作也会影响到子表中的数据，除非明确指定ONLY。 - 限制：尽管继承在某些场景下非常有用，但它也有一些限制。例如，PostgreSQL中的继承不支持自动的外键约束传递。也就是说，如果你在父表上定义了外键约束，这些约束不会自动应用到子表上。此外，唯一性约束和主键约束也只能在单个表的范围内保证。
使用示例：
创建父表cities和子表capitals的SQL语句如下：
CREATE TABLE cities ( name text, population real, elevation int -- (in ft) ); CREATE TABLE capitals ( state char(2) UNIQUE NOT NULL ) INHERITS (cities);复制
在这个例子中，capitals表继承了cities表的所有列（name、population、elevation），并添加了一个额外的列state，该列有一个唯一性约束。
四、总结
PostgreSQL的窗口函数是一种特殊的函数，它可以在一组相关的表行上执行计算，而这组行与当前行有某种关系。与聚合函数不同，窗口函数不会将结果行合并成单个输出行，而是保留各行的独立性。窗口函数可以访问除当前行外的其他行，进行如排名、平均值、累计和等操作，而不改变表的行数。窗口函数在SELECT列表和ORDER BY子句中使用，通过OVER子句定义操作的数据窗口。
PostgreSQL的继承特性提供了一种强大的方式来组织和管理相关的数据表，使得数据模型更加灵活和可扩展。然而，使用继承时也需要注意其限制和影响，特别是在涉及约束和表关系时。
