爱华网作者：万昆日期：20130605
- 准备表和测试数据
- 建表
create tablegroup_test (group_id int, job string, name string, salaryint);
- 准备数据文件
10 Coding Bruce 1000
10 Programmer Clair1000
10 Architect Gideon1000
10 Director Hill 1000
20 Coding Jason 2000
20 Programmer Joey 2000
20 Architect Martin2000
20 Director Michael2000
30 Coding Rebecca 3000
30 Programmer Rex 3000
30 Architect Richard3000
30 Director Sabrina3000
40 Coding Samuel 4000
40 Programmer Susy 4000
40 Architect Tina 4000
40 Director Wendy4000
保存文件为/tmp/g.txt
- 加载数据
load data localinpath '/tmp/g.txt' into table group_test;
- 传统函数测试
select * fromgroup_test;
selectgroup_id,job,sum(salary) from group_test group bygroup_id,job;
selectgroup_id,sum(salary) from group_test group by group_id;
- 函数测试及结果
- Cube功能测试
selectgroup_id,job,sum(salary) from group_test group by group_id, jobwith cube ; -- with cube功能
测试结果：
NULL NULL40000
NULL Architect10000
NULL Coding10000
NULL Director10000
NULL Programmer10000
10 NULL4000
10 Architect1000
10 Coding1000
10 Director1000
10 Programmer1000
20 NULL8000
20 Architect2000
20 Coding2000
20 Director2000
20 Programmer2000
30 NULL12000
30 Architect3000
30 Coding3000
30 Director3000
30 Programmer3000
40 NULL16000
40 Architect4000
40 Coding4000
40 Director4000
40 Programmer4000
- Rullup测试
selectgroup_id,job,sum(salary) from group_test group by group_id, jobwith rollup ;
测试结果
NULL NULL40000
10 NULL4000
10 Architect1000
10 Coding1000
10 Director1000
10 Programmer1000
20 NULL8000
20 Architect2000
20 Coding2000
20 Director2000
20 Programmer2000
30 NULL12000
30 Architect3000
30 Coding3000
30 Director3000
30 Programmer3000
40 NULL16000
40 Architect4000
40 Coding4000
40 Director4000
40 Programmer4000
