- 积分
- 180
- UID
- 14480
- 阅读权限
- 20
- 注册时间
- 2013-9-13
- 精华
- 在线时间
- 小时
- 最后登录
- 1970-1-1
- 职业
- 1
|
-- 创建分区表的sql
CREATE TABLE netflow_report(
netflow bigint,
stat_time datetime,
domain VARCHAR(100),
filename VARCHAR(255),
source VARCHAR(20))
PARTITION BY RANGE(TO_DAYS(stat_time))
(
PARTITION p_20140323 VALUES LESS THAN (TO_DAYS('2014-03-24')),
PARTITION p_20140324 VALUES LESS THAN (TO_DAYS('2014-03-25')),
PARTITION p_20140325 VALUES LESS THAN (TO_DAYS('2014-03-26')),
PARTITION p_20140326 VALUES LESS THAN (TO_DAYS('2014-03-27')),
PARTITION p_20140327 VALUES LESS THAN (TO_DAYS('2014-03-28')),
PARTITION p_20140328 VALUES LESS THAN (TO_DAYS('2014-03-29')),
PARTITION p_20140329 VALUES LESS THAN (TO_DAYS('2014-03-30')),
PARTITION p_20140330 VALUES LESS THAN (TO_DAYS('2014-03-31')),
PARTITION p_20140331 VALUES LESS THAN (TO_DAYS('2014-04-01'))
);
--在客户端解释的sql
select cast(substr(stat_time,1,16) as CHAR)stat_time,domain,
sum(FLOOR(netflow/(10*60))) as netflow
from netflow where stat_time between '2014-03-25 00:00:00'
and '2014-03-25 23:59:59'
group by substr(stat_time,1,16),domain
order by stat_time desc
-- 解释结果
problem: rows还是所有的行数,这是为什么呢?partitions处为什么会显示两个呢?不应该只有个p_20140325吗?
|
|