有没有大神知道这个SQL怎么优化

Question

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 月份,试用期内,一年以下,一到二年,二到三年,三到五年,五到十年,十到二十年,二十年以上

Answer 1

一共union了12个月的数据，有没有大神知道这个SQL怎么优化

Answer 2

你这个sql应该性能非常慢吧。其实就涉及到了两三个表，但是每个表12个月+这么多维度 扫描了100遍以上。优化的目标就是减少扫描遍数，减少IO。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'可以把一样的逻辑提取出来，不如上面这段，每一个维度放到select 块中用case when 来实现，类似：
select  case when  条件1  then  一年内  case when 条件2  then 两到三年 。。
from   （上面提取出来一样的逻辑）
。
类似这样改造可以减少扫描很多次数，大量减少IO，性能提高了。