Hive大數(shù)據(jù)存儲與處理 課件 第5章 廣電用戶賬單與訂單數(shù)據(jù)查詢進(jìn)階

廣電用戶賬單與訂單

數(shù)據(jù)查詢進(jìn)階統(tǒng)計(jì)訂單的消費(fèi)類型統(tǒng)計(jì)用戶每年消費(fèi)應(yīng)付總額統(tǒng)計(jì)用戶每月消費(fèi)應(yīng)付總額統(tǒng)計(jì)用戶每月實(shí)際賬單金額查詢用戶寬帶訂單的地址數(shù)據(jù)抽樣統(tǒng)計(jì)用戶訂購產(chǎn)品情況統(tǒng)計(jì)訂單的消費(fèi)類型掌握Hive的常見內(nèi)置函數(shù)使用條件函數(shù)及類型轉(zhuǎn)換函數(shù)對訂單的消費(fèi)類型進(jìn)行分類Hive內(nèi)置函數(shù)1.

Hive內(nèi)置函數(shù)介紹Hive將一些常用功能以內(nèi)置函數(shù)的方式實(shí)現(xiàn)，以此簡化查詢語句，提升查詢效率常見Hive內(nèi)置函數(shù)種類數(shù)學(xué)函數(shù)字符函數(shù)條件函數(shù)轉(zhuǎn)換函數(shù)日期函數(shù)聚合函數(shù)Hive內(nèi)置函數(shù)2.

Hive內(nèi)置函數(shù)查看查看Hive提供的內(nèi)置函數(shù)查看某個函數(shù)具體的使用方法SHOWFUNCTIONS;DESCFUNCTIONEXTENDED函數(shù)名;條件函數(shù)1.條件函數(shù)的語法如果查詢過程需要根據(jù)不同的條件求出不同的結(jié)果可以在查詢過程中引入條件函數(shù)條件函數(shù)的語法CASEAWHENBTHENC[WHENDTHENE]*[ELSEF]END對A字段的值進(jìn)行判斷如果A字段的值為B則返回CA為數(shù)據(jù)表中的一個字段后續(xù)還可以添加0個或多個判斷分支如果A字段的值為D則返回EB、D的數(shù)據(jù)類型必須與字段A的保持完全一致，否則會導(dǎo)致語法錯誤可選部分，添加則代表所有分支條件均不成立，將返回F結(jié)束符條件函數(shù)2.條件函數(shù)與列別名結(jié)合使用條件函數(shù)的執(zhí)行結(jié)果作為SELECT語句查詢結(jié)果的一列，可以被視為一個整體可以為條件函數(shù)添加列別名列別名添加在條件函數(shù)END關(guān)鍵字之后以“[AS]列別名”

的方式添加AS關(guān)鍵字與普通設(shè)置列別名的過程一樣，為可省略部分類型轉(zhuǎn)換函數(shù)在Hive中對不同類型的兩個數(shù)據(jù)進(jìn)行比較等操作時，Hive會適時地對數(shù)據(jù)進(jìn)行隱式的類型轉(zhuǎn)換在隱式轉(zhuǎn)換過程中將使用CAST轉(zhuǎn)換函數(shù)也可以直接使用CAST函數(shù)對指定的數(shù)據(jù)顯式地進(jìn)行類型轉(zhuǎn)換類型轉(zhuǎn)換函數(shù)的語法CAST(VALUEASTYPE)可以是常量也可以是數(shù)據(jù)表中的某個字段Hive中的數(shù)據(jù)類型用于指定VALUE值將要轉(zhuǎn)換的數(shù)據(jù)類型任務(wù)實(shí)現(xiàn)統(tǒng)計(jì)訂單的消費(fèi)類型——任務(wù)描述使用條件函數(shù)及類型轉(zhuǎn)換函數(shù)對訂單的消費(fèi)類型進(jìn)行分類：若訂單金額大于等于2000.0，則將這筆訂單的消費(fèi)類型認(rèn)定為“高消費(fèi)”；若訂單金額大于等于1000.0且小于2000.0，則將消費(fèi)類型認(rèn)定為“中等消費(fèi)”；若訂單金額小于1000.0，則將消費(fèi)類型認(rèn)定為“低消費(fèi)”對查詢結(jié)果的消費(fèi)類型列需要指定一個列名type最終查詢結(jié)果按照訂單金額降序排列為了保證查詢結(jié)果的有效性，還需對查詢結(jié)果去除重復(fù)數(shù)據(jù)任務(wù)實(shí)現(xiàn)統(tǒng)計(jì)訂單的消費(fèi)類型——任務(wù)實(shí)現(xiàn)思路使用distinct關(guān)鍵字進(jìn)行去重order_index數(shù)據(jù)表中的cost字段數(shù)據(jù)的類型為STRING類型，在查詢結(jié)果的展示中可以使用CAST函數(shù)顯式地將該字段的數(shù)據(jù)轉(zhuǎn)換為浮點(diǎn)數(shù)使用CASE…WHEN…THEN條件函數(shù)對訂單金額進(jìn)行判斷，可以設(shè)置以下3個分支條件：若訂單金額大于等于2000.0為TRUE，則輸出“高消費(fèi)”若訂單金額在[1000.0,2000.0)區(qū)間為TRUE，則輸出“中等消費(fèi)”其他情況則輸出“低消費(fèi)”使用ORDERBY進(jìn)行排序統(tǒng)計(jì)訂單的消費(fèi)類型統(tǒng)計(jì)用戶每年消費(fèi)應(yīng)付總額統(tǒng)計(jì)用戶每月消費(fèi)應(yīng)付總額統(tǒng)計(jì)用戶每月實(shí)際賬單金額查詢用戶寬帶訂單的地址數(shù)據(jù)抽樣統(tǒng)計(jì)用戶訂購產(chǎn)品情況統(tǒng)計(jì)用戶每年消費(fèi)應(yīng)付總額掌握Hive中的字符函數(shù)使用字符函數(shù)對賬單數(shù)據(jù)表mmconsume_billevents中的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，計(jì)算每個用戶的年度消費(fèi)應(yīng)付總額字符函數(shù)1.大小寫轉(zhuǎn)換函數(shù)可以是常量也可以是數(shù)據(jù)表的某個字段LOWER函數(shù)可以用于將字符串中的字符都轉(zhuǎn)換成小寫字符字符串中的小寫字符維持原狀只將大寫字符轉(zhuǎn)換成小寫字符語法結(jié)構(gòu)：LOWER(STRINGS)相應(yīng)地，可以使用UPPER函數(shù)將字符串中的字符都轉(zhuǎn)換成大寫字符字符函數(shù)2.字符串長度函數(shù)可以是常量也可以是數(shù)據(jù)表的某個字段LENGTH函數(shù)可以用于獲取字符串的長度語法結(jié)構(gòu)：LENGTH(STRINGS)LENGTH函數(shù)的返回結(jié)果是S包含的字符個數(shù)字符函數(shù)3.字符串拼接函數(shù)S1、S2是兩個字符串均可以是常量也可以是數(shù)據(jù)表的某個字段CONCAT函數(shù)可以用于將兩個字符串拼接起來形成一個新的字符串語法結(jié)構(gòu)：CONCAT(STRINGS1,STRINGS2)字符函數(shù)4.字符串截取函數(shù)代表被截取的字符串可以是常量也可以是數(shù)據(jù)表的某個字段SUBSTR函數(shù)可以用于從指定字符串中截取部分字符語法結(jié)構(gòu)：SUBSTR(STRINGS,INTSTART,INTLEN)START是整數(shù)，代表從第幾位字符開始截取LEN同樣是整數(shù)，代表共截取幾個字符若省略LEN，代表從第START位開始截取至字符串的最后一位字符函數(shù)5.去除空格函數(shù)需要去除空格的字符串可以是常量也可以是數(shù)據(jù)表的某個字段TRIM函數(shù)可以用于將字符串前后出現(xiàn)的空格全部去掉語法結(jié)構(gòu)：TRIM(STRINGS)任務(wù)實(shí)現(xiàn)統(tǒng)計(jì)用戶每年消費(fèi)應(yīng)付總額任務(wù)描述使用字符函數(shù)對賬單數(shù)據(jù)表mmconsume_billevents中的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，計(jì)算每個用戶的年度消費(fèi)應(yīng)付總額任務(wù)實(shí)現(xiàn)思路使用字符截取函數(shù)SUBSTR對year_month字段值進(jìn)行截取，截取該字段值的前4個字符，得到具體的年度賬單結(jié)合GROUPBY關(guān)鍵字、SUM函數(shù)，按照用戶編號及年度賬單進(jìn)行分組，計(jì)算每個用戶每年消費(fèi)應(yīng)付總額統(tǒng)計(jì)訂單的消費(fèi)類型統(tǒng)計(jì)用戶每年消費(fèi)應(yīng)付總額統(tǒng)計(jì)用戶每月消費(fèi)應(yīng)付總額統(tǒng)計(jì)用戶每月實(shí)際賬單金額查詢用戶寬帶訂單的地址數(shù)據(jù)抽樣統(tǒng)計(jì)用戶訂購產(chǎn)品情況統(tǒng)計(jì)用戶每月消費(fèi)應(yīng)付總額掌握Hive中的日期函數(shù)日期函數(shù)統(tǒng)計(jì)每個用戶每個月消費(fèi)的應(yīng)付總額日期獲取類函數(shù)1.單個日期數(shù)據(jù)獲取函數(shù)可以是常量也可以是數(shù)據(jù)表的某個字段需要符合日期格式Hive提供了豐富的日期獲取類函數(shù)，可以分別用于獲取對應(yīng)的年、月、日等單個數(shù)據(jù)也可以獲取整體數(shù)據(jù)或根據(jù)需要獲取相應(yīng)數(shù)據(jù)符在SELECT語句中可以通過YEAR、MONTH、DAY、HOUR、MINUTE、SECOND分別獲取時間字符串中對應(yīng)的年、月、日、時、分、秒語法結(jié)構(gòu)：YEAR(STRINGS)Hive從字符串中獲取年、月、日、時、分、秒時有容錯機(jī)制例如月份大于12，每滿12會自動進(jìn)位，將年份加1，最終得到的月份的數(shù)值一定是1～12這個范圍內(nèi)的日期獲取類函數(shù)2.

TO_DATE函數(shù)&WEEKOFYEAR函數(shù)TO_DATE函數(shù)可以用于從日期格式的字符串中獲取日期部分語法結(jié)構(gòu)：TO_DATE(STRINGS)WEEKOFYEAR函數(shù)可以用于返回日期在本年中的周數(shù)語法結(jié)構(gòu)：WEEKOFYEAR(STRINGS)日期獲取類函數(shù)3.TRUNC函數(shù)需要符合日期格式的字符串TRUNC函數(shù)可以用于截取日期格式字符串中的時間，截取之后日期會歸零個語法結(jié)構(gòu)：TRUNC(STRINGS[,STRINGFMT])截取的格式支持的格式包括“MONTH”“MON”“MM”“YEAR”“YYYY”“YY”，分別表示按照月份及年份截取若按照月份截取，則將日期歸零為當(dāng)月1號若按照年份截取，則將日期歸零為當(dāng)年的1月1日日期計(jì)算類函數(shù)1.DATEDIFF函數(shù)結(jié)束日期DATEDIFF函數(shù)可以用于比較兩個日期,返回結(jié)束日期與開始日期之間相隔的天數(shù)語法結(jié)構(gòu)：DATEDIFF(STRINGEND,STRINGSTART)開始日期DATEDIFF函數(shù)并不要求結(jié)束日期晚于開始日期，若結(jié)束日期早于開始日期，則返回結(jié)果為負(fù)數(shù)日期計(jì)算類函數(shù)2.日期增減函數(shù)DATE_ADD函數(shù)可以用于返回基于開始日期增加指定天數(shù)之后的日期語法結(jié)構(gòu)：DATE_ADD(STRINGSTARTDATE,INTDAYS)DATE_SUB函數(shù)則可以用于返回基于開始日期減少指定天數(shù)之后的日期語法結(jié)構(gòu)：DATE_SUB(STRINGSTARTDATE,INTDAYS)日期計(jì)算類函數(shù)3.ADD_MONTHS函數(shù)ADD_MONTHS函數(shù)可以用于返回基于指定日期增加指定月數(shù)之后的日期語法結(jié)構(gòu)：ADD_MONTHS(STRINGSTARTDATE,INTMONTHS)可以是正數(shù)也可以是負(fù)數(shù)若為正數(shù)則表示增加指定月數(shù)若為負(fù)數(shù)則表示減少指定月數(shù)任務(wù)實(shí)現(xiàn)統(tǒng)計(jì)用戶每月消費(fèi)應(yīng)付總額任務(wù)描述利用日期函數(shù)統(tǒng)計(jì)每個用戶每個月消費(fèi)的應(yīng)付總額任務(wù)實(shí)現(xiàn)思路根據(jù)mmconsume_billevents數(shù)據(jù)表進(jìn)行數(shù)據(jù)統(tǒng)計(jì)在時間的獲取過程中可以使用Hive的MONTH函數(shù)為避免出現(xiàn)類似將2022年7月及2021年7月的數(shù)據(jù)都作為同樣的7月數(shù)據(jù)進(jìn)行累加，在分組過程中，需要以用戶編號、年份及月份3個值作為分組依據(jù)統(tǒng)計(jì)訂單的消費(fèi)類型統(tǒng)計(jì)用戶每年消費(fèi)應(yīng)付總額統(tǒng)計(jì)用戶每月消費(fèi)應(yīng)付總額統(tǒng)計(jì)用戶每月實(shí)際賬單金額查詢用戶寬帶訂單的地址數(shù)據(jù)抽樣統(tǒng)計(jì)用戶訂購產(chǎn)品情況統(tǒng)計(jì)用戶每月實(shí)際賬單金額掌握HIVE中的數(shù)學(xué)函數(shù)利用數(shù)學(xué)函數(shù)統(tǒng)計(jì)2022年度月均賬單金額最高的20個用戶數(shù)學(xué)函數(shù)1.四則運(yùn)算符Hive支持直接在字段值及常量之間進(jìn)行四則運(yùn)算四則運(yùn)算符含義+加法-減法*乘法/除法數(shù)學(xué)函數(shù)2.取整函數(shù)將浮點(diǎn)數(shù)轉(zhuǎn)換為整數(shù)，稱為取整，Hive提供了3種取整函數(shù)向上取整函數(shù)CEIL(DOUBLED)返回的整數(shù)為比D大的最小整數(shù)向下取整函數(shù)FLOOR(DOUBLED)返回的整數(shù)為比D小的最大整數(shù)四舍五入函數(shù)ROUND(DOUBLED[,INTI])參數(shù)I代表四舍五入過程中保留的小數(shù)的位數(shù),省略則代表不保留小數(shù)，轉(zhuǎn)為整數(shù)任務(wù)實(shí)現(xiàn)統(tǒng)計(jì)用戶每月實(shí)際賬單金額任務(wù)描述利用數(shù)學(xué)函數(shù)統(tǒng)計(jì)2022年度月均賬單金額最高的20個用戶任務(wù)實(shí)現(xiàn)思路mmconsume_billevents數(shù)據(jù)表中should_pay字段表示賬單的應(yīng)收金額，favour_fee字段表示賬單的優(yōu)惠金額，因此用戶賬單的實(shí)付金額為should_pay-favour_fee根據(jù)用戶編號及賬單時間兩個字段的值進(jìn)行分組，篩選賬單時間分組中年度取值為2022年的數(shù)據(jù)，并且使用AVG函數(shù)計(jì)算月均賬單金額根據(jù)日常數(shù)據(jù)使用習(xí)慣，使用ROUND函數(shù)對金額進(jìn)行處理，保留兩位小數(shù)對平均值進(jìn)行降序排列，使用LIMIT關(guān)鍵字獲取前20名用戶統(tǒng)計(jì)訂單的消費(fèi)類型統(tǒng)計(jì)用戶每年消費(fèi)應(yīng)付總額統(tǒng)計(jì)用戶每月消費(fèi)應(yīng)付總額統(tǒng)計(jì)用戶每月實(shí)際賬單金額查詢用戶寬帶訂單的地址數(shù)據(jù)抽樣統(tǒng)計(jì)用戶訂購產(chǎn)品情況查詢用戶寬帶訂單的地址數(shù)據(jù)掌握HIVE中的連接查詢通過連接查詢獲取完整的寬帶訂單數(shù)據(jù)JOIN語句的使用1.JOIN語句的語法結(jié)構(gòu)連接類型對應(yīng)的關(guān)鍵字SELECTselect_expr,select_expr,...FROMleft_table_reference[JOINTYPE]right_table_reference[JOIN_CONDITION]連接查詢中的其中一個表的表名，稱為左表右表Hive中的連接查詢分為內(nèi)連接、外連接以及左半開連接JOIN語句的語法結(jié)構(gòu)連接條件，通常使用ON子句指定JOIN語句的執(zhí)行順序是從左→右一般都將大表作為右表，以節(jié)省內(nèi)存JOIN語句的使用2.內(nèi)連接只保留連接的兩個表中與連接條件相匹配的數(shù)據(jù)關(guān)鍵字為INNERJOIN在連接查詢中通常需要引入表別名，從而簡化查詢語句JOIN語句的使用3.外連接左外連接：LEFT[OUTER]JOIN返回左表中的所有數(shù)據(jù)，若沒有符合連接條件的右表數(shù)據(jù)，則右表數(shù)據(jù)為NULL右外連接：RIGHT[OUTER]JOIN返回右表中的所有數(shù)據(jù)，若沒有符合連接條件的左表數(shù)據(jù)，則左表數(shù)據(jù)為NULL全外連接：FULL[OUTER]JOIN包含所有左表與右表的數(shù)據(jù)，任意表的指定字段沒有符合條件的值，則使用NULL值代替全外連接=左外連接+右外連接-內(nèi)連接JOIN語句的使用4.左半開連接關(guān)鍵字：LEFTSEMIJOIN查詢結(jié)果:結(jié)果是左表的數(shù)據(jù)，前提是數(shù)據(jù)對于右表來說，能夠滿足連接條件最終查詢結(jié)果中并不包含任何右表的字段SELECT和WHERE語句都不能引用右表中的字段因?yàn)樽蟊碇械囊粭l數(shù)據(jù)只要匹配到右表中的一條數(shù)據(jù)，就會停止掃描右表，所以左半開連接查詢比內(nèi)連接查詢的效率高SELECTSENTENCE1UNIONALLSELECTSENTENCE2UNIONALL關(guān)鍵字可以用于聯(lián)合兩個或多個SELECT語句的結(jié)果集將其最終合并為一個結(jié)果集合并過程不能消除重復(fù)行UNIONALL的語法結(jié)構(gòu)UNIONALL關(guān)鍵字1.UNIONALL的語法結(jié)構(gòu)完整的SELECT語句，作為UNIONALL語句的子查詢每一個UNIONALL子查詢返回的列的數(shù)量和名字必須一樣，且對應(yīng)的每個字段的數(shù)據(jù)的類型也必須一致任務(wù)實(shí)現(xiàn)查詢用戶寬帶訂單的地址數(shù)據(jù)任務(wù)描述通過連接查詢獲取完整的寬帶訂單數(shù)據(jù)任務(wù)實(shí)現(xiàn)思路order_index表中使用