有没有大神知道这个SQL怎么优化
SELECT
月份,试用期内,一年以下,一到二年,二到三年,三到五年,五到十年,十到二十年,二十年以上
FROM
(
SELECT DISTINCT
(
SELECT
DISTINCT
extract(month from per_all_people_f.effective_start_date)
FROM
per_all_people_f
WHERE
extract (month from per_all_people_f.effective_start_date) = extract (month from to_date('2017-01-31', 'yyyy-mm-dd'))
) as 月份,
(SELECT DISTINCT
count(*)
FROM
per_all_people_f p1
LEFT JOIN per_all_assignments_f p2 ON p1.person_id = p2.person_id
LEFT JOIN per_contracts_f p3 ON p3.person_id=p1.person_id
WHERE
p1.attribute1 is NOT NULL
AND ((to_date(p3.practice_end_time,'yyyy-mm-dd') - to_date('2017-01-31','yyyy-mm-dd') ) > 0 )
AND p1.effective_end_date is NULL
AND (p2.assignment_status_type_id != 3 OR p2.assignment_status_type_id isnull)
AND CURRENT_DATE BETWEEN p2.effective_start_date AND COALESCE (
p2.effective_end_date,
to_date('2999-12-31', 'yyyy-MM-dd')
) and primary_flag = '1'
AND
p1.effective_end_date ISNULL --在职
AND
p1.attribute1 is not null --参加工作时间不为空
) as 试用期内,
(
SELECT COUNT((
( extract( year from( age (to_date('2017-01-31', 'yyyy-mm-dd'),to_date(p1.attribute1, 'yyyy-mm-dd')))))
<1) or NULL)
FROM
per_all_people_f p1
LEFT JOIN per_all_assignments_f p2 ON p1.person_id = p2.person_id
LEFT JOIN per_contracts_f p3 ON p1.person_id=p3.person_id
WHERE
p1.effective_end_date ISNULL --在职
AND
(
(p3.practice_end_time is null
or p3.practice_end_time ='')
or
to_date(p3.practice_end_time , 'yyyy-mm-dd') <= to_date('2017-01-31', 'yyyy-mm-dd') ) --筛选过了试用期或者试用期结束时间为空
AND
p1.attribute1 is not null --参加工作时间不为空
AND
(p2.assignment_status_type_id !=3 or
p2.assignment_status_type_id isNULL)--没退休
AND CURRENT_DATE BETWEEN p2.effective_start_date AND COALESCE (
p2.effective_end_date,
to_date('2999-12-31', 'yyyy-MM-dd')
) and primary_flag = '1'
)as 一年以下,
(
SELECT COUNT((
( extract( year from( age (to_date('2017-01-31', 'yyyy-mm-dd'),to_date(p1.attribute1, 'yyyy-mm-dd')))))
=1) or NULL)
FROM
per_all_people_f p1
LEFT JOIN per_all_assignments_f p2 ON p1.person_id = p2.person_id
LEFT JOIN per_contracts_f p3 ON p1.person_id=p3.person_id
WHERE
p1.effective_end_date ISNULL --在职
AND
(
(p3.practice_end_time is null
or p3.practice_end_time ='')
or
to_date(p3.practice_end_time , 'yyyy-mm-dd') <= to_date('2017-01-31', 'yyyy-mm-dd') ) --筛选过了试用期或者试用期结束时间为空
AND
p1.attribute1 is not null --参加工作时间不为空
AND
(p2.assignment_status_type_id !=3 or
p2.assignment_status_type_id isNULL)--没退休
AND CURRENT_DATE BETWEEN p2.effective_start_date AND COALESCE (
p2.effective_end_date,
to_date('2999-12-31', 'yyyy-MM-dd')
) and primary_flag = '1'
)as 一到二年,
(
SELECT COUNT((
( extract( year from( age (to_date('2017-01-31', 'yyyy-mm-dd'),to_date(p1.attribute1, 'yyyy-mm-dd')))))
=2) or NULL)
FROM
per_all_people_f p1
LEFT JOIN per_all_assignments_f p2 ON p1.person_id = p2.person_id
LEFT JOIN per_contracts_f p3 ON p1.person_id=p3.person_id
WHERE
p1.effective_end_date ISNULL --在职
AND
(
(p3.practice_end_time is null
or p3.practice_end_time ='')
or
to_date(p3.practice_end_time , 'yyyy-mm-dd') <= to_date('2017-01-31', 'yyyy-mm-dd') ) --筛选过了试用期或者试用期结束时间为空
AND
p1.attribute1 is not null --参加工作时间不为空
AND
(p2.assignment_status_type_id !=3 or
p2.assignment_status_type_id isNULL)--没退休
AND CURRENT_DATE BETWEEN p2.effective_start_date AND COALESCE (
p2.effective_end_date,
to_date('2999-12-31', 'yyyy-MM-dd')
) and primary_flag = '1'
)as 二到三年,
(
SELECT COUNT((
( extract( year from( age (to_date('2017-01-31', 'yyyy-mm-dd'),to_date(p1.attribute1, 'yyyy-mm-dd')))))
between 3 and 4) or NULL)
FROM
per_all_people_f p1
LEFT JOIN per_all_assignments_f p2 ON p1.person_id = p2.person_id
LEFT JOIN per_contracts_f p3 ON p1.person_id=p3.person_id
WHERE
p1.effective_end_date ISNULL --在职
AND
(
(p3.practice_end_time is null
or p3.practice_end_time ='')
or
to_date(p3.practice_end_time , 'yyyy-mm-dd') <= to_date('2017-01-31', 'yyyy-mm-dd') ) --筛选过了试用期或者试用期结束时间为空
AND
p1.attribute1 is not null --参加工作时间不为空
AND
(p2.assignment_status_type_id !=3 or
p2.assignment_status_type_id isNULL)--没退休
AND CURRENT_DATE BETWEEN p2.effective_start_date AND COALESCE (
p2.effective_end_date,
to_date('2999-12-31', 'yyyy-MM-dd')
) and primary_flag = '1'
)as 三到五年,
(
SELECT COUNT((
( extract( year from( age (to_date('2017-01-31', 'yyyy-mm-dd'),to_date(p1.attribute1, 'yyyy-mm-dd')))))
between 5 and 9) or NULL)
FROM
per_all_people_f p1
LEFT JOIN per_all_assignments_f p2 ON p1.person_id = p2.person_id
LEFT JOIN per_contracts_f p3 ON p1.person_id=p3.person_id
WHERE
p1.effective_end_date ISNULL --在职
AND
(
(p3.practice_end_time is null
or p3.practice_end_time ='')
or
to_date(p3.practice_end_time , 'yyyy-mm-dd') <= to_date('2017-01-31', 'yyyy-mm-dd') ) --筛选过了试用期或者试用期结束时间为空
AND
p1.attribute1 is not null --参加工作时间不为空
AND
(p2.assignment_status_type_id !=3 or
p2.assignment_status_type_id isNULL)--没退休
AND CURRENT_DATE BETWEEN p2.effective_start_date AND COALESCE (
p2.effective_end_date,
to_date('2999-12-31', 'yyyy-MM-dd')
) and primary_flag = '1'
)as 五到十年,
(
SELECT COUNT((
( extract( year from( age (to_date('2017-01-31', 'yyyy-mm-dd'),to_date(p1.attribute1, 'yyyy-mm-dd')))))
between 10 and 20) or NULL)
FROM
per_all_people_f p1
LEFT JOIN per_all_assignments_f p2 ON p1.person_id = p2.person_id
LEFT JOIN per_contracts_f p3 ON p1.person_id=p3.person_id
WHERE
p1.effective_end_date ISNULL --在职
AND
(
(p3.practice_end_time is null
or p3.practice_end_time ='')
or
to_date(p3.practice_end_time , 'yyyy-mm-dd') <= to_date('2017-01-31', 'yyyy-mm-dd') ) --筛选过了试用期或者试用期结束时间为空
AND
p1.attribute1 is not null --参加工作时间不为空
AND
(p2.assignment_status_type_id !=3 or
p2.assignment_status_type_id isNULL)--没退休
AND CURRENT_DATE BETWEEN p2.effective_start_date AND COALESCE (
p2.effective_end_date,
to_date('2999-12-31', 'yyyy-MM-dd')
) and primary_flag = '1'
)as 十到二十年,
(
SELECT COUNT((
( extract( year from( age (to_date('2017-01-31', 'yyyy-mm-dd'),to_date(p1.attribute1, 'yyyy-mm-dd')))))
> 20) or NULL)
FROM
per_all_people_f p1
LEFT JOIN per_all_assignments_f p2 ON p1.person_id = p2.person_id
LEFT JOIN per_contracts_f p3 ON p1.person_id=p3.person_id
WHERE
p1.effective_end_date ISNULL --在职
AND
(
(p3.practice_end_time is null
or p3.practice_end_time ='')
or
to_date(p3.practice_end_time , 'yyyy-mm-dd') <= to_date('2017-01-31', 'yyyy-mm-dd') ) --筛选过了试用期或者试用期结束时间为空
AND
p1.attribute1 is not null --参加工作时间不为空
AND
(p2.assignment_status_type_id !=3 or
p2.assignment_status_type_id isNULL)--没退休
AND CURRENT_DATE BETWEEN p2.effective_start_date AND COALESCE (
p2.effective_end_date,
to_date('2999-12-31', 'yyyy-MM-dd')
) and primary_flag = '1'
)as 二十年以上
UNION ALL
SELECT
(
SELECT
DISTINCT
extract(month from per_all_people_f.effective_start_date)
FROM
per_all_people_f
WHERE
extract (month from per_all_people_f.effective_start_date) = extract (month from to_date('2017-02-28', 'yyyy-mm-dd'))
) as 月份,
(SELECT DISTINCT
count(*)
FROM
per_all_people_f p1
LEFT JOIN per_all_assignments_f p2 ON p1.person_id = p2.person_id
LEFT JOIN per_contracts_f p3 ON p3.person_id=p1.person_id
WHERE
p1.attribute1 is NOT NULL
AND ((to_date(p3.practice_end_time,'yyyy-mm-dd') - to_date('2017-02-28','yyyy-mm-dd') ) > 0 )
AND p1.effective_end_date is NULL
AND (p2.assignment_status_type_id != 3 OR p2.assignment_status_type_id isnull)
AND CURRENT_DATE BETWEEN p2.effective_start_date AND COALESCE (
p2.effective_end_date,
to_date('2999-12-31', 'yyyy-MM-dd')
) and primary_flag = '1'
AND
p1.effective_end_date ISNULL --在职
AND
p1.attribute1 is not null --参加工作时间不为空
) as 试用期内2,
(
SELECT COUNT((
( extract( year from( age (to_date('2017-02-28', 'yyyy-mm-dd'),to_date(p1.attribute1, 'yyyy-mm-dd')))))
<1) or NULL)
FROM
per_all_people_f p1
LEFT JOIN per_all_assignments_f p2 ON p1.person_id = p2.person_id
LEFT JOIN per_contracts_f p3 ON p1.person_id=p3.person_id
WHERE
p1.effective_end_date ISNULL --在职
AND
(
(p3.practice_end_time is null
or p3.practice_end_time ='')
or
to_date(p3.practice_end_time , 'yyyy-mm-dd') <= to_date('2017-02-28', 'yyyy-mm-dd') ) --筛选过了试用期或者试用期结束时间为空
AND
p1.attribute1 is not null --参加工作时间不为空
AND
(p2.assignment_status_type_id !=3 or
p2.assignment_status_type_id isNULL)--没退休
AND CURRENT_DATE BETWEEN p2.effective_start_date AND COALESCE (
p2.effective_end_date,
to_date('2999-12-31', 'yyyy-MM-dd')
) and primary_flag = '1'
)as 一年以下2,
(
SELECT COUNT((
( extract( year from( age (to_date('2017-02-28', 'yyyy-mm-dd'),to_date(p1.attribute1, 'yyyy-mm-dd')))))
=1) or NULL)
FROM
per_all_people_f p1
LEFT JOIN per_all_assignments_f p2 ON p1.person_id = p2.person_id
LEFT JOIN per_contracts_f p3 ON p1.person_id=p3.person_id
WHERE
p1.effective_end_date ISNULL --在职
AND
(
(p3.practice_end_time is null
or p3.practice_end_time ='')
or
to_date(p3.practice_end_time , 'yyyy-mm-dd') <= to_date('2017-02-28', 'yyyy-mm-dd') ) --筛选过了试用期或者试用期结束时间为空
AND
p1.attribute1 is not null --参加工作时间不为空
AND
(p2.assignment_status_type_id !=3 or
p2.assignment_status_type_id isNULL)--没退休
AND CURRENT_DATE BETWEEN p2.effective_start_date AND COALESCE (
p2.effective_end_date,
to_date('2999-12-31', 'yyyy-MM-dd')
) and primary_flag = '1'
)as 一到二年2,
(
SELECT COUNT((
( extract( year from( age (to_date('2017-02-28', 'yyyy-mm-dd'),to_date(p1.attribute1, 'yyyy-mm-dd')))))
=2) or NULL)
FROM
per_all_people_f p1
LEFT JOIN per_all_assignments_f p2 ON p1.person_id = p2.person_id
LEFT JOIN per_contracts_f p3 ON p1.person_id=p3.person_id
WHERE
p1.effective_end_date ISNULL --在职
AND
(
(p3.practice_end_time is null
or p3.practice_end_time ='')
or
to_date(p3.practice_end_time , 'yyyy-mm-dd') <= to_date('2017-02-28', 'yyyy-mm-dd') ) AND
p1.attribute1 is not null
AND
(p2.assignment_status_type_id !=3 or
p2.assignment_status_type_id isNULL)--没退休
AND CURRENT_DATE BETWEEN p2.effective_start_date AND COALESCE (
p2.effective_end_date,
to_date('2999-12-31', 'yyyy-MM-dd')
) and primary_flag = '1'
)as 二到三年2,
) as 结果
GROUP BY 月份,试用期内,一年以下,一到二年,二到三年,三到五年,五到十年,十到二十年,二十年以上