group by
sql 查询时,我们常将聚合函数和group by 结合起来对某一个或多个字段进行分组查询,例如:
select addcode,count(distinct sbtid)uv from tb_hive_window group by addcode;+----------+-----+| addcode | uv |+----------+-----+| 0002 | 2 || 000201 | 1 || 000202 | 1 || 000205 | 1 || 000206 | 1 || 000208 | 2 |+----------+-----+
group by fields ... grouping sets ()
有时候因业务需要,我们需要group by 多个字段,例如:
select addcode,count(distinct sbtid)uv from tb_hive_window group by addcode;select rscode,count(distinct sbtid)uv from tb_hive_window group by rscode;select addcode,rscode,count(distinct sbtid)uv from tb_hive_window group by addcode,rscode;//这种情况我们需要写3条sql 语句
通过grouping sets (), 我们可以通过一条sql 完成,相当于是将上面三条语句执行的结果通过union all 组合起来。
select addcode,rscode,count(*)pv,count(distinct sbtid)uv,grouping__id from tb_hive_window group by addcode,rscode grouping sets ((addcode,rscode),(addcode),(rscode));+----------+---------+-----+-----+---------------+| addcode | rscode | pv | uv | grouping__id |+----------+---------+-----+-----+---------------+| NULL | 34 | 1 | 1 | 2 || NULL | 35 | 5 | 3 | 2 || NULL | 40 | 1 | 1 | 2 || NULL | 65 | 1 | 1 | 2 || NULL | 351 | 1 | 1 | 2 || NULL | 352 | 1 | 1 | 2 || NULL | 395