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 月份,試用期內,一年以下,一到二年,二到三年,三到五年,五到十年,十到二十年,二十年以上
uj5u.com熱心網友回復：
一共union了12個月的資料，有沒有大神知道這個SQL怎么優化uj5u.com熱心網友回復：
你這個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，性能提高了。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/71751.html
標籤：開發
下一篇：請教oracle rac集群問題