SQL 百億級數據處理優化 效率/速度 [ 利用暫存表(臨時表)把資料筆數降低，使用虛擬表+子查詢+INNER JOIN ] 

資料來源:https://mp.weixin.qq.com/s?__biz=MzI3NDA4OTk1OQ==&mid=2649902730&idx=2&sn=9c6faf81fb65c9a19abad9ac7a8ea332&chksm=f31fbc02c468351472291f5544c409ca1f8bc325489b3823b33ac88495fd275282801da923b0&scene=0&xtrack=1&key=d9588cb87fa8bcaf22c354e29eeb87df548fc508290797ee2dc1d9dd76ed1c682b41908e53c8c437a512990710c658be28da39938fbaa8322c1c0181980fadb818f7d53d4edda626aa0fb757bf13278e&ascene=1&uin=MjIwODk2NDgxNw%3D%3D&devicetype=Windows+10&versiON=62060833&lang=zh_TW&pass_ticket=dbZkB%2F4JLMf4Rs%2B5lHPciWLvs06vLL3mKHHsB5hlpQGZ3JdrcJwgovy%2FN8oPOeqH

user_article_tb 表:

    uid: 用戶ID
    itemid：文章ID
    dur: 閱讀文章時長，如果大於0代表閱讀了文章，等於0代表沒有點擊文章
    dt：每天的日期(EX:2019/0707)
    PS.每天 55 億筆資料

user_profile_tb 表:
    uid: 用戶ID
    gender：性别，F 代表女，M 代表男
    age:年龄
    city：城市
    dt：每天的日期(EX:2019/0707)
    PS.這是一張總表，每天存儲全量用戶畫像屬性，最新數據十億級別

需求是這樣的：計算7天中，女性用戶在每篇文章上的ctr （最終會按照降序進行截斷）。直接寫SQL很容易

--原解 start--
SELECT itemid , count(if(dur > 0, 1, null)) / count(1) ctr
FROM
　　(
    　　SELECT uid, itemid, dur
    　　FROM user_article_tb
    　　WHERE dt>='20190701' AND dt<='20190707'
　　) data_tb
　　JOIN
　　(
　　　　SELECT *
　　　　FROM user_profile_tb
　　　　WHERE dt='20190707' AND gender='F'
　　) profile_tb
　　ON data_tb.uid = profile_tb.uid
GROUP BY itemid
ORDER BY ctr DESC
LIMIT 50000;

--原解 end--

那麼問題來了：

對於user_article_tb來說，7天的數據量將近400億條記錄，還需要JOIN一張十億級別的畫像表。這個數據量基本上就跑不出來了

像這種探索性質的需求，經常會變化。假設需求變成計算男性或者計算一二線城市用戶的呢？可能又需要重跑整個數據，既要付出時間成本又要付出高昂的資源成本

我們一一解決上面提到的兩個問題。先考慮第一個，既然JOIN的兩張表太大了，我們能不能嘗試把表變小呢。答案是肯定的，對於畫像表來說顯然是沒辦法縮小了，但是對於user_artitle_tb是可以的。我們可以按照表的分區字段dt用每天的數據分別JOIN畫像表，將結果再按天存儲在一張臨時表裡面。這樣每天就是十億級別的數據JOIN，基本可以解決問題。但是每天的數據仍有多餘的JOIN，比如：某天的數據中uid = 00001的用戶，一天看了1000篇文章，那這個用戶就需要多JOIN 999次。在我們的業務中一個用戶一天看文章的數量> 10是很普遍的，因此多餘JOIN的情況還是比較嚴重的。

針對上面提到的多餘JOIN的情況，最徹底的解決方法就是把user_article_tb表變成uid粒度的，跟畫像表一樣。我們將7天的數據轉換成uid粒度的SQL如下：

INSERT OVERWRITE TABLE user_article_uid_tb AS 
SELECT uid, wm_cONcat(':', cONcat_ws(',', itemid, dur)) item_infos
FROM 
　　(
  　　　SELECT *
　　　　FROM user_article_tb
  　　 WHERE dt >= '20190701' and dt <= '20190707'   
　　) tmp
GROUP BY uid

至此，多餘JOIN的問題得到了解決，再來看看第二個問題。這個問題其實就是我們維度建模理論中所說的寬表，為了避免統計不同維度時頻繁JOIN維表，我們可以在上游數據將常用的維度提前關聯起來，形成一張大寬表。下游數據可以直接用從而減少JOIN。以我們的問題為例，SQL如下：

CREATE table user_profile_article_uid_tb AS
SELECT 
    data_tb.uid
    , item_infos
    , gender
    , age
    , city
　　-- 其他维度字段
FROM
　　(
   　　 SELECT uid, item_infos
    　　FROM user_article_uid_tb 
　　) data_tb
　　JOIN
　　(
    　　SELECT uid, gender, age, city
　　　　FROM user_profile_tb
　　　　WHERE dt='20190707'
　　) profile_tb
　　ON data_tb.uid = profile_tb.uid;

這樣，上面提到的兩個問題就都解決了。最終我們的需求：女性用戶每篇文章的ctr計算如下：

SELECT 
    itemid
    , count(if(dur > 0, 1, null)) / count(1) ctr
FROM 
　　(
　　　　SELECT 
　　　　　　split(item_info, ',')[0] itemid
    　 　　, split(item_info, ',')[1] dur
　　　　FROM user_profile_article_uid_tb 
　　　　lateral view explode(split(item_infos, ':')) item_tb as item_info
　　) tmp
GROUP BY itemid
ORDER BY ctr DESC
LIMIT 50000