1. 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間隔，超過時間間隔則窗口關閉。
2. HiveSQL核心技能之表連接
目標：
1、掌握HQL中的各種連接及其組合使用；
2、掌握數據分析中業務指標思路轉換的技巧；
3、區分好full join 和 union all 的使用場景；
4、在多表連接時，注意各種細節和業務邏輯；
5、復雜表連接要學會分步驟處理
需注意：
1、表連接時，必須進行重命名；
2、on後面使用的連接條件必須起到 唯一鍵值 的作用（有時會有多個欄位組合）；
3、inner可省略不寫，效果是一樣的
4、表連接時不能使用 a join b join c這種方式，不然會極度浪費電腦的資源和延長查詢時間，要在子查詢的表裡先做好篩選之後在連接；
1）找出在2019年購買後又退款的用戶（記得要去重）
注意：一定要先去重，再做表連接，養成良好的習慣（雖然可以先連接再去重，但是那麼做會使執行效率很低）
2）在2017年和2018年都購買的用戶
3）在2017年、2018年、2019年都有交易的用戶
進行左連接之後，以左表為全集，返回能夠匹配上的右邊表的匹配結果，沒有匹配上的則顯示NULL。
拓展：
right join：以右表為全集，返回能夠匹配上的左邊表的匹配結果，沒有匹配上的則顯示NULL，可以由left join改寫出同樣的結果。
4）在2019年購買，但是沒有退款的用戶
5）在2019年由購買的用戶的學歷分布
6）在2017年和2018年都購買，但是沒有在2019年購買的用戶
查詢兩個表的所有用戶時使用full join是一個比較好的方法（需要用到coalesce函數：
註：coalesce函數，coalesce(expression1,expression2,...,expression n)，依次參考各參數表達式，遇到非null值即停止並返回該值，如果所有的表達式都是空值，最終將返回一個空值。
註：表合並時欄位名稱必須一致，欄位順序必須一致，而且不用填寫連接條件
7）2017-2019年由交易的所有用戶數
union all 和 union 的區別：
union all 不會去重，不會排序，效率較快；union 會去重且排序，效率較慢。
如果表很大時，推薦先去重，再進行 union all ，不能直接對表進行 union all，不然效率很慢。
8）2019年每個用戶的支付和退款金額匯總
也可以使用 full join 的方式：
9）2019年每個支付用戶的支付金額和退款金額
10）首次激活時間在2017年，但是一直沒有支付的用戶年齡段分布
步驟總結：
1、先篩選出年份為2017注冊的用戶；
2、沒有支付的人；
3、年齡段分布
注意：由於age也是在user_info的表格里，第三步用的欄位需要在第一步進行預處理，所以在限制時間的時候需要同時對年齡段進行預處理，這樣在第三步的時候才會由年齡段這個欄位；需要注意對 case when 的欄位進行重命名才能進行後續的操作
11）2018、2019年交易的用戶，其激活時間段分布
步驟總結：
1. 取出2018和2019年所有的交易用戶的交集
2. 取出所有用戶的激活時間
3. 統計時間分布
3. 數據分析課程筆記 - 19 - HiveSQL 常用優化技巧
大家好呀，這節課學習 HiveSQL 的常用優化技巧。由於 Hive 主要用來處理非常大的數據，運行過程由於通常要經過 MapRece 的過程，因此不像 MySQL 一樣很快出結果。而使用不同方法寫出來的 HiveSQL 語句執行效率也是不一樣的，因此為了減少等待的時間，提高伺服器的運行效率，我們需要在 HiveSQL 的語句上進行一些優化。
本節課的主要內容 ：
引言
1、技巧一：列裁剪和分區裁剪
（1）列裁剪
（2）分區裁剪
2、技巧二：排序技巧——sort by代替order by
3、技巧三：去重技巧——用group by來替換distinct
4、技巧四：聚合技巧——grouping sets、cube、rollup
（1）grouping sets
（2）cube
（3）rollup
5、技巧五：換個思路解題
6、技巧六：union all時可以開啟並發執行
7、技巧七：表連接優化
8、技巧八：遵循嚴格模式
Hive 作為大數據領域常用的數據倉庫組件，在平時設計和查詢時要特別注意效率。影響Hive效率的幾乎從不是數據量過大，而是數據傾斜、數據冗餘、job 或 I/O 過多、MapRece 分配不合理等等。對 Hive 的調優既包含對HiveSQL 語句本身的優化，也包含 Hive 配置項和 MR 方面的調整。
列裁剪就是在查詢時只讀取需要的列。當列很多或者數據量很大時，如果select 所有的列或者不指定分區，導致的全表掃描和全分區掃描效率都很低。Hive中與列裁剪優化相關的配置項是 hive.optimize.cp ，默認是 true 。
分區裁剪就是在查詢時只讀需要的分區。Hive中與分區裁剪優化相關的則是 hive.optimize.pruner ，默認是 true 。
HiveSQL中的 order by 與其他 SQL 語言中的功能一樣，就是將結果按某個欄位全局排序，這會導致所有map端數據都進入一個 rece 中，在數據量大時可能會長時間計算不完。
如果使用 sort by ，那麼就會視情況啟動多個 recer 進行排序，並且保證每個 recer 內局部有序。為了控制 map 端數據分配到 rece 的 key，往往還要配合 distribute by 一同使用。如果不加 distribute by 的話，map 端數據就會隨機分配給 recer。
這里需要解釋一下， distribute by 和 sort by 結合使用是如何相較於 order by 提升運行效率的。
假如我們要對一張很大的用戶信息表按照年齡進行分組，優化前的寫法是直接 order by age 。使用 distribute by 和 sort by 結合進行優化的時候， sort by 後面還是 age 這個排序欄位， distribute by 後面選擇一個沒有重復值的均勻欄位，比如 user_id 。
這樣做的原因是，通常用戶的年齡分布是不均勻的，比如20歲以下和50歲以上的人非常少，中間幾個年齡段的人又非常多，在 Map 階段就會造成有些任務很大，有些任務很小。那通過 distribute by 一個均勻欄位，就可以讓系統均勻地進行「分桶」，對每個桶進行排序，最後再組合，這樣就能從整體上提升 MapRece 的效率。
取出 user_trade 表中全部支付用戶：
原有寫法的執行時長：
優化寫法的執行時長：
考慮對之前的案例進行優化：
注意： 在極大的數據量（且很多重復值）時，可以先 group by 去重，再 count() 計數，效率高於直接 count(distinct **) 。
如果我們想知道用戶的性別分布、城市分布、等級分布，你會怎麼寫？
通常寫法：
缺點 ：要分別寫三次SQL，需要執行三次，重復工作，且費時。
那該怎麼優化呢？
注意 ：這個聚合結果相當於縱向地堆在一起了（Union all），分類欄位用不同列來進行區分，也就是每一行數據都包含 4 列，前三列是分類欄位，最後一列是聚合計算的結果。
GROUPING SETS() ：在 group by 查詢中，根據不同的維度組合進行聚合，等價於將不同維度的 group by 結果集進行 union all。聚合規則在括弧中進行指定。
如果我們想知道用戶的性別分布以及每個性別的城市分布，你會怎麼寫？
那該怎麼優化呢？
注意： 第二列為NULL的，就是性別的用戶分布，其餘有城市的均為每個性別的城市分布。
cube：根據 group by 維度的所有組合進行聚合
注意 ：跑完數據後，整理很關鍵！！！
rollup：以最左側的維度為主，進行層級聚合，是cube的子集。
如果我想同時計算出，每個月的支付金額，以及每年的總支付金額，該怎麼辦？
那應該如何優化呢？
條條大路通羅馬，寫SQL亦是如此，能達到同樣效果的SQL有很多種，要學會思路轉換，靈活應用。
來看一個我們之前做過的案例：
有沒有別的寫法呢？
Hive 中互相沒有依賴關系的 job 間是可以並行執行的，最典型的就是
多個子查詢union all。在集群資源相對充足的情況下，可以開啟並
行執行。參數設置： set hive.exec.parallel=true;
時間對比：
所謂嚴格模式，就是強制不允許用戶執行3種有風險的 HiveSQL 語句，一旦執行會直接報錯。
要開啟嚴格模式，需要將參數 hive.mapred.mode 設為 strict 。
好啦，這節課的內容就是這些。以上優化技巧需要大家在平時的練習和使用中有意識地去注意自己的語句，不斷改進，就能掌握最優的寫法。
4. Hivesql計算兩個時間戳相差的分鍾數
1.Hive row_number() 函數的高級用法 row_num 按照某個欄位分區顯示第幾條數據
select imei,ts,fuel_instant,gps_longitude,gps_latitude,row_number() over (PARTITION BY imei ORDER BY ts ASC) as row_num from sample_data_2
2.row_num 是相互連續的，join 自身，然後時間相減可求差
create table obd_20140101 as
select a.imei,a.row_num,a.ts,COALESCE(unix_timestamp(a.ts, 'yyyy-MM-dd HH:mm:ss.S'), 0) - unix_timestamp(b.ts, 'yyyy-MM-dd HH:mm:ss.S') as intervel ,a.fuel_instant,a.gps_speed as obd_speed,a.gps_status,a.gps_longitude,a.gps_latitude,a.direct_angle,a.obdspeed from obddata_20140101 a join obddata_20140101 b on a.imei = b.imei and a.row_num = b.row_num +1
5. SQL語句如何根據出生日期計算年齡
第一種:
一張人員信息表裡有一人生日(Birthday)列,跟據這個列,算出該人員的年齡
datediff(year,birthday,getdate())
例:birthday = '2003-3-8'
getDate()= '2008-7-7'
結果為:5
這樣結果是會返回該人員的大概年齡,但不精確.不會精確到月或日.
按照上面測試的日期,該人員的實際年齡應該還不滿5歲。在需要精確年齡的時候，就會有錯.
第二種：
FLOOR(datediff(DY,birthday,getdate())/365.25)
FLOOR函數：
FLOOR(expr) 返回小於或等於expr的最大整數.FLOOR(1.1)返回1,FLOOR(-1.1)返回-2,FLOOR(1)返回1
這樣就可以精確算出，該人員當前實際年齡了.
測試:
birthday = '2000-7-8'
getdate()= '2007-7-7'
算出結果為:6
在Oracle中，要獲得日期中的年份,例如把sysdate中的年份取出來。
一種常用的方法是：to_number(to_char(sysdate,'yyyy'))
還有一種更好的方法，那就是使用oracle提供的Extract函數，使用方法是：
extract(year from sysdate) ，此方法獲得的結果是數值型的 ，這種方法省掉了類型轉換，更加簡潔。
相應的，要取得月份或日，可以用extract (month from sysdate) 和extract (day from sysdate)
6. 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、聚合計算，針對年齡段，婚姻狀況的聚合
7. 以下哪些sql語句在hive中執行可以得到系統當前時間年月日
substring(now(),1,10)
from_unixtime(unix_timestamp,'yyyy-MM-dd')
8. 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、對分組後的表進行條件篩選提取
9. 怎麼將本地的.sql文件導入hadoop master
完成sqoop的安裝後，可以這樣測試是否可以連接到mysql(注意：mysql的jar包要放到 SQOOP_HOME/lib 下)：
sqoop list-databases --connect jdbc:mysql://192.168.1.109:3306/ --username root --password 19891231
結果如下
即說明sqoop已經可以正常使用了。
下面，要將mysql中的數據導入到hadoop中。
我准備的是一個300萬條數據的身份證數據表：
先啟動hive（使用命令行：hive 即可啟動）
然後使用sqoop導入數據到hive：
sqoop import --connect jdbc:mysql://192.168.1.109:3306/hadoop --username root --password 19891231 --table test_sfz --hive-import
sqoop 會啟動job來完成導入工作。
完成導入用了2分20秒，還是不錯的。
在hive中可以看到剛剛導入的數據表：
我們來一句sql測試一下數據：
select * from test_sfz where id < 10;
可以看到，hive完成這個任務用了將近25秒，確實是挺慢的（在mysql中幾乎是不費時間），但是要考慮到hive是創建了job在hadoop中跑，時間當然多。
接下來，我們會對這些數據進行復雜查詢的測試：
我機子的配置如下：
hadoop 是運行在虛擬機上的偽分布式，虛擬機OS是ubuntu12.04 64位，配置如下：
TEST 1 計算平均年齡
測試數據：300.8 W
1. 計算廣東的平均年齡
mysql：select (sum(year(NOW()) - SUBSTRING(borth,1,4))/count(*)) as ageAvge from test_sfz where address like '廣東%';
用時： 0.877s
hive：select (sum(year('2014-10-01') - SUBSTRING(borth,1,4))/count(*)) as ageAvge from test_sfz where address like '廣東%';
用時：25.012s
2. 對每個城市的的平均年齡進行從高到低的排序
mysql：select
address, (sum(year(NOW()) - SUBSTRING(borth,1,4))/count(*)) as ageAvge
from test_sfz GROUP BY address order by ageAvge desc;
用時：2.949s
hive：select
address, (sum(year('2014-10-01') - SUBSTRING(borth,1,4))/count(*)) as
ageAvge from test_sfz GROUP BY address order by ageAvge desc;
用時：51.29s
可以看到，在耗時上面，hive的增長速度較mysql慢。
TEST 2
測試數據：1200W
mysql 引擎： MyISAM（為了加快查詢速度）
導入到hive：
1. 計算廣東的平均年齡
mysql：select (sum(year(NOW()) - SUBSTRING(borth,1,4))/count(*)) as ageAvge from test_sfz2 where address like '廣東%';
用時： 5.642s
hive：select (sum(year('2014-10-01') - SUBSTRING(borth,1,4))/count(*)) as ageAvge from test_sfz2 where address like '廣東%';
用時：168.259s
2. 對每個城市的的平均年齡進行從高到低的排序
mysql：select
address, (sum(year(NOW()) - SUBSTRING(borth,1,4))/count(*)) as ageAvge
from test_sfz2 GROUP BY address order by ageAvge desc;
用時：11.964s
hive：select
address, (sum(year('2014-10-01') - SUBSTRING(borth,1,4))/count(*)) as
ageAvge from test_sfz2 GROUP BY address order by ageAvge desc;
用時：311.714s