日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

本文講解窗口函數的概念，窗口函數與數據分組的功能相似，可以指定數據窗口進行統計分析，但窗口函數與數據分組又有所區別，窗口函數對每個組返回多行，而數據分組對每個組只返回一行；窗口函數指定了分析函數工作的數據窗口大小，這個數據窗口大小可能會隨著行的變化而變化，而數據分組是針對所有數據進行統計。

窗口函數的寫法：

<窗口函數> over (partition by <用于分組的列名>order by <用于排序的列名>)

窗口函數主要有兩種，一種是專用窗口函數，包括rank、dense_rank、row_number等。另一種是聚合函數，包括sum、avg、count、max、min等，本文逐一介紹窗口函數的五個功能，分別是聚合、排序、極值、移動、切片，下面一起來學習。

首先創建一個金額表，年份、姓名、國家設置為字符串類型，交易金額設置為整型。

#創建金額表pay
CREATE TABLE pay (
year VARCHAR ( 10 ),
name VARCHAR ( 10 ),
country VARCHAR ( 10 ),
payment INT(10) );

給金額表插入數值。

# 給金額表插入數據
INSERT INTO pay(year,name,country,payment)
VALUES
(2017,'Lining','China',1119),
(2018,'Lining','China',1176),
(2018,'Zhaoqi','China',1388),
(2019,'Zhaoqi','China',1597),
(2018,'Jackie','USA',1028),
(2019,'Jackie','USA',1934),
(2020,'Jackie','USA',1837),
(2017,'Tom','India',1578),
(2018,'Tom','India',1329),
(2019,'Tom','India',1578),
(2020,'Tom','India',1399);

將所有的數據查詢出來結果如下所示。

SELECT * from pay;

一、聚合

1、計算列表總金額

SELECT *, SUM(payment) OVER() as Total_payment from pay;

計算當前列表的總金額可以使用窗口函數，sum是求和，over()中不添加參數，則對所有數據進行求和，輸出的結果都是15963。

2、計算各國家總金額

SELECT *, SUM(payment) OVER() as Total_payment,
SUM(payment) OVER(PARTITION by country) as country_payment from pay;

計算各國家總金額就要對各個國家分組，這里分組使用的是PARTITION by，PARTITION by的功能與GROUP BY的功能類似，指定按照那一列進行分組，用country分組求和，則每個country的輸出結果一致。

3、按國家降序累加求和金額

SELECT *, SUM(payment) OVER() as Total_payment,
SUM(payment) OVER(PARTITION by country) as country_payment,
SUM(payment) OVER(PARTITION by country ORDER BY payment DESC) as order_payment from pay;

這里使用SQL中常用的向下累計求和的方法，當使用order by時，沒有rows between則意味著窗口是從起始行到當前行，所以對不同國家進行累加求和操作。

4、不同國家人數計數

count()用于計數，與前面sum的用法基本一致，可以用count(distinct country)進行去重，如果用partition by進行分組，則分組后再計數。

SELECT *, COUNT(name) OVER() as Total_people,
COUNT(name) OVER(PARTITION by country) as country_people from pay;

5、 不同國家平均金額

SELECT *, AVG(payment) OVER() as avg_payment,
AVG(payment) OVER(PARTITION by country) as country_ayg_payment from pay;

使用avg聚合函數的用法與前面的聚合運算用法一致，PARTITION by同樣用來分組，這里分組后求均值。

6、各國家最低金額

SELECT *, MAX(payment) OVER() as Max_payment,
MIN(payment) OVER(PARTITION by country) as country_min_payment from pay;

這里MAX(payment)函數對整個數據計算最大值，使用PARTITION by對于不同的國家分組后然后計算最小值。

二、排序

1、各國家按金額排序

使用窗口函數排序，會使用到三個函數，row_number,rank,dense_rank，他們的使用區別如下：

• row_number從1開始，按照順序，生成分組內記錄的序列；
• rank生成數據項在分組中的排名，排名相等會在名次中留下空位；
• dense_rank生成數據項在分組中的排名，排名相等會在名詞中不會留下空位。

SELECT *,
ROW_NUMBER()OVER(ORDER BY payment DESC) as '順序排序',
RANK()OVER(ORDER BY payment DESC) as '秩排序',
DENSE_RANK()over(ORDER BY payment DESC) as '數據排序'
from pay;

row_number函數，按照行記錄的順序來排序，此處從1到11按順序排列；rank函數，在排名相等會在名次中留下空位，此處共同排名為第4名，同時忽略第5名，繼續往下排列；dense_rank排名相等會在名詞中不會留下空位此處共同排名為第4名，不忽略第5名，繼續往下排列。

三、極值

1、當前行金額最高的人

first_value截止當前行的第一個，last_value截止當前行的最后一個。

select *,
first_value(name)over(order by payment desc) as max_id,
first_value(name)over(order by payment asc) as min_id,
last_value(name)over(order by payment desc) as min_id_1,
last_value(name)over(partition by country order by payment desc rows between unbounded preceding and unbounded following) as level_min_id
from pay;

first_value按分組排序后取范圍內第1個值，last_value取最后1個值，因為默認窗口的關系，last_value會隨著窗口的改變而改變，所以一般不用last_value，如果要用，則改變窗口為所有行，此處用來查詢當前金額最大的人，以及截至當前金額最小的人。

四、移動

1、按國家分組金額排名前1位和后1位人名

lag和lead是按照排序規則，取前多少位和后多少位，參數有3個，第1個是要取出來的列，第2個移動多少位，第3個是如果取不到，賦予的值，默認取不到是NULL。

select *,
lag(name,1,null)over(partition by country order by payment desc) as lag_id,
lead(name,1,'0')over(partition by country order by payment desc) as lead_id
from pay;

五、切片

1、按金額切片

ntile(n)用于將分組數據按照順序切分成N片，返回當前切片值，ntile把有序分區中的行分發到指定數據的組中，各個組有編號，編號從1開始，對于每一行，ntile返回此行所屬的組的編號，ntile(3)表示將表切分為3組，ntile可以分組排序后切分，表示對當前的組內進行切分后排序。

select *,
ntile(3) over(order by payment desc) as total_part,
ntile(2)over(partition by country order by payment desc) as level_part
from pay;