SELECT Commodity_ID,
COUNT(DISTINCT(CASE WHEN (Buy_Open_Qty>0 OR Sell_Open_Qty>0 OR Buy_Liq_Qty>0 OR Sell_Liq_Qty>0) AND LENGTH(Customer_ID)=4 THEN Customer_ID END)),
COUNT(DISTINCT(CASE WHEN (Buy_Open_Qty>0 OR Sell_Open_Qty>0 OR Buy_Liq_Qty>0 OR Sell_Liq_Qty>0) AND LENGTH(Customer_ID)>4 THEN Customer_ID END)),
COUNT(DISTINCT(CASE WHEN (Buy_Open_Qty>0 OR Buy_Liq_Qty>0) AND LENGTH(Customer_ID)=4 THEN Customer_ID END)),
COUNT(DISTINCT(CASE WHEN (Buy_Open_Qty>0 OR Buy_Liq_Qty>0) AND LENGTH(Customer_ID)>4 THEN Customer_ID END)),
COUNT(DISTINCT(CASE WHEN (Sell_Open_Qty>0 OR Sell_Liq_Qty>0) AND LENGTH(Customer_ID)=4 THEN Customer_ID END)),
COUNT(DISTINCT(CASE WHEN (Sell_Open_Qty>0 OR Sell_Liq_Qty>0) AND LENGTH(Customer_ID)>4 THEN Customer_ID END))
FROM TRADE_Customer_Commodity_Total
WHERE Trade_Date BETWEEN TO_DATE('20060718', 'yyyymmdd') AND TO_DATE('20060918', 'yyyymmdd')
AND LENGTH(Commodity_ID) = 2
GROUP BY Commodity_ID
=========================================
代码如上,总感觉用的时间长,大家帮看一下能不能优化。
COUNT(DISTINCT(CASE WHEN (Buy_Open_Qty>0 OR Sell_Open_Qty>0 OR Buy_Liq_Qty>0 OR Sell_Liq_Qty>0) AND LENGTH(Customer_ID)=4 THEN Customer_ID END)),
COUNT(DISTINCT(CASE WHEN (Buy_Open_Qty>0 OR Sell_Open_Qty>0 OR Buy_Liq_Qty>0 OR Sell_Liq_Qty>0) AND LENGTH(Customer_ID)>4 THEN Customer_ID END)),
COUNT(DISTINCT(CASE WHEN (Buy_Open_Qty>0 OR Buy_Liq_Qty>0) AND LENGTH(Customer_ID)=4 THEN Customer_ID END)),
COUNT(DISTINCT(CASE WHEN (Buy_Open_Qty>0 OR Buy_Liq_Qty>0) AND LENGTH(Customer_ID)>4 THEN Customer_ID END)),
COUNT(DISTINCT(CASE WHEN (Sell_Open_Qty>0 OR Sell_Liq_Qty>0) AND LENGTH(Customer_ID)=4 THEN Customer_ID END)),
COUNT(DISTINCT(CASE WHEN (Sell_Open_Qty>0 OR Sell_Liq_Qty>0) AND LENGTH(Customer_ID)>4 THEN Customer_ID END))
FROM TRADE_Customer_Commodity_Total
WHERE Trade_Date BETWEEN TO_DATE('20060718', 'yyyymmdd') AND TO_DATE('20060918', 'yyyymmdd')
AND LENGTH(Commodity_ID) = 2
GROUP BY Commodity_ID
=========================================
代码如上,总感觉用的时间长,大家帮看一下能不能优化。
解决方案 »
- oracle 表空间和用户的问题
- Linux oracle监听器问题(急)
- oracle DBLINK 着急!!!!!!!!!!!!1
- 两oracle服务器,A访问B的问题
- 调用CreateParameter后抛出的IDispatch Erro #3088错误
- Oracle92启动OracleOraHome92ClientCache服务出错!
- 如何使几个sql语句同时运行
- oracle 10 g 和DB2 V8.2S谁强啊?
- 为什么sql plus 连接不上了?
- 急,oracle9i+win2003,进系统时因为网卡连接太慢导致oracleYTH服务启动失败
- 一个建队列表的问题,请指教!!!
- 在客户端用 export 来dump文件,遇到error,求解
2.对Commodity_ID列建立函数索引:
create index ix_TRADE_Customer_Commodity_Total_l on TRADE_Customer_Commodity_Total (LENGTH(Commodity_ID));
3.能不用DISTINCT就不要用关于where条件,我只知道oracle分析语句的顺序是从右到左,如何执行不清楚
因为统计时要排除相同的客户号,所以要用DISTINCT()。
select ID,case when qty<>0 then l_qty/qty else 0 end
from (select ID,nvl(sum(case when flag='L' then l_qty end),0) as l_qty,
nvl(sum(Buy_Open_Qty+Sell_Open_Qty+Buy_Liq_Qty+Sell_Liq_Qty),0) as qty
from a
where Trade_Date between Date1 and Date2
group by ID)法二:
select ID,case when nvl(sum(Buy_Open_Qty+Sell_Open_Qty+Buy_Liq_Qty+Sell_Liq_Qty),0)=0 then 0
else nvl(sum(case when flag='L' then l_qty end),0)/nvl(sum(Buy_Open_Qty+Sell_Open_Qty+Buy_Liq_Qty+Sell_Liq_Qty),0)
from a where Trade_Date between Date1 and Date2
group by ID
====================
这两个哪个好一点,法二select里面相同的sum(Buy_Open....Liq_Qty)有两上,会不会执行两遍浪费时间?
sum2=sum(L_Qty)
我现在要求的是sum2和sum2/sum1两个值,
我是应该先用一个语句查出sum1,sum2的值再从结果中计算sum2/sum1,
还是应该一个语句查出sum2,sum2/sum1,在一个语句里查的话会出现
select case when sum1=0 then 0 else sum2/sum1 end from ....
这样有两个sum1 会不会重复计算它而浪费时间?
SELECT channels.channel_desc, SUM(amount_sold) AS channel_total
FROM sales, channels
WHERE sales.channel_id = channels.channel_id
GROUP BY channels.channel_desc
)
SELECT channel_desc, channel_total
FROM channel_summary
WHERE channel_total > (
SELECT SUM(channel_total) * 1/3
FROM channel_summary);CHANNEL_DESC CHANNEL_TOTAL
-------------------- -------------
Direct Sales 312829530