文章目錄
- OVER函數
-
- 1.含義：
- 2.示例：
OVER函數
1.含義：
視窗函數主要解決 分析整體資料時，多個資料和一個結果對應，為每一條資料開啟指定行範圍的視窗；
over() 指定 分析函數 工作時的資料視窗大小，這個視窗大小可能會随行的改變而改變；
用于between … and … 表示範圍概念：
CURRENT ROW：目前行
n PRECEDING：往前n行資料
n FOLLOWING：往後n行資料
UNBOUNDED：起點，
UNBOUNDED PRECEDING 表示從前面的起點，
UNBOUNDED FOLLOWING表示到後面的終點
LAG(col,n,default_val)：往前第n行資料
LEAD(col,n, default_val)：往後第n行資料
NTILE(n)：把有序視窗的行分發到指定資料的組中，各個組有編号，編号從1開始，對于每一行，NTILE傳回此行所屬的組的編号。注意：n必須為int類型。
常用格式：
over() 會為每條資料開啟一個視窗，預設的視窗大小就是目前資料集的大小
over(partition by 字段) 會按照指定的字段進行分區，把分區字段值相同的資料劃分到同一個分區；分區中每條資料開啟一個視窗，每條資料的預設的視窗大小就是目前分區資料集的大小
over(order by 字段) 會在視窗中按照指定的字段對資料進行排序；會為每條資料開啟一個視窗，預設的視窗大小為從資料集開始UNBOUNDED PRECEDING到目前行CURRENT ROW
over(partition by 字段 order by 字段) 會按照指定的字段進行分區，把分區字段值相同的資料劃分到同一個分區，會在每個分區中按照指定的字段進行排序；分區中每條資料開啟一個視窗，每條資料的預設的視窗大小就是目前分區從資料集開始UNBOUNDED PRECEDING到目前行CURRENT ROW
over(partition by 字段 order by 字段 rows between … and …) 會按照指定的字段進行分區，把分區字段值相同的資料劃分到同一個分區，會在每個分區中按照指定的字段進行排序；分區中每條資料開啟一個視窗，每條資料的視窗大小為指定的視窗大小
2.示例：
business 表資料：
（1）查詢在2017年4月份購買過的顧客及總人數
select name,count(*) over ()
from business
where substring(orderdate,1,7) = '2017-04'
group by name;
（2）查詢顧客的購買明細及每個月所有顧客月購買總額
select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from
business;
查詢顧客的購買明細及每個顧客月購買總額
（3）将每個顧客的cost按照日期進行累加
select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,--按name分組，組内資料相加
sum(cost) over(partition by name order by orderdate) as sample3,--按name分組，組内資料累加
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一樣,由起點到目前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --目前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--目前行和前邊一行及後面一行
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --目前行及後面所有行
from business;
（4）檢視顧客上次的購買時間以及下一次的購買時間
select name,orderdate,cost,
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1, lag(orderdate,2) over (partition by name order by orderdate) as time2
from business;
（5）查詢前20%時間的訂單資訊
select * from (
select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
from business
) t
where sorted = 1;