select col1,col2,sum(cnt) from table_day where querytime>=to_date('20140101','yyyymmdd') and querytime<to_date('20150101','yyyymmdd') group by col1,col2
先每个分区分组求和,再把求得结果加总: nsert into table1 select col1, col2, sum(cnt) from ( select querytime,col1,col2,sum(cnt) as cnt from table_day where querytime>=to_date('20140101') and querytime<to_date('20150101') group by querytime,col1,col2 ) group by col1, col2
group by col1,col2
你如果按天分区,分区列是querytime,按就用到分区查询,
千万级这样查询即可。
如果分区列不是querytime,要想用分区查询,查询中要用到
分区列。
针对这个查询,可以建(querytime,col1,col2)的组合索引
nsert into table1
select col1, col2, sum(cnt)
from ( select querytime,col1,col2,sum(cnt) as cnt
from table_day
where querytime>=to_date('20140101') and querytime<to_date('20150101')
group by querytime,col1,col2 )
group by col1, col2