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
- SALESMAN for empno= 7844 and empno=7654 should have DERSTARTDATE = 8-Sep-81 and DERENDDATE = 17-Nov-81
连续员工empno = 7499和empno = 7521的SALESMAN应该有DERSTARTDATE = 20-Feb-81和DERENDDATE = 2-Apr-81
对于empno = 7844和empno = 7654的SALESMAN应该有DERSTARTDATE = 8-Sep-81和DERENDDATE = 17-Nov-81
1 个解决方案
#1
0
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
0
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.