1、cube简称数据魔方,可以实现hive多个任意维度的查询,cube(a,b,c)则首先会对(a,b,c)进行group by,然后依次是(a,b),(a,c),(a),(b,c),(b),(c),最后在对全表进行group by,他会统计所选列中值的所有组合的聚合

select device_id,os_id,app_id,client_version,from_id,count(user_id) from test_xinyan_reg group by device_id,os_id,app_id,client_version,from_id with cube;

此语句相当于group by后所有字段的排列组合,然后将结果union all起来

rollup可以实现从右到做递减多级的统计,显示统计某一层次结构的聚合。

相当于:

SELECT device_id,null,null,null,null ,count(user_id) FROM test_xinyan_reg group by device_id

UNION ALL

SELECT null,os_id,null,null,null ,count(user_id) FROM test_xinyan_reg group by os_id

UNION ALL

SELECT device_id,os_id,null,null,null ,count(user_id) FROM test_xinyan_reg group by device_id,os_id

UNION ALL

SELECT null,null,app_id,null,null ,count(user_id) FROM test_xinyan_reg group by app_id

UNION ALL

SELECT device_id,null,app_id,null,null ,count(user_id) FROM test_xinyan_reg group by device_id,app_id

UNION ALL

SELECT null,os_id,app_id,null,null ,count(user_id) FROM test_xinyan_reg group by os_id,app_id

UNION ALL

SELECT device_id,os_id,app_id,null,null ,count(user_id) FROM test_xinyan_reg group by device_id,os_id,app_id

UNION ALL

SELECT null,null,null,client_version,null ,count(user_id) FROM test_xinyan_reg group by client_version

UNION ALL

SELECT device_id,null,null,client_version,null ,count(user_id) FROM test_xinyan_reg group by device_id,client_version

UNION ALL

SELECT null,os_id,null,client_version,null ,count(user_id) FROM test_xinyan_reg group by os_id,client_version

UNION ALL

SELECT device_id,os_id,null,client_version,null ,count(user_id) FROM test_xinyan_reg group by device_id,os_id,client_version

UNION ALL

SELECT null,null,app_id,client_version,null ,count(user_id) FROM test_xinyan_reg group by app_id,client_version

UNION ALL

SELECT device_id,null,app_id,client_version,null ,count(user_id) FROM test_xinyan_reg group by device_id,app_id,client_version

UNION ALL

SELECT null,os_id,app_id,client_version,null ,count(user_id) FROM test_xinyan_reg group by os_id,app_id,client_version

UNION ALL

SELECT device_id,os_id,app_id,client_version,null ,count(user_id) FROM test_xinyan_reg group by device_id,os_id,app_id,client_version

UNION ALL

SELECT null,null,null,null,from_id ,count(user_id) FROM test_xinyan_reg group by from_id

UNION ALL

SELECT device_id,null,null,null,from_id ,count(user_id) FROM test_xinyan_reg group by device_id,from_id

UNION ALL

SELECT null,os_id,null,null,from_id ,count(user_id) FROM test_xinyan_reg group by os_id,from_id

UNION ALL

SELECT device_id,os_id,null,null,from_id ,count(user_id) FROM test_xinyan_reg group by device_id,os_id,from_id

UNION ALL

SELECT null,null,app_id,null,from_id ,count(user_id) FROM test_xinyan_reg group by app_id,from_id

UNION ALL

SELECT device_id,null,app_id,null,from_id ,count(user_id) FROM test_xinyan_reg group by device_id,app_id,from_id

UNION ALL

SELECT null,os_id,app_id,null,from_id ,count(user_id) FROM test_xinyan_reg group by os_id,app_id,from_id

UNION ALL

SELECT device_id,os_id,app_id,null,from_id ,count(user_id) FROM test_xinyan_reg group by device_id,os_id,app_id,from_id

UNION ALL

SELECT null,null,null,client_version,from_id ,count(user_id) FROM test_xinyan_reg group by client_version,from_id

UNION ALL

SELECT device_id,null,null,client_version,from_id ,count(user_id) FROM test_xinyan_reg group by device_id,client_version,from_id

UNION ALL

SELECT null,os_id,null,client_version,from_id ,count(user_id) FROM test_xinyan_reg group by os_id,client_version,from_id

UNION ALL

SELECT device_id,os_id,null,client_version,from_id ,count(user_id) FROM test_xinyan_reg group by device_id,os_id,client_version,from_id

UNION ALL

SELECT null,null,app_id,client_version,from_id ,count(user_id) FROM test_xinyan_reg group by app_id,client_version,from_id

UNION ALL

SELECT device_id,null,app_id,client_version,from_id ,count(user_id) FROM test_xinyan_reg group by device_id,app_id,client_version,from_id

UNION ALL

SELECT null,os_id,app_id,client_version,from_id ,count(user_id) FROM test_xinyan_reg group by os_id,app_id,client_version,from_id

UNION ALL

SELECT device_id,os_id,app_id,client_version,from_id ,count(user_id) FROM test_xinyan_reg group by device_id,os_id,app_id,client_version,from_id

UNION ALL

SELECT null,null,null,null,null ,count(user_id) FROM test_xinyan_reg

2、 rollup可以实现从右到左递减多级的统计,显示统计某一层次结构的聚合。

select device_id,os_id,app_id,client_version,from_id,count(user_id) from test_xinyan_reg group by device_id,os_id,app_id,client_version,from_id with rollup;

相当于

select device_id,os_id,app_id,client_version,from_id,count(user_id)

from test_xinyan_reg

group by device_id,os_id,app_id,client_version,from_id

grouping sets ((device_id,os_id,app_id,client_version,from_id),(device_id,os_id,app_id,client_version),(device_id,os_id,app_id),(device_id,os_id),(device_id),());

最新文章

  1. 辛巴学院-Unity-剑英陪你零基础学c#系列(二)顺序
  2. JS之Form表单相关操作
  3. MongoDB学习笔记九:分片
  4. QBC
  5. Winform 窗体单例
  6. Wo的书单
  7. Tiny6410 LED字符设备驱动
  8. javascript——可以判断值的类型的函数
  9. HDU 3507 Print Article(CDQ分治+分治DP)
  10. KinectFusion解析
  11. 2.2 HOST主桥
  12. Duplicate <http> element detected
  13. 【Python】 用户图形界面GUI wxpython IV 菜单&对话框
  14. Linux 系统目录结构说明
  15. 后端api规范说明文档
  16. 【分享】用Canvas实现画板功能
  17. mysql表管理
  18. POSTMAN发送WebService接口
  19. oracle中如何修改用户名和密码
  20. LeetCode刷题 Flood Fill 洪水填充问题

热门文章

  1. 新手学习Linux之快速上手分析
  2. python->解析xml文件
  3. 深度学习基础(一)LeNet_Gradient-Based Learning Applied to Document Recognition
  4. ajax验证用户名是否被注册 ; ajax提交form表单
  5. Linux服务器可以进百度,但是进阿里云或者别的一些网站提示‘错误代码:NS_ERROR_NET_INADEQUATE_SECURITY’的问题
  6. 使用 Asp.net core 2.0 + Angular 4 构建车辆管理的Web应用程序
  7. tp5 model controlle sql
  8. beego 初体验 - 参数与传值
  9. 前端学习历程--js--原型&闭包
  10. 配置 ROS 的 apt 源