执行这样一个语句,查询数据约10000条,从1200万条数据中,十分钟了还没结果,请教高手给出意见,看看能怎么改。
select count(*) num_1,
decode(sign(substr(f.PURCHASE_TIME,1,4) - '2005'),0,'2005',1,substr(f.PURCHASE_TIME,1,4),'-1','2005') sub_1,
t.CUSTOMER_TYPE
from FOUNDER_STAT.TBL_PRODUCT f,FOUNDER_STAT.TBL_CUSTOMER t
where f.customer_no = t.customer_no
and t.customer_status = '1'
and f.purchase_time >= '20090101'
and f.purchase_time <= '20090202'
group by decode(sign(substr(f.PURCHASE_TIME,1,4) - '2005'),0,'2005',1,substr(f.PURCHASE_TIME,1,4),'-1','2005'), t.CUSTOMER_TYPE
select count(*) num_1,
decode(sign(substr(f.PURCHASE_TIME,1,4) - '2005'),0,'2005',1,substr(f.PURCHASE_TIME,1,4),'-1','2005') sub_1,
t.CUSTOMER_TYPE
from FOUNDER_STAT.TBL_PRODUCT f,FOUNDER_STAT.TBL_CUSTOMER t
where f.customer_no = t.customer_no
and t.customer_status = '1'
and f.purchase_time >= '20090101'
and f.purchase_time <= '20090202'
group by decode(sign(substr(f.PURCHASE_TIME,1,4) - '2005'),0,'2005',1,substr(f.PURCHASE_TIME,1,4),'-1','2005'), t.CUSTOMER_TYPE
条件:
and f.purchase_time >= '20090101'
and f.purchase_time <= '20090202'
然后:
sign(substr(f.PURCHASE_TIME,1,4) - '2005')的结果为0,1,-1
???
select count(主键) num_1,
decode(sign(substr(f.PURCHASE_TIME,1,4) - '2005'),1,substr(f.PURCHASE_TIME,1,4),'2005') sub_1,t.CUSTOMER_TYPE
from FOUNDER_STAT.TBL_PRODUCT f,FOUNDER_STAT.TBL_CUSTOMER t
where f.customer_no = t.customer_no
and t.customer_status = '1'
and f.purchase_time >= '20090101'
and f.purchase_time <= '20090202'
group by decode(sign(substr(f.PURCHASE_TIME,1,4) - '2005'),1,substr(f.PURCHASE_TIME,1,4),'2005'),
t.CUSTOMER_TYPE
customer_status 上建立索引。
还不行,就贴执行计划
05及以前 06 07 08 09
个人 1 2 3 5 5
单位 3 4 5 5 5
我觉得应该不算太复杂吧?为啥PL执行半天也执行不完?跟死机了一样。
另外,customer_status不能索引。
我是做报表用的,不是只要结果,所以也不能用JOB。各位还有啥好主意没?
1楼的怀疑没问题,库里存的就是20080808这样的格式。
应该是这样,
and f.purchase_time >= '20090101'
and f.purchase_time <= '20090202'
而其从你的需求来看,你写的这个SQL貌似达成不了你的目标。
decode(sign(substr(f.PURCHASE_TIME,1,4) - '2005'),0,'2005',1,substr(f.PURCHASE_TIME,1,4),'-1','2005') sub_1,
t.CUSTOMER_TYPE
from FOUNDER_STAT.TBL_PRODUCT f,FOUNDER_STAT.TBL_CUSTOMER t
where f.customer_no = t.customer_no
and t.customer_status = '1'
and f.purchase_time > '20081231' //换成
and f.purchase_time < '20090203'
group by decode(sign(substr(f.PURCHASE_TIME,1,4) - '2005'),0,'2005',1,substr(f.PURCHASE_TIME,1,4),'-1','2005'), t.CUSTOMER_TYPE
and f.purchase_time >= '2008'
and f.purchase_time <= '2009'
日期能直接这么用吗?
左边的是字符川‘2008’,你不做任何处理这么样能比较吗?
表结构是这样的,
表tbl_customer
customer_no,customer_status,customer_type
a1234 1 个人用户
a1224 0 企业用户
表tbl_product 每条记录对应一条产品
customer,purchase_time,product_no
a1234 2006-06-04 ****8
a1224 2008-08-08 ****9
目的是查出
2005及以前 2006
单位用户 购买数量 购买数量
个人用户 购买数量 购买数量刚才按6楼的办法查了一下,还是没结果。
and f.purchase_time >= '2008' 应该是 substr(f.PURCHASE_TIME,1,4) >='2008'
业务上就是两个维度分组
按 customer_type分类,按purchase_time分类
中间是记录数量。
不知道我说明白了没有