SQL、Pandas和Spark：常用數(shù)據(jù)查詢操作對比
導(dǎo)讀
當(dāng)今信息時代，數(shù)據(jù)堪稱是最寶貴的資源。沿承系列文章，本文對SQL、Pandas和Spark這3個常用的數(shù)據(jù)處理工具進(jìn)行對比，主要圍繞數(shù)據(jù)查詢的主要操作展開。
本文首先介紹SQL查詢操作的一般流程，對標(biāo)SQL查詢語句的各個關(guān)鍵字，重點(diǎn)針對Pandas和Spark進(jìn)行介紹，主要包括10個常用算子操作。
在最新TIOBE排行榜中，SQL位居第10位
select：指定查詢字段
distinct：對查詢結(jié)果字段進(jìn)行去重
from：明確查詢的數(shù)據(jù)庫和表
join on：指定查詢數(shù)據(jù)源自多表連接及條件
where：設(shè)置查詢結(jié)果過濾條件
group by：設(shè)置分組聚合統(tǒng)計的字段
having：依據(jù)聚合統(tǒng)計后的字段進(jìn)一步過濾
order by：設(shè)置返回結(jié)果排序依據(jù)
limit：限定返回結(jié)果條數(shù)
from：首先找到待查詢的表
join on：如果目標(biāo)數(shù)據(jù)表不止一個，則對多表建立連接關(guān)系
where：根據(jù)查詢條件過濾數(shù)據(jù)記錄
group by：對過濾結(jié)果進(jìn)行分組聚合
having：對分組聚合結(jié)果進(jìn)行二次過濾
select：對二次過濾結(jié)果抽取目標(biāo)字段
distinct：根據(jù)條件進(jìn)行去重處理
order by：對去重結(jié)果進(jìn)行排序
limit：僅返回排序后的指定條數(shù)記錄
曾經(jīng)，個人一度好奇為何不將SQL語句的書寫順序調(diào)整為與執(zhí)行順序一致，那樣更易于理解其中的一些技術(shù)原理，但查詢資料未果后，就放棄了……
當(dāng)然，本文的目的不是介紹SQL查詢的執(zhí)行原理或者優(yōu)化技巧，而僅僅是對標(biāo)SQL查詢的幾個關(guān)鍵字，重點(diǎn)講解在Pandas和Spark中的實現(xiàn)。
以下按照SQL執(zhí)行順序講解SQL各關(guān)鍵字在Pandas和Spark中的實現(xiàn)，其中Pandas是Python中的數(shù)據(jù)分析工具包，而Spark作為集Java、Scala、Python和R四種語言的通用分布式計算框架，本文默認(rèn)以Scala語言進(jìn)行講述。
1）from。由于Python和Scala均為面向?qū)ο笤O(shè)計語言，所以Pandas和Spark中無需from，執(zhí)行df.xxx操作的過程本身就蘊(yùn)含著from的含義。
2）join on。join on在SQL多表查詢中是很重要的一類操作，常用的連接方式有inner join、left join、right join、outer join以及cross join五種，在Pandas和Spark中也都有相應(yīng)關(guān)鍵字。
Pandas：Pandas實現(xiàn)join操作有兩個主要的API：merge和join。其中merge是Pandas的頂層接口（即可直接調(diào)用pd.merge方法），也是DataFrame的API，支持豐富的參數(shù)設(shè)置，主要介紹如下：
def merge(left, # 左表right, # 右表how: str = "inner", # 默認(rèn)連接方式：inneron=None, # SQL中on連接一段，要求左表和右表中 公共字段left_on=None, # 設(shè)置左表連接字段right_on=None, # 設(shè)置右表連接字段left_index: bool = False, # 利用左表索引作為連接字段right_index: bool = False, # 利用右表索引作為連接字段sort: bool = False, # join結(jié)果排序suffixes=("_x", "_y"), # 非連接字段有重名時，可s何止后綴copy: bool = True,indicator: bool = False,validate=None,) -> "DataFrame":
上述參數(shù)中，可以設(shè)置on連接條件的方式主要有3種：即若連接字段為兩表共有字段，則可直接用on設(shè)置；否則可分別通過left_on和right_on設(shè)置；當(dāng)一個表的連接字段是索引時，可設(shè)置left_index為True。
與merge操作類似，join可看做是merge的一個簡化版本，默認(rèn)以索引作為連接字段，且僅可通過DataFrame來調(diào)用，不是Pandas的頂級接口（即不存在pd.join方法）。
另外，concat也可通過設(shè)置axis=1參數(shù)實現(xiàn)橫向兩表的橫向拼接，但更常用于縱向的union操作。
Spark：相較于Pandas中有多種實現(xiàn)兩個DataFrame連接的方式，Spark中接口則要單一許多，僅有join一個關(guān)鍵字，但也實現(xiàn)了多種重載方法，主要有如下3種用法：
// 1、兩個DataFrame有公共字段，且連接條件只有1個，直接傳入連接列名df1.join(df2, "col")// 2、有多個字段，可通過Seq傳入多個字段df1.join(df2, Seq("col1", "col2")// 3、兩個DataFrame中連接字段不同名，此時需傳入判斷連接條件df1.join(df2, df1("col1")===df2("col2"))// 注意，上述連接條件中，等于用===，不等于用=!=
3）where。數(shù)據(jù)過濾在所有數(shù)據(jù)處理流程中都是重要的一環(huán)，在SQL中用關(guān)鍵字where實現(xiàn)，在Pandas和Spark中也有相應(yīng)的接口。
Pandas。Pandas中實現(xiàn)數(shù)據(jù)過濾的方法有多種，個人常用的主要是如下3類：
通過loc定位操作符+邏輯判斷條件實現(xiàn)篩選過濾。loc是用于數(shù)據(jù)讀取的方法，由于其也支持傳入邏輯判斷條件，所以自然也可用于實現(xiàn)數(shù)據(jù)過濾，這也是日常使用中最為頻繁一種；
通過query接口實現(xiàn)，提起query，首先可能想到的便是SQL中Q，實際上pandas中的query實現(xiàn)的正是對標(biāo)SQL中的where語法，在實現(xiàn)鏈?zhǔn)胶Y選查詢中非常好用，具體可參考Pandas用了一年，這3個函數(shù)是我的最愛……
where語句，Pandas以API豐富而著稱，所以自然是不會放過where關(guān)鍵字的，不過遺憾的是Pandas中的where和Numpy中的where一樣，都是用于對所有列的所有元素執(zhí)行相同的邏輯判斷，可定制性較差。
Spark。Spark中實現(xiàn)數(shù)據(jù)過濾的接口更為單一，有where和filter兩個關(guān)鍵字，且二者的底層實現(xiàn)是一致的，所以實際上就只有一種用法。但在具體使用中，where也支持兩種語法形式，一種是以字符串形式傳入一個類SQL的條件表達(dá)式，類似于Pandas中query；另一種是顯示的以各列對象執(zhí)行邏輯判斷，得到一組布爾結(jié)果，類似于Pandas中l(wèi)oc操作。
4）group by。group by關(guān)鍵字用于分組聚合，實際上包括了分組和聚合兩個階段，由于這一操作屬于比較規(guī)范化的操作，所以Pandas和Spark中也都提供了同名關(guān)鍵字，不同的是group by之后所接的操作算子不盡相同。
Pandas：Pandas中g(shù)roupby操作，后面可接多個關(guān)鍵字，常用的其實包括如下4類：
直接接聚合函數(shù)，如sum、mean等；
接agg函數(shù)，并傳入多個聚合函數(shù)；
接transform，并傳入聚合函數(shù)，但不聚合結(jié)果，即聚合前有N條記錄，聚合后仍然有N條記錄，類似SQL中窗口函數(shù)功能，具體參考Pandas中g(shù)roupby的這些用法你都知道嗎？
接apply，實現(xiàn)更為定制化的函數(shù)功能，參考Pandas中的這3個函數(shù)，沒想到竟成了我數(shù)據(jù)處理的主力
Spark：Spark中的groupBy操作，常用的包括如下3類：
直接接聚合函數(shù)，如sum、avg等；
接agg函數(shù)，并傳入多個聚合算子，與Pandas中類似；
接pivot函數(shù)，實現(xiàn)特定的數(shù)據(jù)透視表功能。
5）having。在SQL中，having用于實現(xiàn)對聚合統(tǒng)計后的結(jié)果進(jìn)行過濾篩選，與where的核心區(qū)別在于過濾所用的條件是聚合前字段還是聚合后字段。而這在Pandas和Spark中并不存在這一區(qū)別，所以與where實現(xiàn)一致。
6）select。選擇特定查詢結(jié)果，詳見Pandas vs Spark：獲取指定列的N種方式。
7）distinct。distinct在SQL中用于對查詢結(jié)果去重，在Pandas和Spark中，實現(xiàn)這一操作的函數(shù)均為drop_duplicates/dropDuplicates。
8）order by。order by用于根據(jù)指定字段排序，在Pandas和Spark中的實現(xiàn)分別如下：
Pandas：sort_index和sort_values，其中前者根據(jù)索引排序，后者根據(jù)傳入的列名字段排序，可通過傳入ascending參數(shù)控制是升序還是降序。
Spark：orderBy和sort，二者也是相同的底層實現(xiàn)，功能完全一致。也是通過傳入的字段進(jìn)行排序，可分別配合asc和desc兩個函數(shù)實現(xiàn)升序和降序。
// 1、指定列+descdf.orderBy(df("col").desc)// 2、desc函數(shù)加指定列df.orderBy(desc("col"))
9）limit。limit關(guān)鍵字用于限制返回結(jié)果條數(shù)，這是一個功能相對單一的操作，二者的實現(xiàn)分別如下：
Pandas：可分別通過head關(guān)鍵字和iloc訪問符來提取指定條數(shù)的結(jié)果；
Spark：直接內(nèi)置了limit算子，用法更接近SQL中的limit關(guān)鍵字。
10）Union。SQL中還有另一個常用查詢關(guān)鍵字Union，在Pandas和Spark中也有相應(yīng)實現(xiàn)：
Pandas：concat和append，其中concat是Pandas 中頂層方法，可用于兩個DataFrame縱向拼接，要求列名對齊，而append則相當(dāng)于一個精簡的concat實現(xiàn)，與Python中列表的append方法類似，用于在一個DataFrame尾部追加另一個DataFrame；
Spark：Spark中直接模仿SQL語法，分別提供了union和unionAll兩個算子實現(xiàn)兩個DataFrame的縱向拼接，且含義與SQL中完全類似。
對標(biāo)SQL標(biāo)準(zhǔn)查詢語句中的常用關(guān)鍵字，重點(diǎn)對Pandas和Spark中相應(yīng)操作進(jìn)行了介紹，總體來看，兩個計算框架均可實現(xiàn)SQL中的所有操作，但Pandas實現(xiàn)的接口更為豐富，傳參更為靈活；而Spark則接口更為統(tǒng)一，但一般也支持多種形式的方法重載。另外，Spark中的算子命名與SQL更為貼近，語法習(xí)慣也與其極為相似，這對于具有扎實SQL基礎(chǔ)的人快速學(xué)習(xí)Spark來說會更加容易。
相關(guān)閱讀：