SQL進(jìn)階，子查詢與窗口函數(shù)

本節(jié)給大家講解SQL在實(shí)際過(guò)程中用途比較多的子查詢與窗口函數(shù)，下面一起學(xué)習(xí)。

示例工具：MySQL8.0、Navicat Premium 12

本文講解內(nèi)容：子查詢與窗口函數(shù)

適用范圍：SQL進(jìn)階應(yīng)用

子查詢用于為主查詢返回其所需數(shù)據(jù)，或者對(duì)檢索數(shù)據(jù)進(jìn)行進(jìn)一步的限制,通常將一個(gè)查詢（子查詢）的結(jié)果作為另一個(gè)查詢（主查詢）的數(shù)據(jù)來(lái)源或判斷條件，常見(jiàn)的子查詢有WHERE子查詢，HAVING子查詢，F(xiàn)ROM子查詢，SELECT子查詢，EXISTS子查詢。

子查詢是一種嵌套在其他 SQL 查詢的 WHERE 子句中的查詢，可以在 SELECT、INSERT、UPDATE 和 DELETE 語(yǔ)句中，同邏輯運(yùn)算符一起使用。

使用子查詢必須遵循以下幾個(gè)規(guī)則：

• 子查詢必須括在圓括號(hào)中。

• 子查詢的 SELECT 子句中只能有一個(gè)列。

• 子查詢不能使用 ORDER BY，在子查詢中，GROUP BY 可以起到同 ORDER BY 相同作用。

• 返回多行數(shù)據(jù)的子查詢只能同多值操作符一起使用，比如 IN 操作符。

• 子查詢不能直接用在聚合函數(shù)中。

• BETWEEN 不能同子查詢一起使用，但 BETWEEN 操作符可以用在子查詢中。

通常情況下子查詢都與 SELECT 語(yǔ)句一起使用，其基本語(yǔ)法如下所示：

SELECT?column_name?[,?column_name?]FROM???table1?[,?table2?]WHERE??column_name?OPERATOR?(SELECT?column_name?[,?column_name?]                             FROM?table1?[,?table2?]                             [WHERE])

對(duì)于子查詢的數(shù)據(jù)演示創(chuàng)建兩個(gè)表，一個(gè)是薪水表，另一個(gè)是職位表，并且插入數(shù)據(jù)。

#創(chuàng)建薪水表SALARYCREATE?TABLE?SALARY?(ID?VARCHAR?(?10?),NAME?VARCHAR?(?10?),AGE?VARCHAR?(?10?),ADDRESS?VARCHAR?(?10?),SAL INT(10) );

給薪水表插入數(shù)據(jù)，數(shù)據(jù)內(nèi)容如下所示：

#?給薪水表插入數(shù)據(jù)INSERT?INTO?SALARY(ID,NAME,AGE,ADDRESS,SAL) VALUES('C001','Rmesh',35,'Ahmedabad',2000),('C002','Khilan',25,'Delhi',1500),('C003','Kaushik',23,'Kota',2000),('C004','Chaitali',25,'Mumbai',6500),('C005','Hardik',27,'Bhopal',8500),('C006','Komal',22,'MP',4500),('C007','Tom',26,'MP',5500),('C008','Muffy',24,'Indore',10000);

查詢所有的薪水?dāng)?shù)據(jù)如下所示：

SELECT * FROM  SALARY;

同理創(chuàng)建一個(gè)職位表。

#創(chuàng)建職位表JOBCREATE TABLE JOB (JID?VARCHAR?(?10?),JB VARCHAR ( 10 ));

給職位表插入數(shù)據(jù)，數(shù)據(jù)內(nèi)容如下所示：

#?給職位表插入數(shù)據(jù)INSERT?INTO?JOB(JID,JB) VALUES('C001','Teacher'),('C002','Docter'),('C003','Teacher'),('C004','Worker'),('C005','Nurse'),('C006','Teacher'),('C007','Docter'),('C008','Teacher');

查詢所有的職位數(shù)據(jù)如下所示：

SELECT * FROM  JOB;

子查詢最常見(jiàn)的使用是在WHERE子句的IN操作符中，以及用來(lái)填充計(jì)算列。先看一個(gè)簡(jiǎn)單的例子，要查詢所有醫(yī)生的薪水情況，這里首先在職位表中查詢所有醫(yī)生的JID，查詢結(jié)果如下：

SELECT JIDFROM JOBWHERE JB='Docter';

然后在薪水表中查詢ID為'C002','C007'的薪水情況，查詢結(jié)果如下：

SELECT SALFROM SALARYWHERE ID IN('C002','C007');

這里使用子查詢更加簡(jiǎn)便，子查詢從內(nèi)向外依次處理，在下面的SELECT語(yǔ)句中，MySQL實(shí)際上執(zhí)行了兩個(gè)操作，首先查詢返回兩個(gè)ID號(hào)：C002和C007。

然后，這兩個(gè)值以IN操作符要求的逗號(hào)分隔的格式傳遞給外部查詢的WHERE子句，可以看到輸出的結(jié)果是正確的，并且與前面WHERE子句所返回的值相同。

SELECT SALFROM SALARYWHERE ID IN(SELECT JID            FROM JOB            WHERE JB='Docter');

使用子查詢查詢薪水大于8000的員工的所有信息，首先內(nèi)部查詢薪水大于8000的ID，然后外部使用一個(gè)WHERE查詢即可得到結(jié)果。

SELECT *FROM SALARYWHERE?ID?IN?(SELECT?ID             FROM?SALARY             WHERE SAL > 8000);

使用子查詢的另一方法是創(chuàng)建計(jì)算字段，創(chuàng)建計(jì)算字段需要使用聚合函數(shù)，例如count，sum，avg，max，min等，這里首先計(jì)算平均薪水作為一個(gè)內(nèi)查詢，然后在外部使用WHERE子句進(jìn)行查詢，得出薪資比平均薪資低的員工的所有信息。

SELECT?* FROM?SALARYWHERE SAL < (SELECT AVG(SAL)             FROM SALARY);

除使用WHERE過(guò)濾，還可以使用HAVING過(guò)濾，HAVING子句對(duì)分組統(tǒng)計(jì)函數(shù)進(jìn)行過(guò)濾，也可以在HAVING子句中使用子查詢，要查詢薪資最高的人及其薪資情況，首先內(nèi)部查詢最高工資，然后外部以人名分組后使用HAVING子句過(guò)濾，查詢結(jié)果如下。

SELECT NAME,SALFROM SALARYGROUP BY NAMEHAVING SAL = (SELECT MAX(SAL)              FROM SALARY);

窗口函數(shù)與數(shù)據(jù)分組功能相似，可指定數(shù)據(jù)窗口進(jìn)行統(tǒng)計(jì)分析，但窗口函數(shù)與數(shù)據(jù)分組又有所區(qū)別，窗口函數(shù)對(duì)每個(gè)組返回多行，而數(shù)據(jù)分組對(duì)每個(gè)組只返回一行；窗口函數(shù)指定分析函數(shù)工作的數(shù)據(jù)窗口大小，這個(gè)數(shù)據(jù)窗口大小可能會(huì)隨著行的變化而變化，而數(shù)據(jù)分組是針對(duì)所有數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，窗口函數(shù)的寫(xiě)法如下。

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

窗口函數(shù)主要有兩種，一種是專用窗口函數(shù)，包括rank、dense_rank、row_number等。另一種是聚合函數(shù)，包括sum、avg、count、max、min等，下面逐一介紹窗口函數(shù)的五個(gè)功能，分別是聚合、排序、極值、移動(dòng)、切片，下面一起來(lái)學(xué)習(xí)。

首先創(chuàng)建一個(gè)金額表，年份、姓名、國(guó)家設(shè)置為字符串類型，交易金額設(shè)置為整型。

#創(chuàng)建金額表payCREATE TABLE pay (year?VARCHAR?(?10?),name VARCHAR ( 10 ),country VARCHAR ( 10 ),payment INT(10) );

給金額表插入數(shù)值。

# 給金額表插入數(shù)據(jù)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);

將所有的數(shù)據(jù)查詢出來(lái)結(jié)果如下所示。

SELECT?*?from?pay;

1、計(jì)算列表總金額

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

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

2、計(jì)算各國(guó)家總金額

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

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

3、按國(guó)家降序累加求和金額

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中常用的向下累計(jì)求和的方法，當(dāng)使用order by時(shí)，沒(méi)有rows between則意味著窗口是從起始行到當(dāng)前行，所以對(duì)不同國(guó)家進(jìn)行累加求和操作。

4、不同國(guó)家人數(shù)計(jì)數(shù)

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

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

5、 不同國(guó)家平均金額

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

使用avg聚合函數(shù)的用法與前面的聚合運(yùn)算用法一致，PARTITION by同樣用來(lái)分組，這里分組后求均值。

6、各國(guó)家最低金額

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

這里MAX(payment)函數(shù)對(duì)整個(gè)數(shù)據(jù)計(jì)算最大值，使用PARTITION by對(duì)于不同的國(guó)家分組后然后計(jì)算最小值。

1、各國(guó)家按金額排序

使用窗口函數(shù)排序，會(huì)使用到三個(gè)函數(shù)，row_number,rank,dense_rank，他們的使用區(qū)別如下：

• row_number從1開(kāi)始，按照順序，生成分組內(nèi)記錄的序列；

• rank生成數(shù)據(jù)項(xiàng)在分組中的排名，排名相等會(huì)在名次中留下空位；

• dense_rank生成數(shù)據(jù)項(xiàng)在分組中的排名，排名相等會(huì)在名詞中不會(huì)留下空位。

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

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

1、當(dāng)前行金額最高的人

first_value截止當(dāng)前行的第一個(gè)，last_value截止當(dāng)前行的最后一個(gè)。

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_idfrom pay;

first_value按分組排序后取范圍內(nèi)第1個(gè)值，last_value取最后1個(gè)值，因?yàn)槟J(rèn)窗口的關(guān)系，last_value會(huì)隨著窗口的改變而改變，所以一般不用last_value，如果要用，則改變窗口為所有行，此處用來(lái)查詢當(dāng)前金額最大的人，以及截至當(dāng)前金額最小的人。

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

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_idfrom pay;

1、按金額切片

ntile(n)用于將分組數(shù)據(jù)按照順序切分成N片，返回當(dāng)前切片值，ntile把有序分區(qū)中的行分發(fā)到指定數(shù)據(jù)的組中，各個(gè)組有編號(hào)，編號(hào)從1開(kāi)始，對(duì)于每一行，ntile返回此行所屬的組的編號(hào)，ntile(3)表示將表切分為3組，ntile可以分組排序后切分，表示對(duì)當(dāng)前的組內(nèi)進(jìn)行切分后排序。

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

對(duì)比Excel系列圖書(shū)累積銷量達(dá)15w冊(cè)，讓你輕松掌握數(shù)據(jù)分析技能，可以在全網(wǎng)搜索書(shū)名進(jìn)行了解：