推薦學習書籍

《CDA一級教材》在線電子版正式上線CDA網校，為你提供系統、實用、前沿的學習資源，助你輕松邁入數據分析的大門！

免費加入閱讀：https://edu.cda.cn/goods/show/3151?targetId=5147&preview=0

做數據分析的，為什么要寫SQL？

沒有數據的情況下，我們分析數據就像是巧婦難為無米之炊。因此，為了進行數據分析，我們必須獲取數據。而大多數情況下，數據都存放在數據庫中，這時候我們就必須要學會SQL取數了。

除了一部分公司專人專崗，有人幫你查好數據發你做分析,大部分情況還是需要你自己取數的。

本次我們沿用之前《極簡統計學入門》的“MVP”思路，用三節的內容梳理一下SQL（基于MySQL8.0），整個系列框架如下

• 第1節 SQL簡介與基礎知識

  • SQL簡介
  • SQL查詢基礎之DDL、DML、DQL
  • SQL數據類型
  • SQL查詢的執行順序
• 第2節 窗口分析函數

  • 窗口函數
  • 分析函數
• 第3節 SQL近N日登錄與連續登錄N日問題

  • 連續登錄N天的用戶數量
• 第4節 近N日留存的用戶數及留存率

  • 近N日留存的用戶數及留存率

1. SQL簡介

我們知道，SQL（結構化查詢語言）是一種數據庫語言，按照功能分類，有DDL、DQL、DML、DCL、TCL 五大類型，簡單了解一下它們分別是做什么用的：

（1）DDL（Data Definition Language） DDL是數據定義語言，主要用來定義或者改變表的結構。例如：create、alter、drop、truncate等語句。

（2）DQL（Data Query Language） DQL是數據查詢語言，主要用來從表中檢索數據。例如：select語句。

（3）DML（Data Manipulation Language） DML是數據操作語言，主要用來對數據庫里表中的數據進行操作。例如：insert、delete、update等語句。

（4）DCL（Data Control Language） DCL是數據控制語言，主要用來設置或更改數據庫用戶或角色對數據的訪問權限。例如：grant、revoke等語句。

（5）TCL（Transaction Control Language） TCL是事務控制語言，主要用來控制事務。例如：COMMIT、ROLLBACK等語句。

了解了以上分類，我們簡單回顧一下其中DDL、DML、DQL的基礎語法（有基礎的可直接跳過看SQL查詢與執行順序）

2. SQL基礎之DDL、DML、DQL

① 數據定義語言 (DDL)

定義數據庫當中的對象 (庫、表) 關鍵字: create、delete、alter、show

創建數據庫

• 創建數據庫 create database 數據庫名
• 創建指定字符集的數據庫 create database 數據庫名 charset set 字符集編碼
• 創建一個數據庫（先判斷數據庫是否已存在，如果不存在則進行創建）create database if not exist數據庫名

查看數據庫

• 查詢所有數據庫名稱

show database;

• 查詢指定數據庫的字符集 并顯示創建語句

show create database 數據庫名;

刪除數據庫

• 刪除指定數據庫

drop database 數據庫名;

• 刪除指定數據庫，如果不存在則不刪除

drop database 數據庫名 if exist;

進入指定數據庫

use 數據庫名;

創建表

create table 表名(字段名 字段類型)

create table table_name
(
 column_1 int null,
 column_2 int null
);

復制表

• 只復制結構

create table 新表名 like 被復制的表名;

執行上述語句后，將創建一個名為table2的新表，其結構與table1完全相同，但不會復制table1中的任何數據。

• 復制結構和數據

create table 新表名 as select * from 被復制的表名;

上述語句將創建一個名為table2的新表，其結構和數據與table1完全相同。

查詢表

• 查詢庫里面有哪些表

show tables;

• 查詢表的結構

describe 表名;

• 查詢指定表的創建語句

show create table 表名;

修改表

• 給表添加 (多個) 字段

alter table 表名 add column (字段名 字段類型,字段名 字段類型);

• 修改表字段的數據類型

alter table 表名 modify column 字段名 字段類型;

• 修改表字段的字段名

alter table 表名 change column 舊字段名 新字段名 字段類型;

• 刪除一個字段

alter table 表名 drop column 字段名;

• 修改表名

alter table 表名 rename 新表名;

② 數據操作語言 (DML)

操作數據 (增、刪、改) 關鍵字: insert、delete、update

添加數據

• 指定列名添加

insert into 表名 (字段名1,字段名2) values(數值1,數值2);

• 全部列的添加

insert into 表名 values (數值1,···,最后一個數值);

• 一次性插入多條數據

insert into 表名 values
(數值1_1,數值2_1···,最后一個數值n_1),
(數值1_2,數值2_2···,最后一個數值n_2),
(數值1_n,數值2_n···,最后一個數值n_n);

刪除數據

• 刪除表的指定數據

delete from 表名 where 字段名 = 字段值;

• truncate 刪除全表的數據

truncate table 表名;

• drop 刪除全表（包括定義和數據。）

drop table 表名;

drop、truncate、delete 三者的區別

• drop用于刪除數據庫對象，包括定義和數據。
• truncate用于刪除表中的所有數據，但保留表的定義。
• delete用于刪除表中的行，可以根據條件刪除特定的數據，并且可以回滾。

修改數據

• 修改指定字段數據

update 表名 set 字段名 = 數據值 where 字段名 = 數據值;

• 同時修改多個字段

update 表名 set 字段名1=數據值1,字段名2=數據值2 where 字段名 = 數據值;

• 在基礎數據上進行修改（某列的值減去3）

update 表名 set 字段名1 = 字段名1 -3;

③ 數據庫查詢語言 (DQL)

查詢數據

• 查詢表中所有數據

select * from 表名;

• 查詢表的指定列

select 字段名1, 字段名2 from 表名;

• 指定別名查詢

select 字段名 as 別名 from 表名;

• 常量列查詢

select 字段名 as 自定義名字,臨時常量 as 別名 from 表名;

• 合并列查詢

select 字段名 as 自定義名字，(字段 1 + 字段2) as 別名 from 表名;

條件查詢 (跟在 where 后面的關鍵字) 條件運算符

<             # 小于
>             # 大于
<=            # 小于等于 
>=            # 大于等于
<>            # 不等于
!=            # 不等于
between…and…  # 在……范圍內
in            # 包括
like          # 模糊查詢
is null       # 是否為空
and           # 且
or            # 或
not           # 非

模糊查詢

select * from 表名 where 字段名 like "關鍵詞";

select * from 表名 where 字段名 like "%hello%";

select * from 表名 where 字段名 like "%_大學%";

聚合查詢

• max():獲取查詢后結果的最大值

select max(字段名) from 表名;

• min():獲取查詢后結果的最小值

select min(字段名) from 表名;

• avg():獲取查詢后結果的平均值

select avg(字段名) from 表名;

• sum():獲取查詢后結果的總和

select sum(字段名) from 表名;

• count():獲取查詢后結果的總記錄數

select count (字段名) from 表名;

排序查詢關鍵字: order by 默認是升序 asc，降序 desc

select *
from table_class
order by id desc;

分組查詢關鍵字:group by

• 統計每個班級有多少人

select class_name,count(*) 
from table_class
group by class_name;

• 統計班級人數大于2個人的班級

select class_name,count(*)
from table_class
group by class_name
having count (*)>=2;

內連接查詢

兩張表交叉后并且過濾后的數據查詢 (交集)關鍵字: inner join

select * 
from table_a a 
inner join table_b b 
on a.aid = b.bid;

左 (外) 連接查詢

左表 (table_a) 的記錄將會全部表示出來，而右表 (table_b) 只會顯示符合搜索條件的記錄，右表記錄不足的地方均為 NULL 關鍵字: left join。

select * 
from table_a  a 
left join  table_b b
on a.a_id = b.b_id;

右 (外) 連接查詢

左表 (a_table) 只會顯示符合搜索條件的記錄，而右表 (b_table) 的記錄將會全部表示出來，左表記錄不足的地方均為 null 關鍵字: right join

select * 
from  table_a a 
right join table_b b 
on a.a_id = b.b_id;

結果合并

(select colum_1,colum_2,...,colum_n 
from table_a)
union
(select colum_1,colum_2,...,colum_n
from table_b)

• 兩個select語句具有相同的列數和相似的數據類型。如果列數不匹配，可以使用null或者空字符串填充缺失的列

• 使用 union 時，數據完全相同的記錄，將會被合并，由于合并比較耗時，一般不直接使用 union 進行合并，而是采用 union all進行合并。

(select id,name from table_a
order by id) 
union all
(select id,name from table_b
order by id);
#沒有排序效果

(select id,name from table_a ) 
union all 
(select id,name from table_b ) 
order by id;
#有排序效果

子查詢

將一個 SQL 語句的查詢結果 (單列數據) 作為另一個 SQL 語句的查詢條件。

select *
from table_a
where id_a in 
    (select id_b
    from table_a);

好了，以上內容，我們簡單回顧了一下SQL的基本函數，下面我們開始正式內容：

如果你接觸過不同編程語言就會發現，任何編程語言的學習，都離不開3個最基本的核心要素，數據類型、流程控制、函數

數據類型是用來描述數據的性質和特征的，它決定了數據在計算和處理過程中的行為和規則。常見的數據類型包括整數、浮點數、字符串、日期等。簡而言之，數據類型就是你將要操作的東西具有什么樣的特點。

流程控制是指通過條件判斷和循環等方式，控制程序按照一定的順序執行不同的操作步驟。它決定了數據的處理流程，包括判斷條件、循環次數、分支選擇等。簡而言之，流程控制解決的問題就是你要操作這個東西的基本流程是什么。

函數是一段預先定義好的代碼，用于執行特定的操作或計算。它接受輸入參數，并返回一個結果。函數可以用來對數據進行各種計算、轉換、篩選等操作，以滿足特定的需求。簡而言之，函數解決的問題就是你要怎么樣才能可復用地操作這一類東西。

SQL極簡教程系列我們重點討論數據類型與函數，下面我們先來看第一個核心要素：

3. 數據類型

① 整數類型

② 浮點類型

③ 字符串類型

④ 日期類型

如果上面內容看明白了，恭喜你已經學會了如何描述你要操作的對象的特點了，接著我們看第二個核心問題：函數。一般無外乎針對字符串的函數、針對日期的函數、針對數值運算的函數、以及操作數據轉化的函數：

5、函數

① 字符串函數

**字符串函數：**返回字符串的長度

select length('learn_mysql_and_find_a_data_analysis_job') str_len;

**字符串連接函數：**返回輸入字符串連接后的結果，支持任意個輸入字符串

select concat('Certified','Data','Analyst') as str_concat;

帶分隔符字符串連接函數： 返回輸入字符串連接后的結果，SEP表示各個字符串間的分隔符

select concat_ws('_','Certified','Data','Analyst') as str_concat_ws; 

字符串截取函數: 返回字符串從start位置到結尾的字符串

select substr('Certified_Data_Analyst',11);

select substr('Certified_Data_Analyst',-12);

字符串截取函數: 返回字符串從start位置開始，長度為len的字符串

select substr('Certified_Data_Analyst',11,4);
select substring('Certified_Data_Analyst',-7,7);

字符串轉大寫函數：upper,ucase 返回字符串A的大寫格式

select upper('certified_data_analyst');  
select ucase('certified_data_analyst');

字符串轉小寫函數：lower,lcase 返回字符串A的小寫格式

select lower('CERTIFIED_DATA_ANALYST'); 
select lcase('CERTIFIED_DATA_ANALYST');

**字符串反轉函數：**返回字符串的反轉結果

select reverse('learn_mysql')  as str_rev;

去空格函數：trim 去除字符串兩邊的空格

select trim(' Data ');  

左邊去空格函數：ltrim去除字符串左邊的空格

select ltrim(' Data '); 

右邊去空格函數：rtrim 去除字符串右邊的空格

select rtrim(' Data ');  `

空格字符串函數：space 返回長度為n的字符串

select space(10);  
select length(space(10));

重復字符串函數：repeat 返回重復n次后的str字符串

select repeat('SQL',5);

左補足函數：lpad 將str進行用pad進行左補足到len位

select lpad('MySQL',11,'go');

右補足函數：rpad 將str進行用pad進行右補足到len位

select rpad('MySQL',11,'go');

分割字符串函數: mysql里面沒有直接做字符串分割的函數，substring_index 按照pat字符串分割str，會返回分割后的字符串數組

select substring_index('Certified_Data_Analyst', '_', 1) AS part1,
       substring_index(substring_index('Certified_Data_Analyst', '_', 2), '_', -1) AS part2,
       substring_index(substring_index('Certified_Data_Analyst', '_', 3), '_', -1) AS part3;

集合查找函數: find_in_set 返回str在strlist第一次出現的位置，strlist是用逗號分割的字符串。如果沒有找該str字符，則返回0

select find_in_set('data', 'certified,data,analyst');
select find_in_set('mysql','certified,data,analyst');

正則表達式替換函數：regexp_replace將字符串A中的符合java正則表達式B的部分替換為C。注意，在有些情況下要使用轉義字符

select regexp_replace('learn_mysql_and_python', 'mysal|python', 'programming');

正則表達式提取函數：regexp_extract 返回第一個匹配的子字符串

select regexp_substr('mysql8', '[0-9]+')  extracted_number;

② 數學函數

四舍五入:round

select round(3.14159);   # round(a) 返回a的值，并對a四舍五入
select round(3.14159,3); # round(a, n) 返回保留n小數位和四舍五入后的a的值

向上取整:ceil

select ceil(3.14);
select ceiling(3.14);
select ceil(-3.14);

向下取整:floor

select floor(3.14);
select floor(-3.14);

求取隨機數:rand

select rand();    # 每行返回一個double型隨機數
select rand(100); # 每行返回一個double型隨機數，整數seed是隨機因子的種子；

其他數學運算函數

# exp(d)           #返回e的 d冪次方，返回double型；
select exp(1); # e的1次方
# 2.718281828459045

# ln(d)         #以自然數為底d的對數，返回double型；
select ln(exp(1)); # 以e為底e的對數
# 1

# log2(d)          #以2為底d的對數，返回double型；
select log2(8);
# 3

# log10(d)         #以10為底d的對數，返回double型；
select log10(100);
# 2

# log(a, b) #以a為底b的對數，返回double型；
select log(3,9);
# 2


# pow(x, n) # x 的n次冪，返回double型；
select pow(10,2);
# 100

# sqrt(DOUBLE d)          #d的平方根，返回double型；
select sqrt(16);
# 4

# abs(DOUBLE d)      #返回d的絕對值，結果為double型；
select abs(-3.14);
# 3.14

# sin()      #返回d的正弦值，結果為double型；
select sin(radians(30));
# 0.49999999999999994

# cos()      #返回d 的余弦值，結果為double型；
select tan(radians(60));
# 0.5000000000000001

# tan()      #返回d的正切值，結果為double型；
select tan(radians(45));
# 0.9999999999999999

# asin()      #返回d的反正弦值，結果為double型；
select degrees(asin(0.5));
# 30.000000000000004

# acos()      #返回d的反余弦值，結果為double型；
select degrees(acos(0.5));
# 60.00000000000001

# atan()      #返回d的反正切值，結果為double型；
select degrees(atan(1));
# 45

# PI()        #數學常數Pi，圓周率；
select PI();
# 3.141593

③ 日期函數

獲取日期 date() 返回時間字符串的日期部分

select date('2023-09-21 15:06:51');

獲取年月日

year()、month()、day() 從一個日期中取出相應的年、月、日

select 
year('2023-09-21 15:06:51'),
month('2023-09-21 15:06:51'),
day('2023-09-21 15:06:51');

獲取第幾周

weekofyear() 返回輸入日期在該年中是第幾個星期

select weekofyear('2023-09-21 15:06:51');

獲取指定間隔的日期

• date_add() 在一個日期基礎上增加天數
• date_sub() 在一個日期基礎上減去天數

#請問，2023-09-21起，7天以后的日期是？7天前的日期是？
select date_add('2023-09-21',interval 7 day) as seven_days_after,
date_sub('2023-09-21',interval 7 day ) as seven_days_before;

# 當前日期，7天以后的日期是？7天前的日期是？
select date_add(current_date(),interval 7 day) as seven_days_after, date_sub(current_date(),interval 7 day) as seven_days_before;

獲取兩個日期之差

返回的是數字

• datediff() 計算開始時間startdate到結束時間enddate相差的天數

select datediff('2023-09-21 15:06:51','2003-09-21 15:06:51');
# 7305

• current_date() 返回當前日期

select current_date();
# 2023-09-21

日期時間格式化

• date_format() 按指定格式返回時間（對日期時間格式化）

#將“2023-09-21 15:06:51”轉化如下格式
select date_format('2023-09-21 15:06:51', '%Y-%m-%d');
select date_format('2023-09-21 15:06:51', '%Y-%M-%D');
select date_format('2023-09-21 15:06:51', '%M-%d-%y');

select date_format('2023-09-21 15:06:51', '%m/%d/%y');
select date_format('2023-09-21 15:06:51', '%m/%d/%Y %H:%i:%s');
select date_format('2023-09-21 15:06:51', '%Y年%m月%d日 %H點%i分%s秒');

附：MySql查詢當天、本周、本月、本季度、本年

# 1.今天
select  to_days(now());

# 2.昨天
select  to_days(now()) - 1 ;

# 3.本周
select  yearweek(now());

# 4.上周
select yearweek(now()) -1;

# 5.往回推，7天前的時間
select date_sub(current_date(), interval 7 day);

# 6.往回推，30天前的時間
select date_sub(current_date(), interval 30 day);

# 7.本月
select  date_format(current_date(),'%Y%m');

# 8.上月
select  date_format(date_sub(current_date(),interval 1 month),'%Y%m') ;


# 9.近6個月
select date_sub(current_date(),interval 6 month);

# 10.本季度
select quarter(current_date());

# 11.上季度
select quarter(date_sub(current_date(),interval 1 quarter));

# 12.今年
select  year(now());

13.去年
select year(date_sub(now(),interval 1 year));

④ 類型轉換函數

類型轉換函數 double、date、char

# 將字符'3.14'轉換為double數值類型
select cast('3.14' as double);

# 將字符串'2023-09-21'轉換為date類型
select cast('2023-09-21' as date);

interval函數

interval(a,n1,n2,n3,...);

其中，a是要判斷的數值，n1,n2,n3,...是分段的間隔。這個函數的返回值是段的位置：如果比n1還小，則返回0，如果在n1和n2中間，則返回1，如果n2<=a<n3，則返回2。

select interval(1, 3, 7, 10);
# 0
select interval(5, 3, 7, 10);
# 1
select interval(9, 3, 7, 10);
# 2

interval關鍵詞

select now()-interval '2' hour;

⑤ 條件函數

函數if

select if(80 > 60,'及格','未及格'); 條件表達式為真返回1，為假返回2

非空查找 coalesce

coalesce(v1,v2,…) 返回參數中的第一個非空值；如果所有值都為null，那么返回 null

select coalesce('Certified',null, 'Analyst');
select coalesce(null, 'Data','Analyst');

判斷是否為null

isnull() 判斷是否為null

• 語法：isnull(a) 如果a為null就返回1，否則返回0

select isnull(null);
select isnull('');

條件判斷 case...when...

select name,
      case
           when score >= 80 then '優秀'
           when score >= 70 and score < 80 then '良好'
           when score >= 60 and score < 70 then '及格'
           else '未及格'
      end as score_label
from cda_exam;

看完了數據類型與函數，我們來了解一下SQL的執行順序。了解SQL的執行順序，不僅有助于深入理解SQL的執行過程，還能在處理異常時快速確定問題所在。

4. SQL查詢的執行順序

下面看一個包含常用SQL關鍵詞的語句模板：

select distinct column_name,
agg_func(column_name_or_expression)
from table_a a
join table_b b
on a.column_name = b.column_name
where constraint_expression
group by column_name
having constraint_expression
order by column_name asc/desc
limit count offset count;

① from 和 join

從指定的表中選擇數據

② where

從數據進行過濾。 注意：as 列別名還不能在這個階段使用，因為這時候select還沒執行，別名是一個還沒執行的表達式

③ group by

按指定的列對數據進行分組。

④ having

對group by 子句中分組后的數據進行過濾。as 列別名也不能在這個階段使用。

⑤ select

選擇要返回的列，決定輸出什么數據。

⑥ distinct

如果數據行有重復 distinct 將負責排重

⑦ order by

對結果做排序。此時可以用 as 別名了，select 中的表達式已經執行完了。

⑧ limit / offset

限制結果集的數量。 limmit a,b 等價于 limit b offset a

日拱一卒，功不唐捐。你所有的奮斗都不會白費！

《SQL數據分析極簡入門》第2節 窗口分析函數

窗口分析函數簡介

窗口分析函數主要用來做數據統計分析，屬于OLAP方式。

我們知道，OLAP聯機分析處理和OLTP聯機事務處理是兩種常見的數據庫處理方式，通常情況下，分析師更喜愛OLAP（分析），開發者更關注的是OLTP（事務）

窗口分析函數可以計算一定范圍內、一定值域內、或者一段時間內的累積和以及移動平均值等，可以方便的實現復雜的數據統計分析需求。

• 窗口函數包括: lead、lag、first_value、last_value
• 分析函數包括: rank、row_number、percent_rank、cume_dist、ntile
• 可以結合聚集函數sum()、avg()、max(),min(),count()等使用。

窗口分析函數

lag, lead, first_value, last_value

1. lag()

學過Python的同學都知道，這個函數與pandas的shift()十分相似

lag(col,n,default) 函數的作用是返回某列的值向下平移n行后的結果。

• 第一個參數為列名
• 第二個參數為當前行之前第n行（可選，默認為1）
• 第三個參數為缺失時默認值（當前行之前第n行為NULL沒有時，返回該默認值，如不指定，則為NULL）。

本節數據/SQL下載：回復“SQL3” user_pv表的建表及數據插入SQL如下（也可以選擇網盤下載后導入，二選一即可）：

例如：對每個用戶當天瀏覽次數與前一天的瀏覽次數進行比較

select uid,
       dt,
       pv,
       lag(pv, 1, 0) over (partition by uid order by dt) as lag_1_pv
from user_pv
order by uid,dt

2. lead()

lead：函數的作用是返回某列的值向上平移n行后的結果。 第一個參數為列名 第二個參數為當前行后面第n行（可選，默認為1） 第三個參數為缺失時默認值（當前行后面第n行為沒有時，返回該默認值，如不指定，則為NULL）。

例如：比較每個用戶當天瀏覽次數和后一天的瀏覽次數。

select uid,
       dt,
       pv,
       lead(pv, 1, 0) over ( partition by uid order by dt) as lead_1_pv
from user_pv
order by uid,dt;

3. first_value()

例如：比較每個用戶當天瀏覽次數與第一天瀏覽次數。

select uid,
       dt,
       pv,
       first_value(pv) over (partition by uid order by dt) first_value_pv
from user_pv
order by uid,dt;

注：上面例子窗口為第一行到當前行(缺失窗口子句，有order by ，默認為rows between unbounded preceding and current row)。 所以，first_value返回窗口的第一行，即第一天瀏覽次數。

4. last_value()

例如：比較每個用戶當天瀏覽次數與最后一天瀏覽次數進行比較。

select uid,
       dt,
       pv,
       last_value(pv) over (partition by uid order by dt rows between current row and unbounded following) last_value_pv
from user_pv
order by uid,dt;

注：上面例子的窗口為當前行到最后一行（rows between current row and unbounded following）。 last_value返回的是窗口最后一行，即最新一天的瀏覽次數。

分析函數

分析函數 row_number, rank, dense_rank、cume_dist, percent_rank, ntile

1. row_number( )

按順序排序，排序的值不會重復，總數不變；

select uid,
       dt,
       pv,
       row_number() over (partition by uid order by pv desc) as row_number_pv
from user_pv
order by uid, pv desc;

2. rank( )

大小一樣排序的值一樣，但會占用排名的位置，總數不變； 下面對用戶每天瀏覽量進行一個排名。

select uid,
       dt,
       pv,
       rank() over (partition by uid order by pv desc) as rank_pv
from user_pv
order by uid, pv desc;

3. dense_rank( )

排序值相同時重復，排名并列，排名依次增加，排序相同時總數會減少； 例如，如果兩行排名為3，則下一個排名為4，不同于RANK()函數返回5。 下面對用戶每天瀏覽量進行一個排名：

select uid,
       dt,
       pv,
       dense_rank() over (partition by uid order by pv desc) dense_rank_pv
from user_pv
order by uid, pv desc;

對比看下，row_number, rank, dense_rank的運行效果：

select uid,
       dt,
       pv,
       row_number() over (partition by uid order by pv desc) as row_number_pv,
       rank() over (partition by uid order by pv desc) as rank_pv,
       dense_rank() over (partition by uid order by pv desc) dense_rank_pv
from user_pv
order by uid, pv desc;

總結來說，ROW_NUMBER函數為每一行分配唯一的行號，而RANK函數和DENSE_RANK函數在處理具有相同排序值的行時有所不同。RANK函數會跳過下一個排名，而DENSE_RANK函數會緊隨其后。選擇使用哪個函數取決于具體的需求和對重復值的處理方式。

4. cume_dist()

累積分布cume_dist()函數，用于計算當前行在排序結果中的累積分布比例。

# 4、5的合并案例
select uid,
       dt,
       pv,
       cume_dist() over (partition by uid order by pv) cume_dist_pv
from user_pv
order by uid, pv;

5. percent_rank()

非常類似于cume_dist函數。同樣用于計算當前行在排序結果中的累積分布比例。

select uid,
       dt,
       pv,
       percent_rank() over (partition by uid order by pv) as  percent_rank_uv
from user_pv
order by uid, pv;

對比看下，cume_dist和 percent_rank函數的運行效果：

select uid,
       dt,
       pv,
       cume_dist() over (partition by uid order by pv) cume_dist_pv,
       percent_rank() over (partition by uid order by pv) as  percent_rank_uv
from user_pv
order by uid, pv;

6. ntile()

學過Python的同學都知道，組內分桶，不就是組內pd.cut()么

ntile()函數，將每個分區的行盡可能均勻地劃分為指定數量的分組。

例如，ntile(4)表示劃分為4個分組，分組取決于over子句中的order by子句。

select uid,
       dt,
       pv,
       ntile(4) over (partition by uid order by pv) as nt_pv
from user_pv;

連續登錄N天的用戶數量

現有用戶登錄表（user_active_log）一份，里面有2個字段：userID（用戶ID），createdTime（登錄時間戳），需要統計2021年12月連續登錄7天的用戶數量。

本節例題的user_active_log數據及SQL下載：在公眾號對話框回復“SQL”即可下載

分析過程：

題目要求的核心是連續登錄，那么我們思考，何為連續登錄呢？

顧名思義，連續登錄就是指登錄的日期連續，那么用數據庫的語言來表達的話，我們該描述表達日期連續呢？

我們簡化一下數據來考慮這個問題，一般我們有2個辦法：

構造一個連續數字構成的輔助列，用原始日期減去輔助列的數字，得到一個新日期，根據這個新日期來判斷是否連續；

或者構造一個連續日期構成的輔助列，用原始日期減去這個輔助列的日期，得到一個新數字，最后根據這個數字來判斷連續。

這里分別展示兩種思路如下：

方法A:

方法B：

一般我們為了方便統計某一起始時間連續登錄了多少天，多半采用方案A。

我們接著看，剛才知道了如何在SQL里面如何描述連續登錄，接下來我們逐步按照題目要求拆解即可：

• Step1：選擇12月的記錄，并根據用戶iD和登錄日期先去重（注：單個用戶一天有多行登錄數據的情況，只保留1行）
• Step2：創建輔助列a_rk (每個userID下的日期排序值)
• Step3：創建輔助列起步時間b_createdTime（用登錄日期減去排序值,得到新時間列）
• Step4：根據起步時間列統計連續登錄天數
• Step5：根據統計結果查詢連續登錄人數（題目要求連續7天）

Step1：選擇12月的記錄，并根據用戶iD和登錄日期先去重（注：單個用戶一天有多行登錄數據的情況，只保留1行）

select userID,date(from_unixtime(createdTime)) a_createdTime
from user_active_log
where substr(date(from_unixtime(createdTime)),1,7) = '2021-12' # 知識點1：時間戳轉為時間字符串格式然后取前7個字符
group by userId,date(from_unixtime(createdTime)) # 知識點2：根據userId，a_createdTime 去重

運行結果如下：

Step2：創建輔助列a_rk (每個userID下的日期排序值)

select userID,a_createdTime,row_number() over(partition by userId order by a_createdTime) a_rk # 知識點3：用row_number() 對每個userID下的a_createdTime進行排名
from
(
select userID,date(from_unixtime(createdTime)) a_createdTime
from user_active_log
where substr(from_unixtime(createdTime),1,7) = '2021-12'
group by userId,a_createdTime) t0

運行結果如下：

Step3：創建輔助列起步時間b_createdTime（用登錄日期減去排序值,得到新時間列）

select *,date_sub(a_createdTime,interval a_rk day ) b_createdTime # 知識點4：date_sub 日期減去數字；datediff 日期減日期
from
(
select userID,a_createdTime,row_number() over(partition by userId order by a_createdTime) a_rk
from
(
select userID,date(from_unixtime(createdTime)) a_createdTime
from user_active_log
where substr(from_unixtime(createdTime),1,7) = '2021-12'
group by userId,a_createdTime) t0 )t1

運行結果如下：

Step4：根據起步時間列統計連續登錄天數

select userId,b_createdTime,count(1) cts
from
(select *,date_sub(a_createdTime,interval a_rk day ) b_createdTime
from
(
select userID,a_createdTime,row_number() over(partition by userId order by a_createdTime) a_rk
from
(
select userID,date(from_unixtime(createdTime)) a_createdTime
from user_active_log
where substr(from_unixtime(createdTime),1,7) = '2021-12'
group by userId,a_createdTime) t0 ) t1 ) t2 group by userId,b_createdTime having  count(1)>6 # 知識點5：having 用在groupby后做條件篩選

運行結果如下：

Step5：根據統計結果查詢連續登錄人數（題目要求連續7天）

select count(distinct userId) num_users
from
(select userId,b_createdTime,count(1) cts
from
    (select *,date_sub(a_createdTime,interval a_rk day ) b_createdTime
    from
        (
        select userID,a_createdTime,row_number() over(partition by userId order by a_createdTime) a_rk
        from
            (
            select userID,date(from_unixtime(createdTime)) a_createdTime
            from user_active_log
            where substr(from_unixtime(createdTime),1,7) = '2021-12'
            group by userId,a_createdTime
            ) t0
        ) t1
    ) t2 group by userId,b_createdTime having  count(1)>6
) t3;

運行結果如下：

近N日留存的用戶數及留存率

現有用戶登錄表（user_active_log）一份，里面有2個字段：userID（用戶ID），createdTime（登錄時間戳），需要統計近1、2、3、5、7、30日留存用戶數量及留存率。

本節例題的user_active_log數據及SQL下載：在公眾號對話框回復“SQL”即可下載

分析過程：

題目要求的核心是近N日留存，那么我們思考，何為近N日留存呢？

顧名思義，就是指距離某個日期的間隔為N，那么用數據庫的語言來表達的話，我們該描述表達近N日留存呢？

我們簡化一下數據來考慮這個問題：

構造一個起始日期構成的輔助列，用原始日期減去輔助列的日期，得到一個新數字N，根據這個新數字，結合起始日期來判斷某個日期的近N日留存；

知道了如何在SQL里面如何描述連續登錄，接下來我們逐步按照題目要求拆解即可：

• Step1：根據用戶id和登錄日期先去重
• Step2：創建新列first_time，獲取每個userID下的最早登錄日期
• Step3：創建輔助列delta_time，用登錄日期列減去最早登錄日期first_time,得到留存天數
• Step4：按first_time列統計不同留存天數對應的次數和 即 某日的近N日留存數
• Step5：用某日的近N日留存數除以首日登錄人數即留存率

Step1：根據用戶id和登錄日期先去重

select
       userID,
       date(from_unixtime(createdTime)) a_createdTime
from user_active_log
group by userId,a_createdTime;

運行結果截圖如下：

Step2：創建新列first_time，獲取每個userID下的最早登錄日期

select
       userID,
       a_createdTime,
       first_value(a_createdTime) over(partition by userId order by a_createdTime ) first_time
from
     (
         select userID,date(from_unixtime(createdTime)) a_createdTime
         from user_active_log
         group by userId,a_createdTime
    )t0;

運行結果截圖如下：

Step3：創建輔助列delta_time，用登錄日期列減去最早登錄日期first_time,得到留存天數

select
       userID,
       a_createdTime,
       first_value(a_createdTime) over(partition by userId order by a_createdTime ) first_time,
       datediff(a_createdTime, first_value(a_createdTime) over(partition by userId order by a_createdTime )) delta_time
from
     (
         select userID,date(from_unixtime(createdTime)) a_createdTime
         from user_active_log
         group by userId,a_createdTime
    )t0;

運行結果截圖如下：

Step4：按首次登錄日期統計不同留存天數對應的次數和 即 某日的近N日留存數

select
    t1.first_time,
    sum( case when t1.delta_time = 1 then 1 else 0 end)  day_1,
    sum( case when t1.delta_time = 2 then 1 else 0 end)  day_2,
    sum( case when t1.delta_time = 3 then 1 else 0 end)  day_3,
    sum( case when t1.delta_time = 5 then 1 else 0 end)  day_5,
    sum( case when t1.delta_time = 7 then 1 else 0 end)  day_7,
    sum( case when t1.delta_time = 30 then 1 else 0 end ) day_30
from
     (
        select
               userID,
               a_createdTime,
               first_value(a_createdTime) over(partition by userId order by a_createdTime ) first_time,
               datediff(a_createdTime, first_value(a_createdTime) over(partition by userId order by a_createdTime )) delta_time
        from
             (
                 select userID,date(from_unixtime(createdTime)) a_createdTime
                 from user_active_log
                 group by userId,a_createdTime
            )t0
    ) t1
group by t1.first_time
order by t1.first_time;

Step5：用某日的近N日留存數除以首日登錄人數即留存率

select
    t1.first_time,
    sum( case when t1.delta_time = 1 then 1 else 0 end ) / count(distinct t1.userID)  day_1,
    sum( case when t1.delta_time = 2 then 1 else 0 end ) / count(distinct t1.userID) day_2,
    sum( case when t1.delta_time = 3 then 1 else 0 end ) / count(distinct t1.userID) day_3,
    sum( case when t1.delta_time = 5 then 1 else 0 end ) / count(distinct t1.userID) day_5,
    sum( case when t1.delta_time = 7 then 1 else 0 end ) / count(distinct t1.userID) day_7,
    sum( case when t1.delta_time = 30 then 1 else 0 end )/ count(distinct t1.userID) day_30
from
     (
        select
               userID,
               a_createdTime,
               first_value(a_createdTime) over(partition by userId order by a_createdTime ) first_time,
               datediff(a_createdTime , first_value(a_createdTime) over(partition by userId order by a_createdTime )) delta_time
        from
             (
                 select userID,date(from_unixtime(createdTime)) a_createdTime
                 from user_active_log
                 group by userId,a_createdTime
            )t0
    ) t1
group by t1.first_time
order by t1.first_time;

“每一個不曾起舞的日子，都是對生命的辜負?！备魑患佑?！我們下個系列見