視窗函數與分析函數
應用場景：
（1）用于分區排序
（2）動态Group By
（3）Top N
（4）累計計算
（5）層次查詢
視窗函數
FIRST_VALUE：取分組内排序後，截止到目前行，第一個值
LAST_VALUE： 取分組内排序後，截止到目前行，最後一個值
LEAD(col,n,DEFAULT) ：用于統計視窗内往下第n行值。第一個參數為列名，第二個參數為往下第n行（可選，預設為1），第三個參數為預設值（當往下第n行為NULL時候，取預設值，如不指定，則為NULL）
LAG(col,n,DEFAULT) ：與lead相反，用于統計視窗内往上第n行值。第一個參數為列名，第二個參數為往上第n行（可選，預設為1），第三個參數為預設值（當往上第n行為NULL時候，取預設值，如不指定，則為NULL）
OVER從句
1、使用标準的聚合函數COUNT、SUM、MIN、MAX、AVG
2、使用PARTITION BY語句，使用一個或者多個原始資料類型的列
3、使用PARTITION BY與ORDER BY語句，使用一個或者多個資料類型的分區或者排序列
4、使用視窗規範，視窗規範支援以下格式：
當ORDER BY後面缺少視窗從句條件，視窗規範預設是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
當ORDER BY和視窗從句都缺失, 視窗規範預設是 ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
OVER從句支援以下函數， 但是并不支援和視窗一起使用它們。
Ranking函數: Rank, NTile, DenseRank, CumeDist, PercentRank.
Lead 和 Lag 函數.
分析函數
ROW_NUMBER() 從1開始，按照順序，生成分組内記錄的序列,比如，按照pv降序排列，生成分組内每天的pv名次,ROW_NUMBER()的應用場景非常多，再比如，擷取分組内排序第一的記錄;擷取一個session中的第一條refer等。
RANK() 生成資料項在分組中的排名，排名相等會在名次中留下空位
DENSE_RANK() 生成資料項在分組中的排名，排名相等會在名次中不會留下空位
CUME_DIST 小于等于目前值的行數/分組内總行數。比如，統計小于等于目前薪水的人數，所占總人數的比例
PERCENT_RANK 分組内目前行的RANK值-1/分組内總行數-1
NTILE(n) 用于将分組資料按照順序切分成n片，傳回目前切片值，如果切片不均勻，預設增加第一個切片的分布。NTILE不支援ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)。
增強的聚合 Cube和Grouping 和Rollup
這幾個分析函數通常用于OLAP中，不能累加，而且需要根據不同次元上鑽和下鑽的名額統計，比如，分小時、天、月的UV數。
GROUPING SETS
在一個GROUP BY查詢中，根據不同的次元組合進行聚合，等價于将不同次元的GROUP BY結果集進行UNION ALL,
其中的GROUPING__ID，表示結果屬于哪一個分組集合。
CUBE
根據GROUP BY的次元的所有組合進行聚合。
數學函數
round(double d, int n):傳回保留n位小數的近似d值
floor(double d): 傳回小于d的最大整值
ceil(double d): 傳回大于d的最小整值
rand(int seed): 傳回随機數,seed是随機因子
bin(int d): 計算二進制值d的string值
日期函數
to_date(string timestamp):傳回時間字元串中的日期部分,如to_date('1970-01-01 00:00:00')='1970-01-01'
current_date:傳回目前日期
year(date)：傳回日期date的年,類型為int如year('2019-01-01')=2019
month(date)：傳回日期date的月,類型為int,如month('2019-01-01')=1
day(date): 傳回日期date的天,類型為int,如day('2019-01-01')=1
weekofyear(date1)：傳回日期date1位于該年第幾周。如weekofyear('2019-03-06')=10
datediff(date1,date2):傳回日期date1與date2相差的天數，如datediff('2019-03-06','2019-03-05')=1
date_add(date1,int1):傳回日期date1加上int1的日期,如date_add('2019-03-06',1)='2019-03-07'
date_sub(date1,int1):傳回日期date1減去int1的日期,如date_sub('2019-03-06',1)='2019-03-05'
months_between(date1,date2):傳回date1與date2相差月份,如months_between('2019-03-06','2019-01-01')=2
add_months(date1,int1):傳回date1加上int1個月的日期，int1可為負數。如add_months('2019-02-11',-1)='2019-01-11'
last_day(date1):傳回date1所在月份最後一天。如last_day('2019-02-01')='2019-02-28'
next_day(date1,day1):傳回日期date1的下個星期day1的日期。day1為星期X的英文前兩字母如next_day('2019-03-06','MO') 傳回'2019-03-11'
trunc(date1,string1):傳回日期最開始年份或月份。string1可為年(YYYY/YY/YEAR)或月(MONTH/MON/MM)。如trunc('2019-03-06','MM')='2019-03-01'，trunc('2019-03-06','YYYY')='2019-01-01'
unix_timestamp():傳回目前時間的unix時間戳，可指定日期格式。如unix_timestamp('2019-03-06','yyyy-mm-dd')=1546704180
from_unixtime():傳回unix時間戳的日期，可指定格式。如select from_unixtime(unix_timestamp('2019-03-06','yyyy-mm-dd'),'yyyymmdd')='20190306'
條件函數
if(boolean,t1,t2):若布爾值成立，則傳回t1,反正傳回t2。如if(1>2,100,200)傳回200
case when boolean then t1 else t2 end:若布爾值成立，則t1,否則t2,可加多重判斷
coalesce(v0,v1,v2):傳回參數中的第一個非空值,若所有值均為null,則傳回null。如coalesce(null,1,2)傳回1
isnull(a):若a為null則傳回true，否則傳回false
字元串函數
length(string1):傳回字元串長度
concat(string1,string2):傳回拼接string1及string2後的字元串
concat_ws(sep,string1,string2):傳回按指定分隔符拼接的字元串
lower(string1):傳回小寫字元串，同lcase(string1)。upper()/ucase()：傳回大寫字元串
trim(string1):去字元串左右空格，ltrim(string1):去字元串左空格。rtrim(string1):去字元串右空格
repeat(string1，int1)：傳回重複string1字元串int1次後的字元串
reverse(string1):傳回string1反轉後的字元串。如reverse('abc')傳回'cba'
rpad(string1,len1,pad1):以pad1字元右填充string1字元串，至len1長度。如rpad('abc',5,'1')傳回'abc11'。lpad()：左填充
split(string1,pat1):以pat1正則分隔字元串string1,傳回數組。如split('a,b,c',',')傳回["a","b","c"]
substr(string1,index1,int1):以index位置起截取int1個字元。如substr('abcde',1,2)傳回'ab'
聚合函數
count():統計行數
sum(col1):統計指定列和
avg(col1):統計指定列平均值
min(col1):傳回指定列最小值
max(col1):傳回指定列最大值
表生成函數
與聚合函數相反,将字段内複雜的資料拆分成多行。
explode (array):傳回多行array中對應的元素。如explode(array('A','B','C'))傳回
explode(map):傳回多行map鍵值對對應元素。如explode(map(1,'A',2,'B',3,'C'))傳回
explode常用來做行列轉換。
作者的原創文章，轉載須注明出處。原創文章歸作者所有，歡迎轉載，但是保留版權。對于轉載了部落客的原創文章，不标注出處的，作者将依法追究版權，請尊重作者的成果。