计算CY closing day 前10日, 每日的累计交柜量的百分比的规律(D-10包括10天以前)?select count(*) from vessel_move_log a , voyage_member b where a.voyid_company_code = b.voyid_company_code and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2' and a.service_code ='LPAW' and b.ob_cntr_close_datetime <=to_date('2011-09-27','yyyy/mm/dd HH:MI:SS')
union all
select count(*) from vessel_move_log a , voyage_member b where a.voyid_company_code = b.voyid_company_code and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2' and a.service_code ='LPAW' and b.ob_cntr_close_datetime <=to_date('2011-09-26','yyyy/mm/dd HH:MI:SS')
union all
select count(*) from vessel_move_log a , voyage_member b where a.voyid_company_code = b.voyid_company_code and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2' and a.service_code ='LPAW' and b.ob_cntr_close_datetime <=to_date('2011-09-25','yyyy/mm/dd HH:MI:SS')
union all
select count(*) from vessel_move_log a , voyage_member b where a.voyid_company_code = b.voyid_company_code and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2' and a.service_code ='LPAW' and b.ob_cntr_close_datetime <=to_date('2011-09-24','yyyy/mm/dd HH:MI:SS')
union all
select count(*) from vessel_move_log a , voyage_member b where a.voyid_company_code = b.voyid_company_code and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2' and a.service_code ='LPAW' and b.ob_cntr_close_datetime <=to_date('2011-09-23','yyyy/mm/dd HH:MI:SS')
union all .....
and a.voyid_voyage_code = b.voyid_voyage_code and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2' and a.service_code ='LPAW' and b.ob_cntr_close_datetime <=to_date('2011-09-27','yyyy/mm/dd HH:MI:SS')
union all
select count(*) from vessel_move_log a , voyage_member b where a.voyid_company_code = b.voyid_company_code and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2' and a.service_code ='LPAW' and b.ob_cntr_close_datetime <=to_date('2011-09-26','yyyy/mm/dd HH:MI:SS')
union all
select count(*) from vessel_move_log a , voyage_member b where a.voyid_company_code = b.voyid_company_code and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2' and a.service_code ='LPAW' and b.ob_cntr_close_datetime <=to_date('2011-09-25','yyyy/mm/dd HH:MI:SS')
union all
select count(*) from vessel_move_log a , voyage_member b where a.voyid_company_code = b.voyid_company_code and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2' and a.service_code ='LPAW' and b.ob_cntr_close_datetime <=to_date('2011-09-24','yyyy/mm/dd HH:MI:SS')
union all
select count(*) from vessel_move_log a , voyage_member b where a.voyid_company_code = b.voyid_company_code and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2' and a.service_code ='LPAW' and b.ob_cntr_close_datetime <=to_date('2011-09-23','yyyy/mm/dd HH:MI:SS')
union all .....
第一种:create table #temp ---临时表
如果在应用程序用的话最好还是采取这种方式效率还是比较高的
然后把查询的结果放在临时表中,在应用程序中调用临时表中的数据,这样的效率会高些的。
如果单纯的查询时间段的数据就可以无需用union all
select count(*)
from vessel_move_log a, voyage_member b
where a.voyid_company_code = b.voyid_company_code
and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code
and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'
and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'
and a.service_code = 'LPAW'
and b.ob_cntr_close_datetime <=
to_date('2011-09-27', 'yyyy/mm/dd HH:MI:SS')
union all
select count(*)
from vessel_move_log a, voyage_member b
where a.voyid_company_code = b.voyid_company_code
and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code
and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'
and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'
and a.service_code = 'LPAW'
and b.ob_cntr_close_datetime <=
to_date('2011-09-26', 'yyyy/mm/dd HH:MI:SS')
union all
select count(*)
from vessel_move_log a, voyage_member b
where a.voyid_company_code = b.voyid_company_code
and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code
and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'
and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'
and a.service_code = 'LPAW'
and b.ob_cntr_close_datetime <=
to_date('2011-09-25', 'yyyy/mm/dd HH:MI:SS')
union all
select count(*)
from vessel_move_log a, voyage_member b
where a.voyid_company_code = b.voyid_company_code
and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code
and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'
and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'
and a.service_code = 'LPAW'
and b.ob_cntr_close_datetime <=
to_date('2011-09-24', 'yyyy/mm/dd HH:MI:SS')
union all
select count(*)
from vessel_move_log a, voyage_member b
where a.voyid_company_code = b.voyid_company_code
and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code
and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'
and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'
and a.service_code = 'LPAW'
and b.ob_cntr_close_datetime <=
to_date('2011-09-23', 'yyyy/mm/dd HH:MI:SS')
除了索引以外,其它没什么好办法来优化了。
from (
select trunc(b.ob_cntr_close_datetime) D1
from vessel_move_log a , voyage_member b
where a.voyid_company_code = b.voyid_company_code
and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code
and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'
and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'
and a.service_code ='LPAW'
and b.ob_cntr_close_datetime <= SYSDATE - 10 )
group by D1 ;
from (
select trunc(b.ob_cntr_close_datetime) D1
from vessel_move_log a , voyage_member b
where a.voyid_company_code = b.voyid_company_code
and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code
and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'
and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'
and a.service_code ='LPAW'
and b.ob_cntr_close_datetime >= trunc(SYSDATE - 10) )
group by D1 ;
voyage_member b
where a.voyid_company_code = b.voyid_company_code
and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code
and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'
and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'
and a.service_code = 'LPAW'
and b.ob_cntr_close_datetime < to_date('2011/09/18', 'yyyy/mm/dd'))
select t.base_sum + sum_add_by_day
from t,
(
select sum(1) over(order by trunc(t.cntr_close_datetime))
from vessel_move_log a,
voyage_member b,
(select to_date('2011/09/17', 'yyyy/mm/dd') + rownum as cntr_close_datetime
from dual
connect by rownum <= 10) t
where a.voyid_company_code = b.voyid_company_code
and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code
and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'
and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'
and a.service_code = 'LPAW'
and trunc(b.ob_cntr_close_datetime) = trunc(t.cntr_close_datetime)
);
主要思想就是使用sum() over() 函数进行累加计算with t as (select count(*) base_sum from vessel_move_log a,
voyage_member b
where a.voyid_company_code = b.voyid_company_code
and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code
and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'
and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'
and a.service_code = 'LPAW'
and b.ob_cntr_close_datetime < to_date('2011/09/18', 'yyyy/mm/dd'))
select t.base_sum + sum_add_by_day
from t,
(
select sum(1) over(order by t.cntr_close_datetime)
from vessel_move_log a,
voyage_member b,
(select to_date('2011/09/17', 'yyyy/mm/dd') + rownum as cntr_close_datetime
from dual
connect by rownum <= 10) t
where a.voyid_company_code = b.voyid_company_code
and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code
and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'
and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'
and a.service_code = 'LPAW'
and b.ob_cntr_close_datetime >= t.cntr_close_datetime
and b.ob_cntr_close_datetime < t.cntr_close_datetime + 1
);
起的一个别名,没有特别的意义。#6可能还是有重复记录,试试下面这个吧with t as (select count(*) base_sum from vessel_move_log a,
voyage_member b
where a.voyid_company_code = b.voyid_company_code
and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code
and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'
and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'
and a.service_code = 'LPAW'
and b.ob_cntr_close_datetime < to_date('2011/09/18', 'yyyy/mm/dd'))
select t.base_sum + sum(t1.sum_per_day) over(order by t1.ob_cntr_close_datetime)
from
(
select trunc(b.ob_cntr_close_datetime), sum(1) as sum_per_day from vessel_move_log a,
voyage_member b
where a.voyid_company_code = b.voyid_company_code
and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code
and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'
and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'
and a.service_code = 'LPAW'
and b.ob_cntr_close_datetime between to_date('2011/09/18', 'yyyy/mm/dd') and to_date('2011/09/27', 'yyyy/mm/dd')
group by trunc(b.ob_cntr_close_datetime)
)t1, t;