入數據行業近3年了，這3年的工作時間我絕大多數時間都在寫python做分析。但作為合格的一個數據分析師，sql的精通肯定是必不可少的，所以最近瘋狂刷sql題，同時也來總結下我以前比較少用的語法。

（工作寫的是hive，為方便演示，本文章均使用Mysql8.0.16版本）

一、窗口函數

1、什么是窗口函數

窗口函數，也叫OLAP函數（Online Anallytical Processing，聯機分析處理），可以對數據庫數據進行實時分析處理。

2、窗口函數基本語法

分析函數 over（partition by 列名 order by 列名 ）

2.1、分析函數分類

• 聚合類
  avg(列名)、sum(列名)、count(列名)、max(列名)、min(列名)
• 排名類
  row_number() 按照值排序時產生一個自增編號，不會重復
  rank() 按照值排序時產生一個自增編號，值相等時會重復，會產生空位
  dense_rank() 按照值排序時產生一個自增編號，值相等時會重復，不會產生空位
• 注意：排名類分析函數不需要任何參數。
• 其他類
  lag(列名,往前的行數,[行數為null時的默認值，不指定為null])
  lead(列名,往后的行數,[行數為null時的默認值，不指定為null])
  ntile(n) 用于將分組數據按照順序切分成n片，返回當前切片值，如果切片不均勻，默認增加第一個切片的分布。

3、窗口函數的功能

1、同時具備分組和排序的功能。

2、不像group by，分組后不減少原表行數

3、不用表連接就能建立輔助分析列。

4、實戰理解

#創建學生表格 create table tb_score (id int,class varchar(255),score int);#插入數據 insert into tb_score (id,class,score) values (1,'語文',99), (1,'數學',90), (1,'英語',80), (3,'英語',80), (3,'語文',80), (3,'數學',60), (2,'數學',50), (2,'語文',40);#創建用戶購買明細表 create table tb_user (name1 varchar(255),orderdate varchar(255),cost int);#插入數據 insert into tb_user(name1,orderdate,cost) values ('jack','2017-01-01',10), ('tony','2017-01-02',15), ('jack','2017-02-03',23), ('tony','2017-01-04',29), ('jack','2017-01-05',46), ('jack','2017-04-06',42), ('tony','2017-01-07',50), ('jack','2017-01-08',55), ('mart','2017-04-08',62), ('mart','2017-04-09',68), ('neil','2017-05-10',12), ('mart','2017-04-11',75), ('neil','2017-06-12',80), ('mart','2017-04-13',94);

4.1、排名函數row_number() ,rank() ,dense_rank()的區別與用法

• row_number() 按照值排序時產生一個自增編號，不會重復。
• rank() 按照值排序時產生一個自增編號，值相等時會重復，會產生空位 。
• dense_rank() 按照值排序時產生一個自增編號，值相等時會重復，不會產生空位。

select *, rank() over( order by score desc) ranking , dense_rank() over( order by score desc) dense_ranking, row_number() over( order by score desc) row_num from tb_score

4.2、排名函數經典面試題。

4.2.1、排名問題 ：增加成績排名一列，如果兩個分數相同，則排名并列（dense_rank）。

select *, dense_rank() over(order by score desc) ranking from tb_score

4.2.2、TopN問題：取出各科成績前兩名的記錄（TopN問題用row_number）

select * from (select *,row_number() over(partition by class order by score desc) as ranking from tb_score) t where ranking <=2

4.3 其他類：lag，lead，ntile的用法

• lag（列名,往前的行數,[行數為null時的默認值，不指定為null]）
• lead（列名,往后的行數,[行數為null時的默認值，不指定為null]）
• ntile（n） 用于將分組數據按照順序切分成n片，返回當前切片值，如果切片不均勻，默認增加第一個切片的分布。

4.3.1、lad,lead函數的理解需要想清楚“為什么會出現Null值”

SELECT *,lag(cost,1) over(partition by name1 order by cost desc) as lag_function,lead(cost,1) over(partition by name1 order by cost desc) as lead_function FROM test.tb_user

4.4、其他類函數經典面試題

4.4.1、查詢顧客上次的購買時間

select *,lag(orderdate,1) over(partition by name1 order by orderdate) as last_buy from tb_user

4.4.2、查詢前20%時間的訂單信息：20%即把數據集切成5分，所以ntile(5)

select * from (select *,ntile(5) over(order by orderdate) as group_num from tb_user) t where group_num = 1

4.5、聚合函數用法及經典面試題

語法：sum() over（partition by 列名 order by 列名 ）

組內對比問題：查詢單科成績高于該科目平均成績的學生

select * from (select *,avg(score) over(partition by class) as avg_score from tb_score) t where score >avg_score

小結：

1、窗口函數原則上只能寫在Select句子中。

2、窗口函數做數據處理的核心思想是建立“ 輔助列“去幫助我們做篩選，比較。

3、對我而言，掌握了窗口函數，感覺再也不怕寫SQL了(很可能是錯覺)

二、變量

1、變量的一些概念：

• SQL的變量就是一個參數，和python一樣，可以對這個參數進行賦值。
• 變量分為局部變量和全局變量，局部變量用@標識，全局變量@@標識（全局變量一般已經定義好的）

2、變量的申明與賦值

2.1、變量的申明；sql server中變量要先申明后才能賦值。mysql中變量不用事前申明，在用的時候直接賦值。

語法 ：declare @變量名 數據類型；例如：declare @num int；

2.2、賦值方法有兩種

#第一種方法 set @num = 1; 或 set @num :=1#第二種方法 select @num:=1; 或 select @num:=字段名 from 表名 where (注意篩選出的結果應為單值）

注意：上面兩種賦值符號，使用set時可以用“=”或“：=”，但是使用select時必須用“：=賦值”。

3、變量的使用。

#創建訂單明細表，字段：訂單號，瀏覽日期，加購物車日期，下訂單日期，收貨日期 create table tb_order(order_id int,view_time varchar(255),cart_time varchar(255),buy_time varchar(255), rec_time varchar(255));#插入數據 insert into tb_order(order_id,view_time,cart_time,buy_time,rec_time) values (1,'2020-05-20','2020-05-21','2020-05-22','2020-05-23'), (2,'2020-05-20','2020-05-20','2020-05-20','2020-05-20'), (3,'2020-05-24','2020-05-25','2020-05-26','2020-05-27'), (4,'2020-05-20','2020-05-21','2020-05-22','2020-05-23'), (5,'2020-05-28','2020-05-28','2020-05-28','2020-05-28');

找出瀏覽日期為'2020-05-20'的訂單明細

set @date1 = '2020-05-20'; select * from tb_order where view_time =@date1;

有同學會問，我直接寫view_time ='2020-05-20'不就行了嘛，為啥要搞這么復雜？

在上面場景下，確實直接寫view_time ='2020-05-20'就足夠了，但在某些場景下，寫變量會更加簡便。比如：

找出瀏覽日期，瀏覽日期，加購物車日期，下訂單日期，收貨日期均為2020年5月20日的訂單明細。

set @date1 = '2020-05-20'; select * from tb_order where view_time =@date1 and cart_time =@date1 and buy_time = @date1 and rec_time =@date1;

之后，找出各日期均為2020年5月28日的訂單明細，這時，你僅僅需要改一個變量即可，無需將所有日期都改。

set @date1 = '2020-05-28'; select * from tb_order where view_time =@date1 and cart_time =@date1 and buy_time = @date1 and rec_time =@date1;

使用變量也可以提高查詢效率。

例如：數據庫制定兩個執行計劃，制定執行計劃直接是需要消耗資源的。

select * from table where id =1 select * from table where id =2

而如果我們改成這樣，當我們把id改成2時，數據庫還是會執行之前的執行計劃，這樣就節省了時間。

set @idv = 1 select * from table where id =@idv

三、利用case when作數據透視圖

1、case when 語法；

select col0, (case when col1 = codition2 then 選項值1when col2 = codition2 then 選項值2else 默認值 end) as casewhen_col fron table

2、case when的三種最常用的應用場景

(1)、等值替換

(2)、范圍替換

(3)、數據透視圖（也稱列轉行）

3、實戰

#創建訂單價格表 create table tb_order2(order_id varchar(255),price int,time varchar(255),area varchar(255)); #插入數據 insert into tb_order2(order_id,price,time,area) values ('S001',10,'2019/1/1','A區'), ('S002',20,'2019/1/1','B區'), ('S003',30,'2019/1/1','C區'), ('S004',40,'2019/1/2','A區'), ('S005',10,'2019/1/2','B區'), ('S006',20,'2019/1/2','C區'), ('S007',30,'2019/1/3','A區'), ('S008',40,'2019/1/3','C區');select * from tb_order2;

3.1、值替換：將所有A區的訂單改成D區的訂單

select *,case when area ='A區' then 'D區' end as casewhen_col from tb_order2

3.2、范圍替換：假設有個需求為：價格<=10，劣質訂單；>=10&<=20，中等訂單；>20，優質訂單；

select *,(case when price <=10 then '劣質訂單'when price <=20 then '中等訂單'else '優質訂單' end) label from tb_order2

3.3、數據透視圖（也稱列轉行）

需求，想計算各區域各天的銷售總價。這種用excel作就很簡單直接作數據透視圖，如果寫sql去實現的話需要用到case when，結果如下表：

### 實現數據透視。 select time,sum(case when area ='A區' then price else 0 end) as 'A區總價',sum(case when area='B區' then price else 0 end) as 'B區總價',sum(case when area='C區' then price else 0 end) as 'C區總價' from tb_order2 group by time

四、With as

1、概念：with as也叫做子查詢部分，就類似于一個視圖或臨時表，可以用來存儲一部分的SQL語句，它可以提高代碼可讀性。

2、優點：

（1）、其最大的好處就是提高代碼的可讀性，如何with子句在后面要多次使用到，這可大大簡化SQL。

（2）、可以提高查詢性能。

3、使用

3.1、單表with as

with t1 as (select * from tb_score where class ='語文') select * from t1;

3.2、多表with as

with t1 as (select * from tb_score where class ='語文'),t2 as (select * from tb_score where class ='數學') #笛卡爾積 select * from t1,t2

注意：這里必須要整體作為一條SQL查詢，即with as語句之后不能加分號結束，不然會報錯，必須用select結束。

五、group_concat

因為我個人主要用clickhouse數據庫 + grafana BI工具，是一款寫SQL作數據可視化的BI工具，它需要符合這樣的固定格式才能出圖：[時間，維度，值]，所以若我想多維度展示的時候，就必須用groupArray函數去構建維度這列。后來我發現Mysql也有和groupArray函數幾乎相同功能的函數就是group_concat，所以就來學習下。

其語法：

group_concat([DISTINCT] 要連接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

注意：需要和group by聯合使用。

其用法：

1、以id分組，查看所有的class有哪些元素

select id,group_concat(distinct class) as groupc_col from tb_score group by id

2、改變分隔符為'-'

select id,group_concat(distinct class separator '-') as groupc_col from tb_score group by id

嗯，以上就是我以前比較少用的SQL知識點了。經過這次復習鞏固，看別的同事很長的SQL也沒這么怕啦。

感謝閱讀！

一個記錄會計轉到數據、算法路上所學的微信公眾號：Dathon數據分析

總結

以上是生活随笔為你收集整理的sql 赋值 null_巩固SQL - 窗口函数amp;变量amp;数据透视图的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。