分組比較是看起來比較簡單，但是寫起來比較麻煩的問題，一般就是先進行兩個不同分組計數、求和、求均值，然后兩個均值作比較，這樣就涉及表連接和判斷，寫的代碼量就比其他問題多很多。它與連續問題、排名問題和累加問題不同的是，后面三個問題是數據行之間縱向產生關系，而這里是橫向產生關系。

這類問題也是有一定套路的，下面用實際案例數據還原真實取數場景，幫助你在實戰中理解如何實現分組比較取數的過程，總結思路。建議在電腦大屏閱讀效果會更好。

需求：老板要求寫一個查詢語句，求出在每月每個經銷商的平均銷售金額與公司的平均銷售金額的比較結果 （高 / 低 / 相同），這樣以便于后續制定激勵經銷商的政策措施。你該如何寫呢？

背景：數據來源于微軟示例數據庫，一家銷售自行車制造公司的銷售數據，分為網絡銷售FactInternetSales和經銷商銷售FactResellerSales兩張表，其中網絡銷售訂單數據60398行，經銷商銷售數據有60855行。此處我們簡化問題，重點在問題的解決上，只用到FactResellerSaless表，表中有包含價格orderdate、resellerkey和salesamount在內的共計27個字段，每個日期存在多個訂單情況。

分析：先分析需求看我們要取的是哪些字段，每月、每個經銷商、經銷商平均銷售金額，公司、公司平均銷售金額，比較結果，這里面公司字段值相同不需要取，經銷商平均銷售金額、公司平均銷售金額，比較結果三個字段是原表沒有的，因此需要新增計算字段。求平均需要用到avg函數，而判斷條件，自然需要if語句或者case when語句（兩種在功能上是相同的）。這一步是很多初學者可能都還沒有形成的思維，一看到需求還無法迅速定位到要取什么數、用到什么函數。

角度一：簡單查詢是一張表取數，這里雖然原數據在一張表中，但是顯然比較兩個分組會產生兩張臨時表，所以就需要復合查詢來解決。

角度二：聚合函數+復合查詢

解法一 復合查詢

下面是復合查詢的代碼：

with t1 as (select date_format(orderdate,"%Y-%m") as paydate,resellerkey,avg(salesamount) as avd from?FactResellerSalesgroup by date_format(orderdate,"%Y-%m"),resellerkey), t2 as (select date_format(orderdate,"%Y-%m") as paydate,avg(salesamount) as ava from FactResellerSalesgroup by date_format(orderdate,"%Y-%m")) select paydate,resellerkey,t2.ava as allsa,t1.avd as allsd,if(t1.avd<< span="">t2.ava,"lower",if(t1.avd=t2.ava,"same","higher")) as comparison from t1 left join t2 using(paydate) order by paydate,resellerkey;

解析上面的代碼，t1表是產生每個月每個經銷商評價銷售額，t2表是每個月總平均銷售額，在主SQL語句中用if語句來判斷產生對應的結果。整體思路沒什么難的，只是一些小問題需要解決，比如日期，實際工作中日期一般都是自然日期，不會增加一個字段專門記錄每年每月，所以需要日期格式化；另外，表連接的時候on關鍵字還可以換成using關鍵字，on t1.paydate = t2.paydate 就可以直接用using(paydate)代替節省代碼，但是這要在兩張表的相同字段相同名稱才可以。

解法二 子查詢

上面用了公共臨時表作為過渡表，這里我們也可以把它轉成子查詢。

select b.paydate,b.resellerkey, a.ava,b.avd,case when b.avd > a.ava then 'higher'when b.avd << span=""> a.ava then 'lower'else 'same' end as comparison from?(select?DATE_FORMAT(orderdate,'%Y-%m')?as?paydate,?avg(salesamount)?as?avafrom FactResellerSales group by paydate) as a right JOIN (select resellerkey,DATE_FORMAT(orderdate,'%Y-%m') as paydate, avg(salesamount) as avdfrom FactResellerSales group?by?paydate,resellerkey)?as?b? on (a.paydate = b.paydate) order by paydate,resellerkey;

兩者的處理思路一樣、執行效率也是差不多的，子查詢的衍生表放在with as 結構中就是公共臨時表，而公共臨時表（CTE）的好處在于可讀性很強，邏輯關系分得清楚，而且如果多次使用衍生表，則可以引用CTE就可以了，代碼復用性強。這里只是把if語句換成了case when。

解法三 窗口函數

之前我們在文章“茴”字有幾種寫法？SQL排名問題之全局排名的四種解法說到，窗口函數實際是聚合函數加group by的功能封裝，所以我們也可以使用聚合函數加over來實現。

select?distinct?date_format(orderdate,"%Y-%m")?as?paydate,resellerkey,casewhen?avg(salesamount)?over(partition?by?date_format(orderdate,"%Y-%m"))?>avg(salesamount)?over(partition?by?date_format(orderdate,"%Y-%m"),resellerkey)?then?'higher'when avg(salesamount) over(partition by date_format(orderdate,"%Y-%m")) >avg(salesamount) over(partition by date_format(orderdate,"%Y-%m"),resellerkey) then 'lower'else 'same' end as comparison from FactResellerSales order by paydate,resellerkey;

上面這段代碼結構與之前兩種寫法相比簡單，沒有使用表連接。一般寫SQL語句能用單表查詢盡量用單表，因為表連接是有代價的。在select后面字段里直接將分組聚合和條件判斷放在一起，分組的功能是通過窗口over里的partition by來實現。這也啟發了一種解題思路，如果有具備相似功能的工具，即使是用在不同問題上也可以創造條件來借用。舉一反三的能力就是在這樣情況下鍛煉得到。case when then語句的使用很簡單，when后面接第一個條件，滿足就把結果給then后面，如果不滿足就給else后面，最后用end關鍵字結尾，如果有多個條件就重復使用when…then句式，它比if語句更方便用在多個條件，因為if嵌套有很多括號，往往容易出錯。

思路三：自定義變量。一般情況下窗口函數都可以使用自定義變量方法，但是這里使用自定義變量而不用avg函數也不用窗口函數，代碼量就會非常大。

解法四 自定義變量

以下就是代碼：

with t1 as ( select orderdate,resellerkey,max(sss)/max(sss1) as as1 from(select?orderdate,resellerkey,salesamount,@cur:=salesamount,if(@dt = orderdate,if(@re=resellerkey,@sa:=@sa+@cur,@sa:=@cur),@sa:=@cur) sss,if(@dt = orderdate,if(@re=resellerkey,if(@r=salesamount,@sa1:=@sa1,@sa1:=@sa1+1),@sa1:=1),@sa1:=1) sss1,@dt:=?orderdate,@re:=resellerkeyfrom?FactResellerSales,(select?@dt?:=0,@re?:=0,@sa:=null,@cur:=null,@sa1:=0)?initorder?by?orderdate,resellerkey,salesamount)c group?by?orderdate,resellerkey),t2?as?(select?orderdate,resellerkey,max(sss2)/max(sss3)?as?as2? from(select?orderdate,resellerkey,salesamount,@cur2:=salesamount,if(@dt2 = orderdate,@sa2:=@sa2+@cur2,@sa2:=@cur2) sss2,if(@dt2 = orderdate,@sa3:=@sa3+1,@sa3:=1) sss3,@dt2:= orderdate,@re2:=resellerkeyfrom FactResellerSales,(select @dt2 :=0,@re2 :=0,@sa2:=null,@cur2:=null,@sa3:=0) initorder?by?orderdate,resellerkey,salesamount)?f group by orderdate)select t1.orderdate,t1.resellerkey,t1.as1,t2.as2,if(t1.as1<< span="">t2.as2,"lower",if(t1.as1=t2.as2,"same","higher")) as comparison from t1 left join t2 using(orderdate);

可以看到這個代碼比其他方法多了一倍。但是這也是練習理解自定義變量使用的很好的實踐，假如你能寫通這段代碼，以后當你遇到無法使用窗口函數的時候，就可以得心應手的使用這種方法啦。這也是種裝逼方式，你可以跟人家說你一條SQL語句寫了一百多行，自己都看不懂哈哈。

代碼整體邏輯很簡單，t1表生成每個月每個經銷商平均銷售額，它表生成每個月公司總平均銷售額，最后兩張表連接，用if語句做比較得到結果。這里我沒有將日期進行格式化為每月，因為表中原始數據實際上就是每個月只有月末數據，相當于格式化分組了，另外這種解法在實際工作中不建議使用，只有在沒有辦法后才使用，因為它變量太多，執行效率太低，代碼可讀性也很差，這里為了減少冗余就沒處理。這里核心的部分就是求平均值的地方，你可以看到不用avg函數也是可以求均值。這里用到了累加的思路，先把每個組的銷售額累加，再取最后一個值，同時也把每累加一次進行計數，取最后一個數，這樣相當于算出了每組的總和和數據行數，兩者相除就得到平均值。求解思路搞清楚之后，來看一下具體怎么寫代碼。這里是通過if語句條件判斷來實現分組的效果，t1表中字段別名為sss的if語句，意思是如果變量@dt= orderdate，再判斷是否@re=resellerkey，如果是同一個日期同一個經銷商就用@sa（代表累加值）來迭代相加得到累計值每一行數據對應的累計值@sa:=@sa+@cur表示新的累加值都是前一個累加值加上當前值，而每進行一次迭代都會將當前值salesamount賦值給變量@cur,這樣一行一行迭代，跟分組累加求解一樣。但是光有總數沒有每個組的個數，就沒有辦法求平均值。所以這里用同樣的思維累加計數，得到每組個數。它的實現步驟比累加求和多了一步，不僅要判斷是否同一日期同一經銷商還要判斷是否同一金額，達到對每個金額排名作為計數的效果（這里有個問題，可能同一日期同一經銷同一金額有重復，實際上還要判斷是否同一訂單，為了簡化我沒有做，在這里你就能感受到做題題題會做，但是實際工作處處是細節問題，處處是坑啊），這兩個if語句實現的效果如下：

再做一個子查詢找到每組最大值，實現的效果如下：

最終t1表實現的效果如下：

可以看到兩者的效果是一樣的。但是寫法和執行效率遠不如avg函數簡潔高效。t2表也是同樣的方法得到。

總結一下自定義變量的套路就是：

第一步，先想好分組條件有幾個，比如這里對同一日期同一經銷同一金額計數，就需要三個變量，還要再加一個臨時中間變量；每個變量的命名都以@開頭，名字最好是對應原字段縮寫，可以方便知道代表含義。

第二步，將這些變量寫進select 語句中組成一張臨時表，就是上面的(select @dt :=0,@re :=0,@sa:=null,@cur:=null,@sa1:=0) init，初始值都默認為0或者null，表別名可以自定義，但是建議統一命名為init，不需要再費腦筋想名字；將這張臨時表跟在原數據表后面。

第三步，在主select語句中先將數據原字段取出，還要將所有臨時變量都要寫上，不能不寫，不寫或少寫就會出錯，順序也不能錯誤，寫錯順序也會得不到結果。書寫順序依次是：臨時中間變量@cur，以@sa為最終結果的計算或判斷語句，判斷條件一@dt，判斷條件二@re，這四個變量都是以“:="號形式分別將原數據表字段賦值給它們。下面是動畫效果：

這張動圖也能很好的說明MySQL執行的順序，先from從表中一行一行從左到右取出數據，若要計算或者判斷都是先從左往右判斷執行。

以上就是教大家怎么理解用戶自定義變量和具體怎么寫，大家是否看懂了呢？如果沒看懂請關注“二八Data”并私信我，我再詳細為你解釋。

這是SQL系列進階文章第四篇，這篇用到了前面所講的排名問題和累加問題的方法，如果還不理解請看相關文章。對于數據分析師來說，不求十八般武藝樣樣精通，但是至少要懂得最基礎最常用的幾種，這樣才能讓你有一戰之力。寫SQL語句不需要你必須完全懂”增刪改“的知識，而只需要精通如何查詢就好了，否則你就是數據庫管理員了。

最后歡迎大家關注我，我是拾陸，搜索公眾號“二八Data”，更多技術干貨持續奉獻。

總結

以上是生活随笔為你收集整理的销售额超过公司均值的优秀经销商？SQL比例问题之分组比较的四种解法的全部內容，希望文章能夠幫你解決所遇到的問題。

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