`

Hive开窗函数测试-cube,rollup

    博客分类:
  • Hive
 
阅读更多

作者:万昆 日期: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的开窗函数?

相关推荐

Global site tag (gtag.js) - Google Analytics