数据与基础分析2

Hive分组、排序与连接查询理解数据流向，选择正确的HiveQL语义讲师：________日期：2026-08-21GROUPBYORDER/SORTJOIN分组→排序→多表分析大数据采集与分析技术·知识链接目录与时间分配123分组查询操作GROUPBY·聚合函数·HAVING35分钟排序查询操作ORDERBY·SORTBY·DISTRIBUTEBY55分钟连接查询操作INNER·LEFT·RIGHT·FULL·LEFTSEMI50分钟课后练习与答疑：10分钟Hive分组、排序与连接查询02学习目标完成指标统计与结果交付本任务主要实现了明细表的构建、数据指标的统计分析，根据统计指标构建主题层，创建开发层数据库dws_traffic，再通过分组、排序、聚合函数等HiveQL查询操作实现各个业务指标统计，并生成指标统计数据表，最后利用Sqoop将数据结果导入MariaDB数据库。技术链明细表构建分组·排序·聚合主题层指标数据表Sqoop→MariaDBHive分组、排序与连接查询0301分组查询操作GROUPBY·聚合函数·HAVING按照一列或多列的值进行分组，将相同值记录归为一组。GROUPBY通常与聚合函数协同使用，并会触发MapReduce任务。知识链接·0104GROUPBY语法GROUPBY子句的语法格式如下。GROUPBYgroupByExpression(,groupByExpression)*groupByExpression表示分组表达式。可以按照一列或多列的值分组，下面通过具体示例说明GROUPBY语句的使用方法。分组查询操作05GROUPBY示例：查询全部唯一订单地址1/3在Node02节点中，查询orders订单表内所有订单地址：SELECTorder_addrFROMorder_report.ordersGROUPBYorder_addr;该语句对order_addr字段进行分组查询，结果仅显示所有唯一值，即分组名称。图5.16查询order_report.orders订单表内所有订单地址分组查询操作06GROUPBY示例：统计各地区订单总金额2/3在分组内配合SUM()聚合函数统计总金额：SELECTorder_addr,ROUND(SUM(order_amount),2)ASsum_amountFROMorder_report.ordersGROUPBYorder_addr;该语句不仅按order_addr分组，还使用SUM()对金额求和，统计每个省、直辖市、自治区的订单总金额。图5.17统计每个省、直辖市、自治区的订单总金额分组查询操作07GROUPBY示例：HAVING筛选分组结果3/3分组统计后的条件不可使用WHERE，应使用HAVING：SELECTorder_addr,ROUND(SUM(order_amount),2)ASsum_amountFROMorder_report.ordersGROUPBYorder_addrHAVINGROUND(SUM(order_amount),2)>300000;图5.18查询订单总金额大于300000元的订单地址及总金额注意SELECT中的每个字段只能是GROUPBY后的分组字段。GROUPBY先于SELECT执行，因此不能使用SELECT定义的列别名。分组查询操作0802排序查询操作ASC·DESC·MapReduce按照一个或多个字段排序，支持升序ASC和降序DESC。Hive支持ORDERBY、SORTBY、CLUSTERBY和DISTRIBUTEBY。知识链接·0209ORDERBY排序SELECTexpression(','expression)*FROMsrcORDERBYcolNamecolOrdercolNullOrder(','colNamecolOrdercolNullOrder)*①查询子句SELECTexpression[,expression...]FROMsrc②排序字段ORDERBYcolName③排序方向ASC升序；DESC降序④NULL位置NULLSFIRST；NULLSLAST排序查询操作10ORDERBY示例：单字段降序1/2对order_amount字段降序排序并显示前11行：SELECT*FROMorder_report.ordersORDERBYorder_amountDESCLIMIT11;图5.19ORDERBY对order_amount字段降序并显示前11行排序查询操作11ORDERBY示例：分组后多字段排序2/2SELECTorder_addr,ROUND(SUM(order_amount),2)ASsum_amount,ROUND(SUM(order_actual_pay),2)ASsum_actual_payFROMorder_report.ordersGROUPBYorder_addrORDERBYsum_amountDESC,sum_actual_payDESC;ORDERBY对查询结果全局排序，仅由一个Reducer处理。数据量大时建议配合LIMIT限制记录条数，否则处理时间会显著延长。图5.20查询结果排序查询操作12SORTBY排序SELECTexpression(','expression)*FROMsrcSORTBYcolNamecolOrder(','colNamecolOrder)*参数SELECTexpression：查询子句colName：排序字段名称colOrder：ASC或DESC可按照多列排序与ORDERBY不同SORTBY用于局部排序，由多个Reducer处理。每个Reducer的输出有序，但整个结果集可能并非全局有序；只有Reducer数量为1时例外。排序查询操作13SORTBY查询示例单字段降序SELECT*FROMordersSORTBYorder_amountDESCLIMIT11;图5.21SORTBY降序排序结果分组后多字段降序GROUPBYorder_addrSORTBYsum_amountDESC,sum_actual_payDESC;图5.22分组并使用SORTBY降序小数据集默认一个Reducer时，结果与ORDERBY相同。排序查询操作14SORTBY联合DISTRIBUTEBY1/3分区排序原理SORTBY为每个Reducer生成一个排序文件。DISTRIBUTEBY控制特定行分配至哪个Reducer，类似MapReduce中的Partition操作，常为后续聚合服务。Hive根据DISTRIBUTEBY后的列和Reducer数量，默认采用Hash算法进行数据分发。MapperHash分区Reducerscore.txt示例数据202301,MySQL,90202302,MySQL,89202303,MySQL,92202301,Python,91202302,Python,93202303,Python,92202301,Java,93202302,Java,92202303,Java,94排序查询操作15创建scores表并准备HDFS数据2/3CREATETABLEIFNOTEXISTSeducation.scores(student_idSTRING,course_nameSTRING,scoreINT)ROWFORMATDELIMITEDFIELDSTERMINATEDBY','LINESTERMINATEDBY'\n'STOREDAStextfileLOCATION'/education/scores';HDFS与Reducer设置hdfsdfs-mkdir-p/education/scoreshdfsdfs-put/root/score.txt/education/scoresSELECT*FROMeducation.scores;手动将Reducer设置为2：setmapred.reduce.tasks=2排序查询操作16比较ORDERBY、SORTBY与DISTRIBUTEBY3/3图5.23education.scores表数据图5.24ORDERBYstudent_idASC,scoreDESC图5.25SORTBYstudent_idASC,scoreDESC图5.26DISTRIBUTEBY+SORTBYORDERBY全局排序；SORTBY局部排序；DISTRIBUTEBY按student_id分区后排序，结果与全局排序一致。CLUSTERBY等于同列DISTRIBUTEBY与SORTBY的结合，但只能升序。排序查询操作1703连接查询操作按字段关系横向组合多个表连接查询基于两个或多个表中的字段关系，将这些表横向连接起来。scoresstudents知识链接·0318JOIN语法与参数SELECT...FROMtable_reference[join_condition][INNERJOIN|{LEFT|RIGHT|FULL}[OUTER]JOIN|LEFTSEMIJOIN]table_reference[join_condition]ONexpression①table_reference：参与连接的表名。②join_condition：连接条件，如WHERE子句。③INNERJOIN：返回两表中所有匹配数据。④LEFTOUTERJOIN：保留左表全部，右表未匹配为NULL。⑤RIGHTOUTERJOIN：保留右表全部，左表未匹配为NULL。⑥FULLOUTERJOIN：返回两表全部，未匹配为NULL。⑦LEFTSEMIJOIN：只返回左表中匹配的数据。⑧ONexpression：指定表之间的关联条件。连接类型决定匹配与未匹配行如何保留。连接查询操作19准备students表与测试数据CREATETABLEIFNOTEXISTSeducation.students(student_idSTRING,student_nameSTRING,birthSTRING,statusSTRING)ROWFORMATDELIMITEDFIELDSTERMINATEDBY','LINESTERMINATEDBY'\n'STOREDAStextfileLOCATION'/education/students';INSERTINTOTABLEeducation.studentsVALUES('202301','张三','2003-08-12','在读'),('202302','李四','2002-12-22','在读'),('202303','王五','2003-02-17','在读'),('202304','马六','2003-07-23','休学');图5.27education.students表数据向education.scores补充测试数据：('202305','MySQL',98),('202305','Python',96),('202305','Java',86);连接查询操作20INNERJOIN内连接SELECTt1.student_id,t2.student_name,t2.status,t1.course_name,t1.scoreFROMeducation.scorest1INNERJOINeducation.studentst2ONt1.student_id=t2.student_id;内连接只返回两表student_id相互匹配的数据行。查询结果中不会出现“田七”以及学号为“202505”的学生数据。图5.28内连接查询结果连接查询操作21左外连接与右外连接LEFTOUTERJOINFROMscorest1LEFTOUTERJOINstudentst2ONt1.student_id=t2.student_id;图5.29左外连接查询结果保留scores全部；students未匹配字段以NULL显示。RIGHTOUTERJOINFROMscorest1RIG