sql语句的语法&＃xff1a;
select ..... from .... join ..... where .....group by ... having...order by|sort by|cluster by|distribute by ....
&＃xff08;1&＃xff09;group by&＃xff1a;按照某些字段的值进行分组&＃xff0c;相同的一组。注意&＃xff1a;限制了select字段&＃xff0c;发生在select之前&＃xff0c;不可以使用select别名&＃xff0c;select后面非聚合列&＃xff0c;必须出现在group by中 select后面除了普通列就是一些聚合操作 group by后面也可以跟表达式。
&＃xff08;2&＃xff09;where和having&＃xff1a;都是限定返回的数据集。
where&＃xff1a;对查询结果进行分组前&＃xff0c;将不符合where条件的行去掉&＃xff0c;即在分组之前过滤数据&＃xff0c;条件中不能包含聚组函数&＃xff0c;使用where条件显示特定的行。
having&＃xff1a;在分组group by后&＃xff0c;将分组后的不符合条件的数据过滤掉&＃xff0c;
查询语句的聚合函数只能出现在select 和group by ,having 子句。
&＃xff08;3&＃xff09;四个排序关键字
①order by&＃xff1a;对输入做全局排序&＃xff0c;所以只有一个reduce&＃xff0c;当数据量大时&＃xff0c;导致效率低&＃xff1b;
②sort by&＃xff1a;局部排序&＃xff0c;不是全局排序&＃xff0c;其在数据进入 reducer 前完成排序。如果用 sort by 进行排序&＃xff0c;并且设置 mapred.reduce.tasks>1&＃xff0c;那么 sort by只保证每个 reducer 的输出有序&＃xff0c;不能保证全局有序&＃xff1b;
③distribute by&＃xff1a;根据指定的字段将数据分到不同的 reducer&＃xff0c;分发算法是 hash 散列&＃xff1b;
④cluster by&＃xff1a;如果distribute by后面的字段和sort by后面的字段一样&＃xff0c;cluster by &＃61; distribute by &＃43; sort by
聚合函数可以将多行数据按照规则聚集为一行&＃xff0c;一般来讲聚集后的行数是要少于聚集前的行数的&＃xff0c;但是有时我们想要既显示聚集前的数据&＃xff0c;又要显示聚集后的数据&＃xff0c;这时我们便引入了窗口函数。窗口函数的执行时间且仅位于Order by字句之前。
分析函数 over&＃xff08;开窗依据distribute by |partition by 每一个窗口的排序规则 sort by |order by &＃xff09; 子句 窗口函数 开窗函数
distribute by与 sort by搭配&＃xff0c;partition by与order by 。
over子句不可单独使用&＃xff0c;有两种搭配方式&＃xff1a;
&＃xff08;1&＃xff09;与聚合函数一起使用&＃xff0c;求的是每一个窗口内的聚合
①聚合函数 &＃43; over(distribute by ) 整个窗口的整体聚合
select max(sumpv) over(distribute by userid) from test;
②聚合函数 &＃43; over(distribute by sort by ) 截止到排序数据之前的&＃xff0c;即当前。sort by子句会让输入的数据强制排序
执行顺序&＃xff1a;distribute by 整个窗口 &＃43; sort by一条 &＃43; max
select userid,month,sumpv,max(sumpv) over(distribute by userid sort by sumpv) from test;
③如果已经通过使用partition by子句将数据进行了分组的处理&＃xff0c;如果我们想要更细粒度的划分&＃xff0c;我们就要引入window子句了
window子句&＃xff1a; - PRECEDING&＃xff1a;往前 - FOLLOWING&＃xff1a;往后 - CURRENT ROW&＃xff1a;当前行
- UNBOUNDED&＃xff1a;起点 UNBOUNDED PRECEDING 从前面的起点 UNBOUNDED FOLLOWING&＃xff1a;到后面的终点
| select
sum(sumpv) over() as sumpvv1 #所有行相加
sum(sumpv) over(partition by userid) as sumpvv2 #按userid分组&＃xff0c;组内数据相加
sum(sumpv) over(partition by userid order by month) as sumpvv3 #按userid分组&＃xff0c;组内数据累加
sum(sumpv) over(partition by userid order by month rows between UNBOUNDED PRECEDING and current row) as sumpvv4 #按userid分组&＃xff0c;组内数据累加
sum(sumpv) over(partition by userid order by month rows between 1 PRECEDING and current row) as sumpvv5 #当前行和前面一行做聚合
sum(sumpv) over(partition by userid order by month rows between 1 PRECEDING and 1 FOLLOWING) as sumpvv6 #当前行和前边一行及后面一行
sum(sumpv) over(partition by userid order by month rows between current row and UNBOUNDED FOLLOWING) as sumpvv 7 #当前行及后面所有行
from test; |
④over(partition by)与group by的主要区别为&＃xff0c;带上group by的hql语句只能显示与分组聚合相关的字段&＃xff0c;而带上over(partition by ......)的hql语句能显示所有字段.。
&＃xff08;2&＃xff09;与序列函数使用
序列函数是不支持window子句的
①NTILE(n)&＃xff0c;用于将分组数据按照顺序切分成n片&＃xff0c;返回当前切片值&＃xff0c;如果切片不均匀&＃xff0c;默认增加第一个切片的分布
使用场景&＃xff1a;假如我们想要每位顾客购买数量前1/5的交易记录,我们便可以使用这个函数.
| select userid,month,sumpv, nitle(5) over() #全局数据切片 nitle(5) over(partition by userid) #按照userid进行分组&＃xff0c;在分组内切片 nitle(5) over(order by month) #全局按照月份升序排列&＃xff0c;数据切分成5份 nitle(5) over(partition by userid order by month) #按照userid进行分组&＃xff0c;在分组内按照月份升序排列&＃xff0c;数据切分成5份 from test; |
②row_number()、rank()、dense_rank()
row_number()&＃xff1a;生成分组内记录的序列,从1开始&＃xff0c;按照顺序,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列&＃xff1b;
RANK()&＃xff1a;生成数据项在分组中的排名&＃xff0c;排名相等会在名次中留下空位 ;
DENSE_RANK()&＃xff1a;生成数据项在分组中的排名&＃xff0c;排名相等会在名次中不会留下空位。
③lag和lead
LAG (scalar_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (scalar_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause);
这两个函数为常用的窗口函数,可以返回上下数据行的数据.
使用场景&＃xff1a;如要查看顾客上次的购买时间可以这样去查询
| select userid,month,sumpv, lag(month,1,&＃39;1900-01-01&＃39;) over(partition by userid order by month) #按照userid进行分组&＃xff0c;在分组内按照月份升序排列&＃xff0c;取上一行数据的值。当没有上一行时取1900-01-01&＃39; lag(month,2&＃39;) over(partition by userid order by month) #按照userid进行分组&＃xff0c;在分组内按照月份升序排列&＃xff0c;取上面2行的数据的值&＃xff0c;当没有上两行时取NULL lead(month,1,&＃39;1900-01-01&＃39;) over(partition by userid order by month) #按照userid进行分组&＃xff0c;在分组内按照月份升序排列&＃xff0c;取上下行数据的值 from test; |
当lag函数未设置行数值时,默认为1行.未设定取不到时的默认值时,取null值.
④first_value和last_value
first_value取分组内排序后&＃xff0c;截止到当前行&＃xff0c;第一个值 &＃xff1b;last_value取分组内排序后&＃xff0c;截止到当前行&＃xff0c;最后一个值。
| select userid,month,sumpv, first_value(month,1,&＃39;1900-01-01&＃39;) over(partition by userid order by month) #按照userid进行分组&＃xff0c;在分组内按照月份升序排列&＃xff0c;截止到当前行的第一个值 last_value(month,2&＃39;) over(partition by userid order by month) #按照userid进行分组&＃xff0c;在分组内按照月份升序排列&＃xff0c;截止到当前行的最后一个值&＃xff0c;即当前行 lead(month,1,&＃39;1900-01-01&＃39;) over(partition by userid order by month) #按照userid进行分组&＃xff0c;在分组内按照月份升序排列&＃xff0c;取上下行数据的值 from test; |
| 现有这么一批数据 三个字段的意思&＃xff1a; A,2015-01,5 现要求出&＃xff1a;如每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数
|
分析&＃xff1a;求每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数
既显示聚集前的数据&＃xff0c;又要显示聚集后的数据&＃xff0c;并且如果单是求最大单月访问次数的话直接按照用户&＃43;月份分组就行&＃xff0c;但是还要求累计到该月&＃xff0c;就应该联想当窗口函数&＃xff0c;窗口函数有很多&＃xff0c;比如&＃xff1a;聚合函数 &＃43; over() ---整个窗口的整体聚合 first_value() over--分组后截止到当前行第一个值等。因为是要求累计到当前月&＃xff0c;所以前面的数据是全部都要的。那么就可以使用 sum(字段名) &＃43; over(distribute by 字段名 sort by 字段名) ---------截止到排序数据之前的即当前 或 sum(字段名) over(partition by 字段名 order by 字段名 rows between UNBOUNDED PRECEDING and current row)
| select userid, MONTH, sumpv, max(sumpv) over ( PARTITION BY userid ORDER BY MONTH ) maxpv, sum(sumpv) over ( PARTITION BY userid ORDER BY MONTH ) sumpvv FROM test; |
当然这题还有其他的解决方案&＃xff0c;比如自连接
| SELECT buserid, bmonth, bsumpv, max(asumpv) maxpv, sum(asumpv) sumpv FROM ( SELECT * FROM ( SELECT a.userid auserid, a. MONTH amonth, a.sumpv asumpv, b.userid buserid, b. MONTH bmonth, b.sumpv bsumpv FROM test a JOIN test b ON a.userid &＃61; b.userid ) c WHERE amonth <&＃61; bmonth ) d GROUP BY buserid, bmonth, bsumpv; |
总结&＃xff1a;当题目要求既显示聚集前的数据&＃xff0c;又要显示聚集后的数据&＃xff0c;并且还要对数据进行累加的时候要想到用窗口函数。
&＃xff08;1&＃xff09;行转列
数据如下&＃xff1a;
求&＃xff1a;所有数学课程成绩 大于 语文课程成绩的学生的学号
思路&＃xff1a;要求的是数学成绩>语文成绩的学生的学号&＃xff0c;目前的数据一行是每个学生每一科的成绩&＃xff0c;我们查询出来的数据是一条一条的&＃xff0c;并且可能每个学生的科目数量有可能不一样&＃xff0c;那么要比较不同的行的数据是非常困难的。而我们知道当数据在同一行时&＃xff0c;这一行的数据是非常好进行比较的&＃xff0c;那么我们就可以将行转换成列&＃xff0c;那么要比较的就在同一行了&＃xff0c;处理起来非常容易。
那么接下来就是怎样将列转换成行&＃xff0c;我们知道查询出来的结果的列是根据select后面的字段名的个数而定的&＃xff0c;select后有多少个字段&＃xff0c;查询的结果就有多少个列。因为科目是固定的&＃xff0c;那么列就加上所有的科目&＃xff0c;所以我们就可以原表中的每一行查询出来后进行进行判断&＃xff0c;并对这条记录中的科目放到对应的select后字段下。那么就可以使用case when条件判断。
| case when switch(字段){ 语法2&＃xff1a;相当于if else
|
使用条件判断语句后有如下结果
| SELECT sid, CASE WHEN course &＃61; "yuwen" THEN score ELSE 0 END yuwen, CASE WHEN course &＃61; "shuxue" THEN score ELSE 0 END shuxue FROM course; |
接下来就要合并相同id的数据&＃xff0c;最终语句如下
| SELECT
|
需求如下&＃xff1a;
思路&＃xff1a;因为要显示所有的课程&＃xff0c;所以首先要使用collect_set&＃xff08;&＃xff09;函数---将分组中的某列转为一个数组返回&＃xff0c;获取到所有课程&＃xff0c;然后获取到每一个学员所选的科目&＃xff0c;判断有哪些科目&＃xff0c;这里可以用if判断语句。所以把查询出一个所有课程作为一个表&＃xff0c;每一个学员所选的科目作为另一个表&＃xff0c;因为所有课程的表只有一条数据&＃xff0c;所以可以用笛卡尔积。
hive 处于安全考虑 没有开启笛卡尔积&＃xff0c;所以要手动开启&＃xff0c;设置如下
set hive.strict.checks.cartesian.product&＃61;false;
set hive.mapred.mode&＃61;nonstrict;
| select )c; |
总结&＃xff1a;行转列即是把行转列前的分行条件&＃xff0c;分别作为行转列后的各列的筛选条件。可以借助case when&＃xff0c;if等判断函数&＃xff0c;与collect_set(),collect_list()函数
&＃xff08;2&＃xff09;列转行在下面的TopN例子中一起讲解
| 现有如下数据 2001010310 需求&＃xff1a;求出每一年的最高温度是哪一天&＃xff08;日期&＃xff0c; 最高温度&＃xff09; |
这题根据题意可知需要求每一年...所以对年进行分组&＃xff0c;相同年份的为一组&＃xff0c;分组之后使用max函数就可知最高温度是哪一天。因为最高温度那一天只有一条&＃xff0c;所以可以和原表进行笛卡尔积连接&＃xff0c;即可得知那一天的的日期和最高温度等。
| select a.year year,a.max_temp max_temp,substr(b.data,1,8) day from (select substr(data,1,4) year,max(cast(substr(data,-2) as int)) max_temp from test04 group by substr(data,1,4)) a join test04 b on a.year&＃61;substr(b.data,1,4) and a.max_temp&＃61;cast(substr(b.data,-2) as int); |
总结&＃xff1a;当我们遇到每一&＃xff0c;每一个这种字眼时&＃xff0c;就需要使用到分组&＃xff0c;分组的字段就是跟在这些字眼后面的字段。
| 现在有这样一份数据&＃xff1a; 字段的意义&＃xff1a; 每一条记录中的爱好有多个值&＃xff0c;以"-"分隔 需求&＃xff1a;求出每种爱好中&＃xff0c;年龄最大的两个人&＃xff08;爱好&＃xff0c;年龄&＃xff0c;姓名&＃xff09; |
总体思路&＃xff1a;由求出每种爱好中可知要对爱好进行分组&＃xff0c;根据年龄最大的两个人可知分组之后对年龄进行倒叙排序&＃xff0c;然后取前两个。
因为表中的爱好以a-c-d-f方式存在&＃xff0c;这样无法对每种爱好进行分组&＃xff0c;我们要想对每种爱好进行分组&＃xff0c;那么就必须一行只有一种爱好我们才能求得。所以这里我们就需要列转换成行&＃xff0c;就需要用到explode&＃xff08;&＃xff09;函数&＃xff0c;explode只能有两种类型的参数&＃xff0c;如下&＃xff1a;
explode(ARRAY)&＃xff1a; 列表中的每个元素生成一行
explode(MAP)&＃xff1a;map中每个key-value对&＃xff0c;生成一行&＃xff0c;key为一列&＃xff0c;value为一列
注意&＃xff1a;explode()是有很多限制的&＃xff0c;限制如下
| 1、No other expressions are allowed in SELECT #SELECT中不允许有其他表达式 如&＃xff1a;SELECT id, explode(adid_list) AS aa... is not supported
2、UDTF&＃39;s can&＃39;t be nested #UDTF不能嵌套 如&＃xff1a;SELECT explode(explode(adid_list)) AS aaa... is not supported
3、GROUP BY / CLUSTER BY / DISTRIBUTE BY / SORT BY is not supported #不支持分组排序等 如&＃xff1a;SELECT explode(adid_list) AS aaa ... GROUP BY aaa is not supported |
&＃xff0c;所以经常和lateral view搭配&＃xff0c;除以上限制 LATERAL VIEW explode(expression) tableAlias AS columnAlias (&＃39;,&＃39; columnAlias)
这里将爱好explode&＃xff08;&＃xff09;炸裂之后&＃xff0c;有几个爱好对应就生成几行&＃xff0c;那么就需要将原表与炸裂后的爱好进行连接&＃xff0c;那么怎么连接呢&＃xff1f;原表的数据全都需要所以用原表做连接炸裂后的爱好表&＃xff0c;因为炸裂后的就得出一个爱好的字段&＃xff0c;无法用其他字段进行关联&＃xff0c;那就用array_contains&＃xff08;&＃xff09;函数&＃xff0c;判断炸裂出来的爱好是否在原始的a-c-d-f中&＃xff0c;如果在的话&＃xff0c;那么就关联起来。然后再对姓名&＃xff0c;年龄&＃xff0c;爱好分组&＃xff0c;起去重作用。到这就将炸裂后的字段关联上了&＃xff0c;因为需求中要保留原表的爱好&＃xff0c;年龄&＃xff0c;姓名等信息&＃xff0c;那么就可以使用窗口函数row_number() over(partition by hobc order by agec desc)对爱好排序&＃xff0c;对年龄分组&＃xff0c;生成一个新的序列字段。因为窗口函数是除了order外最后执行的&＃xff0c;所以我们需要在生成窗户函数的这个表外进行过滤。最终代码如下&＃xff1a;
| select d.hobc,d.agec,d.namec from (select hobc,agec,namec, row_number() over (partition by hobc order by agec desc) as seqs from (select b.name as namec,b.age as agec,a.hob as hobc from test05 b left join (select explode(hobby) as hob from test05 ) a on array_contains(hobby,hob) group by b.name,b.age,a.hob) c )d where d.seqs <&＃61;2; |
总结&＃xff1a;当遇到需求中有最...的几个&＃xff0c;前几个这种字眼眼时&＃xff0c;就可以用求解TopN的思路去分析。当列中字段的内容是以组合的形式存在&＃xff0c;而我们又要对该列中的内中进行分组等操作&＃xff0c;就需要使用explode()函数&＃xff0c;将列炸裂成行&＃xff0c;由于explode()有诸多限制&＃xff0c;所以经常会和lateral view一起使用。
| 统计每个月每天出现的情况&＃xff0c;比如下面两串数字 现在是想把这两串数字拼起来&＃xff0c;比如第一天&＃xff0c;通话标识为0&＃xff0c;流量标识为1&＃xff0c;我需要的数字是两个标识同一天有1的就置为1&＃xff0c;两个1也置为1。结果为 1101010101010101010100101010101 数据&＃xff1a; |
建表语句&＃xff1a;create table test06(day1 string,day2 string) row format delimited fields terminated by "," ;
&＃xff08;1&＃xff09;内置函数实现
思路&＃xff1a;将通话记录和流量记录分别用两个表取出来&＃xff0c;并使用explode()函数炸裂&＃xff0c;并不对炸裂后的结果使用row_number() over() &＃xff0c;生成行号&＃xff0c;然后将这两个表根据行号进行左连接查询&＃xff08;有为空的就可以过滤掉&＃xff09;&＃xff0c;最后再用concat_ws()行数将列转换成行。
最终实现如下&＃xff1a;
| select concat_ws(&＃39;&＃39;,collect_list(cast(dyd as string))) from (select c.seqs,cast(c.dyc as int)|cast(b.dyb as int) as dyd from (select row_number() over() as seqs,a.dy as dyc from (select explode(split(day2,"")) as dy from test06) a where a.dy!&＃61;&＃39;&＃39; order by seqs desc ) c left join (select row_number() over () as seqs,a.dy as dyb from (select explode(split(day1,"")) as dy from test06) a where a.dy!&＃61;&＃39;&＃39; order by seqs desc ) b on c.seqs&＃61;b.seqs order by c.seqs desc) d; |
&＃xff08;2&＃xff09;自定义UDF临时函数步骤&＃xff1a;
①继承 org.apache.hadoop.hive.ql.exec.UDF&＃xff0c;重载 evaluate 方法
②打成 jar 包上传到服务器
③将 jar 包添加到 hive 的 classpath hive>add JAR “jar包在服务器的路径”
④查看jar包是否加入成功&＃xff0c;hive> list jar
⑤创建临时函数与开发好的 class 关联起来
hive>create temporary function tolowercase as &＃39;类名全路径&＃39;;
⑥show functions;就可以看多多了一个函数&＃xff0c;不过这个为临时函数。
//思路将两串数字遍历一遍&＃xff0c;每一为进行或运算&＃xff0c;再拼接起来
public class MyUDF extends UDF {public String evaluate(String str1,String str2)throws HiveException {if(str1.length()!&＃61;str2.length()) {throw new UDFArgumentTypeException(0,"The lengths of the arguments are inconsistent");}String[] s1&＃61;str1.split("");String[] s2&＃61;str2.split("");StringBuffer str &＃61; new StringBuffer();for(int i&＃61;0;i
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有 