Ⅰ Hivesql核心技能之常用函數
目標：
1、掌握hive基礎語法、常用函數及其組合使用
2、掌握一些基本業務指標的分析思路與實現技巧
1）某次經營活動中，商家發起了「異性拼團購」，試著針對某個地區的用戶進行推廣，找出匹配用戶。
注意：如果該表是一個分區表，則where條件中必須對分區欄位進行限制
2）選出在2018年12月31日，購買的商品品類是food的用戶名、購買數量、支付金額
3）試著對本公司2019年第一季度商品的熱度與價值度進行分析。
"2019年一月到三月，每個品類有多少人購買，累計金額是多少"
GROUP BY ... HAVING（分類匯總過濾）
4）找出在2019年4月支付金額超過5萬元的用戶，給這些VIP用戶贈送優惠券
5）2019年4月，支付金額最多的TOP5用戶
查看Hive中的函數：show functions；
查看具體函數的用法：1）desc function 函數名；2）desc function extended函數名；
6）
7）用戶的首次激活時間，與2019年5月1日的日期間隔。
datediff(string enddate,string startdate):結束日期減去開始日期的天數
拓展:日期增加函數（增加天數）、減少函數（減少天數） —— date_add、date_sub（類型要是string類型的）
date_add（string startdate,int days)、date_sub（string startdate,int days)
case when 函數
8）統計以下四個年齡段20歲以下、20-30歲、30-40歲、40歲以上的用戶數
case when 的時候不用加group by，只有在聚合函數的時候才需要group by
if 函數
9）統計每個性別的用戶等級高低分布情況（假設level大於5為高級，注意列名不能使用中文的）
10）分析每個月都拉新情況
substring(stringA,INT start,int len),substr(stringA,INT start,int len)，截取起始位置和截取長度
extra1需要解析json欄位，然後用$.key取出想要獲取的value;
extra2使用的是中括弧加引號的方式進行欄位提取和分組；
兩種寫法要看欄位類型的不同採取不同的方式
11）求不同手機品牌的用戶數
12）ELLA用戶的2018年的平均每次支付金額，以及2018年最大的支付日期和最小的支付日期的間隔
13）2018年購買的商品品類在兩個以上的用戶數
步驟總結：
1、先求出每個人購買的商品品類書
2、篩選出購買商品品類書大於2的用戶
3、統計符合條件的用戶有多少個
14）用戶激活時間在2018年，年齡段在20-30歲和30-40歲的婚姻情況分布
步驟總結：
1、先選出激活時間在2018年的用戶，並把他們所在的年齡段計算好，並提取出婚姻狀況；
如何select後面沒有進行聚合，則可以不用使用group by，直接使用where進行過濾就可以；
2、取出年齡段在20-30歲和30-40歲的用戶，把他們的婚姻狀況轉義成可理解的說明；
3、聚合計算，針對年齡段，婚姻狀況的聚合
Ⅱ HiveSQL核心技能之窗口計算
目標：
1、掌握 sum()、avg()等用於累計計算的聚合函數，學會對行數的限制（移動計算）；
2、掌握 row_number(),rank()、dense_rank()用於排序的函數；
3、掌握 ntile()用於分組查詢的函數；
4、掌握 lag()、lead()偏移分析函數
窗口函數（window function）：
與聚合函數類似，但是窗口函數是每一行數據都生成一個結果，聚合函數可以將多行數據按照規定聚合為一行，一般來說聚合後的行數要少於聚合前的行數，但是有時我們想要既顯示聚合前的數據，又要顯示聚合後的數據，這時便引入了窗口函數， 窗口函數是在 select 時執行的，位於 order by 之前 。
在日常工作中，經常遇到 計算截止某月或某天的累計數值 ，在Excel可以通過函數來實現，
在HiveSQL里，可以利用窗口函數實現。
1）2018年每月的支付總額和當年累計支付總額
2）對2017年和2018年公司的支付總額按月度累計進行分析，按年度進行匯總
說明：1、over中的 partition by 起到分組的作用；
2、order by 按照什麼順序進行累加，升序ASC、降序DESC，默認升序
3、正確的分組是非常重要的，partition by 後面的欄位是需要累計計算的區域，需要仔細理解
（計算三日留存、七日留存、三十日留存等方式可以使用這個函數。）
3）對2018年每個月的近三個月進行移動的求平均支付金額
用法：這三個函數的作用都是返回相應規則的排序序號，由於排序函數不是二次聚合計算，因此不一定要使用子查詢
4）2019年1月，用戶購買商品品類數量的排名
5）選出2019年支付金額排名在第10、20、30名的用戶
6）將2019年1月的支付用戶，按照支付金額分成5組
7）選出2019年退款金額排名前10%的用戶
說明：Lag和Lead分析函數可以在同一次查詢中取出同一欄位的 前N行數據(Lag)和後N行的數據(Lead) 作為獨立的列。
在實際應用當中，若要用到取今天和昨天的某欄位差值時，Lag和Lead函數的應用就顯得尤為重要。
當然，這種操作可以用表的 自連接實現 ，但是Lag和Lead與 left join、 right join等自連接相比，效率更高，SQL語句更簡潔。
8）支付時間間隔超過100天的用戶數(這一次購買距離下一次購買的時間？，注意datediff函數是日期大的在前面)
9）每個城市，不同性別，2018年支付金額最高的TOP3用戶
步驟總結：
1、首先篩選出每個用戶和每個用戶總的消費金額；
2、對兩個表進行連接提取需要的欄位；
3、對連接後的表進行二次聚合計算，計算出不同城市、性別的金額排名；
4、對二次聚合計算的表進行條件篩選提取
10）每個手機品牌退款金額前25%的用戶
步驟總結：
1、首先篩選出每個用戶和每個用戶的總退款金額；
2、對兩個表進行連接提取需要的欄位；
3、對連接後的表進行按手機品牌內分組；
4、對分組後的表進行條件篩選提取
Ⅲ HIve中SQL如何判斷一個欄位是連續10個數字
目前我知道的方法是把你希望添加的數據寫入到文本中，然後從文本導入到你的表格中。
但是，hive不知道oracle的insert into , update。
load data [local] inpath 'yourfile_location' [overwrite] into your_table;
Ⅳ Hive SQL控制map數和rece數
讀取小文件較多，那麼則需要在map端進行小文件合並，參數設置如下：
-- 設置輸入文件格式
set hive.input.format = org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
-- 是否支持可切分的CombieInputFormat ，true是支持
set hive.hadoop.supports.splittable.combineinputformat = true;
set maprece.input.fileinputformat.split.maxsize = 256000000;
set maprece.input.fileinputformat.split.minsize.per.node=256000000;
set maprece.input.fileinputformat.split.minsize.per.rack=256000000;
在設置動態分區後，產生的文件數會取決於map數和分區數的大小，假設動態分區初始有N個map數，同時生成M個分區，則中間會生成N*M個文件，通常這種情況就是讓大部分數據盡量輸出到一個rece中進行處理，但是有些HiveSql不會產生rece，也就是說文件最後沒有進行合並處理，這種情況下可以用distribute by rand()的方式保證數據進行一次rece操作，實現文件的合並。
兩種處理方式參數設置如下：
a. 設置rece個數
set mapred.rece.tasks=50;
insert into table xxx
select * from xxx distribute by rand();
備註：set設置的參數是生成的文件個數，distribute by rand()保證數據隨機分配到50個文件中。
b. 設置每個recer處理的數據
set hive.exec.recers.bytes.per.recer=5120000000;
insert into table xxx
select * from xxx distribute by rand();
備註：set設置的參數是生成的文件大小，distribute by rand()保證數據的平均大小是512Mb。
Ⅳ hive sql 優化的常用手段有哪些
1、join連接時的優化：當三個或多個以上的表進行join操作時，如果每個on使用相同的欄位連接時只會產生一個maprece。
2、join連接時的優化：當多個表進行查詢時，從左到右表的大小順序應該是從小到大。原因：hive在對每行記錄操作時會把其他表先緩存起來，直到掃描最後的表進行計算
3、在where字句中增加分區過濾器。
4、當可以使用left semi join 語法時不要使用inner join，前者效率更高。原因：對於左表中指定的一條記錄，一旦在右表中找到立即停止掃描。
Ⅵ Hive sql及窗口函數
hive函數：
1、根據指定條件返回結果：case when then else end as
2、基本類型轉換：CAST（）
3、nvl：處理空欄位：三個str時，是否為空可以指定返回不同的值
4、sql通配符： https://www.w3school.com.cn/sql/sql_wildcards.asp
5、count(1)與COUNT(*)：返回行數
如果表沒有主鍵，那麼count（1）比count（*）快；
如果有主鍵，那麼count（主鍵，聯合主鍵）比count（*）快；
count(1)跟count(主鍵)一樣，只掃描主鍵。count(*)跟count(非主鍵)一樣，掃描整個表。明顯前者更快一些。
性能問題：
1.任何情況下SELECT COUNT(*) FROM tablename是最優選擇,(指沒有where的情況）；
2.盡量減少SELECT COUNT(*) FROM tablename WHERE COL = 『value』 這種查詢；
3.杜絕SELECT COUNT(COL) FROM tablename WHERE COL2 = 『value』 的出現。
count(expression)：查詢 is_reply=0 的數量： SELECT COUNT(IF(is_reply=0,1,NULL)) count FROM t_iov_help_feedback;
6、distinct與group by
distinct去重所有distinct之後所有的欄位，如果有一個欄位值不一致就不作為一條
group by是根據某一欄位分組，然後查詢出該條數據的所需欄位，可以搭配 where max(time)或者Row_Number函數使用，求出最大的一條數據
7、使用with 臨時表名 as() 的形式,簡單的臨時表直接嵌套進sql中，復雜的和需要復用的表寫到臨時表中，關聯的時候先找到關聯欄位，過濾條件最好在臨時表中先過濾後關聯
處理json的函數：
split(json_array_string(schools), '\\|\\|') AS schools
get_json_object(school, '$.id') AS school_id,
字元串函數：
1、instr（』源字元串』 , 『目標字元串』 ,』開始位置』,』第幾次出現』）
instr(sourceString,destString,start,appearPosition)
1.sourceString代表源字元串； destString代表要從源字元串中查找的子串；
2.start代表查找的開始位置，這個參數可選的，默認為1；
3.appearPosition代表想從源字元中查找出第幾次出現的destString，這個參數也是可選的， 默認為1
4.如果start的值為負數，則代表從右往左進行查找，但是位置數據仍然從左向右計算。
5.返回值為：查找到的字元串的位置。如果沒有查找到，返回0。
最簡單例子： 在abcd中查找a的位置，從第一個字母開始查，查找第一次出現時的位置
select instr(『abcd』,』a』,1,1) from al; —1
應用於模糊查詢：instr(欄位名/列名, 『查找欄位』)
select code,name,dept,occupation from staff where instr(code, 『001』)> 0;
等同於 select code, name, dept, occupation from staff where code like 『%001%』 ;
應用於判斷包含關系：
select ccn,mas_loc from mas_loc where instr(『FH,FHH,FHM』,ccn)>0;
等同於 select ccn,mas_loc from mas_loc where ccn in (『FH』,』FHH』,』FHM』);
2、substr（string A，int start，int len）和 substring（string A，int start，int len），用法一樣
substr(time,1,8) 表示將time從第1位開始截取，截取的長度為8位
第一種用法：
substr（string A，int start）和 substring（string A，int start），用法一樣
功效：返回字元串A從下標start位置到結尾的字元串
第二種用法：
substr（string A，int start，int len）和 substring（string A，int start，int len），用法一樣
功效：返回字元串A從下標start位置開始，長度為len的字元串
3、get_json_object(form_data,'$.學生姓名') as student_name
json_tuple 函數的作用：用來解析json字元串中的多個欄位
4、split(full_name, '\\.') [5] AS zq; 取的是數組里的第六個
日期(時間)函數：
1、to_date(event_time) 返回日期部分
2、date_sub：返回當前日期的相對時間
當前日期：select curdate()
當前日期前一天：select date_sub(curdate(),interval 1 day)
當前日期後一天：select date_sub(curdate(),interval -1 day)
date_sub(from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss'), 14) 將現在的時間總秒數轉為標准格式時間，返回14天之前的時間
時間戳>>>>日期：
from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') 將現在的時間總秒數轉為標准格式時間
from_unixtime(get_json_object(get_json_object(form_data,'$.挽單時間'),'$.$date')/1000) as retain_time
unix_timestamp('2019-08-15 16:40:00','yyyy-MM-dd HH:mm:ss') --1565858400
日期>>>>時間戳：unix_timestamp()
date_format：yyyy-MM-dd HH:mm:ss 時間轉格式化時間
select date_format('2019-10-07 13:24:20', 'yyyyMMdd000000')-- 20191007000000select date_format('2019-10-07', 'yyyyMMdd000000')-- 20191007000000
1.日期比較函數: datediff語法: datediff(string enddate,string startdate)
返回值: int
說明: 返回結束日期減去開始日期的天數。
舉例： hive> select datediff('2016-12-30','2016-12-29'); 1
2.日期增加函數: date_add語法: date_add(string startdate, intdays)
返回值: string
說明: 返回開始日期startdate增加days天後的日期。
舉例： hive>select date_add('2016-12-29',10); 2017-01-08
3.日期減少函數: date_sub語法: date_sub (string startdate,int days)
返回值: string
說明: 返回開始日期startdate減少days天後的日期。
舉例： hive>select date_sub('2016-12-29',10); 2016-12-19
4.查詢近30天的數據
select * from table where datediff(current_timestamp,create_time)<=30；
create_time 為table里的欄位，current_timestamp 返回當前時間 2018-06-01 11:00:00
3、trunc()函數的用法：當前日期的各種第一天,或者對數字進行不四捨五入的截取
日期：
1.select trunc(sysdate) from al --2011-3-18 今天的日期為2011-3-18
2.select trunc(sysdate, 'mm') from al --2011-3-1 返回當月第一天.
上月1號 trunc(add_months(current_date(),-1),'MM')
3.select trunc(sysdate,'yy') from al --2011-1-1 返回當年第一天
4.select trunc(sysdate,'dd') from al --2011-3-18 返回當前年月日
5.select trunc(sysdate,'yyyy') from al --2011-1-1 返回當年第一天
6.select trunc(sysdate,'d') from al --2011-3-13 (星期天)返回當前星期的第一天
7.select trunc(sysdate, 'hh') from al --2011-3-18 14:00:00 當前時間為14:41
8.select trunc(sysdate, 'mi') from al --2011-3-18 14:41:00 TRUNC()函數沒有秒的精確
數字：TRUNC（number,num_digits） Number 需要截尾取整的數字。Num_digits 的默認值為 0。TRUNC()函數截取時不進行四捨五入
11.select trunc(123.458,1) from al --123.4
12.select trunc(123.458,-1) from al --120
4、round()：四捨五入：
select round(1.455, 2) #結果是：1.46，即四捨五入到十分位，也就是保留兩位小數
select round(1.5) #默認四捨五入到個位，結果是：2
select round(255, -1) #結果是：260，即四捨五入到十位，此時個位是5會進位
floor()：地板數
ceil()天花板數
5、
6.日期轉年函數: year語法: year(string date)
返回值: int
說明: 返回日期中的年。
舉例：
hive> select year('2011-12-08 10:03:01') from al;
2011
hive> select year('2012-12-08') fromal;
2012
7.日期轉月函數: month語法: month (string date)
返回值: int
說明: 返回日期中的月份。
舉例：
hive> select month('2011-12-08 10:03:01') from al;
12
hive> select month('2011-08-08') fromal;
8
8.日期轉天函數: day語法: day (string date)
返回值: int
說明: 返回日期中的天。
舉例：
hive> select day('2011-12-08 10:03:01') from al;
8
hive> select day('2011-12-24') fromal;
24
9.日期轉小時函數: hour語法: hour (string date)
返回值: int
說明: 返回日期中的小時。
舉例：
hive> select hour('2011-12-08 10:03:01') from al;
10
10.日期轉分鍾函數: minute語法: minute (string date)
返回值: int
說明: 返回日期中的分鍾。
舉例：
hive> select minute('2011-12-08 10:03:01') from al;
3
11.日期轉秒函數: second語法: second (string date)
返回值: int
說明: 返回日期中的秒。
舉例：
hive> select second('2011-12-08 10:03:01') from al;
1
12.日期轉周函數: weekofyear語法: weekofyear (string date)
返回值: int
說明: 返回日期在當前的周數。
舉例：
hive> select weekofyear('2011-12-08 10:03:01') from al;
49
查看hive表在hdfs中的位置：show create table 表名;
在hive中hive2hive，hive2hdfs：
HDFS、本地、hive -----> Hive：使用 insert into | overwrite、loaddata local inpath "" into table student;
Hive ----> Hdfs、本地：使用：insert overwrite | local
網站訪問量統計：
uv：每用戶訪問次數
ip：每ip(可能很多人)訪問次數
PV:是指頁面的瀏覽次數
VV:是指你訪問網站的次數
sql：
基本函數：
count、max、min、sum、avg、like、rlike（'2%'、'_2%'、%2%'、'[2]'）（java正則）
and、or、not、in
where、group by、having、{ join on 、full join} 、order by（desc降序）
sort by需要與distribut by集合結合使用：
hive (default)> set maprece.job.reces=3; //先設置rece的數量
insert overwrite local directory '/opt/mole/datas/distribute-by'
row format delimited fields terminated by '\t'
先按照部門編號分區，再按照員工編號降序排序。
select * from emp distribute by deptno sort by empno desc;
外部表 create external table if not exists dept
分區表：create table dept_partition ( deptno int, dname string, loc string ) partitioned by ( month string )
load data local inpath '/opt/mole/datas/dept.txt' into table default.dept_partition partition(month='201809');
alter table dept_partition add/drop partition(month='201805') ,partition(month='201804');
多分區聯合查詢：union
select * from dept_partition2 where month='201809' and day='10';
show partitions dept_partition;
desc formatted dept_partition;
二級分區表：create table dept_partition2 ( deptno int, dname string, loc string ) partitioned by (month string, day string) row format delimited fields terminated by '\t';
分桶抽樣查詢：分區針對的是數據的存儲路徑；分桶針對的是數據文件
create table stu_buck(id int, name string) clustered by(id) into 4 bucketsrow format delimited fields terminated by '\t';
設置開啟分桶與rece為1：
set hive.enforce.bucketing=true;
set maprece.job.reces=-1;
分桶抽樣：select * from stu_bucktablesample(bucket x out of y on id);
抽取，桶數/y，x是從哪個桶開始抽取，y越大 抽樣數越少，y與抽樣數成反比，x必須小於y
給空欄位賦值：
如果員工的comm為NULL，則用-1代替或用其他欄位代替 ：select nvl(comm,-1) from emp;
case when:如何符合記為1，用於統計、分組統計
select dept_id, sum(case sex when '男' then 1 else 0 end) man , sum(case sex when '女' then 1 else 0 end) woman from emp_sex group by dept_id;
用於組合歸類匯總(行轉列)：UDAF：多轉一
concat：拼接查詢結果
collect_set(col)：去重匯總，產生array類型欄位，類似於distinct
select t.base, concat_ws('|',collect_set(t.name)) from (select concat_ws(',',xingzuo,blood_type) base,name from person_info) t group by t.base;
解釋：先第一次查詢得到一張沒有按照（星座血型）分組的表，然後分組，使用collect_set將名字組合成數組，然後使用concat將數組變成字元串
用於拆分數據：(列轉行)：UDTF：一轉多
explode(col)：將hive一列中復雜的array或者map結構拆分成多行。
lateral view 側面顯示：用於和UDTF一對多函數搭配使用
用法：lateral view udtf(expression) tablealias as cate
cate：炸開之後的列別名
temptable ：臨時表表名
解釋：用於和split, explode等UDTF一起使用，它能夠將一列數據拆成多行數據，在此基礎上可以對拆分後的數據進行聚合。
開窗函數：
Row_Number,Rank，Dense_Rank over：針對統計查詢使用
Row_Number：返回從1開始的序列
Rank:生成分組中的排名序號，會在名詞s中留下空位。3 3 5
dense_rank：生成分組中的排名序號，不會在名詞中留下空位。3 3 4
over：主要是分組排序，搭配窗口函數使用
結果：
SUM、AVG、MIN、MAX、count
preceding：往前
following：往後
current row：當前行
unbounded：unbounded preceding 從前面的起點， unbounded following：到後面的終點
sum：直接使用sum是總的求和，結合over使用可統計至每一行的結果、總的結果、當前行+之前多少行/之後多少行、當前行到往後所有行的求和。
over(rowsbetween 3/current ) 當前行到往後所有行的求和
ntile：分片，結合over使用，可以給數據分片，返回分片號
使用場景：統計出排名前百分之或n分之一的數據。
lead,lag,FIRST_VALUE,LAST_VALUE
lag與lead函數可以返回上下行的數據
lead(col,n,dafault) 用於統計窗口內往下第n行值
第一個參數為列名，第二個參數為往下第n行（可選，默認為1），第三個參數為默認值（當往下第n行為NULL時候，取默認值，如不指定，則為NULL）
LAG(col,n,DEFAULT) 用於統計窗口內往上第n行值
第一個參數為列名，第二個參數為往上第n行（可選，默認為1），第三個參數為默認值（當往上第n行為NULL時候，取默認值，如不指定，則為NULL）
使用場景：通常用於統計某用戶在某個網頁上的停留時間
FIRST_VALUE:取分組內排序後，截止到當前行，第一個值
LAST_VALUE:取分組內排序後，截止到當前行,最後一個值
范圍內求和: https://blog.csdn.net/happyrocking/article/details/105369558
cume_dist，percent_rank
–CUME_DIST :小於等於當前值的 行數 / 分組內總行數
–比如，統計小於等於當前薪水的人數，占總人數的比例
percent_rank:分組內當前行的RANK值-1/分組內總行數-1
總結：
在Spark中使用spark sql與hql一致，也可以直接使用sparkAPI實現。
HiveSql窗口函數主要應用於求TopN，分組排序TopN、TopN求和，前多少名前百分之幾。
與Flink窗口函數不同。
Flink中的窗口是用於將無線數據流切分為有限塊處理的手段。
window分類：
CountWindow：按照指定的數據條數生成一個 Window，與時間無關。
TimeWindow：按照時間生成 Window。
1. 滾動窗口（Tumbling Windows）：時間對齊，窗口長度固定，不重疊：：常用於時間段內的聚合計算
2.滑動窗口（Sliding Windows）：時間對齊，窗口長度固定，可以有重疊：：適用於一段時間內的統計（某介面最近 5min 的失敗率來報警）
3. 會話窗口（Session Windows）無時間對齊，無長度，不重疊：：設置session間隔，超過時間間隔則窗口關閉。
Ⅶ hive支持以下哪些sql特性
一、關系運算：1. 等值比較: =
語法：A=B
操作類型：所有基本類型
描述: 如果表達式A與表達式B相等，則為TRUE；否則為FALSE
舉例：
hive>select 1 from lxw_al where 1=1;
1
2. 不等值比較: <>
語法: A <> B
操作類型: 所有基本類型
描述: 如果表達式A為NULL，或者表達式B為NULL，返回NULL；如果表達式A與表達式B不相等，則為TRUE；否則為FALSE
舉例：
hive> select1 from lxw_al where 1 <> 2;
1
3. 小於比較: <
語法: A < B
操作類型: 所有基本類型
描述: 如果表達式A為NULL，或者表達式B為NULL，返回NULL；如果表達式A小於表達式B，則為TRUE；否則為FALSE
舉例：
hive> select1 from lxw_al where 1 < 2;
1
4. 小於等於比較: <=
語法: A <= B
操作類型: 所有基本類型
描述: 如果表達式A為NULL，或者表達式B為NULL，返回NULL；如果表達式A小於或者等於表達式B，則為TRUE；否則為FALSE
舉例：
hive> select1 from lxw_al where 1 <= 1;
1
5. 大於比較: >
語法: A > B
操作類型: 所有基本類型
描述: 如果表達式A為NULL，或者表達式B為NULL，返回NULL；如果表達式A大於表達式B，則為TRUE；否則為FALSE
舉例：
hive> select1 from lxw_al where 2 > 1;
1
6. 大於等於比較: >=
語法: A >= B
操作類型: 所有基本類型
描述: 如果表達式A為NULL，或者表達式B為NULL，返回NULL；如果表達式A大於或者等於表達式B，則為TRUE；否則為FALSE
舉例：
hive> select1 from lxw_al where 1 >= 1;
1
注意：String的比較要注意(常用的時間比較可以先to_date之後再比較)
hive> select* from lxw_al;
OK
201111120900:00:00 2011111209
hive> selecta,b,a<b,a>b,a=b from lxw_al;
201111120900:00:00 2011111209 false true false
7. 空值判斷: IS NULL
語法: A IS NULL
操作類型: 所有類型
描述: 如果表達式A的值為NULL，則為TRUE；否則為FALSE
舉例：
hive> select1 from lxw_al where null is null;
1
8. 非空判斷: IS NOTNULL
語法: A IS NOT NULL
操作類型: 所有類型
描述: 如果表達式A的值為NULL，則為FALSE；否則為TRUE
舉例：
hive> select1 from lxw_al where 1 is not null;
1
9. LIKE比較: LIKE
語法: A LIKE B
操作類型: strings
描述: 如果字元串A或者字元串B為NULL，則返回NULL；如果字元串A符合表達式B 的正則語法，則為TRUE；否則為FALSE。B中字元」_」表示任意單個字元，而字元」%」表示任意數量的字元。
舉例：
hive> select1 from lxw_al where 'football' like 'foot%';
1
hive> select1 from lxw_al where 'football' like 'foot____';
1
注意：否定比較時候用NOT ALIKE B
hive> select1 from lxw_al where NOT 'football' like 'fff%';
1
10. JAVA的LIKE操作: RLIKE
語法: A RLIKE B
操作類型: strings
描述: 如果字元串A或者字元串B為NULL，則返回NULL；如果字元串A符合JAVA正則表達式B的正則語法，則為TRUE；否則為FALSE。
舉例：
hive> select1 from lxw_al where 'footbar』 rlike '^f.*r$』;
1
注意：判斷一個字元串是否全為數字：
hive>select 1from lxw_al where '123456' rlike '^\\d+$';
1
hive> select1 from lxw_al where '123456aa' rlike '^\\d+$';
11. REGEXP操作: REGEXP
語法: A REGEXP B
操作類型: strings
描述: 功能與RLIKE相同
舉例：
hive> select1 from lxw_al where 'footbar' REGEXP '^f.*r$';
1
二、數學運算：1. 加法操作: +
語法: A + B
操作類型：所有數值類型
說明：返回A與B相加的結果。結果的數值類型等於A的類型和B的類型的最小父類型（詳見數據類型的繼承關系）。比如，int + int 一般結果為int類型，而int + double 一般結果為double類型
舉例：
hive> select1 + 9 from lxw_al;
10
hive> createtable lxw_al as select 1 + 1.2 from lxw_al;
hive>describe lxw_al;
_c0 double
2. 減法操作: -
語法: A – B
操作類型：所有數值類型
說明：返回A與B相減的結果。結果的數值類型等於A的類型和B的類型的最小父類型（詳見數據類型的繼承關系）。比如，int – int 一般結果為int類型，而int – double 一般結果為double類型
舉例：
hive> select10 – 5 from lxw_al;
5
hive> createtable lxw_al as select 5.6 – 4 from lxw_al;
hive>describe lxw_al;
_c0 double
3. 乘法操作: *
語法: A * B
操作類型：所有數值類型
說明：返回A與B相乘的結果。結果的數值類型等於A的類型和B的類型的最小父類型（詳見數據類型的繼承關系）。注意，如果A乘以B的結果超過默認結果類型的數值范圍，則需要通過cast將結果轉換成范圍更大的數值類型
舉例：
hive> select40 * 5 from lxw_al;
200
4. 除法操作: /
語法: A / B
操作類型：所有數值類型
說明：返回A除以B的結果。結果的數值類型為double
舉例：
hive> select40 / 5 from lxw_al;
8.0
注意：hive中最高精度的數據類型是double,只精確到小數點後16位，在做除法運算的時候要特別注意
hive>select ceil(28.0/6.999999999999999999999) from lxw_allimit 1;
結果為4
hive>select ceil(28.0/6.99999999999999) from lxw_al limit1;
結果為5
5. 取余操作: %
語法: A % B
操作類型：所有數值類型
說明：返回A除以B的余數。結果的數值類型等於A的類型和B的類型的最小父類型（詳見數據類型的繼承關系）。
舉例：
hive> select 41 % 5 from lxw_al;
1
hive> select 8.4 % 4 from lxw_al;
0.40000000000000036
注意：精度在hive中是個很大的問題，類似這樣的操作最好通過round指定精度
hive> select round(8.4 % 4 , 2) from lxw_al;
0.4
6. 位與操作: &
語法: A & B
操作類型：所有數值類型
說明：返回A和B按位進行與操作的結果。結果的數值類型等於A的類型和B的類型的最小父類型（詳見數據類型的繼承關系）。
舉例：
hive> select 4 & 8 from lxw_al;
0
hive> select 6 & 4 from lxw_al;
4
7. 位或操作: |
語法: A | B
操作類型：所有數值類型
說明：返回A和B按位進行或操作的結果。結果的數值類型等於A的類型和B的類型的最小父類型（詳見數據類型的繼承關系）。
舉例：
hive> select 4 | 8 from lxw_al;
12
hive> select 6 | 8 from lxw_al;
14
8. 位異或操作: ^
語法: A ^ B
操作類型：所有數值類型
說明：返回A和B按位進行異或操作的結果。結果的數值類型等於A的類型和B的類型的最小父類型（詳見數據類型的繼承關系）。
舉例：
hive> select 4 ^ 8 from lxw_al;
12
hive> select 6 ^ 4 from lxw_al;
2
9．位取反操作: ~
語法: ~A
操作類型：所有數值類型
說明：返回A按位取反操作的結果。結果的數值類型等於A的類型。
舉例：
hive> select ~6 from lxw_al;
-7
hive> select ~4 from lxw_al;
-5
三、邏輯運算：1. 邏輯與操作: AND
語法: A AND B
操作類型：boolean
說明：如果A和B均為TRUE，則為TRUE；否則為FALSE。如果A為NULL或B為NULL，則為NULL
舉例：
hive> select 1 from lxw_al where 1=1 and 2=2;
1
2. 邏輯或操作: OR
語法: A OR B
操作類型：boolean
說明：如果A為TRUE，或者B為TRUE，或者A和B均為TRUE，則為TRUE；否則為FALSE
舉例：
hive> select 1 from lxw_al where 1=2 or 2=2;
1
3. 邏輯非操作: NOT
語法: NOT A
操作類型：boolean
說明：如果A為FALSE，或者A為NULL，則為TRUE；否則為FALSE
舉例：
hive> select 1 from lxw_al where not 1=2;
1
四、數值計算1. 取整函數: round
語法: round(double a)
返回值: BIGINT
說明: 返回double類型的整數值部分（遵循四捨五入）
舉例：
hive> select round(3.1415926) from lxw_al;
3
hive> select round(3.5) from lxw_al;
4
hive> create table lxw_al as select round(9542.158) fromlxw_al;
hive> describe lxw_al;
_c0 bigint
2. 指定精度取整函數: round
語法: round(double a, int d)
返回值: DOUBLE
說明: 返回指定精度d的double類型
舉例：
hive> select round(3.1415926,4) from lxw_al;
3.1416
3. 向下取整函數: floor
語法: floor(double a)
返回值: BIGINT
說明: 返回等於或者小於該double變數的最大的整數
舉例：
hive> select floor(3.1415926) from lxw_al;
3
hive> select floor(25) from lxw_al;
25
4. 向上取整函數: ceil
語法: ceil(double a)
返回值: BIGINT
說明: 返回等於或者大於該double變數的最小的整數
舉例：
hive> select ceil(3.1415926) from lxw_al;
4
hive> select ceil(46) from lxw_al;
46
5. 向上取整函數: ceiling
語法: ceiling(double a)
返回值: BIGINT
說明: 與ceil功能相同
舉例：
hive> select ceiling(3.1415926) from lxw_al;
4
hive> select ceiling(46) from lxw_al;
46
6. 取隨機數函數: rand
語法: rand(),rand(int seed)
返回值: double
說明: 返回一個0到1范圍內的隨機數。如果指定種子seed，則會等到一個穩定的隨機數序列
舉例：
hive> select rand() from lxw_al;
0.5577432776034763
hive> select rand() from lxw_al;
0.6638336467363424
hive> select rand(100) from lxw_al;
0.7220096548596434
hive> select rand(100) from lxw_al;
0.7220096548596434
7. 自然指數函數: exp
語法: exp(double a)
返回值: double
說明: 返回自然對數e的a次方
舉例：
hive> select exp(2) from lxw_al;
7.38905609893065
自然對數函數: ln
語法: ln(double a)
返回值: double
說明: 返回a的自然對數
舉例：
hive> select ln(7.38905609893065) from lxw_al;
2.0
8. 以10為底對數函數: log10
語法: log10(double a)
返回值: double
說明: 返回以10為底的a的對數
舉例：
hive> select log10(100) from lxw_al;
2.0
9. 以2為底對數函數: log2
語法: log2(double a)
返回值: double
說明: 返回以2為底的a的對數
舉例：
hive> select log2(8) from lxw_al;
3.0
10. 對數函數: log
語法: log(double base, double a)
返回值: double
說明: 返回以base為底的a的對數
舉例：
hive> select log(4,256) from lxw_al;
4.0
11. 冪運算函數: pow
語法: pow(double a, double p)
返回值: double
說明: 返回a的p次冪
舉例：
hive> select pow(2,4) from lxw_al;
16.0
12. 冪運算函數: power
語法: power(double a, double p)
返回值: double
說明: 返回a的p次冪,與pow功能相同
舉例：
hive> select power(2,4) from lxw_al;
16.0
13. 開平方函數: sqrt
語法: sqrt(double a)
返回值: double
說明: 返回a的平方根
舉例：
hive> select sqrt(16) from lxw_al;
4.0
14. 二進制函數: bin
語法: bin(BIGINT a)
返回值: string
說明: 返回a的二進制代碼表示
舉例：
hive> select bin(7) from lxw_al;
111
15. 十六進制函數: hex
語法: hex(BIGINT a)
返回值: string
說明: 如果變數是int類型，那麼返回a的十六進製表示；如果變數是string類型，則返回該字元串的十六進製表示
舉例：
hive> select hex(17) from lxw_al;
11
hive> select hex(『abc』) from lxw_al;
616263
16. 反轉十六進制函數: unhex
語法: unhex(string a)
返回值: string
說明: 返回該十六進制字元串所代碼的字元串
舉例：
hive> select unhex(『616263』)from lxw_al;
abc
hive> select unhex(『11』)from lxw_al;
-
hive> select unhex(616263) from lxw_al;
abc
17. 進制轉換函數: conv
語法: conv(BIGINT num, int from_base, int to_base)
返回值: string
說明: 將數值num從from_base進制轉化到to_base進制
舉例：
hive> select conv(17,10,16) from lxw_al;
11
hive> select conv(17,10,2) from lxw_al;
10001
18. 絕對值函數: abs
語法: abs(double a) abs(int a)
返回值: double int
說明: 返回數值a的絕對值
舉例：
hive> select abs(-3.9) from lxw_al;
3.9
hive> select abs(10.9) from lxw_al;
10.9
19. 正取余函數: pmod
語法: pmod(int a, int b),pmod(double a, double b)
返回值: int double
說明: 返回正的a除以b的余數
舉例：
hive> select pmod(9,4) from lxw_al;
1
hive> select pmod(-9,4) from lxw_al;
3
20. 正弦函數: sin
語法: sin(double a)
返回值: double
說明: 返回a的正弦值
舉例：
hive> select sin(0.8) from lxw_al;
0.7173560908995228
21. 反正弦函數: asin
語法: asin(double a)
返回值: double
說明: 返回a的反正弦值
舉例：
hive> select asin(0.7173560908995228) from lxw_al;
0.8
22. 餘弦函數: cos
語法: cos(double a)
返回值: double
說明: 返回a的餘弦值
舉例：
hive> select cos(0.9) from lxw_al;
0.6216099682706644
23. 反餘弦函數: acos
語法: acos(double a)
返回值: double
說明: 返回a的反餘弦值
舉例：
hive> select acos(0.6216099682706644) from lxw_al;
0.9
24. positive函數: positive
語法: positive(int a), positive(double a)
返回值: int double
說明: 返回a
舉例：
hive> select positive(-10) from lxw_al;
-10
hive> select positive(12) from lxw_al;
12
25. negative函數: negative
語法: negative(int a), negative(double a)
返回值: int double
說明: 返回-a
舉例：
hive> select negative(-5) from lxw_al;
5
hive> select negative(8) from lxw_al;
-8
Ⅷ Hive SQL語句執行順序
Hive 中 sql 語句的執行順序如下：
from .. where .. join .. on .. select .. group by .. select .. having .. distinct .. order by .. limit .. union/union all
下面我們通過一個 sql 語句分析下：
上面這條 sql 語句是可以成功執行的，我們看下它在 MR 中的執行順序：
Map 階段 ：
Rece 階段 ：
上面這個執行順序到底對不對呢，我們可以通過 explain 執行計劃來看下，內容過多，我們分階段來看。
我們看到 Stage-5 是根，也就是最先執行 Stage-5，Stage-2 依賴 Stage-5，Stage-0 依賴 Stage-2。
圖中標 ① 處是表掃描操作，注意先掃描的 b 表，也就是 left join 後面的表，然後進行過濾操作（圖中標 ② 處），我們 sql 語句中是對 a 表進行的過濾，但是 Hive 也會自動對 b 表進行相同的過濾操作，這樣可以減少關聯的數據量。
先掃描 a 表（圖中標 ① 處）；接下來進行過濾操作 idno > �'（圖中標 ② 處）；然後進行 left join，關聯的 key 是 idno（圖中標 ③ 處）；執行完關聯操作之後會進行輸出操作，輸出的是三個欄位，包括 select 的兩個欄位加 group by 的一個欄位（圖中標 ④ 處）；然後進行 group by 操作，分組方式是 hash（圖中標 ⑤ 處）；然後進行排序操作，按照 idno 進行正向排序（圖中標 ⑥ 處）。
首先進行 group by 操作，注意此時的分組方式是 mergepartial 合並分組（圖中標 ① 處）；然後進行 select 操作，此時輸出的欄位只有兩個了，輸出的行數是 30304 行（圖中標 ② 處）；接下來執行 having 的過濾操作，過濾出 count_user>1 的欄位，輸出的行數是 10101 行（圖中標 ③ 處）；然後進行 limit 限制輸出的行數（圖中標 ④ 處）；圖中標 ⑤ 處表示是否對文件壓縮，false 不壓縮。
限制最終輸出的行數為 10 行。
通過上面對 SQL 執行計劃的分析，總結以下幾點：
Ⅸ SQL查詢中，如何判斷一個字元串欄位的內容的長度
實現的方法和詳細的操作步驟如下：
1、首先，打開sql查詢器，並連接相應的資料庫表，例如store表，如下圖所示。
