从头学习大数据培训课程 数据仓储工具 hive（五）hive 的 grouping sets、排序、窗口函数用法
最新回答
选择奔跑
2026-10-30 17:32:57
GROUPING SETS 功能允许您按照不同的组合分组数据。语法如下：SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( (a, b), a, b, () )。这等于 SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b UNION ALL SELECT a, null, SUM( c ) FROM tab1 GROUP BY a UNION ALL SELECT null, b, SUM( c ) FROM tab1 GROUP BY b UNION ALL SELECT null, null, SUM( c ) FROM tab1。
示例：select country,gpcategory,count(1) from user_install_status_mid where dt=20141228 group by country,gpcategory grouping sets((country,gpcategory),(country),(gpcategory),()) limit 10; 等同于 select * from (select country,gpcategory,count(1) from user_install_status_mid where dt=20141228 group by country,gpcategory union all select country,null,count(1) from user_install_status_mid where dt=20141228 group by country union all select null,gpcategory,count(1) from user_install_status_mid where dt=20141228 group by ,gpcategory union all select null,null,count(1) from user_install_status_mid where dt=20141228 ) a limit 10;
使用IF、CASE，可以进一步增加灵活度。例如：select if(country is not null,country,'ALL') as country, case when gpcategory is not null then gpcategory else 'ALL' end as gpcategory, count(1) as num from user_install_status_mid where dt=20141228 group by country,gpcategory grouping sets((country,gpcategory),(country),(gpcategory),()) limit 100;
WITH CUBE 是将所有 key 的组合进行分组，WITH ROLLUP 则是按照右侧递减的顺序组合。
排序
ORDER BY 会进行全局排序，而 SORT BY 只保证单个文件内有序，且不保证全局排序。DISTIBUTE BY 则是将相同的 key 分到一个 reducer 中，而 CLUSTER BY 则是在 DISTIBUTE BY 的基础上加上 SORT BY。
示例：设置 ORDER BY 仅有一个 reducer，但其不会起作用。set mapred.reduce.tasks=42; select * from user_install_status where dt=20141228 order by uptime limit 100; 设置的 reducer 数量不起作用。
SORT BY 只保证单个文件内有序，如果设置为一个 reducer，则其作用与 ORDER BY 相同。set mapred.reduce.tasks=42; insert overwrite directory '/user/hainiu/user_install_status_select_sort' select * from user_install_status where dt=20141228 sort by uptime;
CLUSTER BY 是 DISTIBUTE BY 和 SORT BY 的结合。示例：set mapred.reduce.tasks=42; insert overwrite directory '/user/hainiu/user_install_status_select_cluster' select from user_install_status where dt=20141228 cluster by country; 等同于 select from user_install_status where dt=20141228 distribute by country sort by country;
窗口函数
ROW_NUMBER() 功能统计当前行号，从1开始。示例：insert overwrite directory '/user/hainiu/user_install_status_over_partition' select country,row_number() over(partition by country order by uptime) as rn from user_install_status where dt='20141228';
RANK() 为排名统计，若值相同则后面的值保持不变，当出现不同值时从真实行数开始计算。DENSE_RANK() 类似于 RANK()，只是在出现不同值时按位数计算。示例：select country,n, RANK() over (partition by country order by n) as rank, DENSE_RANK() over (partition by country order by n) as drank from( select country,count(1) n from user_install_status_mid where dt=20141228 group by country,gpcategory ) a;
NTILE() 是分片函数，将数据等分为 N 份。示例：select country,n,NTILE(5) over (order by n desc) t from (select country,count(1) n from user_install_status_mid where dt=20141228 group by country) a;
窗口函数中的行选择器 ROWS BETWEEN [n|unbounded preceding]|[n|unbounded following]|[current row] and [n|unbounded preceding]|[n|unbounded following]|[current row] 参数解释：n 表示行数，unbounded 表示不限行数，preceding 表示前 n 行，following 表示后 n 行，current row 表示当前行。示例：select country,n,sum(n) over (partition by country order by n asc ROWS BETWEEN 1 preceding AND 1 following) t,gpcategory from (select country,gpcategory,count(1) n from user_install_status_mid where dt=20141228 group by country,gpcategory) a;
热门标签
