postgresql 中第一个和最后一个值聚合函数可以正确处理 NULL 值

PostgreSQL中第一个和最后一个值聚合函数可以正确处理NULL值

first_value(expression) OVER (PARTITION BY col1, col2 ORDER BY col3)
last_value(expression) OVER (PARTITION BY col1, col2 ORDER BY col3)

sql
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    hire_date DATE
);
INSERT INTO employees (name, department, hire_date) VALUES
    ('John Doe', 'Sales', '2020-01-01'),
    ('Jane Smith', 'Sales', '2021-02-01'),
    ('Mark Johnson', 'Marketing', '2019-12-01'),
    ('Emily Brown', 'Marketing', NULL);

sql
SELECT
    department,
    first_value(name) OVER (PARTITION BY department ORDER BY hire_date) AS first_employee,
    last_value(name) OVER (PARTITION BY department ORDER BY hire_date) AS last_employee
FROM
    employees;

 department | first_employee | last_employee
------------+----------------+---------------
 Sales      | John Doe       | Jane Smith
 Marketing  | Mark Johnson   | Emily Brown