下面这个sql能不能优化啊，急等高手解答

骑猪去东莞 2011-09-19 10:40:16
计算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 .....
...全文
236 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
shiyiwan 2011-09-19
  • 打赏
  • 举报
 回复
前面那个可能会产生大量重复记录，小做修改如下，代码没有测试。
主要思想就是使用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

);
shiyiwan 2011-09-19
  • 打赏
  • 举报
 回复 
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 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)

);
xiaobn_cn 2011-09-19
  • 打赏
  • 举报
 回复
上面的SQL有错误，在此更正。Ps:SQL没有经过调试，楼主使用前自己调试下。


select count(*)

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 ;
xiaobn_cn 2011-09-19
  • 打赏
  • 举报
 回复
不是很明白这条SQL是完成什么功能的，简单分析了一下，得到的结果集是27日的数据每条出现1次，26日的数据每条出现2次，25日的数据每条出现3次，后面的日期以此类推。。。。。。

如果楼主的需求就是如此，那么这条SQL没有优化的可能。如

楼主提到要统计每日交柜量百分比，这个百分比的分母是什么？

最后，提供给楼主1个可以统计每日数量的SQL以供参考。


select count(*)

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 ;
秋雨飘落 2011-09-19
  • 打赏
  • 举报
 回复

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')
除了索引以外，其它没什么好办法来优化了。
Rotel-刘志东 2011-09-19
  • 打赏
  • 举报
 回复
这样的写法怎么执行效率会高呢？
第一种：create table #temp ---临时表
如果在应用程序用的话最好还是采取这种方式效率还是比较高的
然后把查询的结果放在临时表中，在应用程序中调用临时表中的数据，这样的效率会高些的。


如果单纯的查询时间段的数据就可以无需用union all
骑猪去东莞 2011-09-19
  • 打赏
  • 举报
 回复
[Quote=引用 10 楼 shiyiwan 的回复:]
修改了下可能的语法错误，私信发给你了
[/Quote]
多谢，我知道怎么弄了，呵呵
shiyiwan 2011-09-19
  • 打赏
  • 举报
 回复
修改了下可能的语法错误，私信发给你了
骑猪去东莞 2011-09-19
  • 打赏
  • 举报
 回复
[Quote=引用 8 楼 shiyiwan 的回复:]
引用 7 楼 xiewenping 的回复:
这个sum_add_by_day表示什么意思哇


起的一个别名，没有特别的意义。#6可能还是有重复记录，试试下面这个吧

SQL code


with t as (select count(*) base_sum from vessel_move_log a,
voyage_member b
where a.v……
[/Quote]
这个是有问题的，我觉得#6那个才能查出真正的值
shiyiwan 2011-09-19
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 xiewenping 的回复:]
这个sum_add_by_day表示什么意思哇
[/Quote]

起的一个别名，没有特别的意义。#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;
骑猪去东莞 2011-09-19
  • 打赏
  • 举报
 回复
[Quote=引用 6 楼 shiyiwan 的回复:]
前面那个可能会产生大量重复记录，小做修改如下，代码没有测试。
主要思想就是使用sum() over() 函数进行累加计算

SQL code


with t as (select count(*) base_sum from vessel_move_log a,
voyage_member b
where a.voyid_company_code ……
[/Quote] 这个sum_add_by_day表示什么意思哇
SQLSERVER完全优化教程
1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 　我们把这种 正文内容 本身就是一种按照一定规则排列的 目录 称为“聚集索引”。 　需要两个过程，先找到目录中的结果，然后再翻到您所...
DBA专家门诊一期：索引与sql优化
 非常感谢大家踊跃参加DBA专家门诊一期：索引与sql优化，很多云友都提出了自己的问题，门诊主任医师玄惭对大家提的问题一一作了解答。现已整理好这些问题，分享在此，欢迎来拿，绝对干货！ 篇幅较长，耐心细看！  ...
浅谈SQL Server内部运行机制
对于已经很熟悉T-SQL的读者，或者对于较...然而，要向成为一个好的Sql优化高手，首先要做的一件事无疑就是了解sql语句在SQL Server中是如何执行的。在这一系列中，我们将开始sqlserver优化系列讲解，本 讲为优化系...
干货分享：DBA专家门诊一期：索引与sql优化问题汇总
 非常感谢大家踊跃参加DBA专家门诊一期：索引与sql优化，很多云友都提出了自己的问题，门诊主任医师玄惭对大家提的问题一一作了解答。现已整理好这些问题，分享在此，欢迎来拿，绝对干货！ 篇幅较长，耐心细看！...
SQL性能调优实践——SELECT COUNT
最近想深入学习SQL，在网上搜索到一些SQL 优化的资料要么是张冠李戴，Oracle 优化的资料硬是弄成啦MS SQL 优化的资料，而且被很多人转载，收藏，有些要么有些含糊不清，好像是那么回事，也没经过验证，实践出真知...
Oracle 高级技术

3,491

社区成员

18,714

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章