Hive开窗函数测试-cube，rollup

作者：万昆 日期：20130605

     

1. 准备表和测试数据
2. 建表

   create table group_test (group_id int, job string, name string, salary int);

        

3. 准备数据文件

        

   10 Coding Bruce 1000

   10 Programmer Clair 1000

   10 Architect Gideon 1000

   10 Director Hill 1000

   20 Coding Jason 2000

   20 Programmer Joey 2000

   20 Architect Martin 2000

   20 Director Michael 2000

   30 Coding Rebecca 3000

   30 Programmer Rex 3000

   30 Architect Richard 3000

   30 Director Sabrina 3000

   40 Coding Samuel 4000

   40 Programmer Susy 4000

   40 Architect Tina 4000

   40 Director Wendy 4000

        

   保存文件为/tmp/g.txt

4. 加载数据

   load data local inpath '/tmp/g.txt' into table group_test;

        

5. 传统函数测试

   select * from group_test;

   select group_id,job,sum(salary) from group_test group by group_id,job;

   select group_id,sum(salary) from group_test group by group_id;

        

6. 函数测试及结果
7. Cube功能测试

   select group_id,job,sum(salary) from group_test group by group_id, job with cube ; -- with cube功能

   测试结果：

   NULL NULL 40000

   NULL Architect 10000

   NULL Coding 10000

   NULL Director 10000

   NULL Programmer 10000

   10 NULL 4000

   10 Architect 1000

   10 Coding 1000

   10 Director 1000

   10 Programmer 1000

   20 NULL 8000

   20 Architect 2000

   20 Coding 2000

   20 Director 2000

   20 Programmer 2000

   30 NULL 12000

   30 Architect 3000

   30 Coding 3000

   30 Director 3000

   30 Programmer 3000

   40 NULL 16000

   40 Architect 4000

   40 Coding 4000

   40 Director 4000

40 Programmer 4000

     

1. Rullup测试

   select group_id,job,sum(salary) from group_test group by group_id, job with rollup ;

   测试结果

   NULL NULL 40000

   10 NULL 4000

   10 Architect 1000

   10 Coding 1000

   10 Director 1000

   10 Programmer 1000

   20 NULL 8000

   20 Architect 2000

   20 Coding 2000

   20 Director 2000

   20 Programmer 2000

   30 NULL 12000

   30 Architect 3000

   30 Coding 3000

   30 Director 3000

   30 Programmer 3000

   40 NULL 16000

   40 Architect 4000

   40 Coding 4000

   40 Director 4000

40 Programmer 4000

评论

5 楼 yugouai 2013-06-26  

我是用了0.11.0也是报同样的错误，是不是需要设置什么参数才能是用开窗函数？

4 楼 wankunde 2013-06-26  

yugouai 写道

你好，开窗函数的语法是您上面描述那样的吗？
hive> SELECT game,game_name,sum(weight) from dim_jzts_game group by game,game_name with cube;
FAILED: Parse Error: line 1:67 cannot recognize input near 'game_name' 'with' 'cube' in expression specification
dim_jzts_game表结构为：
hive> desc dim_jzts_game;
OK
game    string
weight  int
game_name       string
game_type       string
game_theme      string
game_style      string

为什么不对？

你的hive是什么版本的，我的是hive 0.11的，我测试了你的语句，完全没问题

3 楼 yugouai 2013-06-26  

你好，开窗函数的语法是您上面描述那样的吗？
hive> SELECT game,game_name,sum(weight) from dim_jzts_game group by game,game_name with cube;
FAILED: Parse Error: line 1:67 cannot recognize input near 'game_name' 'with' 'cube' in expression specification
dim_jzts_game表结构为：
hive> desc dim_jzts_game;
OK
game    string
weight  int
game_name       string
game_type       string
game_theme      string
game_style      string

为什么不对？

2 楼 wankunde 2013-06-09  

yugouai 写道

请问一下，什么叫hive的开窗函数？

这个是对于一组相同的数据进行数据汇总之类的统计函数，具体的解释，你可以百度oracle 开窗函数

1 楼 yugouai 2013-06-05  

请问一下，什么叫hive的开窗函数？