问题是这样的:我们秒数据表分区是按每天分区的 现在报表显示的时间比较慢,想优化一下 SQL如下:
select
ROUND(nvl(sum(case when to_number(speed) <= 20 then 1 else 0 end),0)/(case when count(*)=0 then 1 else count(*) end)*100,2) speeding_0_20,
ROUND(nvl(sum(case when to_number(speed) > 20 and to_number(vehicle_speed) <= 40 then 1 else 0 end),0)/(case when count(*)=0 then 1 else count(*) end)*100,2) speeding_20_40,
ROUND(nvl(sum(case when to_number(speed) > 40 and to_number(vehicle_speed) <= 60 then 1 else 0 end),0)/(case when count(*)=0 then 1 else count(*) end)*100,2) speeding_40_60,
ROUND(nvl(sum(case when to_number(speed) > 60 and to_number(vehicle_speed) <= 70 then 1 else 0 end),0)/(case when count(*)=0 then 1 else count(*) end)*100,2) speeding_60_70,
ROUND(nvl(sum(case when to_number(speed) > 70 and to_number(vehicle_speed) <= 80 then 1 else 0 end),0)/(case when count(*)=0 then 1 else count(*) end)*100,2) speeding_70_80,
ROUND(nvl(sum(case when to_number(speed) > 80 and to_number(vehicle_speed) <= 90 then 1 else 0 end),0)/(case when count(*)=0 then 1 else count(*) end)*100,2) speeding_80_90,
ROUND(nvl(sum(case when to_number(speed) > 90 and to_number(vehicle_speed) <= 100 then 1 else 0 end),0)/(case when count(*)=0 then 1 else count(*) end)*100,2) speeding_90_100,
ROUND(nvl(sum(case when to_number(speed) > 100 then 1 else 0 end),0)/(case when count(*)=0 then 1 else count(*) end)*100,2) speeding_100_140
from A_B_C_data_t
where vehicle_speed !='FFFF'
and vehicle_vin = '12345678'
and vehicle_speed is not null
and TEMINAL_TIME < add_months(to_date('2011-06', 'yyyy-mm'), 1)
and TEMINAL_TIME >= to_date('2011-06', 'yyyy-mm')
这里没有加分区条件 partition ("SEC_DATA_20110627") 我想问一下 可不可以 分区条件 是一个月的呢 因为报表时按一个月来统计的 而分区时按天来分区的
select
ROUND(nvl(sum(case when to_number(speed) <= 20 then 1 else 0 end),0)/(case when count(*)=0 then 1 else count(*) end)*100,2) speeding_0_20,
ROUND(nvl(sum(case when to_number(speed) > 20 and to_number(vehicle_speed) <= 40 then 1 else 0 end),0)/(case when count(*)=0 then 1 else count(*) end)*100,2) speeding_20_40,
ROUND(nvl(sum(case when to_number(speed) > 40 and to_number(vehicle_speed) <= 60 then 1 else 0 end),0)/(case when count(*)=0 then 1 else count(*) end)*100,2) speeding_40_60,
ROUND(nvl(sum(case when to_number(speed) > 60 and to_number(vehicle_speed) <= 70 then 1 else 0 end),0)/(case when count(*)=0 then 1 else count(*) end)*100,2) speeding_60_70,
ROUND(nvl(sum(case when to_number(speed) > 70 and to_number(vehicle_speed) <= 80 then 1 else 0 end),0)/(case when count(*)=0 then 1 else count(*) end)*100,2) speeding_70_80,
ROUND(nvl(sum(case when to_number(speed) > 80 and to_number(vehicle_speed) <= 90 then 1 else 0 end),0)/(case when count(*)=0 then 1 else count(*) end)*100,2) speeding_80_90,
ROUND(nvl(sum(case when to_number(speed) > 90 and to_number(vehicle_speed) <= 100 then 1 else 0 end),0)/(case when count(*)=0 then 1 else count(*) end)*100,2) speeding_90_100,
ROUND(nvl(sum(case when to_number(speed) > 100 then 1 else 0 end),0)/(case when count(*)=0 then 1 else count(*) end)*100,2) speeding_100_140
from A_B_C_data_t
where vehicle_speed !='FFFF'
and vehicle_vin = '12345678'
and vehicle_speed is not null
and TEMINAL_TIME < add_months(to_date('2011-06', 'yyyy-mm'), 1)
and TEMINAL_TIME >= to_date('2011-06', 'yyyy-mm')
这里没有加分区条件 partition ("SEC_DATA_20110627") 我想问一下 可不可以 分区条件 是一个月的呢 因为报表时按一个月来统计的 而分区时按天来分区的
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货