來源：阿里雲開發者

阿里妹導讀

本文主要分享了作者在螞蟻集團高管資料鏈路改造升級過程中，針對去重Cube的最佳化實踐。

引言

SQL作為目前最通用的資料庫查詢語言，其功能和特性複雜度遠不止大家常用的“SELECT * FROM tbl”這樣簡單，一段好的SQL和差的SQL，其效能可能有幾十乃至上千倍的差距。而寫出一個好的能兼顧效能和易用性的SQL，考驗的不僅僅是瞭解到多少新特性新寫法，而是要深入理解資料的處理過程，然後設計好資料的處理過程。

因此想推出本系列文章，並取名為《奇思妙想的SQL》，希望能以實際案例出發，和大家分享一些SQL處理資料的新方案新思路，並在過程中融入對問題本質的理解，希望大家能喜歡～。

本篇為系列第1篇，分享下在螞蟻集團高管資料鏈路改造升級過程中，針對去重Cube的最佳化實踐。

一、場景描述

在做資料彙總計算和統計分析時，最頭疼的就是去重類指標計算（比如使用者數、商家數等），尤其還要帶多種維度的下鑽分析，由於其不可累加的特性，幾乎每換一種統計維度組合，都得重新計算。資料量小時可以暴力的用明細資料直接即時統計，但當資料量大時就不得不考慮提前進行計算了。

典型場景如下：省、市、區等維度下的支付寶客戶端的日支付使用者數（其中省、市、區為使用者支付時所在的位置，表格中指標資料均為虛構的）。

存在一個情況，某使用者早上在杭州市使用支付寶支付了一次，下午跑到紹興市時又使用支付寶線下支付了一次。那麼在統計省+市維度的日支付使用者數，需要為杭州市、紹興市各計1；但在省維度下，需要按使用者去重，只能為浙江省計1。針對這種情況，通常就需要以Cube的方式完成資料預計算，同時每個維度組合都需要進行去重操作，因為不可累加。本文將此種場景簡稱為去重Cube。

二、常見的實現方法

直接計算，每個維度組合單獨計算。比如單獨生成省、省+市、省+市+區等維度組合的多張表。每個表只計算固定的維度。然後是資料膨脹再計算，如Union All或者Lateral View Explode或者MaxCompute的 Cube計算功能，透過資料膨脹實現一行資料滿足多種維度組合的資料計算方法，如下圖所示。

這三種寫法其實都類似，重點都在於對資料進行膨脹，再進行去重統計。其執行流程如下圖所示，核心思路都是先把資料"膨脹"拆為多行，再按照“普通”的Distinct去重統計，因此效能上本身無太大差異，主要在於程式碼可維護性上。

三、效能分析

上述方法核心都是先把資料"膨脹"拆為多行，再按照“普通”的Distinct去重統計，本身效能無太大差異，主要在於程式碼可維護性上。這幾種方案計算消耗會隨著所需維度組合線性增加，同時還要疊加Distinct本身的計算效能差的影響。

在實際實驗中，我們發現，去重Cube的計算過程中，80%+的計算成本消耗在資料膨脹和資料傳輸上。比如高管核心指標場景，需要計算各種組合維度下的支付使用者數以支援分析。實際實驗中，選取100億資料x25種維度組合進行測試，實際執行任務如下圖所示，其中R3_2為核心的資料膨脹過程，資料膨脹近10倍，中間結果資料大小由100GB膨脹至1TB、資料量由100億膨脹至近1300億，大部分計算資源和計算耗時都花在資料膨脹和傳輸上了。若實際的組合維度進一步增加的話，資料膨脹大小也將進一步增加。

四、一種新的思路

首先對問題進行拆解下，去重Cube的計算過程核心分為兩個部分，資料膨脹+資料去重。資料膨脹解決的是一行資料同時滿足多種維度組合的計算，資料去重則是完成最終的去重統計，核心思路還是在於原始資料去匹配結果資料的需要。其中資料去重本身的計算量就較大，而資料膨脹會導致這一情況加劇，因為計算過程中需要拆解和在shuffle過程中傳輸大量的資料。資料計算過程中是先膨脹再聚合，加上本身資料內容的中英文字串內容較大，所以才導致了大量的資料計算和傳輸成本。

而我們的核心想法是能否避免資料膨脹，同時進一步減少資料傳輸大小。因此我們聯想到，是否可以採用類似於使用者打標籤的資料打標方案，先進行資料去重生成UID粒度的中間資料，同時讓需要的結果維度組合反向附加到UID粒度的資料上，在此過程中並對結果維度進行編號，用更小的資料結構去儲存，避免資料計算過程中的大量資料傳輸。整個資料計算過程中，資料量理論上是逐漸收斂的，不會因為統計維度組合的增加而增加。

4.1.核心思路

核心計算思路如上圖，普通的資料膨脹計算cube的方法，中間需要對資料進行膨脹，再聚合，其中結果統計需要的組合維度數就是資料膨脹的倍數，比如上述的“省、省+市”共計兩種維度組合，資料預計要膨脹2倍。

而新的資料聚合方法，透過一定的策略方法將維度組合拆解為維度小表並進行編號，然後將原本的訂單明細資料聚合至使用者粒度的中間過程資料，其中各類組合維度轉換為數字標記錄至使用者維度的資料記錄上，整個計算過程資料量是呈收斂聚合的，不會膨脹。

4.2.邏輯實現

• 明細資料準備：以使用者線下支付資料為例，明細記錄包含訂單編號、使用者ID、支付日期、所在省、所在市、支付金額。最終指標統計需求為統計包含省、市組合維度+支付使用者數的多維Cube。

訂單編號	使用者ID	支付日期	所在省	所在市	支付金額
2023111101	U001	2023-11-11	浙江省	杭州市	1.11
2023111102	U001	2023-11-11	浙江省	紹興市	2.22
2023111103	U002	2023-11-11	浙江省	杭州市	3.33
2023111104	U003	2023-11-11	江蘇省	南京市	4.44
2023111105	U003	2023-11-11	浙江省	溫州市	5.55
2023111106	U004	2023-11-11	江蘇省	南京市	6.66

整體方案流程如下圖。

• STEP1：對明細資料進行所需的維度提取（即Group By對應欄位），得到維度集合。

• STEP2：對得到的維度集合生成Cube，並對Cube的行進行編碼 （假設最終需要所在省、所在省+所在市 2種組合維度），可以用ODPS的Cube功能實現，再根據生成的Cube維度組合進行排序生成唯一編碼。
  

  原始維度：所在省	原始維度：所在省	Cube 維度：所在省	Cube 維度：所在市	Cube行ID（可透過排序生成）
  浙江省	杭州市	浙江省	ALL	1
  浙江省	杭州市	浙江省	杭州市	2
  浙江省	紹興市	浙江省	ALL	1
  浙江省	紹興市	浙江省	紹興市	3
  浙江省	溫州市	浙江省	ALL	1
  浙江省	溫州市	浙江省	溫州市	4
  江蘇省	南京市	江蘇省	ALL	5
  江蘇省	南京市	江蘇省	南京市	6

  
  

• STEP3：將Cube的行編碼，根據對映關係回寫到使用者明細上，可用Mapjoin的方式實現。

訂單編號	使用者ID	支付日期	所在省	所在市	彙總Cube ID
2023111101	U001	2023-11-11	浙江省	杭州市	[1,2]
2023111102	U001	2023-11-11	浙江省	紹興市	[1,3]
2023111103	U002	2023-11-11	浙江省	杭州市	[1,2]
2023111104	U003	2023-11-11	江蘇省	南京市	[5,6]
2023111105	U003	2023-11-11	浙江省	溫州市	[1,4]
2023111106	U004	2023-11-11	江蘇省	南京市	[5,6]

• STEP4：彙總到使用者維度，並對 Cube ID集合欄位進行去重 （可以用ARRAY 的DISTINCT）

• STEP5：按照Cube ID進行計數計算（由於STEP4已經去重啦，因此這裡不需要再進行去重）；然後按照對映關係進行維度還原。
  

  Cube ID	下單使用者數指標	Cube 維度還原：所在省	Cube 維度還原：所在市
  1	3	浙江省	ALL
  2	2	浙江省	杭州市
  3	1	浙江省	紹興市
  4	1	浙江省	溫州市
  5	2	江蘇省	ALL
  6	2	江蘇省	江蘇省

  
  

• Over～

4.3.程式碼實現

WITH -- 基本的明細資料表準備base_dwd AS (  SELECT   pay_no          ,user_id          ,gmt_pay          ,pay_amt          ,prov_name          ,prov_code          ,city_name          ,city_code  FROM tmp_user_pay_order_detail)-- 生成多維Cube,並進行編碼,dim_cube AS (  -- Step02:CUbe生成  SELECT *,DENSE_RANK() OVER(PARTITION BY 1 ORDER BY cube_prov_name,cube_city_name) AS cube_id   FROM (    SELECT dim_key          ,COALESCE(IF(GROUPING(prov_name) = 0,prov_name,'ALL'),'na') AS cube_prov_name                       ,COALESCE(IF(GROUPING(city_name) = 0,city_name,'ALL'),'na') AS cube_city_name     FROM (      -- Step01:維度統計        SELECT  CONCAT(''                       ,COALESCE(prov_name ,''),'#'                        ,COALESCE(city_name     ,''),'#'                 ) AS dim_key                ,prov_name                ,city_name        FROM base_dwd        GROUP BY prov_name        ,city_name    ) base    GROUP BY dim_key            ,prov_name              ,city_name    GROUPING SETS (           (dim_key,prov_name)          ,(dim_key,prov_name,city_name)    )  ))-- 將CubeID回寫到明細記錄上,並生成UID粒度的中間過程資料,detail_ext AS (  -- Step04:指標統計  SELECT   user_id          ,ARRAY_DISTINCT(SPLIT(WM_CONCAT(';',cube_ids),';')) AS cube_id_arry  FROM (    -- Step03:CubeID回寫明細    SELECT  /*+ MAPJOIN(dim_cube) */           user_id        ,cube_ids    FROM (        SELECT   user_id                ,CONCAT(''                       ,COALESCE(prov_name,''),'#'                       ,COALESCE(city_name,''),'#'                  ) AS dim_key        FROM base_dwd    ) dwd_detail    JOIN (        SELECT dim_key,WM_CONCAT(';',cube_id) AS cube_ids        FROM dim_cube         GROUP BY dim_key    ) dim_cube    ON dwd_detail.dim_key = dim_cube.dim_key  ) base  GROUP BY user_id)-- 指標彙總並將CubeID翻譯回可理解的維度,base_dws AS (  -- Step05:CubeID翻譯  SELECT cube_id        ,MAX(prov_name) AS prov_name        ,MAX(city_name    ) AS city_name        ,MAX(uid_cnt      ) AS user_cnt  FROM (      SELECT cube_id              AS cube_id            ,COUNT(1)             AS uid_cnt            ,CAST(NULL AS STRING) AS prov_name            ,CAST(NULL AS STRING) AS city_name      FROM detail_ext      LATERAL VIEW EXPLODE(cube_id_arry) arr AS cube_id      GROUP BY cube_id      UNION ALL       SELECT CAST(cube_id AS STRING) AS cube_id            ,CAST(NULL AS BIGINT) AS uid_cnt            ,cube_prov_name       AS prov_name            ,cube_city_name       AS city_name          FROM dim_cube  ) base   GROUP BY cube_id)-- 大功告成,輸出結果！！！SELECT   prov_name        ,city_name        ,user_cntFROM base_dws;

• 實際的執行過程（ODPS的Logview）如下圖。

4.4.實驗效果

左邊是基於Cube打標方案的新鏈路。實驗過程中將實驗資料由100億增加至200億，組合維度數由原來的25個增加至50種組合維度，整體耗時在18分鐘，若只計算和原始資料量、組合維度均相同的資料，整體計算耗時可控制在10分鐘內。

右邊是基於資料膨脹計算的老鏈路。實驗資料設定為100億，組合維度數為25種，中間過資料將膨脹至1300億+，資料大小更是膨脹至1TB+，整體耗時47分鐘。若此方案擴充套件至新方法的200億資料x50種組合維度，中間過程資料將膨脹至4000億+，資料大小增加將膨脹至3TB+，整體計算耗時預估將達到2.5小時+。

新方法目前已經在業務核心高管鏈路上線，在資料統計維度組合、資料計算量都大幅增加的情況下，整體核心指標產出相較於以往，進一步提前1小時以上，有效的保障了相關核心指標資料的穩定性。

4.5.方案總結

常見的基於資料膨脹的Cube計算方法，資料計算大小和過程資料傳輸量將隨著組合維度的數量呈線性增長，組合維度數越多，花費在資料膨脹與Shuffle傳輸的資源和耗時佔比越高。在實驗過程中，100億實驗資料x25種維度組合場景，過程資料已經膨脹至1300億+，資料大小由100GB膨脹至1TB，當資料量和維度組合數進一步增加時，整個計算過程基本上難以完成。

為了解決資料膨脹過程中產生的大量過程資料，我們基於資料打標的思路反向操作，先對資料聚合為UID粒度，過程中將需要的維度組合轉化編碼數字並賦予明細資料上，整個計算過程資料呈收斂聚合狀，資料計算過程較為穩定，不會隨著維度組合的進一步增加而大幅增加。在實驗中，將實驗資料由100億增加至200億+，組合維度數由原來的25個增加至50種組合維度，整體耗時控制在18分鐘左右。若同等的資料量，採用老的資料膨脹方案，中間過程資料將膨脹至4000億+，資料大小將增加至3TB+，整體計算耗時將達到2.5小時+。

綜上，當前的方案整體效能相較於以往有大幅度的提升，並且不會隨著維度組合的增加而有明顯的增加。但當前的方案也有不足之處，即程式碼的可理解性和可維護性，過程中的打標計算過程雖然流程較為固定，但整體上需要有個初始化理解的過程，目前尚無法做到普通UnionAll/Cube等方案的易讀和易寫。另外，當組合維度數較少（即資料膨脹倍數不高）時，兩者的效能差異不大，此時建議還是用原始普通的Cube計算方案；但當組合維度數達幾十倍時，可以改用這種資料打標的思路進行壓縮，畢竟此時的效能優勢開始凸顯，並且維度組合數越高，此方案的效能優勢越大。

五、其他方案

BitMap方案。核心思路在於將不可累計的資料指標，透過可累加計算的資料結構，近似實現可累加指標的效果。具體實現過程方案是對使用者ID進行編碼，存入BitMap結構中，比如一個二進位制位表示一個使用者是否存在，消耗1個Bit。維度統計上卷時，再對BitMap的資料結構進行合併和計數統計。

HyperLogLog方案。非精確資料去重，相對於Distinct的精確去重，效能提升明顯。

這兩種方案，效能上相對於普通的Cube計算有巨大的提升，但BitMap方案需要對去重統計用的UID進行編碼儲存，對一般使用者的理解和實操成本較高，除非系統級整合此功能，不然通常需要額外的程式碼開發實現。而HyperLogLog方案的一大弊端就是資料的非精確統計。