⑴ 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間隔，超過時間間隔則窗口關閉。
⑵ 數據分析課程筆記 - 20 - HIVE 核心技能之窗口函數
大家好呀，這節課我們學習 Hive 核心技能中最難的部分——窗口函數。窗口函數我們之前在學 MySQL 的時候有學過一些，但是只學了三個排序的窗口函數。這節課我們會學習更多的窗口函數，包括累計計算、分區排序、切片排序以及偏移分析。
在正式學習之前，我們需要先明確一下窗口函數和GROUP BY分組的區別。二者在功能上有相似之處，但是它們存在本質區別。
1. 分組會改變表的結構，而窗口函數不會改變表的結構。比如原表有10行數據，分成兩組後只有兩行，而窗口函數仍然返回十行數據。
2. 分組只能查詢分組後的欄位，包括分組欄位（組名）和聚合函數欄位。而窗口函數對查詢欄位沒有限制，也就是可以查詢原表的任意欄位，再加上窗口函數新增的一列值。
好啦，現在讓我們一起進入窗口函數的世界吧~
本節課主要內容：
1、累計計算窗口函數
（1）sum(…) over(……)
（2）avg(…) over(……)
（3）語法總結
2、分區排序窗口函數
（1）row_number()
（2）rank()
（3）dense_rank()
3、切片排序窗口函數
（1）ntile(n) over(……)
4、偏移分析窗口函數
5、重點練習
大家在做報表的時候，經常會遇到計算截止某月的累計數值，通常在EXCEL里可以通過函數來實現。
那麼在HiveSQL里，該如何實現這種累計數值的計算呢？那就是利用窗口函數！
關於窗口函數的幾點說明：
需求分析 ：既然要進行按月累計，我們就先要把2018年的每筆交易時間轉換成月並按月分組聚合計算，得出一個2018年每月支付金額總合表，再基於這張表用窗口函數進行累計計算。
2018年每月支付金額總和表：
再用窗口函數進行月度累計：
年度進行匯總。
這個需求比需求1多了一個需求，那就是年度匯總。那我們只需要在上個需求的子查詢中加一個 year 欄位即可。
說明：
1、over 中的 partition by 起到了窗口內將數據分組的作用。事實上，加上partition by之後，可以理解為分成了多個窗口，並在每個窗口內進行累加計算或者分區。
如果不加 partition by a.year 的話，運行結果就是這樣單純按月份進行分組的：
2、order by 按照什麼順序進行累加，升序ASC、降序DESC，默認是升序。
大家看股票的時候，經常會看到這種K線圖，裡面經常用到的就是7日、30日移動平均的趨勢圖，那如何使用窗口函數來計算移動平均值呢？
需求分析 ：這個需求要求每個月近三個月的移動平均支付金額，這里我們要用到一個新知識點，在窗口函數 avg over 的 order by a.month 之後加一句 rows between 2 preceding and current row 來設定計算移動平均的范圍，這個語句的含義就是包含本行及前兩行。其他部分的寫法跟前面的需求類似，先取出2018年每個月的支付金額總和，再用窗口函數求移動平均。
注意：
sum(…A…) over(partition by …B… order by …C… rows between …D1… and …D2…)
avg(…A…) over(partition by …B… order by …C… rows between…D1… and …D2…)
A：需要被加工的欄位名稱
B：分組的欄位名稱
C：排序的欄位名稱
D：計算的行數范圍
rows between unbounded preceding and current row
——包括本行和之前所有的行
rows between current row and unbounded following
——包括本行和之後所有的行
rows between 3 preceding and current row
——包括本行以內和前三行
rows between 3 preceding and 1 following
——從前三行到下一行(5行)
max(……) over(partition by …… order by …… rows between ……and ……)
min(……) over(partition by …… order by …… rows between ……and ……)
row_number() 、rank()、dense_rank()
用法：這三個函數的作用都是返回相應規則的排序序號
row_number() over(partition by …A… order by …B… )
rank() over(partition by …A… order by …B… )
dense_rank() over(partition by …A… order by …B… )
A：分組的欄位名稱
B：排序的欄位名稱
注意： 這3個函數的括弧內是不加任何欄位名稱的！
row_number ：它會為查詢出來的每一行記錄生成一個序號，依次排序且不會重復。
rank&dense_rank ：在各個分組內， rank() 是跳躍排序，有兩個第一名時接下來就是第三名， dense_rank() 是連續排序，有兩個第一名時仍然跟著第二名。
實例練習：
再眼熟一下 user_trade 的表結構：
需求分析 ：先限定時間范圍，然後根據 user_name 進行分組，接著選出 分組去重後的 user_name，並計算每個用戶 goods_category 的數量（記得 distinct 去重），再然後就是用窗口函數對 goods_category 的數量進行排序，當然選擇哪一種排序方法要看具體要求，這里我們可以三種方法都試一下看看結果：
注意 ：窗口函數中的 order by 欄位不能用 select 中欄位的重命名，因為二者是同時執行的。
需求分析 ： 先用窗口函數將2019年每個用戶的支付總金額算出來並進行排序，再以此作為子查詢，從中取出排名在第10、20、30名的用戶名、支付總金額以及排名次序。企業一般會使用 dense_rank 進行排序，所以我們這里直接用 dense_rank。
2019年每個用戶的支付總金額排名：
2019年支付金額排名在第10、20、30名的用戶：
ntile(n) over(partition by …A… order by …B… )
n：切分的片數
A：分組的欄位名稱
B：排序的欄位名稱
需求分析 ：這個需求很簡單，把需求5第一步的排序窗口函數變成切片即可。注意時間篩選條件變成2019年1月。
需求分析 ： 排名前10%，也就是一共分成10組，取第1組。那麼我們先切片分組：
然後再取第一組：
說明：Lag和Lead分析函數可以在同一次查詢中取出同一欄位的前N行的數據(Lag)和後N行的數據(Lead)作為獨立的列。
在實際應用當中，若要用到取今天和昨天的某欄位差值時，Lag和Lead函數的應用就顯得尤為重要。當然，這種操作可以用表的自連接實現，但是LAG和LEAD與left join、right join等自連接相比，效率更高，SQL語句更簡潔。
lag(exp_str,offset,defval) over(partion by ……order by ……)
lead(exp_str,offset,defval) over(partion by ……order by ……)
lag() 函數示例：
lead() 函數示例：
需求分析： 先要從 user_trade 表中取出每個用戶的支付時間，把每個用戶放到一個窗口中，按照支付時間進行排序，取出偏移列： lead(dt,1,dt) over(partition by user_name order by dt)。接著基於該子查詢，篩選出時間間隔大於100天的用戶，並計算數量。
注意 ： 如果上面偏移分析函數寫成 lead(dt,1,dt) 就不用加後面的 dt is not null 了，因為有默認值的話，間隔就是0，肯定是不滿足條件的。
需求分析 ：
第一步 ：這個需求要用到 user_trade 和 user_info 兩張表，前者取支付時間和金額，後者取城市和性別。先對這兩張表基於 user_name 進行左連接，並取出相應欄位，用窗口函數進行分組排序：
這一步的運行結果是這樣的：
第二步 ：基於上述結果取出TOP3：
需求分析：
第一步 ：這個需求同樣要用到兩張表 user_refund 和 user_info。我們先把每個退款用戶的退款金額和手機品牌取出來，並用窗口函數進行切片排序，25%就是分成4片：
注意 ：這里之所以要加 WHERE dt is not null 是因為 user_refund 是一個分區表，分區表要對分區欄位進行限制，否則 hive 會報錯。
第二步 ：選擇前25%，也就是第一片：
最後補充一個從 hive 導出結果數據的命令：
以上就是這節課的全部內容了。做完整個練習，真的半條命都沒了。窗口函數果然很難，不過掌握方法、多多練習，學會拆解需求，一步一步來做，就能明顯降低難度。希望以後有機會能用到這么復雜的技能，哈哈~！
⑶ hive關於窗口函數的使用
窗口函數可以理解為給數據劃到一個窗口內並排上序號。
over()即那個窗口函數，需要搭配其他函數進行分析
over()括弧內部可以加上partiton by進行分組，加上order by進行排序。不加上partition by是對所有數據開了一個窗，加上partition by是對分組內部所有數據進行開窗。加上order by對分組內數據按排序進行開窗。
形如over(partition by class order by score)，這個會對class欄位進行分組，然後在分組的內部基於score進行排序。
此外還可以給窗口計算加行范圍，over(rows between 開始位置 and 結束位置),開始位置和結束位置可以填的包括
PRECEDING：往前
FOLLOWING：往後
CURRENT ROW：當前行
UNBOUNDED PRECEDING：窗口的起點
UNBOUNDED FOLLOWING：窗口的終點
形如：over(partition by class order by score rows between UNBOUNDED PRECEDING and 1 PRECEDING)，這個會對class欄位進行分組，然後在排序後從窗口第一個值取到當前行前一個值
先假設一個總分表score_table有姓名name、班級class、總分score三個欄位
row_number()、rank()、dense_rank()
對各個班級內部進行總分排名
對排名連續無相同名次，比如有三個分數98、98、97，排名為1、2、3，使用row_number()
形如sql：
select *, row_number() over(partition by class order by score desc) as num from score_table;
對排名不連續有相同名次，比如有三個分數98、98、97，排名為1、1、3，使用rank()
形如sql：
select *, rank() over(partition by class order by score desc) as num from score_table;
對排名連續有相同名次，比如有三個分數98、98、97，排名為1、1、2使用dense_rank()
形如sql：
select *,dense_rank() over(partition by class order by score desc) as num from score_table;
avg(列名)、sum(列名)、max(列名)、min(列名)
計算排擠內前各個名次的平均分
形如sql：
select *, avg(score) over(partition by class order by score desc) as num from score_table;
ntile(n)函數
把窗口內按班級分組後的數據按先成績排名後分成3份，分成1、2、3組
形如sql：
select *, ntile(3) over(partition by class order by score desc) as num from score_table;
lag(列名,往前的行,默認值)
把窗口內按班級分組後的數據，取該分組內每一行數據的前一名同學的成績，如果第一名就顯示100；
形如sql：
select *, lag(score,1,100) over(partition by class order by score desc) as num from score_table;
lead(列名,往後的行,默認值)
把窗口內按班級分組後的數據，取該分組內每一行數據的後一名同學的成績，如果最後一名就顯示0；
形如sql：
select *, lag(score,1,0) over(partition by class order by score desc) as num from score_table;
first_value(列名)和last_value(列名)
把窗口內按班級分組後的數據，取該分組內第一名同學的成績
形如sql：select *, first_value(score) over(partition by class order by score desc) as num from score_table;
把窗口內按班級分組後的數據，取該分組內最後一名同學的成績
形如sql：select *, last_value(score) over(partition by class order by score desc) as num from score_table;
總結：
窗口函數的使用大致遵循以下
函數名字(arg1, arg2, ... argN) OVER( [PARTITION BY 分組列] [ORDER BY 排序列] [計算的行范圍] )
本文舉的函數有row_number()、rank()、dense_rank()、avg(列名)、sum(列名)、max(列名)、min(列名)、ntile(n)、lag(列名,往前的行,默認值)、lead(列名,往後的行,默認值)、first_value(列名)和last_value(列名)
⑷ PostgreSQL 高級SQL(五) 內建窗口函數
前面倆個章節我們介紹了窗口函數、滑動窗口函數的概念，接下來我們介紹一下PG支持的原生通用窗口函數，總共11個(9.6版本, 中國社區官網文檔地址 ）
1、row_number 函數
row_number函數可以給每隔數據行返回一個虛擬的自增ID,也就是相當於給行分配一個編號，這些編號不會出現重復，即使over()裡面沒有按照欄位排序欄位也能正常工作，
2、rank函數
rank的官方解釋是：帶間隙的當前行排名； 與該行的第一個同等行的row_number相同
從上面的的結果我們可以看出 rank函數和row_number一樣可以將行編號，但是號碼可能重復，比如我們按照年份排序，年份相同的話rank值相同，2017年的數據rank直接跳到了7，這就相當於上學的時候考試，用rank計算排名的話，如果同年級出現三個並列的第一名的話，那麼計算的結果將是三個人的rank值都是第一，但是實際上的第二高的分數的同學會被rank排名為第四名，如果我們想第二高的分數的排名為2,我們可以使用dense_rank函數；
3、dense_rank函數
從上面的結果我們可以看出dense_rank函數會把編號弄得更加緊密，中間不會出現像rank那樣的斷層編碼。
4、percent_rank函數
官方文檔解釋： 當前行的相對排名=(rank- 1) / (總行數 - 1) ,
排名和rank值成正相關 ，rank值相同的行號 percent_rank獲取的結果也一樣，返回的 結果是個小數范圍在[0,1]之間 ，可以等於0或者1
5、cume_dist函數
官方文檔解釋： 當前行的相對排名=(rank- 1) / (總行數 - 1) ，
排名和rank值成正相關 ，rank值相同的行號 percent_rank獲取的結果也一樣，返回的 結果是個小數范圍在[0,1]之間 ，可以等於0或者1
6、ntile函數
官方文檔解釋：從1到參數值的整數范圍，盡可能等分分區，
ntile(num_buckets)，num_buckets的值表示將結果集分成num_buckets組，有限填滿前面的組，最後一組可能出現個數不足(非等分)情況，實際上就是把每隔行分個組號。
7、lag函數
官方文檔解釋：lag( value anyelement [, offset integer [, default anyelement ]])，返回 value ， 它在分區內當前行的之前 offset 個位置的行上計算；如果沒有這樣的行，返回 default 替代。 (作為 value 必須是相同類型)。 offset 和 default 都是根據當前行計算的結果。如果忽略它們，則 offset 默認是1， default 默認是空值
官方文檔的解釋很晦澀難懂，我們直接使用用例執行一下看一下數據分布就好了
從上圖可以知道當前行的lag值是當前行的前offset行的值，沒有的話就返回default,default不想存在的話就返回null,從數據姐過再去看官方文檔的解釋的話可能清晰很多，lag函數可以在結果集的行內移動，經常使用到的場景是計算今年和全年的年產量的差值，
8、lead函數
官方文檔解釋：lead( value anyelement [, offset integer [, default anyelement ]]) 返回 value ，它在分區內當前行的之後 offset 個位置的行上計算；如果沒有這樣的行，返回 default 替代。(作為 value 必須是相同類型)。 offset 和 default 都是根據當前行計算的結果。如果忽略它們，則 offset 默認是1， default 默認是空值
其實lead函數和lag函數的作用是相同的，如果lead的offset參數值為-N,那麼lag的offset的參數為N的話計算結果是相同的，lag(gdp,-1)是lead(gdp,1)的替代
9、first_value、last_value、nth_value函數較為簡單不做介紹
至此我們講完了幾乎所有的窗口函數，希望這五篇關於PostgreSQL的文章能對大家在平時的開發中有所幫助
⑸ 如何用普通sql寫出lead函數的效果
, to_date('20161112','yyyymmdd') from al union all
select 1 id, to_date('20161113','yyyymmdd') from al union all
select 2 id, to_date('20161114','yyyymmdd') from al)
select id,dt,lead(dt)over(partition by id order by dt) dt1 from t;
with t as(
select 1 id, to_date('20161111','yyyymmdd')dt from al union all
select 2 id, to_date('20161112','yyyymmdd') from al union all
select 1 id, to_date('20161113','yyyymmdd') from al union all
select 2 id, to_date('20161114','yyyymmdd') from al)
,t1 as(select rownum rn,t.*
⑹ sql server 中 使用lead() over()函數報錯：lead無法識別的內置函數。
LEAD()，OVER()：ANSI SQL 2008 standard：關鍵是要ANSI的
⑺ MYSQL lag() 和lead()函數使用介紹
LAG()函數是一個窗口函數，允許您從當前行向前看多行數據。與LEAD()函數類似，LEAD()函數對於計算同一結果集中當前行和後續行之間的差異非常有用。
LAG語法： LAG(列名,[offset], [default_value]) OVER ( PARTITION BY 列名,... ORDER BY 列名 [ASC|DESC],... )
LEAD語法： LEAD(列名,[offset], [default_value]) OVER ( PARTITION BY 列名,... ORDER BY 列名 [ASC|DESC],... )
offset：offset是從當前行偏移的行數，以獲取值。offset必須是一個非負整數。如果offset為零，則LEAD()函數計算當前行的值。如果省略 offset，則LEAD()函數默認使用一個。
default_value：如果沒有後續行，則LEAD()函數返回default_value。例如，如果offset是1，則最後一行的返回值為default_value。如果您未指定default_value，則函數返回 NULL 。
PARTITION BY子句：PARTITION BY子句將結果集中的行劃分LEAD()為應用函數的分區。如果PARTITION BY未指定子句，則結果集中的所有行都將被視為單個分區。
ORDER BY子句：ORDER BY子句確定LEAD()應用函數之前分區中行的順序。
用途舉例：
ps：
不適合計算留存，舉例說明：
求3日留存用戶，以下為用戶登錄表login_history_table：
首先使用LEAD函數對用戶登錄時間做偏移，SQL如下：
結果如下：
根據上面查詢到的結果，3日留存用戶中不能統計到abc，而實際應該包含abc，因為該用戶20211022登錄後，在3天後的20211025日又重新登錄了。
⑻ 開窗函數
over在聚合函數中的使用：
一般格式：
聚合函數名(列) over(選項)
over必須與聚合函數或排序函數一起使用，聚合函數為：
sum(),max(),min(),count(),avg()
排序函數為：
rank(),row_number(),dense_rank(),ntile()
over表示把函數當成開窗函數而不是聚合函數，SQL標准允許將所有聚合函數用做開窗函數，使用over關鍵字來區分這兩種用法。
開窗函數不需要使用group by就可以對數據進行分組，就可以同時返回基礎行的列和聚合列。
開窗函數sum(*) over()，對於查詢結果的每一行都返回所有符合條件的行的條數，over關鍵字後的括弧中還經常添加選項來改變進行聚合運算的窗口范圍，如果over關鍵字後的括弧中選項為空，則開窗函數會對結果集中的所有行進行聚合運算。
常用格式：
sum(*) over(partition by A order by B)
partition by：進行分組，得到對應組內的所有求和值
order by：按照B進行排序，得到對應組內的累計求和值(如果B為id,兩個id相同，則這兩個id返回的sum那一列是相同的聚合值，是累計到最後一個id對應值的和--下面的例子會詳細說明)
order by 欄位名 rows|range between 邊界規則1 and 邊界規則2
rows：表示按照行的范圍進行范圍的定位
range：表示按照取值的范圍進行范圍的定位
這兩種不同的定位方式主要用來處理並列排序的情況（見下面的例子）
邊界規則的可取值為：
current row--當前行
n preceding--前n行
unbounded preceding--一直到第一條記錄
n following--後n行
unbounded following--一直到最後一條記錄
'range/rows between 邊界規則1 and 邊界規則2'：用來定位聚合計算范圍，被稱為定位框架。
eg:
1、建表
2、插入數據
3、關於partition by
（1）所屬城市的人員數-按城市進行分組聚合
（2）顯示每一個人員的信息、所屬城市的人員數以及同齡人的人數
在同一個SELECT語句中可以同時使用多個開窗函數，而且這些開窗函數並不會相互干擾。
4、關於order by的詳解：
（1）查詢從第一行到當前行的的工資總和
（2）將上面的row換成range
結果和(1)的區別體現在紅框和黃框部分，按照FSalary進行排序，row-按照行的范圍進行范圍定位，所以每一行後面對應的『到當前行工資求和』都不一樣，都嚴格的是第一行到當前行的累計和；range-按照取值的范圍進行范圍定位，雖然定位框架的語法仍然是從第一行到當前行的累計和，但是由於取值的范圍：等於2000元的工資有3人，所以計算的累計為從第一條到2000元工資的最後一個人，寫在每個2000元工資的人的後面都是7000。
（3）將(2)中的定位框架省略
上述框架是開窗函數中最常用的定位框架，如果是這種框架的話，可以省略上述定位框架部分
得到的結果和(2)的結果一樣。
（4）將上面的sum()換成count()，計算工資排名
按照salary進行排序，然後計算從第一行（unbounded preceding）到當前行（current row）的人員的個數，相當於計算人員的的工資水平排名。
Question:
怎麼讓工資為2000元的排名都為2？--見後面排序函數的rank()和dence_rank()
5、關於over(partition by A order by B)
over在排序函數中的使用：
一般格式：
排序函數(列) over(選項)
排序函數為：
rank(),dense_rank(),row_number(),ntile(),lead(),lag()
1、rank(),dense_rank(),row_number()的區別
rank()與dense_rank()的區別：
兩者都是計算一組數值中的排序值，
但是在有並列關系時，dence_rank中相關等級不會跳過，rank則跳過。
rank() 是跳躍排序，有兩個第二名時接下來就是第四名（同樣是在各個分組內）
dense_rank()是連續排序，有兩個第二名時仍然跟著第三名。
row_number()：
row_number over(partition by A order by B)
根據A分組，在分組內根據B排序，且得出來的值是每組內部排序後的順序編號（組內連續的唯一的）
其主要是『行』的信息，並沒有排名。row_number()必須與order by一起使用，
多用於分頁查詢，比如查詢10-100個學生。
2、ntile(x)--平均分區函數
3、lag() over(partition by A order by B)
lead() over(partition by A order by B)
lag和lead中有三個參數，lag('列名',offset,'超出記錄窗口時的默認值')
lag和lead可以獲取，按一定順序B排列的當前行的上下相鄰若干offset的莫隔行的某個列。
lag()是向前，lead()是向後。
參考 https://www.cnblogs.com/lihaoyang/p/6756956.html
⑼ 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、對分組後的表進行條件篩選提取