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

yu_zheng_ 2018-02-05 03:36:11
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 月份,试用期内,一年以下,一到二年,二到三年,三到五年,五到十年,十到二十年,二十年以上
...全文
582 2 打赏 收藏 转发到动态 举报
写回复
用AI写文章
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
liu志坚 2018-02-05
  • 打赏
  • 举报
回复
你这个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,性能提高了。
yu_zheng_ 2018-02-05
  • 打赏
  • 举报
回复
一共union了12个月的数据,有没有大神知道这个SQL怎么优化

17,078

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧