Hive大数据存储与处理 课件 第5章 广电用户账单与订单数据查询进阶

广电用户账单与订单

数据查询进阶统计订单的消费类型统计用户每年消费应付总额统计用户每月消费应付总额统计用户每月实际账单金额查询用户宽带订单的地址数据抽样统计用户订购产品情况统计订单的消费类型掌握Hive的常见内置函数使用条件函数及类型转换函数对订单的消费类型进行分类Hive内置函数1.

Hive内置函数介绍Hive将一些常用功能以内置函数的方式实现，以此简化查询语句，提升查询效率常见Hive内置函数种类数学函数字符函数条件函数转换函数日期函数聚合函数Hive内置函数2.

Hive内置函数查看查看Hive提供的内置函数查看某个函数具体的使用方法SHOWFUNCTIONS;DESCFUNCTIONEXTENDED函数名;条件函数1.条件函数的语法如果查询过程需要根据不同的条件求出不同的结果可以在查询过程中引入条件函数条件函数的语法CASEAWHENBTHENC[WHENDTHENE]*[ELSEF]END对A字段的值进行判断如果A字段的值为B则返回CA为数据表中的一个字段后续还可以添加0个或多个判断分支如果A字段的值为D则返回EB、D的数据类型必须与字段A的保持完全一致，否则会导致语法错误可选部分，添加则代表所有分支条件均不成立，将返回F结束符条件函数2.条件函数与列别名结合使用条件函数的执行结果作为SELECT语句查询结果的一列，可以被视为一个整体可以为条件函数添加列别名列别名添加在条件函数END关键字之后以“[AS]列别名”

的方式添加AS关键字与普通设置列别名的过程一样，为可省略部分类型转换函数在Hive中对不同类型的两个数据进行比较等操作时，Hive会适时地对数据进行隐式的类型转换在隐式转换过程中将使用CAST转换函数也可以直接使用CAST函数对指定的数据显式地进行类型转换类型转换函数的语法CAST(VALUEASTYPE)可以是常量也可以是数据表中的某个字段Hive中的数据类型用于指定VALUE值将要转换的数据类型任务实现统计订单的消费类型——任务描述使用条件函数及类型转换函数对订单的消费类型进行分类：若订单金额大于等于2000.0，则将这笔订单的消费类型认定为“高消费”；若订单金额大于等于1000.0且小于2000.0，则将消费类型认定为“中等消费”；若订单金额小于1000.0，则将消费类型认定为“低消费”对查询结果的消费类型列需要指定一个列名type最终查询结果按照订单金额降序排列为了保证查询结果的有效性，还需对查询结果去除重复数据任务实现统计订单的消费类型——任务实现思路使用distinct关键字进行去重order_index数据表中的cost字段数据的类型为STRING类型，在查询结果的展示中可以使用CAST函数显式地将该字段的数据转换为浮点数使用CASE…WHEN…THEN条件函数对订单金额进行判断，可以设置以下3个分支条件：若订单金额大于等于2000.0为TRUE，则输出“高消费”若订单金额在[1000.0,2000.0)区间为TRUE，则输出“中等消费”其他情况则输出“低消费”使用ORDERBY进行排序统计订单的消费类型统计用户每年消费应付总额统计用户每月消费应付总额统计用户每月实际账单金额查询用户宽带订单的地址数据抽样统计用户订购产品情况统计用户每年消费应付总额掌握Hive中的字符函数使用字符函数对账单数据表mmconsume_billevents中的数据进行统计，计算每个用户的年度消费应付总额字符函数1.大小写转换函数可以是常量也可以是数据表的某个字段LOWER函数可以用于将字符串中的字符都转换成小写字符字符串中的小写字符维持原状只将大写字符转换成小写字符语法结构：LOWER(STRINGS)相应地，可以使用UPPER函数将字符串中的字符都转换成大写字符字符函数2.字符串长度函数可以是常量也可以是数据表的某个字段LENGTH函数可以用于获取字符串的长度语法结构：LENGTH(STRINGS)LENGTH函数的返回结果是S包含的字符个数字符函数3.字符串拼接函数S1、S2是两个字符串均可以是常量也可以是数据表的某个字段CONCAT函数可以用于将两个字符串拼接起来形成一个新的字符串语法结构：CONCAT(STRINGS1,STRINGS2)字符函数4.字符串截取函数代表被截取的字符串可以是常量也可以是数据表的某个字段SUBSTR函数可以用于从指定字符串中截取部分字符语法结构：SUBSTR(STRINGS,INTSTART,INTLEN)START是整数，代表从第几位字符开始截取LEN同样是整数，代表共截取几个字符若省略LEN，代表从第START位开始截取至字符串的最后一位字符函数5.去除空格函数需要去除空格的字符串可以是常量也可以是数据表的某个字段TRIM函数可以用于将字符串前后出现的空格全部去掉语法结构：TRIM(STRINGS)任务实现统计用户每年消费应付总额任务描述使用字符函数对账单数据表mmconsume_billevents中的数据进行统计，计算每个用户的年度消费应付总额任务实现思路使用字符截取函数SUBSTR对year_month字段值进行截取，截取该字段值的前4个字符，得到具体的年度账单结合GROUPBY关键字、SUM函数，按照用户编号及年度账单进行分组，计算每个用户每年消费应付总额统计订单的消费类型统计用户每年消费应付总额统计用户每月消费应付总额统计用户每月实际账单金额查询用户宽带订单的地址数据抽样统计用户订购产品情况统计用户每月消费应付总额掌握Hive中的日期函数日期函数统计每个用户每个月消费的应付总额日期获取类函数1.单个日期数据获取函数可以是常量也可以是数据表的某个字段需要符合日期格式Hive提供了丰富的日期获取类函数，可以分别用于获取对应的年、月、日等单个数据也可以获取整体数据或根据需要获取相应数据符在SELECT语句中可以通过YEAR、MONTH、DAY、HOUR、MINUTE、SECOND分别获取时间字符串中对应的年、月、日、时、分、秒语法结构：YEAR(STRINGS)Hive从字符串中获取年、月、日、时、分、秒时有容错机制例如月份大于12，每满12会自动进位，将年份加1，最终得到的月份的数值一定是1～12这个范围内的日期获取类函数2.

TO_DATE函数&WEEKOFYEAR函数TO_DATE函数可以用于从日期格式的字符串中获取日期部分语法结构：TO_DATE(STRINGS)WEEKOFYEAR函数可以用于返回日期在本年中的周数语法结构：WEEKOFYEAR(STRINGS)日期获取类函数3.TRUNC函数需要符合日期格式的字符串TRUNC函数可以用于截取日期格式字符串中的时间，截取之后日期会归零个语法结构：TRUNC(STRINGS[,STRINGFMT])截取的格式支持的格式包括“MONTH”“MON”“MM”“YEAR”“YYYY”“YY”，分别表示按照月份及年份截取若按照月份截取，则将日期归零为当月1号若按照年份截取，则将日期归零为当年的1月1日日期计算类函数1.DATEDIFF函数结束日期DATEDIFF函数可以用于比较两个日期,返回结束日期与开始日期之间相隔的天数语法结构：DATEDIFF(STRINGEND,STRINGSTART)开始日期DATEDIFF函数并不要求结束日期晚于开始日期，若结束日期早于开始日期，则返回结果为负数日期计算类函数2.日期增减函数DATE_ADD函数可以用于返回基于开始日期增加指定天数之后的日期语法结构：DATE_ADD(STRINGSTARTDATE,INTDAYS)DATE_SUB函数则可以用于返回基于开始日期减少指定天数之后的日期语法结构：DATE_SUB(STRINGSTARTDATE,INTDAYS)日期计算类函数3.ADD_MONTHS函数ADD_MONTHS函数可以用于返回基于指定日期增加指定月数之后的日期语法结构：ADD_MONTHS(STRINGSTARTDATE,INTMONTHS)可以是正数也可以是负数若为正数则表示增加指定月数若为负数则表示减少指定月数任务实现统计用户每月消费应付总额任务描述利用日期函数统计每个用户每个月消费的应付总额任务实现思路根据mmconsume_billevents数据表进行数据统计在时间的获取过程中可以使用Hive的MONTH函数为避免出现类似将2022年7月及2021年7月的数据都作为同样的7月数据进行累加，在分组过程中，需要以用户编号、年份及月份3个值作为分组依据统计订单的消费类型统计用户每年消费应付总额统计用户每月消费应付总额统计用户每月实际账单金额查询用户宽带订单的地址数据抽样统计用户订购产品情况统计用户每月实际账单金额掌握HIVE中的数学函数利用数学函数统计2022年度月均账单金额最高的20个用户数学函数1.四则运算符Hive支持直接在字段值及常量之间进行四则运算四则运算符含义+加法-减法*乘法/除法数学函数2.取整函数将浮点数转换为整数，称为取整，Hive提供了3种取整函数向上取整函数CEIL(DOUBLED)返回的整数为比D大的最小整数向下取整函数FLOOR(DOUBLED)返回的整数为比D小的最大整数四舍五入函数ROUND(DOUBLED[,INTI])参数I代表四舍五入过程中保留的小数的位数,省略则代表不保留小数，转为整数任务实现统计用户每月实际账单金额任务描述利用数学函数统计2022年度月均账单金额最高的20个用户任务实现思路mmconsume_billevents数据表中should_pay字段表示账单的应收金额，favour_fee字段表示账单的优惠金额，因此用户账单的实付金额为should_pay-favour_fee根据用户编号及账单时间两个字段的值进行分组，筛选账单时间分组中年度取值为2022年的数据，并且使用AVG函数计算月均账单金额根据日常数据使用习惯，使用ROUND函数对金额进行处理，保留两位小数对平均值进行降序排列，使用LIMIT关键字获取前20名用户统计订单的消费类型统计用户每年消费应付总额统计用户每月消费应付总额统计用户每月实际账单金额查询用户宽带订单的地址数据抽样统计用户订购产品情况查询用户宽带订单的地址数据掌握HIVE中的连接查询通过连接查询获取完整的宽带订单数据JOIN语句的使用1.JOIN语句的语法结构连接类型对应的关键字SELECTselect_expr,select_expr,...FROMleft_table_reference[JOINTYPE]right_table_reference[JOIN_CONDITION]连接查询中的其中一个表的表名，称为左表右表Hive中的连接查询分为内连接、外连接以及左半开连接JOIN语句的语法结构连接条件，通常使用ON子句指定JOIN语句的执行顺序是从左→右一般都将大表作为右表，以节省内存JOIN语句的使用2.内连接只保留连接的两个表中与连接条件相匹配的数据关键字为INNERJOIN在连接查询中通常需要引入表别名，从而简化查询语句JOIN语句的使用3.外连接左外连接：LEFT[OUTER]JOIN返回左表中的所有数据，若没有符合连接条件的右表数据，则右表数据为NULL右外连接：RIGHT[OUTER]JOIN返回右表中的所有数据，若没有符合连接条件的左表数据，则左表数据为NULL全外连接：FULL[OUTER]JOIN包含所有左表与右表的数据，任意表的指定字段没有符合条件的值，则使用NULL值代替全外连接=左外连接+右外连接-内连接JOIN语句的使用4.左半开连接关键字：LEFTSEMIJOIN查询结果:结果是左表的数据，前提是数据对于右表来说，能够满足连接条件最终查询结果中并不包含任何右表的字段SELECT和WHERE语句都不能引用右表中的字段因为左表中的一条数据只要匹配到右表中的一条数据，就会停止扫描右表，所以左半开连接查询比内连接查询的效率高SELECTSENTENCE1UNIONALLSELECTSENTENCE2UNIONALL关键字可以用于联合两个或多个SELECT语句的结果集将其最终合并为一个结果集合并过程不能消除重复行UNIONALL的语法结构UNIONALL关键字1.UNIONALL的语法结构完整的SELECT语句，作为UNIONALL语句的子查询每一个UNIONALL子查询返回的列的数量和名字必须一样，且对应的每个字段的数据的类型也必须一致任务实现查询用户宽带订单的地址数据任务描述通过连接查询获取完整的宽带订单数据任务实现思路order_index表中使用