当前位置:Gxlcms > mysql > HSQL语句,说说HSQLunionall的用法

HSQL语句,说说HSQLunionall的用法

时间:2021-07-01 10:21:17 帮助过:32人阅读

今天,在写hive的HSQL语句,又是重复性的计算pv、uv(不爽),而且还是,算完分类算总类,就比如:算pc端的pv、uv,移动端的pv、uv,然后又要计算总的pv、uv,总的pv还好说,pc+移动端就OK了,但uv就得重新排重了,每次遇到这样的事情就非常不爽,因为不能快

今天,在写hive 的HSQL语句,又是重复性的计算pv、uv(不爽),而且还是,算完分类算总类,就比如:算pc端的pv、uv,移动端的pv、uv,然后又要计算总的pv、uv,总的pv还好说,pc+移动端就OK了,但uv就得重新排重了,每次遇到这样的事情就非常不爽,因为不能快速在一个HSQL中处理(可能自己有点强迫症吧),于是自己挤出上班时间测试了几种不同的写法,对比效率
好了废话不多说,上代码了 <无>
1、以前统计总量pv,uv和各分类的pv,uv都这么写也就是
SELECT a.type,a.pv,a.uv FROM 
(
   SELECT type,count(1) as pv,COUNT(distinct(uid))as uv 
   FROM t1 
   WHERE dt='201410129' AND req_url like 'mbloglist?domain=100808&ajwvr=6%' 
   group by type
 union all
   SELECT 'all' as type,count(1) as pv,COUNT(distinct(uid))as uv 
   FROM t1 
   WHERE dt='201410129' AND req_url like 'mbloglist?domain=100808&ajwvr=6%' 
) a
说明:distinct虽然写起来挺方便的,但是效率真的太差,建议永远不要用distinct
2、然后我们的语句就可以改为:
SELECT a.type,sum(pv),count(uid) FROM 
(
   SELECT type,count(1) as pv,uid
   FROM t1 
   WHERE dt='201410129' AND req_url like 'mbloglist?domain=100808&ajwvr=6%' 
   group by uid,type
 union all
   SELECT 'all' as type,count(1) as pv,uid
   FROM t1 
   WHERE dt='201410129' AND req_url like 'mbloglist?domain=100808&ajwvr=6%' 
   group by uid
) a
group by type
这样虽然效率提高了些,而且我也一直这么用了,有段时间,但总感觉还是很不爽,总觉得没有发挥union all的功能
3、今天才发现,这group by 不能写在里面,真的严重影响效率,而且按照上面写job数量还多,果断需改:
SELECT type,SUM(pv),count(uid) FROM (
SELECT a.type,sum(pv),uid FROM 
(
   SELECT type,1 as pv,uid
   FROM t1 
   WHERE dt='201410129' AND req_url like 'mbloglist?domain=100808&ajwvr=6%' 
 union all
   SELECT 'all' as type,1 as pv,uid
   FROM t1 
   WHERE dt='201410129' AND req_url like 'mbloglist?domain=100808&ajwvr=6%' 
) a
group by uid,type) b group by type
经测试,效率果然杠杠的

人气教程排行