三种方法解分组操作极值问题

对于每个员工，自入职以来的薪水涨幅是：当前的薪水-入职时的薪水。
但是有个误区要先指出来，不是最高薪水 - 最低薪水（存在降薪的可能；如果是查找最大的一次涨薪幅度，可以使用表自连接完成）

找到当前薪水和入职时的薪水有3种思路：

1. （最直接但最慢）通过连接employees表的hire_date找到初始薪水，再INNER JOIN salaries表的特殊性（在职人员当前薪水记录to_date值为'9999-01-01'的特性）找到在职薪水相减。（运行时间超过47%；内存超过3.5%）
2. （优化查找在职薪水）通过连接employees表的hire_date找到初始薪水，再通过窗口函数找出最后一次的薪水记录，但此时需要排除离职员工（如果有员工状态表就可以更加优化）（运行时间超过82.3%；内存超过43%）
3. 不使用employees表：直接使用窗口函数找出最起始和在职的薪水记录去差值（运行时间超过21.3%；内存超过21%）
4. 使用子查询显示growth列

通过连接employees表的hire_date找到初始薪水，再INNER JOIN salaries表的特殊性（在职人员当前薪水记录to_date值为'9999-01-01'的特性）找到在职薪水相减

SELECT s1.emp_no AS "emp_no", s2.salary - s1.salary AS "growth"
FROM (
    SELECT emp_no, salary
    FROM salaries
    WHERE (emp_no, from_date) IN (
        SELECT emp_no, hire_date 
        FROM employees
    )
) s1
INNER JOIN (
    SELECT emp_no, salary
    FROM salaries
    WHERE to_date = '9999-01-01'
) s2 ON s1.emp_no = s2.emp_no -- 因为INNER JOIN只会连接匹配行，所以s2中筛除的已离职员工则不会被显示
ORDER BY growth ASC
;

通过连接employees表的hire_date找到初始薪水，再通过窗口函数找出最后一次的薪水记录，但此时需要排除离职员工（如果有员工状态表就可以更加优化）

SELECT s1.emp_no AS "emp_no", s2.salary - s1.salary AS "growth"
FROM (
    SELECT emp_no, salary
    FROM salaries
    WHERE (emp_no, from_date) IN (
        SELECT emp_no, hire_date 
        FROM employees
    )
) s1
INNER JOIN (
    SELECT emp_no, salary,
        -- 利用窗口函数优化查找在职薪水
        rank() OVER (PARTITION BY emp_no ORDER BY from_date DESC) AS ranking
    FROM salaries
) s2 ON s1.emp_no = s2.emp_no
WHERE s2.ranking = 1 AND 
    -- 如果员工表有状态列，这一条件就可以省略（数据库表有优化 / 有新的员工状态表）
    s1.emp_no IN (
        SELECT emp_no
        FROM salaries
        WHERE to_date = '9999-01-01'
    )
ORDER BY growth ASC
;

使用子查询显示growth列

SELECT emp_no AS "emp_no", 
    (
        SELECT s.salary - salary
        FROM salaries s_temp
        WHERE from_date = (
            SELECT hire_date
            FROM employees e
            WHERE e.emp_no = s_temp.emp_no
        ) AND s_temp.emp_no = s.emp_no
    ) AS "growth"
FROM salaries s
WHERE to_date = '9999-01-01' AND
    -- 如果员工表有状态列，这一条件就可以省略（数据库表有优化 / 有新的员工状态表）
    emp_no IN (
        SELECT emp_no
        FROM salaries
        WHERE to_date = '9999-01-01'
    )
ORDER BY growth ASC
;