ORACLE SQL Query查找组中的最小值和最大值

I have the following data and need quick help for the following.(giving EMP table example, as I cannot give real data due to compliance issues) :

我有以下数据,需要以下快速帮助。(提供EMP表示例,因为我无法根据合规性问题提供真实数据):

SELECT EMPNO,
       JOB,
       HIREDATE,
       HIREDATE AS STARTDATE,
       LEAD(HIREDATE) OVER (ORDER BY HIREDATE) AS ENDDATE
  FROM SCOTT.EMP
 ORDER BY HIREDATE;

EMPNO   JOB        HIREDATE   STARTDATE  ENDDATE  
--------------------------------------------------
7369    CLERK      17-Dec-80  17-Dec-80  20-Feb-81  
7499    SALESMAN   20-Feb-81  20-Feb-81  22-Feb-81  
7521    SALESMAN   22-Feb-81  22-Feb-81  2-Apr-81  
7566    MANAGER    2-Apr-81   2-Apr-81   1-May-81  
7698    MANAGER    1-May-81   1-May-81   9-Jun-81  
7782    MANAGER    9-Jun-81   9-Jun-81   8-Sep-81  
7844    SALESMAN   8-Sep-81   8-Sep-81   28-Sep-81  
7654    SALESMAN   28-Sep-81  28-Sep-81  17-Nov-81  
7839    PRESIDENT  17-Nov-81  17-Nov-81  3-Dec-81  
7900    CLERK      3-Dec-81   3-Dec-81   3-Dec-81  
7902    ANALYST    3-Dec-81   3-Dec-81   23-Jan-82  
7934    CLERK      23-Jan-82  23-Jan-82  19-Apr-87  
7788    ANALYST    19-Apr-87  19-Apr-87  23-May-87  
7876    CLERK      23-May-87  23-May-87  NULL

Here I want the partition by job in oracle SQL, such that the consecutive rows get the same derived start date(DERSTARTDATE) and derived end date(DERENDDATE) as shown below. Can you please advise with SQL in Oracle.

在这里,我希望在oracle SQL中按作业分区,以便连续的行获得相同的派生开始日期(DERSTARTDATE)和派生结束日期(DERENDDATE),如下所示。您能否在Oracle中使用SQL提供建议。

I want something like this:

我想要这样的东西:

EMPNO   JOB       HIREDATE   DERSTARTDATE  DERENDDATE   
-----------------------------------------------------
7369    CLERK     17-Dec-80  17-Dec-80     20-Feb-81   
7499    SALESMAN  20-Feb-81  20-Feb-81     2-Apr-81    
7521    SALESMAN  22-Feb-81  20-Feb-81     2-Apr-81       
7566    MANAGER   2-Apr-81   2-Apr-81      8-Sep-81       
7698    MANAGER   1-May-81   2-Apr-81      8-Sep-81    
7782    MANAGER   9-Jun-81   2-Apr-81      8-Sep-81    
7844    SALESMAN  8-Sep-81   8-Sep-81      17-Nov-81   
7654    SALESMAN  28-Sep-81  8-Sep-81      17-Nov-81   
7839    PRESIDENT 17-Nov-81  17-Nov-81     3-Dec-81    
7900    CLERK     3-Dec-81   3-Dec-81      3-Dec-81    
7902    ANALYST   3-Dec-81   3-Dec-81      23-Jan-82   
7934    CLERK     23-Jan-82  23-Jan-82     19-Apr-87   
7788    ANALYST   19-Apr-87  19-Apr-87     23-May-87   
7876    CLERK     23-May-87  23-May-87     NULL

My requirement is if the same job is repeated in consecutive e.g. for Salesman then the startdate and enddate should be min of startdate and max of enddate in all consecutive columns.

我的要求是如果连续重复相同的工作,例如对于Salesman,startdate和enddate应该是startdate的min和所有连续列中enddate的最大值。

Example:

SALESMAN for both consecutive employees empno= 7499 and empno=7521 should have DERSTARTDATE = 20-Feb-81 and DERENDDATE =2-Apr-81

连续员工empno = 7499和empno = 7521的SALESMAN应该有DERSTARTDATE = 20-Feb-81和DERENDDATE = 2-Apr-81

SALESMAN for empno= 7844 and empno=7654 should have DERSTARTDATE = 8-Sep-81 and DERENDDATE = 17-Nov-81

对于empno = 7844和empno = 7654的SALESMAN应该有DERSTARTDATE = 8-Sep-81和DERENDDATE = 17-Nov-81

1 个解决方案

#1

You first need to identify groups of same consecutive jobs. Tabibitosan is easy way to do it.

您首先需要识别相同连续作业的组。 Tabibitosan是一种简单的方法。

 select empno, job, hiredate,
        lead(hiredate) over (order by hiredate,empno) startdate,
        row_number() over (order by hiredate,empno) -
        row_number() over (order by job, hiredate,empno) grp
from emp
);

Once you identify the groups, you can use MIN/MAX as analytical function and find the MIN/MAX in each group.

识别组后,可以使用MIN / MAX作为分析功能,并在每组中找到MIN / MAX。

with x as (
  select empno, job, hiredate,
        lead(hiredate) over (order by hiredate,empno) startdate,
        row_number() over (order by hiredate,empno) -
        row_number() over (order by job, hiredate,empno) grp
    from emp
  )
select empno, job, hiredate,
      min(hiredate) over (partition by grp) derstartdate,
      max(startdate) over (partition by grp) derenddate
  from x
order by hiredate;

Demo.

#1