"获取第三排名的记录或最旧的记录"

Question

我有一个名为emp_sal的表，我想根据change_date按降序找出每个员工的第三条记录。

但问题在于，对于一些员工来说，他们的记录少于三条。在这种情况下，如果该员工在表中有两条记录，我需要获取第二条记录；如果只有一条记录，则需要获取第一条记录。

emp_sal表中的数据：

| empid | change_date | salary |
|-------|-------------|--------|
| 1 | 2023-01-01 | 1000 |
| 1 | 2023-02-01 | 1400 |
| 1 | 2023-03-01 | 1450 |
| 1 | 2023-04-01 | 1500 |
| 2 | 2023-11-01 | 2500 |
| 2 | 2023-12-01 | 2400 |
| 2 | 2023-12-15 | 2200 |
| 3 | 2023-05-01 | 500 |
| 3 | 2023-06-01 | 640 |
| 4 | 2023-10-01 | 3000 |

我尝试使用了ROW_NUMBER()函数，但无法正确地获取记录数少于3条的员工信息。

select * from 
(
  select e.*, row_number() over (partition by empid order by change_date desc) as rn
  from emp_sal e
)
where rn = 3; -- 这里还需要添加什么条件，我不确定

预期输出结果：

| empid | change_date | salary |
|-------|-------------|--------|
| 1 | 2023-02-01 | 1400 |
| 2 | 2023-11-01 | 2500 |
| 3 | 2023-05-01 | 500 |
| 4 | 2023-10-01 | 3000 |

非常感谢您的帮助。如有需要更多详细信息，请告知。

Flexo · Answer

这是一个解决方案。

样例数据：

SQL> with emp_sal (empid, change_date, salary) as
  2    (select 1, date '2023-01-01', 1000 from dual union all select
  3     1, date '2023-02-01', 1400 from dual union all select
  4     1, date '2023-03-01', 1450 from dual union all select
  5     1, date '2023-04-01', 1500 from dual union all select
  6     -- 示例数据的其他部分...
  7     4, date '2023-10-01', 3000 from dual
 14    ),

查询：temp 常量表达式（CTE）与您的子查询类似：

 16  temp as
 17    (select empid, change_date, salary,
 18       row_number() over (partition by empid order by change_date desc) rn
 19     from emp_sal
 20    )

以下是返回所需数据的部分：

 21  select *
 22  from temp a
 23  where a.rn = (select max(b.rn) from temp b
 24                where b.empid = a.empid
 25                  and b.rn <= 3
 26               )
 27  order by a.empid;

执行结果如下：

     EMPID CHANGE_DATE   SALARY         RN
---------- ---------- ---------- ----------
         1 2023-02-01      1400          3
         2 2023-11-01      2500          3
         3 2023-05-01       500          2
         4 2023-10-01      3000          1

SQL>

tux21b · Answer

如果你在内层查询中添加 count(*) over (partition by empid) as cnt，你可以将过滤条件设置为 where rn = least(3, cnt)。

select s.empid, s.change_date, s.salary
from (
    select
        e.*,
        row_number() over (partition by e.empid order by e.change_date desc) as rn,
        count(*) over (partition by e.empid) as cnt
    from emp_sal e
) s
where s.rn = least(3, s.cnt)
order by s.empid;

查看此 db<>fiddle 示例。

nnnnnn · Answer

一种选择是使用MAX(..) KEEP (DENSE_RANK ..)分析函数来获取每个empid的最大rn，如下所示：

SELECT DISTINCT empid, 
       MAX(change_date) KEEP (DENSE_RANK FIRST ORDER BY empid, rn DESC) 
                        OVER (PARTITION BY empid) AS change_date,
       MAX(salary) KEEP (DENSE_RANK FIRST ORDER BY empid, rn DESC) 
                        OVER (PARTITION BY empid) AS salary  
FROM
(
  SELECT e.*, 
         ROW_NUMBER() OVER (PARTITION BY empid ORDER BY change_date DESC) AS rn
  FROM emp_sal e
) 
WHERE rn <= 3

示例

或者你可以两次使用ROW_NUMBER()函数，并结合包含WITH TIES选项的FETCH子句：

SELECT empid, change_date, salary
FROM
(
  SELECT e.*, 
         ROW_NUMBER() OVER (PARTITION BY empid ORDER BY change_date DESC) AS rn
  FROM emp_sal e
) e1
WHERE rn <= 3
ORDER BY ROW_NUMBER() OVER (PARTITION BY empid ORDER BY rn DESC)
FETCH FIRST 1 ROW WITH TIES

示例

SilentGhost · Answer

你可以使用ROW_NUMBER()函数两次，首先对每个empid的最近3条记录进行过滤，然后从这些记录中获取最早的一条：

SELECT empid,
       change_date,
       salary
FROM (
  SELECT empid,
         change_date,
         salary,
         ROW_NUMBER() over (PARTITION BY empid ORDER BY change_date ASC) AS rn2
  FROM (
    SELECT empid,
           change_date,
           salary,
           ROW_NUMBER() over (PARTITION BY empid ORDER BY change_date DESC) AS rn1
    FROM   emp_sal
  )
  WHERE  rn1 <= 3
)
WHERE  rn2 = 1
ORDER BY empid;

对于以下示例数据：

CREATE TABLE emp_sal (empid, change_date, salary) AS
SELECT 1, DATE '2023-01-01', 1000 FROM DUAL UNION ALL
SELECT 1, DATE '2023-02-01', 1400 FROM DUAL UNION ALL
SELECT 1, DATE '2023-03-01', 1450 FROM DUAL UNION ALL
SELECT 1, DATE '2023-04-01', 1500 FROM DUAL UNION ALL
SELECT 2, DATE '2023-11-01', 2500 FROM DUAL UNION ALL
SELECT 2, DATE '2023-12-01', 2400 FROM DUAL UNION ALL
SELECT 2, DATE '2023-12-15', 2200 FROM DUAL UNION ALL
SELECT 3, DATE '2023-05-01',  500 FROM DUAL UNION ALL
SELECT 3, DATE '2023-06-01',  640 FROM DUAL UNION ALL
SELECT 4, DATE '2023-10-01', 3000 FROM DUAL;

上述查询将输出如下结果：

| EMPID | CHANGE_DATE | SALARY |
|-------|-------------------|--------|
| 1 | 2023-02-01 00:00:00 | 1400 |
| 2 | 2023-11-01 00:00:00 | 2500 |
| 3 | 2023-05-01 00:00:00 | 500 |
| 4 | 2023-10-01 00:00:00 | 3000 |

在线运行示例