Hive 函数、运算符使用 |
您所在的位置：网站首页 › 倒序查找函数 › Hive 函数、运算符使用 |
Hive 函数、运算符使用
|
# 1. Hive内置运算符 关系运算符、算术运算符、逻辑运算符 -- 显示所有的函数和运算符 show functions; -- 查看运算符或者函数的使用说明 describe function +; -- 使用extended 可以查看更加详细的使用说明 describe function extended count; # 1.1 测试环境准备创建空表dual，用于测试运算符 -- 1、创建表dual create table dual ( id string ); -- 2、加载一个文件dual.txt到dual表中 -- dual.txt只有一行内容：内容为一个空格 load data local inpath '/root/hivedata/dual.txt' into table dual; -- 3、在select查询语句中使用dual表完成运算符、函数功能测试 select 1 + 1 from dual; # 1.2 关系运算符关系运算符返回的都是boolean结果 ---------------- Hive中关系运算符 ------------------------- -- is null空值判断 select 1 from dual where 'vingkin' is null; -- is not null 非空值判断 select 1 from dual where 'vingkin' is not null; -- like比较： _表示任意单个字符 %表示任意数量字符 -- 否定比较： NOT A like B select 1 from dual where 'vingkin' like 'it_'; select 1 from dual where 'vingkin' like 'it%'; select 1 from dual where 'vingkin' not like 'hadoo_'; select 1 from dual where not 'vingkin' like 'hadoo_'; -- rlike：确定字符串是否匹配正则表达式，是REGEXP_LIKE()的同义词。 select 1 from dual where 'vingkin' rlike '^i.*t$'; select 1 from dual where '123456' rlike '^\\d+$'; -- 判断是否全为数字 select 1 from dual where '123456aa' rlike '^\\d+$'; -- regexp：功能与rlike相同 用于判断字符串是否匹配正则表达式 select 1 from dual where 'vingkin' regexp '^i.*t$'; # 1.3 算术运算符算术运算符操作数必须是数值类型。 分为一元运算符和二元运算符：一元运算符,只有一个操作数; 二元运算符有两个操作数,运算符在两个操作数之间。 -------------------Hive中算术运算符--------------------------------- -- 取整操作: div 给出将A除以B所得的整数部分。例如17 div 3得出5。 select 17 div 3; -- 取余操作: % 也叫做取模mod A除以B所得的余数部分 select 17 % 3; -- 位与操作: & A和B按位进行与操作的结果。 与表示两个都为1则结果为1 select 4 & 8 from dual; select 6 & 4 from dual; -- 位或操作: | A和B按位进行或操作的结果 或表示有一个为1则结果为1 select 4 | 8 from dual; select 6 | 4 from dual; -- 位异或操作: ^ A和B按位进行异或操作的结果 异或表示两者的值不同,则结果为1 select 4 ^ 8 from dual; select 6 ^ 4 from dual; # 1.4 逻辑运算符逻辑是否存在: [NOT] EXISTS (subquery) -- 3、Hive逻辑运算符 -- 与操作: A AND B 如果A和B均为TRUE，则为TRUE，否则为FALSE。如果A或B为NULL，则为NULL。 select 1 from dual where 3 > 1 and 2 > 1; -- 或操作: A OR B 如果A或B或两者均为TRUE，则为TRUE，否则为FALSE。 select 1 from dual where 3 > 1 or 2 != 2; -- 非操作: NOT A 、!A 如果A为FALSE，则为TRUE；如果A为NULL，则为NULL。否则为FALSE。 select 1 from dual where not 2 > 1; select 1 from dual where ! 1 = 2; -- 在:A IN (val1, val2, ...) 如果A等于任何值，则为TRUE。 select 1 from dual where 11 in (11, 22, 33); -- 不在:A NOT IN (val1, val2, ...) 如果A不等于任何值，则为TRUE select 1 from dual where 11 not in (22, 33, 44); -- 逻辑是否存在: [NOT] EXISTS (subquery) -- 将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE）来决定主查询的数据结果是否得以保留。 select A.* from A where exists (select B.id from B where A.id = B.id); # 2. Hive 函数入门# 2.1 Hive函数概述及分类标准Hive函数可以分成两大类： 内置函数（Built-in Functions）用户定义函数（UDF User-Defined Functions）其中用户定义可以分成三类： UDF（User-Defined-Function）：普通函数，一进一出UDAF（User-Defined Aggregation Function）：聚合函数，多进一出UDTF（User-Defined Table-Generating Function）：表生成函数，一进多出但是由于UDF分类标准扩大化，UDF分类标准可以扩大到Hive的所有函数中：包括内置函数和用户自定义函数。所以现在没有内置函数和用户定义函数之分，所有函数可以分成UDF，UDAF，UDTF三类。 # 2.2 Hive内置函数# 2.2.1 字符串函数函数名函数作用length字符串长度函数reverse字符串反转函数concat字符串连接函数concat_ws带分隔符字符串连接函数substr，substring字符串截取函数upper，ucase字符串转大写函数lower，lcase字符串转小写函数trim去空格函数ltrim左边去空格函数rtrim右边去空格函数regexp_replace正则表达式替换函数regexp_extract正则表达式解析函数parse_urlURL解析函数get_json_objectjson解析函数space空格字符串函数repeat重复字符串函数ascii首字符ascii函数lpad左补足函数rpad右补足函数split分隔字符串函数find_in_set集合查找函数------------ String Functions 字符串函数 ------------ select concat("angela", "baby"); -- 带分隔符字符串连接函数：concat_ws(separator, [string | array(string)]+) select concat_ws('.', 'www', array('vingkin', 'cn')); -- 字符串截取函数：substr(str, pos[, len]) 或者 substring(str, pos[, len]) select substr("angelababy", -2); -- pos是从1开始的索引，如果为负数则倒着数 select substr("angelababy", 2, 2); -- 正则表达式替换函数：regexp_replace(str, regexp, rep) select regexp_replace('100-200', '(\\d+)', 'num'); -- 正则表达式解析函数：regexp_extract(str, regexp[, idx]) 提取正则匹配到的指定组内容 select regexp_extract('100-200', '(\\d+)-(\\d+)', 2); -- URL解析函数：parse_url 注意要想一次解析出多个 可以使用parse_url_tuple这个UDTF函数 select parse_url('http://www.vingkin.cn/path/p1.php?query=1', 'HOST'); -- 分割字符串函数: split(str, regex) select split('apache hive', '\\s+'); -- \\s+表示用来匹配空白符，一个或多个都行 -- json解析函数：get_json_object(json_txt, path) -- $表示json对象 select get_json_object( '[{"website":"www.vingkin.cn","name":"allenwoon"}, {"website":"cloud.vingkin.com","name":"carbondata 中文文档"}]', '$.[1].website'); -- 字符串长度函数：length(str | binary) select length("angelababy"); -- 字符串反转函数：reverse select reverse("angelababy"); -- 字符串连接函数：concat(str1, str2, ... strN) -- 字符串转大写函数：upper,ucase select upper("angelababy"); select ucase("angelababy"); -- 字符串转小写函数：lower,lcase select lower("ANGELABABY"); select lcase("ANGELABABY"); -- 去空格函数：trim 去除左右两边的空格 select trim(" angelababy "); -- 左边去空格函数：ltrim select ltrim(" angelababy "); -- 右边去空格函数：rtrim select rtrim(" angelababy "); -- 空格字符串函数：space(n) 返回指定个数空格 select space(4); -- 重复字符串函数：repeat(str, n) 重复str字符串n次 select repeat("angela", 2); -- 首字符ascii函数：ascii select ascii("angela");-- a对应ASCII 97 -- 左补足函数：lpad select lpad('hi', 5, '??'); -- ???hi select lpad('hi', 1, '??'); -- h -- 右补足函数：rpad select rpad('hi', 5, '??'); --集合查找函数: find_in_set(str,str_array) select find_in_set('a', 'abc,b,ab,c,def'); -- 0，表示没找到 # 2.2.2 日期函数函数名函数作用获取当前日期current_date获取当前时间戳current_timestampUNIX时间戳转日期函数from_unixtime获取当前UNIX时间戳函数unix_timestamp日期转UNIX时间戳函数unix_timestamp抽取日期函数to_date日期转年函数year...month...day...hour...minute...second日期转周函数weekofyear日期比较函数datediff日期增加函数date_add日期减少函数date_sub----------- Date Functions 日期函数 ----------------- -- 获取当前日期: current_date select current_date(); -- 获取当前时间戳: current_timestamp -- 同一查询中对current_timestamp的所有调用均返回相同的值。 select current_timestamp(); -- 2021-09-06 01:18:22.871000000 -- 获取当前UNIX时间戳函数: unix_timestamp select unix_timestamp(); -- 1630916306 -- 日期转UNIX时间戳函数: unix_timestamp select unix_timestamp("2011-12-07 13:01:03"); -- 指定格式日期转UNIX时间戳函数: unix_timestamp select unix_timestamp('20111207 13:01:03', 'yyyyMMdd HH:mm:ss'); -- UNIX时间戳转日期函数: from_unixtime select from_unixtime(1618238391); select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss'); -- 日期比较函数: datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd' select datediff('2012-12-08', '2012-05-09'); -- 日期增加函数: date_add select date_add('2012-02-28', 10); -- 日期减少函数: date_sub select date_sub('2012-01-1', 10); -- 抽取日期函数: to_date select to_date('2009-07-30 04:17:52'); -- 日期转年函数: year select year('2009-07-30 04:17:52'); -- 日期转月函数: month select month('2009-07-30 04:17:52'); -- 日期转天函数: day select day('2009-07-30 04:17:52'); -- 日期转小时函数: hour select hour('2009-07-30 04:17:52'); -- 日期转分钟函数: minute select minute('2009-07-30 04:17:52'); -- 日期转秒函数: second select second('2009-07-30 04:17:52'); -- 日期转周函数: weekofyear 返回指定日期所示年份第几周 select weekofyear('2009-07-30 04:17:52'); # 2.2.3 数学函数函数名函数功能round四舍五入round指定精度取整函数floor向下取整函数ceil向上取整函数rand取随机数函数bin二进制函数conv进制转换函数abs绝对值函数----Mathematical Functions 数学函数------------- -- 取整函数: round 返回double类型的整数值部分 （遵循四舍五入） select round(3.1415926); -- 指定精度取整函数: round(double a, int d) 返回指定精度d的double类型 select round(3.1415926, 4); -- 向下取整函数: floor select floor(3.1415926); select floor(-3.1415926); -- 向上取整函数: ceil select ceil(3.1415926); select ceil(-3.1415926); -- 取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数 select rand(); -- 指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列 select rand(3); -- 二进制函数: bin(BIGINT a) select bin(18); -- 进制转换函数: conv(BIGINT num, int from_base, int to_base) select conv(17, 10, 16); -- 绝对值函数: abs select abs(-3.9); # 2.2.4 集合函数函数名函数功能size()集合元素个数函数map_keys()取map集合keys函数map_values()取map集合values函数array_contains()判断数组是否包含指定元素sort_array()数组排序函数-------Collection Functions 集合函数-------------- -- 集合元素size函数: size(Map) size(Array) select size(`array`(11, 22, 33)); select size(`map`("id", 10086, "name", "zhangsan", "age", 18)); select `array`(11, 22, 33) from dual; select `map`("id", 10086, "name", "zhangsan", "age", 18) from dual; -- 取map集合keys函数: map_keys(Map) select map_keys(`map`("id", 10086, "name", "zhangsan", "age", 18)); -- 取map集合values函数: map_values(Map) select map_values(`map`("id", 10086, "name", "zhangsan", "age", 18)); -- 判断数组是否包含指定元素: array_contains(Array, value) select array_contains(`array`(11, 22, 33), 11); select array_contains(`array`(11, 22, 33), 66); -- 数组排序函数:sort_array(Array) select sort_array(`array`(12, 2, 32)); # 2.2.5 条件函数函数名函数功能if()if条件判断isnull()空判断函数isnotnull()非空判断函数nvl()空值转换函数coalesce非空查找函数case a when b then c [when * then * ... else *] end条件转换函数nullif(a, b)a=b返回NULL，否则返回aassert_true(condition)condition为false报错，否则返回NULL使用之前课程创建好的student表数据 create table student( num int, name string, sex string, age int, dept string ) row format delimited fields terminated by ','; load data local inpath '/root/hivedata/students.txt' into table student; -- if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull) select if(1 = 2, 100, 200); -- 200 select if(sex = '男', 'M', 'W') from student limit 3; -- 空判断函数: isnull( a ) select isnull("allen"); select isnull(null); -- 非空判断函数: isnotnull ( a ) select isnotnull("allen"); select isnotnull(null); -- 空值转换函数: nvl(T value, T default_value) select nvl("allen", "vingkin"); select nvl(null, "vingkin"); -- 非空查找函数: COALESCE(T v1, T v2, ...) -- 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL select COALESCE(null, 11, 22, 33); select COALESCE(null, null, null, 33); select COALESCE(null, null, null); -- 条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end; select case sex when '男' then 'male' else 'female' end from student limit 3; -- nullif( a, b ): -- 如果a = b，则返回NULL，否则返回第一个 select nullif(11, 11); select nullif(11, 12); -- assert_true(condition) -- 如果'condition'不为真，则引发异常，否则返回null SELECT assert_true(11 >= 0); SELECT assert_true(-1 >= 0); # 2.2.6 类型转换函数主要用于显式数据类型转换 ----Type Conversion Functions 类型转换函数----------------- -- 任意数据类型之间转换:cast select cast(12.14 as bigint); select cast(12.14 as string); select cast("hello" as int); # 2.2.7 数据脱敏函数----Data Masking Functions 数据脱敏函数------------ -- mask -- 将查询回的数据，大写字母转换为X，小写字母转换为x，数字转换为n。 select mask("abc123DEF"); select mask("abc123DEF", '-', '.', '^'); -- 自定义替换的字母 -- mask_first_n(string str[, int n] -- 对前n个进行脱敏替换 select mask_first_n("abc123DEF", 4); -- mask_last_n(string str[, int n]) select mask_last_n("abc123DEF", 4); -- mask_show_first_n(string str[, int n]) -- 除了前n个字符，其余进行掩码处理 select mask_show_first_n("abc123DEF", 4); -- mask_show_last_n(string str[, int n]) select mask_show_last_n("abc123DEF", 4); -- mask_hash(string|char|varchar str) -- 返回字符串的hash编码。 select mask_hash("abc123DEF"); # 2.2.8 其他杂项函数----- Misc. Functions 其他杂项函数--------------- -- 如果你要调用的java方法所在的jar包不是hive自带的 可以使用add jar添加进来 -- hive调用java方法: java_method(class, method[, arg1[, arg2..]]) select java_method("java.lang.Math", "max", 11, 22); -- 反射函数: reflect(class, method[, arg1[, arg2..]]) select reflect("java.lang.Math", "max", 11, 22); -- 取哈希值函数:hash select hash("allen"); -- current_user()、logged_in_user()、current_database()、version() -- SHA-1加密: sha1(string/binary) select sha1("allen"); -- SHA-2家族算法加密：sha2(string/binary, int) (SHA-224, SHA-256, SHA-384, SHA-512) select sha2("allen", 224); select sha2("allen", 512); -- crc32加密: select crc32("allen"); -- MD5加密: md5(string/binary) select md5("allen"); # 2.3 案例：UDF实现手机号加密在企业中处理数据的时候，对于敏感数据往往需要进行脱敏处理。比如手机号。我们常见的处理方式是将手机号中间4位进行****处理。 需求： 能够对输入数据进行非空判断、手机号位数判断能够实现校验手机号格式，把满足规则的进行****处理对于不符合手机号规则的数据直接返回，不处理实现步骤： 写一个java类，继承UDF，并重载evaluate方法，方法中实现函数的业务逻辑；重载意味着可以在一个java类中实现多个函数功能；程序打成jar包，上传HS2服务器本地或者HDFS;客户端命令行中添加jar包到Hive的classpath： hive>add JAR xxx.jar;注册成为临时函数（给UDF命名）：create temporary function encrypt_phonum as 'com.vingkin.hive.udf.PhoneNumberUDF';HQL中使用函数:select encrypt_phonum('13888888888')相关依赖： org.apache.hive hive-exec 3.1.2 org.apache.hadoop hadoop-common 3.1.4 org.apache.maven.plugins maven-shade-plugin 2.2 package shade *:* META-INF/*.SF META-INF/*.DSA META-INF/*.RSA业务代码：PhoneNumberUDF.java public class PhoneNumberUDF extends UDF { public String evaluate(String phoNum) { String encryptPhoNum = null; if (StringUtils.isNotEmpty(phoNum) && phoNum.trim().length() == 11) { String regex = "^(1[3-9]\\d{9}$)"; Pattern p = Pattern.compile(regex); Matcher m = p.matcher(phoNum); if (m.matches()) { encryptPhoNum = phoNum.trim().replaceAll("(\\d{3})\\d{4}(\\d{4})", "$1****$2"); } else { encryptPhoNum = phoNum; } } else { encryptPhoNum = phoNum; } return encryptPhoNum; } // public static void main(String[] args) { // System.out.println(new PhoneNumberUDF().evaluate("13888888888")); // } } # 3. Hive 函数高阶# 3.1 UDTF之explode函数explode接收map、array类型的数据作为输入，然后把输入数据中的每个元素拆开变成一行数据，一个元素一行。 对于map每行有两列分别为key，value # 3.1.1 示例select explode(`array`(11,22,33)) as item; select explode(`map`("id",10086,"name","zhangsan","age",18)); # 3.1.2 案例：NBA总冠军球队名单分析有一份数据The_NBA_Championship.txt，关于部分年份的NBA总冠军球队名单；第一个字段表示球队名称，第二个字段是获取总冠军的年份；字段之间以，分割；总冠军年份之间以|进行分割。  建表加载数据： -- step1:建表 create table the_nba_championship( team_name string, champion_year array ) row format delimited fields terminated by ',' collection items terminated by '|'; -- step2:加载数据文件到表中 load data local inpath '/root/hivedata/The_NBA_Championship.txt' into table the_nba_championship;错误的演示： select team_name,explode(champion_year) from the_nba_championship; explode函数属于UDTF表生成函数，explode执行返回的结果可以理解为一张虚拟的表，其数据来源于源表；在select中只查询源表数据没有问题，只查询explode生成的虚拟表数据也没问题，但是不能在只查询源表的时候，既想返回源表字段又想返回explode生成的虚拟表字段；通俗点讲，有两张表，不能只查询一张表但是又想返回分别属于两张表的字段；解决方法： 从SQL层面上来说上述问题的解决方案是：对两张表进行join关联查询;Hive专门提供了语法lateral View侧视图，专门用于搭配explode这样的UDTF函数，以满足上述需要。select a.team_name,b.year from the_nba_championship a lateral view explode(champion_year) b as year order by b.year desc; image-20210906174525863# 3.1.3 Hive Lateral View 侧视图Lateral View是一种特殊的语法，主要搭配UDTF类型函数一起使用，用于解决UDTF函数的一些查询限制的问题。 一般只要使用UDTF，就会固定搭配lateral view使用。  针对explode案例中NBA冠军球队年份排名案例，使用explode函数+lateral view侧视图 -- 根据年份倒序排序 select a.team_name ,b.year from the_nba_championship a lateral view explode(champion_year) b as year order by b.year desc; -- 统计每个球队获取总冠军的次数 并且根据倒序排序
select a.team_name ,count(*) as nums
from the_nba_championship a lateral view explode(champion_year) b as year
group by a.team_name
order by nums desc;
 # 3.2 UDAF之聚合函数# 3.2.1 基础聚合student create table student ( num int, name string, sex string, age int, dept string ) row format delimited fields terminated by ','; -- 加载数据 load data local inpath '/root/hivedata/students.txt' into table student; -- 场景1：没有group by子句的聚合操作 -- count(*)：所有行进行统计，包括NULL行 -- count(1)：所有行进行统计，包括NULL行 -- count(column)：对column中非Null进行统计 select count(*) as cnt1, count(1) as cnt2 from student; select count(sex) as cnt3 from student; -- 场景2：带有group by子句的聚合操作 注意group by语法限制 select sex, count(*) as cnt from student group by sex; -- 场景3：select时多个聚合函数一起使用 select count(*) as cnt1, avg(age) as cnt2 from student; -- 场景4：聚合函数和case when条件转换函数、coalesce函数、if函数使用 select sum(CASE WHEN sex = '男' THEN 1 ELSE 0 END) from student; select sum(if(sex = '男', 1, 0)) from student; -- 场景5：聚合参数不支持嵌套聚合函数 select avg(count(*)) from student; -- 报错 -- 场景6：聚合操作时针对null的处理 CREATE TABLE tmp_1(val1 int, val2 int); INSERT INTO TABLE tmp_1 VALUES (1, 2),(null, 2),(2, 3); -- 第二行数据(NULL, 2) 在进行sum(val1 + val2)的时候会被忽略 select sum(val1), sum(val1 + val2) from tmp_1; -- 可以使用coalesce函数解决（返回第一个不为空的值） select sum(coalesce(val1, 0)), sum(coalesce(val1, 0) + val2) from tmp_1; -- 场景7：配合distinct关键字去重聚合 -- 此场景下，会编译期间会自动设置只启动一个reduce task处理数据 可能造成数据拥堵 select count(distinct sex) as cnt1 from student; -- 可以先去重 在聚合 通过子查询完成 -- 因为先执行distinct的时候 可以使用多个reducetask来跑数据 select count(*) as gender_uni_cnt from (select distinct sex from student) a; -- 案例需求：找出student中男女学生年龄最大的及其名字 -- 这里使用了struct来构造数据 然后针对struct应用max找出最大元素 然后取值 select case sex when '男' then '男' when '女' then '女' else '其他' end, max(struct(age, name)).col1 as age, max(struct(age, name)).col2 as name from student where sex != '\N' group by sex order by age desc; select struct(age, name) from student; -- {"col1":20,"col2":"李勇"}... select struct(age, name).col1 from student; -- 所有年龄 select max(struct(age, name)) from student; -- 最大年龄 # 3.2.2 增强聚合grouping_sets：一种将多个group by逻辑写在一个sql语句中的便利写法。等价于将不同维度的GROUP BY结果集进行UNION ALL。GROUPING__ID表示结果属于哪一个分组集合。cube：根据GROUP BY的维度的所有组合进行聚合。比如cube有a,b,c 3个维度，则所有组合情况是： (a,b,c),(a,b),(b,c),(a,c),(a),(b),(c),()，总个数是2n2^n2nrollup：rollup是cube的子集，以最左侧的维度为主，从该维度进行层级聚合。比如ROLLUP有a,b,c3个维度，则所有组合情况是：(a,b,c),(a,b),(a),()cookie_info CREATE TABLE cookie_info ( month STRING, -- 2018-03 day STRING, -- 2018-03-30 cookieid STRING -- cookie1... ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; load data local inpath '/root/hivedata/cookie_info.txt' into table cookie_info; # grouping_sets对于grouping__id，原则上是通过将group by中的字段倒序排序，然后通过二进制来表示的 比如说下面的month，day，倒序排序后为（day，month），出现的位置置1 但是我运行出来的grouping__id与理论上有点出入，具体问题还不知道 字段二进制表示十进制表示month(0, 1)1day(1, 0)2(month, day)(1, 1)3SELECT month, day, COUNT(DISTINCT cookieid) AS nums, GROUPING__ID FROM cookie_info GROUP BY month, day GROUPING SETS (month, day) -- 这里是关键 ORDER BY GROUPING__ID; -- 等价于 select month, NULL as day, count(distinct cookieid) as nums, 1 as grouping__id from cookie_info group by month union all select NULL as month, day, count(distinct cookieid) as nums, 2 as grouping__id from cookie_info group by day; 对于有(month, day)的情况，如下，grouping__id与理论情况有点出入 SELECT month, day, COUNT(DISTINCT cookieid) AS nums, grouping__id FROM cookie_info GROUP BY month, day GROUPING SETS ( month, day, ( month, day)) -- 1 month 2 day 3 (month,day) ORDER BY grouping__id; -- 等价于 SELECT month, NULL, COUNT(DISTINCT cookieid) AS nums, 1 AS GROUPING__ID FROM cookie_info GROUP BY month UNION ALL SELECT NULL, day, COUNT(DISTINCT cookieid) AS nums, 2 AS GROUPING__ID FROM cookie_info GROUP BY day UNION ALL SELECT month, day, COUNT(DISTINCT cookieid) AS nums, 3 AS GROUPING__ID FROM cookie_info GROUP BY month, day; # cubecube下，grouping__id依然有出入 SELECT month, day, COUNT(DISTINCT cookieid) AS nums, GROUPING__ID FROM cookie_info GROUP BY month, day WITH CUBE ORDER BY GROUPING__ID; -- 等价于 SELECT NULL, NULL, COUNT(DISTINCT cookieid) AS nums, 0 AS GROUPING__ID FROM cookie_info UNION ALL SELECT month, NULL, COUNT(DISTINCT cookieid) AS nums, 1 AS GROUPING__ID FROM cookie_info GROUP BY month UNION ALL SELECT NULL, day, COUNT(DISTINCT cookieid) AS nums, 2 AS GROUPING__ID FROM cookie_info GROUP BY day UNION ALL SELECT month, day, COUNT(DISTINCT cookieid) AS nums, 3 AS GROUPING__ID FROM cookie_info GROUP BY month, day; # rollup-- 比如，以month维度进行层级聚合：
SELECT month,
day,
COUNT(DISTINCT cookieid) AS nums,
GROUPING__ID
FROM cookie_info
GROUP BY month, day
WITH ROLLUP
ORDER BY GROUPING__ID;
 -- 把month和day调换顺序，则以day维度进行层级聚合：
SELECT day,
month,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM cookie_info
GROUP BY day, month
WITH ROLLUP
ORDER BY GROUPING__ID;
 # 3.3 窗口函数窗口函数（Window functions）也叫做开窗函数、OLAP函数，其最大特点是：输入值是从SELECT语句的结果集中的一行或多行的“窗口”中获取的。 如果函数具有OVER子句，则它是窗口函数。 窗口函数可以简单地解释为类似于聚合函数的计算函数，但是通过GROUP BY子句组合的常规聚合会隐藏正在聚合的各个行，最终输出一行，窗口函数聚合后还可以访问当中的各个行，并且可以将这些行中的某些属性添加到结果集中。  # 3.3.1 示例表数据： --建表加载数据 CREATE TABLE employee ( id int, name string, deg string, salary int, dept string ) row format delimited fields terminated by ','; load data local inpath '/root/hivedata/employee.txt' into table employee; 普通聚合： ----sum+group by普通常规聚合操作------------ select dept, sum(salary) as total from employee group by dept; 窗口函数聚合： select id, name, deg, salary, dept, sum(salary) over (partition by dept) as total from employee; # 3.3.2 语法树------- 窗口函数语法树 --------
Function(arg1,..., argn) OVER ([PARTITION BY ] [ORDER BY ]
[])
-- 其中Function(arg1,..., argn) 可以是下面分类中的任意一个
-- 聚合函数：比如sum max avg等
-- 排序函数：比如rank row_number等
-- 分析函数：比如lead lag first_value等
-- OVER [PARTITION BY ] 类似于group by 用于指定分组 每个分组你可以把它叫做窗口
-- 如果没有PARTITION BY 那么整张表的所有行就是一组
-- [ORDER BY ] 用于指定每个分组内的数据排序规则 支持ASC、DESC
-- [] 窗口表达式，用于指定每个窗口中 操作的数据范围 默认是窗口中所有行
# 3.3.3 后续数据准备create table website_pv_info
(
cookieid string, -- cookie1 / cookie2
createtime string, -- 2018-04-14 ...
pv int -- 2 浏览量
) row format delimited
fields terminated by ',';
create table website_url_info
(
cookieid string, -- cookie1 / cookie2
createtime string, -- 访问时间 2018-04-10 10:00:02
url string -- 访问页面 url1 / url2 ....
) row format delimited
fields terminated by ',';
load data local inpath '/root/hivedata/website_pv_info.txt' into table website_pv_info;
load data local inpath '/root/hivedata/website_url_info.txt' into table website_url_info;
# 3.3.4 窗口聚合函数所谓窗口聚合函数指的是sum、max、min、avg这样的聚合函数在窗口中的使用 对于sum+窗口函数，总共有四种用法 sum(...) over( )对表所有行求和sum(...) over( order by ... ) 连续累积求和sum(...) over( partition by... ) 同组内所行求和sum(...) over( partition by... order by ... ) 在每个分组内，连续累积求和需求：求出每个用户的pv数 select cookieid, sum(pv) as total_pv from website_pv_info group by cookieid; 需求：求出网站总的pv数 所有用户所有访问加起来 select cookieid, createtime, pv, sum(pv) over () as total_pv -- 所有行求和 from website_pv_info;需求：求出每个用户总pv数 select cookieid, createtime, pv, sum(pv) over (partition by cookieid) as total_pv -- 同一组所有行求和 from website_pv_info;需求：求出每个用户截止到当天，累计的总pv select cookieid, createtime, pv, -- 同一组内累计求和 sum(pv) over (partition by cookieid order by createtime) as current_total_pv from website_pv_info; # 3.3.5 窗口表达式在sum(...) over( partition by... order by ... )语法完整的情况下，进行累积聚合操作，默认累积聚合行为是：从第一行聚合到当前行。 Window expression窗口表达式给我们提供了一种控制行范围的能力，比如向前2行，向后3行。 其实就是前缀和 关键字：row between preceding：往前following：往后current row：当前行unbounded：边界unbounded preceding 表示从前面的起点unbounded following：表示到后面的终点需求：第一行到当前行，写不写都一样 select cookieid, createtime, pv, sum(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2 from website_pv_info;需求：向前三行至当前行 select cookieid, createtime, pv, sum(pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4 from website_pv_info; # 3.3.6 窗口排序函数（TOPN / nm\frac{n}{m}mn）# row_number家族(TOPN)row_number：在每个分组中，为每行分配一个从1开始的唯一序列号，递增，不考虑重复；(1,2,3,4,5,6,7)rank: 在每个分组中，为每行分配一个从1开始的序列号，考虑重复，挤占后续位置；(1,2,3,3,5,6,7)dense_rank: 在每个分组中，为每行分配一个从1开始的序列号，考虑重复，不挤占后续位置；(1,2,3,3,4,5,6)SELECT cookieid,
createtime,
pv,
RANK() OVER (PARTITION BY cookieid ORDER BY pv desc) AS rn1,
DENSE_RANK() OVER (PARTITION BY cookieid ORDER BY pv desc) AS rn2,
ROW_NUMBER() OVER (PARTITION BY cookieid ORDER BY pv DESC) AS rn3
FROM website_pv_info
WHERE cookieid = 'cookie1';
 需求：找出每个用户访问pv的最多的Top3，重复并列的不考虑 SELECT * from (SELECT cookieid, createtime, pv, ROW_NUMBER() OVER (PARTITION BY cookieid ORDER BY pv DESC) AS seq FROM website_pv_info) as tmp where tmp.seq |
【本文地址】
今日新闻 |
推荐新闻 |