讓你的系統“應付自如”！大數據自定義報表系統設計

大家晚上好，我是百度外賣·研發中心·大數據研發部的高級研發工程師，劉海宇。今天有幸為大家分享下我們大數據部門的一個報表系統。

大家應該都看到了圖中的彩色字體，“自如報表”、十分醒目。這個“自如”在我們今晚的分享中是啥呢？不是租房、不是搬家，這個“自如”呢其實是我們馬上要介紹的這個報表系統的項目代號。

為什麼叫“自如”呢？來自首席架構樑福坤賦名，抽象自《金剛經》第三十一品 知見不生分，“於一切法，應如是知，如是見，如是信解，不生法相。”，初衷想讓我們的報表系統可以做到：展示靈活、數據源靈活，不要拘相。這種平臺化交付設計在百度外賣大數據平臺中體現的淋漓盡致，通過我們本節課的介紹，大家會有較深刻體會。

下面來看下本次課程的一個簡要的介紹。從上一頁的大標題也看出來了，本次課程會緊密圍繞“報表”兩個字來展開，數據報表、報表平臺，離不開“報表”兩個字。

課程內容

如本頁ppt所述，課程內容方面呢，本次課程會為大家介紹一個有高度定製化特點的、並且能夠按規則例行的這樣一套報表體系。這裡有兩個關鍵字昂，定製化和例行化。什麼是定製化？就是很靈活，內容可以完全由自己指定，包括數據表格的內容、展現形式、佈局以及樣式等等從大到小的點、只要你想指定。

那麼什麼是例行化？比如每小時例行、每日例行、每週例行、每月例行，就像大家平時發的的時報、日報、週報、月報，甚至季度報、半年報。叨叨了這麼多，配置方面呢，大家放心，也是很高效靈活的，並且我們也提供了一個非常強大的可視化界面。

課程關鍵字

課程關鍵字方面，報表解決方案。這裡要重點強調的是說，我們的這個報表系統啊，適用於用sql出報表的使用場景。之所以是sql，是因為sql的靈活度高，只要基礎數據在那裡，用戶想要什麼數據完全可以自己搞出來。其它的出數據的方式，我們這個課程暫不涉及。如果您平時不用sql出報表，也不妨聽一下，說不定也會有所收穫呢。

課程傾向

課程傾向這一塊呢，會有功能展示與設計方案這兩部分組成。因為是一個我們這邊正在線上使用的一個報表工具，直接講設計思路和解決方案太抽象，所以會有一些ppt頁面給大家展示這個工具的界面、以及簡單的使用流程。雖然是界面和功能的展示，也希望能夠在與大家分享的過程中，哪怕有零星幾個亮點功夠引起大家的共鳴、甚至能夠在未來的某日落地應用到大家的報表體驗中。當然，大家有改進建議或者問題吐槽我們也隨時歡迎。然後基於這些功能展示，我們會講一些重點難點的設計思路和解決方案。

面向人群

面向人群方面，就是與報表、與數據相關的我們。不是研發人員，也完全能夠融入其中。

看到本次課程的主要內容。第一章、第二章主要是體驗與展示，第三章第四章是系統設計與解決方案方面的，我們會首先介紹下整體的架構和設計，然後介紹下這個報表系統發展上遇到的痛點，有兩個具體的案例會跟大家分享。第五章，介紹下我們的所見即所得的高大上的可視化，如何三步走秒配報表。最後，總結和答疑。

第一章 初體驗

好，下面我們來看下第一章，對這個報表系統的初體驗。

1.需求場景

1.1 人工報表時代

首先來看一下需求場景，通俗的講，就是我們為什麼當初要做這個報表系統、我們當初遇到了什麼困境讓我們“不得已”開發了這套系統呢。當然，不得已加引號，開發人員開發的還是很開心的。

相信大家中的一部分人，是天天與sql、與數據、與報表、與郵件打交道的報表配置人員。也許你們已經有了更加高大上的報表系統，但是一部分同學呢，肯定還是需要每日人工查詢sql、每日人工導出數據、每日人工發出報表郵件。

如ppt中所述，為了每天獲取最新的數據，所以必須要人工完成一系列查詢、導出、郵件流程，以及一些美化。美化方面就比較細節了，比如說有插入折線圖、文字大小字體，甚至行列轉置、合併單元格，以及數據千分位、正數用什麼顏色負數用什麼顏色這種細節問題。

這個過程，無疑耗費了人力，每個人維護多張報表的話，一張報表可能一天要花1小時的時間來維護，少則幾分鐘。而且，如果數據需求方需要每天5點6點就給到數據呢？這樣如果每日都人工去做，很難受，內心是拒絕的。當然，這個報表系統上線之前我們也是這麼做的。

1.2 “自動”代替“人工”

所以，我們為何不想辦法把“人工”改為“自動”呢？可以看到ppt三個紅色的圓圈，自動查詢、自動郵件、自動美化和加工。這就是我們這個報表系統做的工作。

當然，sql自己寫，前面也說到了，我們這個報表系統適用於配置人員使用sql出數據的場景，出什麼數據我們不管，sql配置人員自己來寫。其實通過後面的講解，會發現配置人員關注的點有且只有sql本身，其它的都交給我們。那麼這個過程對於曾經的報表配置人員來說，大幅度的解放了人力，全自動、一次配置、一勞永逸。具體的配置過程，我們後面會看。當然，當業務變動而牽扯到sql細節修改的這個更新過程，這個還是需要配置人員親自動手的。

還有，最下面那個帶蛤蟆鏡的小人頭表情包左邊的兩句話，大家請瞄一眼。高度自定義、按規則例行。

2.自如報表閃亮登場

2.1 定製化與例行化

下面，我們的報表系統，即“自如報表”閃亮登場。是能夠實現那兩個功能的報表系統，重複了好幾遍了。聽眾們是否有共鳴呢？是否你也需要這樣的內容高度定製化、按規則例行的這樣一套報表解決方案呢，帶著這樣的需求我們繼續講解。

2.2 如何實現定製化

看到ppt裡面的交互圖，整個報表系統的工作流程是這樣的。紅色的兩條箭頭，是配置人員需要關注的。專人配置報表，專人配置例行規則。

以“日報”為例，這兩個“專人配置”是人工的，但是是一次性的。只需要配置一次，明天開始甚至當天就可以開始例行發送了，後面大家就不用操心了。當然，一些變動和修改除外。配置日報，即第一條紅色箭頭呢，需要做的就是把sql告訴我們的報表系統。第二條紅色箭頭，就是告訴我們的系統每天什麼時候發，比如早晨8點、上午10點，或者等某個前置任務成功之後日報發出，這個前置任務可能是數據準備或一個數據校驗的任務。收斂一下，第一條紅色箭頭，把報表配置告訴報表系統。第二條紅色箭頭，把例行規則告訴調度系統。

談及“調度系統”，這個系統是與我們的報表系統相獨立的一個系統。按照事先指定的小時、日、周、月等時間頻次以及依賴關係進行任務觸發，並監控任務執行狀況的系統。比如什麼任務需要幾點執行、什麼任務需要在哪一個或哪幾個任務執行完了再執行。關於這個系統，我們後面也安排了相應的課程為大家講解，名字叫“通天”調度，一個很高大上的名字。本節課程不多描述。

2.3 如何實現例行化

日報是如圖紅色箭頭的兩個流程，週報、月報一樣。重點文字，大家可以看到右下角。配置之後呢，等到了相應的時間點，或者相應的條件符合了，調度系統就會自動回調報表系統，觸發日報發送動作。稍許等待，日報就發出了，發到了用戶的郵箱。這兩個過程，回調和發出是完全自動的，即上圖兩個藍色箭頭，用戶不用操心。當然，發出的時間也與用戶sql本身的複雜程度和數量有關，後面我們也會講到一些優化的方式。

好了，這就是一個自如日報的配置和發送流程。

3.自如報表能做什麼

3.1一張標準報表

下面開始展現一下我們的報表系統能做什麼。如ppt中，這是一封標準的報表郵件。出於數據安全的考慮，部分數據需要打碼。

一般的報表呢，由一個表或多個表、每個表格都是一行一行的數據。這個功能，我們完全能夠實現，也是最最基礎的功能。同時，可以方便的添加標題、二級標題、三級標題等，就是ppt中這個例子圖片中的紅色字體。

以及樣式，字體、顏色、字號、粗體，表格的樣式、文字的樣式都可以很方便的配置。當然，也可以選擇默認的樣式，更加減少自己的配置成本。

還有百分比的紅正綠負這樣的動態數據樣式策略，以及正文中第一行寫到的配置人員是誰、有問題找誰。

這是一封最基礎的最普通最標準的數據報表郵件。其實只要你有sql，這封報表就能出，其它的什麼都不用管。

3.2 帶附件的報表

這一個功能說的是附件。郵件正文是一部分，有的數據不適合放郵件正文，我們也支持放到附件。附件支持xls和csv格式，後者會有更小的體積。

以及ppt中這個例子的最下面一行，有一句“指標定義”的解釋，只要你想加上，隨便加。常見的比如指標口徑的解釋，更加方便大家理解和使用你的報表。

仔細看的話，可以看到這個例子中報表的結構，一張表格不像一條sql能跑出來的。可能需要更多的變換和組織。不用擔心，絕大多數情況下，只要你知道原本用excel怎麼出，該系統幾乎都能支持到。

3.3 帶圖形的報表

表格展示展示完了。報表中難道只有表格嗎？還會有圖形，比如常用的折線圖、柱狀圖、扇形圖。都可以使用這套系統進行繪製和例行發出。

而且，對於一張已經現成的表格。繪製為圖形也是很簡單的，我們支持對已有表格數據的引用。以及表格、圖形、附件可以融合在一張報表中發出。

圖形本身呢，寬度、高度、是否顯示圖例、柱子顏色等屬性，用戶可以使用默認配置，也可以進行個性化的設置。

只要你有sql，圖形就能出來。

3.4 與第三方數據平臺打通

除了基礎的表格和圖形功能之外呢，這個報表系統本身還會與其它的數據平臺進行打通。這裡舉個例子，我們這裡有個數據平臺叫做“亮劍”，是提供數據的例行拉取或主動推送，並支持多維度多方式展示數據的一個數據平臺。

當日報郵件發出，用戶收到的只是一封郵件。關於歷史數據只能去歷史郵件中找，而且不能夠點擊和交互。當用戶有這種分析歷史數據，還能夠以交互的方式分析歷史數據的需求的時候，就適合用到這一個功能。

日報發出的時刻，會把數據同步推送到亮劍。日報中也會攜帶一個鏈接，用戶單擊鏈接就可以看到平臺上的這一張報表，如圖所示，含有歷史數據以及豐富的圖形化展示。

3.5 細節功能——行列轉置

下面看5個典型的、能夠提升用戶體驗、豐富大家日報內容和形式的細節功能。

先來看第一個，這裡首先介紹的是行列轉置功能。如ppt中的例子，一個sql輸出A表格很簡單，只需要按照時間字段group by，然後select語句中輸出相應指標即可。但是一個sql輸出B不容易，B的特點是是一行一個指標，一列一個日期，大家可以思考下如何一個sql產出呢？也許可以，但是並不容易。

這種場景下，你只需想辦法查出A，然後在這個報表系統中進行一個配置、或者可以理解為勾選一個“行列轉置”的勾選框，B就會出來了，並隨郵件發出。很方便實用的功能。

3.6 細節功能——動態合併單元格

第二個細節功能，合併單元格。不需要大家手動的指定哪幾個單元格合併。因為很多情況下，需要根據查詢出來的具體數據進行合併，這個數據的樣子在我們配置的時候往往是未知的，而且隨著業務發展是會發生變化的。雖然人工報表時代可以實現，但是在這種託管式的一次配置一勞永逸的報表方式下如何才能應付自如呢？

所以，這裡就提供了動態合併單元格的功能。並且，為了可控，還會提供下標和區間控制選項，讓合併行為發生在你想要的可控範圍內。

右下角那個可憐的很小的圖片，是前面講到的行列轉置與本頁講到的動態合併單元格混合使用的案例。大家有興趣，可以放大看一下。以及考慮下，如果沒有這兩個小功能，如何用一條sql產出這一個表格呢。

3.7 細節功能——自定義動態樣式

第三個細節功能，自定義動態樣式。這是個很炫的細節功能，支持五彩斑斕的動態樣式設置。比如你有一個表格描述了所有銷售人員的銷售業績，你要把銷售額最高的那一個銷售人員的那一行展現為紅色背景、白色字體、加粗、字體大一號，銷售額最低的是綠色背景、白色字體、加粗，雖然這對銷售額低的同學來講很是沒面子。

用以往的人工報表肯定可以實現，人工判斷下，然後選中、標個顏色即可。但是這種託管式的、一勞永逸的配置模式下，如何實現呢？而且數據本身在查詢和發出前都是不可預測的。所以，在這種場景下這個細節功能就有了用武之地。

支持最大值、最小值、區間的樣式策略。最大值最小值好理解，區間值的策略，比如對一列進行設置，數值1000-2000是紅色粗體，2000-3000是紫色粗體等等。樣式方面，你能想到的樣式策略比如字體、字號、加粗等都是支持的。而且可以控制樣式的作用範圍是單元格範圍還是行的範圍。

與行列轉置結合使用，比如圖中圖E的例子。如果不提供這一系列的功能，E以及其它例子的樣子是很難實現的。其他的例子，大家可以點開本頁ppt看下文字描述。就不一一說明了。

3.8 細節功能——數據格式化

第四個細節功能，數據格式化，這是個很普遍的功能。比如訂單量要用千分位來表示，流水要用千分位並且保留兩位小數來表示，新用戶佔比要用百分比來表示。這些都是可以靈活指定的。具體的語法，可以看到ppt裡面的這個表格，指定是很靈活的。而且，我們後面要講到的可視化部分也做了對常用格式的一鍵選擇的支持。

3.9 細節功能——收件人列表託管

第五個要展示的細節功能，也是最後一個細節功能，收件人列表的託管。

傳統的郵件發送呢，都是由配置人員手工指定、人工維護主送和抄送人員的。那麼在某種場景下，我們更希望通過系統來維護、自動分法。這就用到了我們這個收件人列表託管功能。

比如說，ppt中的圖片中的例子，我們要按城市權限分發報表郵件給代理商，代理商是與城市掛鉤的，A代理商是某城市的、那麼他就只能收到該城市的數據。

這個掛鉤的映射關係呢是由系統來維護的。那麼允許用戶指定開啟這樣一種分發方式，並按照規範指定權限參考字段，這個例子裡面就是城市名字段嘛。然後平臺會根據分發規則進行自動的發送，把相應城市的數據分發給相應的城市。因為城市有很多，所以會產生很多獨立的郵件報表，但是對於配置人員來講他只需要維護一份配置。

3.10 配套設施——日報級監控

功能展示的最後，來看兩個配套的監控。

這裡首先出場的監控功能是報表級的監控，能夠查看當前報表的發送狀態和耗時。以及報表本身的一些信息，比如分類、用戶簽到記錄、發送歷史和關聯的調度信息。

這裡不詳細的一一描述了，感興趣的同學可以看到ppt裡面的文字，有寫到使用場景和解決方案，關於簽到信息那塊我們後面會作為案例重點講到。

3.11 配套設施——sql級監控

有了報表級別的監控，其實還不夠。比如一個報表開始了很久了但是發送狀態一直是發送中，感覺有問題，當然可以找研發同學幫忙看日誌。但是為了讓用戶能夠自助定位問題，包括也為了方便研發人員定位問題，這時候我就需要一個sql級別的監控，從而定位到用戶當前查詢的是哪一條sql、以及sql的耗時。從而呢，就可以統計出哪些sql耗時較久影響了整體發送，方便定向優化。

包括查詢報錯、發送失敗，這時候呢，也是需要這麼一個sql級的監控，來定位到具體是哪一條sql有問題，如ppt中的兩個圖片。下圖是一個查詢失敗的sql的報錯信息。

3.12 小節

介紹了很多功能。主要功能是表格和圖形。以及各種好用好玩的細節功能，幫助報表配置人員方便的輕鬆實現各種想要的靈活的報表內容和形式。

杜絕日復一日的人工，要一次配置、一勞永逸。

第二章 簡單示例

下面來看到第二章。第二章會比第一章短很多。設計與方案相關的後面章節會講到，為了避免講解起來太抽象，這裡主要結合一個非常簡單的示例來為大家介紹一下配置流程。

1.人工時代的流程

比如現在一個同學，叫小明。小明同學呢，寫出了一個sql，如ppt中所述。是一個查詢某日期的若干城市的白天溫度和白天天氣的sql，~例子非常簡單。以往的方式呢，是手動查詢、導出、郵件、發出。小明同學失去了每天早晨多吃一根油條的的時間。

2.自如的自動化流程

2.1 下載預置模板

於是，老巫婆飄過來了，在右上角，那個sql也在。講解了配置我們自如報表的基本步驟。老巫婆講解起來的表情，比天橋下貼手機膜的都認真。

第一步，到平臺去下載一個基礎的預置模板（默認模板）。

這裡大家肯定會有疑問。什麼是模板？官方定義，模板就是承載報表全部內容的一個載體，比如數據內容、就像用戶書寫的sql，比如樣式內容、就像字體和表格的高矮胖瘦，比如細節屬性、就像是否轉為附件啊、是否使用行列轉置啊類似的功能。在這裡呢，我們的模板採用html來進行存儲。

當然可以使用其它的文件來存儲或者更好的方式，比如xml，那還得自己定義一套標籤體系和樣式規則。這裡之所以用html，是因為它的語法是標準化的，而且很方便的可以定義頁面的內容和樣式。估計大家也都用過html，內容可以通過div、table、h1這類的標籤來指定，樣式呢即css。瀏覽器可以直接打開html，所見即所得。

雖然這裡講到了很多html、css這樣的代碼。一般情況下，用戶是無需關注的，搞到預置模板（默認模板），sql粘貼進去就可以了。包括我們後面還開發了可視化，來進一步減輕這一個過程的配置成本。

2.2 粘貼sql

預置模板（默認模板）下載到了，那麼下一步就按照我們前面所說的，把sql粘貼到相應位置就可以了。當然，表頭的列中文名以及展示順序也需要同步的指定下，否則出來的表格很難看。

這裡大家的疑惑，一定是為什麼要使用這樣的標籤結構？那個標籤是什麼東西呢？之所以用這樣的標籤配置結構。是因為我們系統中事先制定了一套約定和規範，服務端就會按照這樣的結構去解析，所以呢也會引導用戶這麼去寫。總之，這裡跟大家分享的是報表的解決方案，不是使用和實現的細節。當然，如果大家有更友好的約定和規範，以及實現方案，也歡迎與我交流。這裡呢，只是舉例說明。

2.3 上傳到平臺

最後，上傳到我們的平臺。在頁面中寫好郵件標題、主送抄送，點發送，郵件就發出來了。最終結果如ppt中右圖所示。

2.4 例行化的配置

其實到上一步，整個配置過程就完成了。

回顧一下，第一下載，第二粘貼sql，第三上傳發送。就完成了。要關注的重點似乎只有sql，如果一個報表中有多個sql，那就照貓畫虎的把基礎模板中相應的區域複製幾遍即可。

最後這裡呢，說的是例行化的配置，比如日報、來指定每天幾點發，週報、來指定每週幾的幾點發，月報、來指定每月的第幾天的幾點發。

到這裡呢，第二章所要介紹的配置流程就講解完了。

第三章 系統設計篇

那麼有了形象的功能展示和配置流程作為基礎，下面我們來看系統設計篇。會從平臺架構與交互流程這兩塊入手來看，以及最後為大家簡要介紹一下主要模塊的技術方案。

1.平臺架構圖

1.1 基礎版架構圖

首先來看基礎版本的架構圖。之所以叫基礎版本，是因為這個架構圖所提供的服務，已經滿足了最基本的報表發送流程的訴求。關於後續功能如可視化、第三方集成這張圖中沒有。也是為了方便大家理解。

這個架構圖中，結構比較簡單。最上層用戶UI，就是用戶下載上傳模板、填寫主送抄送。然後與自如的基礎服務進行交互，比如作業註冊、模板解析等，這個後面會一一提到。然後底層依賴了緩存，比如用戶多次查詢同一條sql，那麼如果用戶需要，第二次及其以後的就會從緩存中讀取。以及依賴了API接口還有底層的集群服務。最後，是右邊的通天調度，負責例行化，貫穿了整個流程的始終。

自如報表系統最關心的是上兩層，即平臺UI和基礎服務。其實本身這個報表系統也是建立在一些底層服務之上的數據應用平臺。

1.2 擴展版架構圖

看完基礎版本的架構圖，我們再快速看一眼擴展版本的架構圖。這裡就加入了可視化、第三方的支持。以及我們後續不斷的對模板解析和優化插件的升級。

可視化後面會有一章為大家講解。第三方系統因為涉及了其它較多的系統，牽扯內容較多，本節課不會介紹。

2.交互流程圖

下面我們重點看一下自如的服務器端，即我們所關心的自如的基礎服務那一層所做的事情。右上角的縮略圖是前面提到過的一個簡易的流程圖，現在我們把中間的那塊“報表系統”展開，其它的基本沒有變化。大家重點看到ppt中流程圖的中間部分的兩個矩形框。上面的矩形框是作業註冊的流程，下面的矩形框是作業發送的流程。

2.1 作業註冊

根據我們前面的流程的舉例講解，用戶配置好報表模板後，即可以到平臺進行作業的註冊以及例行規則的指定了。這就是上面的矩形框。

2.2 作業發送

當時間符合或者依賴就緒的時候，比如到了早晨8點，某日報要開始發送了。通天系統回調報表系統，觸發發送流程。這就是下面的矩形框。

發送流程呢，首先獲取到了作業的元數據，如配置人員、郵件主題、主送抄送，最重要的是獲取到所對應的模板文件。讀取到這一系列信息後，開始對那個模板文件進行解析，比如取出某一個表格的sql、以及解析到是否需要轉化為附件、是否需要行列轉置等一系列內容和屬性。知道了sql以後，就可以進行查詢了，於是把sql提交到了自如端底層的查詢隊列進行併發查詢。稍等片刻，數據返回，進行數據的組織、後續的優化，最終發出郵件。整個過程還是很簡單的。

3.主要就模塊技術方案

上面的一系列流程很簡單。其中涉及到的主要模塊的技術方案也不困難。平臺的服務端使用java語言實現的。

3.1 模板解析

模板解析部分，使用的是jsoup這一個開源工具。大家有興趣可以瞭解下，可以非常方便的解析html文件，可以在服務器端代碼中，使用類似css選擇器的方式取出和操作數據。

3.2 併發隊列

併發隊列這一塊，用到的是jdk的java.util.concurrent包裡的工具類，非常常用，用法就不多介紹了。需要重點說明的是，併發隊列在報表系統中有兩層。首先，底層有一個sql級別的隊列進行流量控制，限制一下同一時刻允許提交sql的最大數量，避免sql大量湧入集群。再者，上層還有一個日報級別的隊列，限制一個日報最多有幾個sql同時進行查詢，從而避免有過多sql的日報佔滿底層隊列，讓其它短平快的日報需要等待好久才能發出。二者分別實現了集群資源的合理利用以及自如端併發資源的合理利用。

3.3 其它模塊

再往下數據組織、優化插件以及郵件服務。尤其是優化插件，前面在功能展示部分也有展示。大家可以看一下ppt中的問題，不做詳細說明了。

第四章 技術痛點和解決方案

到這裡，自如的主要部分已經講完了。但是，還是感覺比較平淡。那就再嘮個5毛錢的吧。下面，就為大家介紹下自如發展過程中遇到的技術痛點以及相應的解決方案。結合兩個具體的案例來看。

1.發展史上的里程碑

首先簡單看一下發展歷程。系統第一版於2015年10月上線，上線之後隨著用戶的使用和反饋，遇到了一些問題和瓶頸。圖中紫色的字體說明了當時遇到的問題。

其中，第三個臺階的自如可視化部分下一章節講解。第二個臺階和後三個臺階，分別遇到的問題是SQL複雜度高集群壓力大，和不必要的SQL數量多。為了說明當時的場景以及解決方案，我們會對其中兩個案例進行重點說明。分別是SQL拆解聚合和日報簽到。

2.典型案例——SQL拆解聚合

2.1 需求場景

2.1.1 從現象入手

首先看SQL拆解聚合這一個案例。名字聽起來怪怪的，隨著講解，大家會理解這裡的拆和聚的含義。這一頁ppt描述了當時的場景，用戶sql、以及其它來源的sql、以及一些其它因素，導致集群狀況不佳，導致用戶查詢得不到數據。用戶很苦惱，我們也是在極力的去改善。

用戶得不到數據咋辦呢？會通過我們的各種平臺去嘗試，自如是其中一部分，這裡還有其它的數據平臺可供用戶獲得數據。自如端來看呢，就是用戶不停的重試，包括調度系統也會對失敗作業自動重試。於是查詢請求越多，集群狀況越是不容樂觀，造成了這一個惡性循環。

2.1.2 惡性循環

既然有了惡性循環，我們就打算把它打破，或者有效的緩解。針對圖中黃色節點，用戶在自如提交的sql，我們打算從單條sql本身以及sql數量上做文章。

第一，單條sql方面，有的sql本身呢，佔用著併發隊列的席位，提交到集群后，查詢的慢，很久才返回或者返回報錯信息，或者命中集群的監控腳本的策略，比如查詢時間過長導致被殺掉。這種查詢時間長的、消耗集群資源大的，一般是由於sql本身的過於複雜，或者相對其它短平快的sql來講較複雜。我們下面就要對症下藥，來降低這樣的sql的複雜度。

第二，sql數量上，其實由於自如端有併發控制，在自如這個口子上不會無上限的提交sql到集群，這裡要做的呢，是儘量減少不必要sql的提交，在這個解決方案上來講，其實是為了減少不必要的高複雜度的sql的提交數量。

其它的，紅色節點和藍色節點的優化方案，這裡不再詳細說明。

2.2 原因探究

2.2.1 單條sql複雜度高

既然要對症下藥，那麼首先分析一下症狀產生的原因。經過對自如端用戶sql的觀察，發現高複雜度的sql，有這樣的特點。比如一個sql查詢多個指標、多個指標來源於多個事實表、以及子查詢的嵌套和關聯，還有就是本身的業務需求時間跨度長、結果集條數多。那麼針對這樣的情況，除了第四種業務強需求之外，對於前三種，用戶為什麼要寫這樣的sql呢？是因為傳統的配置方式，一個表格的一行或者多行只能由一個sql來決定，這一個sql必須完整的輸出最終要展現的所有列。

一部分情況下，也是用戶被迫的。那麼對於這種原因產生的高複雜度的sql，我們的策略就是“拆”，之後再“聚”。解決方案的思路，就是把一個高複雜度的sql拆分為多個低複雜度的sql，至少複雜度是相對比較低。具體實現起來，就是把來源於同一個sql的多個指標拆分為來源於多個sql的多個指標，然後再把最終結果想辦法聚合在一起。

後面馬上會結合一個具體的例子來講解。

2.2.2 高複雜度sql數量多

上面說的是單條sql複雜度高，對應ppt的左邊的圖。右邊的圖，講到的是高複雜度sql的數量多。經觀察，發現有些不必要的高複雜度sql，除了業務強需求的原因外，是由於對現有sql的結果進行二次查詢、或者要取得現有sql結果集中某一部分作為子集等因素造成的。

大家可以考慮下這種場景，現在某查詢需要用到現有sql查詢出來的結果，由於迫於這樣的結果用戶並沒有辦法渠道，因為查詢出來直接就展示到表格了嘛，所以只能在sql中一層層嵌套、把原本一個挺大的sql嵌套為一個子查詢、然後再在這個基礎上做二次查詢、三次查詢等。所以，我們會針對這一種場景進行解決，為了能夠讓用戶引用到自己寫過的sql的查詢結果。

基於這兩點，圖中的先拆後聚，以及先存後用。我們就有了解決方案。

2.3 解決方案

2.3.1 傳統模式寫出的sql

結合一個具體的例子首先來看一下“先拆後聚”。如ppt中報表的示例所述，是我們這邊每天都在發送的一封報表的一部分。如果用以往的傳統模式，就是我們前面所提到的“一個表格的一行或者多行只能由一個sql來決定，這一個sql必須完整的輸出最終要展現的所有列”。

使用這樣的模式呢，用戶寫出來的sql可能就是如圖中那樣的，三個子查詢相互關聯然後外面包一個外層查詢輸出結果，這還是一個比較簡單的例子，不過也能說明問題。

那麼這樣的sql呢，相對而言，在集群狀況不佳的情況下，有可能導致被殺掉、查詢慢或者報錯。以及佔用著各服務的內存，不僅不出結果，甚至還會引發一些性能問題。

2.3.2 先拆後聚——怎麼拆

於是呢，我們前面所研究而產生的“先拆後聚”的解決方案就派上用場了。

怎麼拆？可以看到ppt右側，拆為了三個短小的sql。就是原來的大sql中的三個子查詢。他們都有相同的維度列index_day。這裡需要提一句，可能有的同學會覺得這個例子比較典型，直接把子查詢拿出來就完成了拆解，其實只要是迫於使用一個sql輸出最終表格所有列的、並且發現這個sql可以拆分為多個小sql來對每個指標列分別查詢的、或者一個小sql查詢其中某幾個指標、並且這幾個拆分後的sql都有相同維度列的，都能夠進行拆分，進而使用這個解決方案。這裡為了方便描述，舉了這個比較典型的例子。從而，三個短平快的小sql，速戰速決。至少比原本的大sql查詢的要快。

2.3.3 先拆後聚——內存聚合

怎麼聚？大家可以看到ppt右側的那個大大的紅色的Map，相信大家一看就懂。Map的key存儲維度值，多個維度列那麼就是多個維度值的組合值，Map的value是一個k-v映射，存儲什麼指標對應什麼數值。

但是用內存聚合的方式呢，會有弊端。比如消耗服務內存、多維度不好管理、冗餘代碼維護、不利於二次查詢。最明顯的第三點，自己在代碼中寫這樣一個Map，肯定需要更多的代碼。這時候如果用戶再求個日環比、周同比，可能還需要一些代碼進行兼容和維護。很不方便。

本頁的最後，大家看一下上面的三個小sql的樣子，有相同的維度列index_day，各自有自己不同的指標列。我們要進行另一個聚合方案的講解了。

2.4 解決方案——“聚”的升級版

2.4.1 中間表聚合方案

由於內存裡面聚合不太方便，於是我們採用數據庫中間表的形式進行聚合。如圖所示，這個例子生成了這樣一張中間表。其中呢，對維度列建立了唯一索引，就相當於前面講到的Map的key。當然，當維度列有多個的時候，比如日期、城市為維度列，那麼就建立聯合唯一索引。

2.4.2 中間表聚合步驟流程

心裡帶著前面的三個小sql，跟著圖中的流程步驟一起看一下。

第一個sql查詢完畢後，如圖所示，會把index_day和第一個指標的指標值插入到中間表中。

第二個sql查詢完畢後，會以on duplicate key update的形式把第二個指標也插入到中間表中。這一種插入方式，簡單解釋一下，會首先判斷唯一索引的字段值為當前值的記錄是否存在，本例中，這裡就會判斷index_day為20170817的值的記錄是否存在，如果不存在就會執行插入，如同第一個sql插入數據時候的樣子，如果記錄已存在，就會以更新的方式更新到那條記錄的相應字段上。

同理，第三個sql也會攜帶第三個指標值回來，以同樣的方式把指標值插入到對應記錄的對應字段中。

於是，到目前為止，在我們的數據庫裡面就會生成一張填充好數據的物理中間表。當然，也允許其中的一個小sql帶多個指標值回來，都是支持的，具體使用的時候可以在考慮和權衡。這裡為了方便舉例說明。

對於我們上面說到的這種中間表，用戶就可以用sql進行任何他們想要的select操作了。一般的，用戶會指定一個查詢，把最終結果查詢出來。如ppt右上角的sql所示，輸出了最終結果。

2.4.3 使用中間表聚合的優勢

到這裡，使用中間表進行聚合的解決方案就講完了。它的好處首先與前面講的內存映射的幾個弊端相對應，不再消耗服務內存、多維度好管理、無需冗餘代碼。比較值得強調的是第四點，因為現有數據會存在一張中間表裡，用戶可以用sql進行他們任何想要的操作，比如做除法、做環比、使用count或sum等進行再次的聚合、進行orderby以及limit等操作。這些，使用冗餘代碼進行兼容會很頭疼，即使兼容了用戶還要學習如何去使用，倒不如直接讓他們使用他們最擅長的sql更加方便。

最後，需要說明的是。用中間表的優勢不止這些。有一個很好的特點是，中間表與中間表之間可以相互關聯，從而方便做二次、三次查詢。更加豐富了用戶對數據產出流程的把控、豐富了數據組織的形式，從而得以製作出更加豐富靈活的報表。只要你的sql能寫得出，數據產出流程隨你怎麼控制。當然，當用戶需要做更加複雜的報表的時候，對用戶的需求就不再是簡單的一條sql出數據了。所以，使用者可以在靈活與簡單之間權衡一下。其實，有了這樣靈活的組織形式，使用者也是樂於參與其中的。

2.5 解決方案——多維度舉例

2.5.1 案例特點分析

如果覺得剛才的例子過於生拉硬拽，我們可以再簡單看一下這個多維度表格的例子。那麼這個表格的特點是有兩個維度列，就是圖中的平臺和日期，維度列之外呢還有5個基本指標，圖中已經圈出。未圈出的指標可以由已有指標計算所得。

除此之外呢，可以看到左側，平臺這個維度有三個維度項，除了iphone和android外還有一行彙總。這一個彙總值，在以往的方式中必須由完整的sql去集群查詢從而獲得結果，現有的方式，其實根本不需要提交它的查詢，它的結果可以由iphone和android的結果彙總而來。

那麼如果使用原有的模式，單一sql查詢集群輸出所有列，可能會導致各種問題，至少風險是有的。

2.5.2 對本例實施“先拆後聚”

那麼採用先拆後聚，如何拆？拆分為5個獨立的sql，就是五個指標所對應的各自的查詢，每個指標的sql都需要攜帶平臺和日期。怎麼聚？在中間表中，以平臺、日期作為聯合唯一索引，相應的sql回來數據後以on duplicate key update的形式進行插入。

到了這裡，中間表的數據就到位了，用戶就可以基於中間表進行二次查詢了。中間表的數據往往是通過用戶的sql查詢明細數據彙總過一遍而得的彙總數據，量級一般不會很大，速度是很快的。即使由於量級大、查詢慢，耗時幾秒，在整個報表發送的過這種也是可以忽略的。

2.5.3 隨心所欲的二次查詢

中間表有了數據，用戶的二次查詢就開始了，比如比值相關的，可以直接select 某一指標列除以另一指標列，或者判斷某列分母是否為0，都是很方便的。同時，我們的目標表格中那一行彙總值，也可以由iphone和android的值彙總而來。

當然，不是所有的值都可以彙總，比如uv指標，需要去重，不能夠直接sum聚合。所以，還需要根據具體的場景進行選擇和權衡。當然，我們所說的這個例子中是可以直接做sum的。

最後，看一眼最右邊的圖，這是用戶配置這個報表中實際寫的一個查詢中間表的sql。這個sql雖然長，但是細看呢都是指標字段的select，以及除法求比值，sum求彙總，union求並集。相信，這一個功能為集群減輕查詢壓力的同時，也為用戶減輕了絞盡腦汁產出數據拼寫複雜sql的負擔。

2.6 用戶的配置

這個解決方案介紹完了。這裡再來看一眼，用戶如何配置以激活這樣的數據產出流程呢。大家可以看到上面的第一步、第二步、第三步做參考，幫助理解和加深下剛才講到的解決方案的步驟。這裡就不詳細說明了。畢竟這樣的標籤結構，是我們現有平臺的規範，相信大家如果要進行實現，一定也會有更好的配置規範。

2.7 收益與反饋

2.7.1 收益

收益方面。對於集群來講，相對減輕集群壓力、有效合理利用集群資源。自如端來講，如果用戶在需要的場景下使用了這樣的配置，會保證提交的sql相對短平快、速戰速決。對於用戶本身而言，報表發送更加準時穩定、體驗更好。

2.7.2 反饋

最下面是一個用戶曾經的反饋。左側的反饋，是說到了4個報表24個sql的查詢，通過這個解決方案的提供，優化為了4個報表4個sql查詢。4個報表串行發送，時間從原來的10min降低到了2min。大大提升了用戶的體驗。右側的反饋，是說到了中間表的高效，對於本身的二次計算和三次計算，以及多箇中間表之間的相互關聯都是很靈活方便和高效的。

3.殭屍日報例行關閉

3.1 需求場景

3.1.1 每日例行的報表發送流程

下面來看到第二個案例。看到題目就覺得很奇怪，殭屍日報？殭屍日報就是說的在我們平臺中，還在例行的、但是對於業務而言，其實是並不再需要的。

這個解決方案，是使用用戶簽到的反饋機制來實現的。用來應對不斷增多的報表、不斷增多的sql查詢。ppt中的圖片所示流程，這個流程大家也都很瞭解了。通天觸發自如，自如查詢數據以及發出報表，這個過程呢是每日例行的。

3.1.2 發現了“只增不減”的詬病

報表配置人員很辛苦，不斷擁抱變化、應對業務需求、配置新的報表。於是自如報表的報表數越來越多，這是根源。從而，與之配套而生的通天調度的任務也越來越多。於是通天每日觸發的報表數也越來越多，從而導致查詢集群的sql也是隻增不減。

查詢的sql只增不減，就會增加集群的查詢壓力，本來3個就能查詢完成、現在卻需要5個小時才能完成全部查詢，不能很好的有效利用，而且也增加了觸發性能、穩定性問題的風險，也會反過來影響自如本身的查詢過程，甚至影響報表發出的穩定性和耗時。當然，除了集群本身，這一部分不再需要的作業對通天和自如本身的佔用，也是浪費。

那麼針對圖中的三個頭疼的“只增不減”怎麼辦呢？

一個字，“減”。如何做到呢？在自如原有的方案中啊，會讓配置人員配置日報的同時手工設定一個截止時間點，就是說到了這個時間點，日報就不發了。但是經過實踐和觀察，這個時間點往往設置的不準，會導致仍需要的日報呢已經停止了，不需要的日報還在發。而且大部分用戶的需求和美好願景呢，是希望好不容易配置的這一封日報一直髮下去，直到世界末日。

於是就有必要採取另一種方案來實現“減”。

3.1.3 日報簽到機制實現“減”

所以就有了方案二。決定清理所謂的“殭屍日報”，這一部分日報清理了，從而自如作業少了、通天任務少了，那麼相應的通天回調觸發的報表就少了，查詢集群的sql就減少了。當然，用戶郵件數也會變少。那麼何為殭屍日報？前面有解釋了，即不需要的但是還仍在發送的。怎麼判斷是否需要呢？這個就需要用戶來幫忙反饋了。從而，我們就有了日報簽到機制。如圖中綠色的箭頭所示。

最終，清理這一部分“殭屍作業”，能夠幫助我們把有限的集群資源用在真正需要產出的數據和日報上。

3.2 功能預覽

簡單看一下用戶的簽到流程。在每一封發出的自如報表郵件的最下端，就會有一個簽到區域。用戶點擊後出現綠色的簽到成功。這個簽到記錄可以在平臺看到，以及平臺會提供一個簽到排行榜的頁面鼓勵用戶簽到。

就這麼簡單，用戶只需要點一下即可。當然，我們收集到這樣的簽到信息，也是報表熱度信息，基於這些熱度信息，我們也可以做一些優先級隊列等類似的功能。

3.3 例行關閉

3.3.1 簽到為“是否仍需要”做了反饋

上面講到了用戶的簽到，簽到呢對日報的“是否仍需要”做了很好的反饋。那麼下面如何根據收集到的簽到信息來判斷哪一些作業是“殭屍日報”從而關閉它們呢。ppt中的這一個圖，描述了自如發出郵件、用戶收到郵件、以及用戶簽到的流程。

3.3.2 例行判斷和關閉的邏輯

為了實現“減”，我們會每日例行這一個判斷“殭屍作業”並關閉它們的邏輯。看到流程圖中的A部分的文字，每天0點通天會調用自如，這次可不是發送日報，而是觸發自如的關閉“殭屍作業”的邏輯。

這裡需要提到的是，通天調度系統不僅為自如報表系統提供報表發送的觸發操作。而是可以為任何可以根據時間觸發和依賴觸發的任務提供調度服務機制。

途中的A部分的文字，觸發了關閉“殭屍作業”的邏輯後。圖中的B部分文字描述的邏輯，就是自如去判定殭屍作業的過程，判定的條件呢我們這邊制定了有5個，其中“30天沒有任何簽到記錄的作業”是其中最重要的一條。如果中標，代表這30天都沒有人在看，但是還在照發不誤。於是符合這5條條件的作業，就會被識別為“殭屍作業”。

於是就會由自如回調通天，關閉這些“殭屍作業”所對應的通天任務。

3.3.3 實現最終目的

那麼“殭屍作業”的通天任務被關閉了，每天活躍的通天任務就少了，從而例行的日報數減少，最終查詢集群的sql減少。確保每條sql查詢都是業務所真正需要的數據，實現了我們想要把有限的集群資源用在真正需要產出的數據和日報上這一目的。

有同學會有疑問？為什麼要關閉通天作業，而不是刪掉自如作業或者通天呢？因為還是會存在極少量的日報忘記簽到，當他們再次想起需要例行的時候，只需要把對應的通天調度任務再次打開即可。如果這個過程執行了刪除，那麼再恢復的過程可能就不是像點一下鼠標直接開啟對應任務這麼簡單了。

第五章 所見即所得的可視化配置

除了前面介紹的兩個典型的技術痛點和解決方案外，還有一個非常重要的環節。就是所見即所得的可視化配置，前面也有多次提到。本章節，相對前面來講，內容以展示為主，解決方案也比較簡單。

1.引言

老巫婆又出來了，認真的為大講解。儘管在現有的配置方式中，配置人員只需要準備好sql，然後粘貼到模板的對應位置上。但是，對於用戶而言，特別是沒有代碼背景的用戶而言，需要接觸html代碼，配置是有成本的，而且報錯了也不能很好的一眼看出。

於是另一隻巫婆提議，那麼用拖拉拽、點擊雙擊、0代碼的方式讓大家配置報表是否可以接受呢？

2.需求場景

於是基於用戶的學習、配置、調試和維護成本的考慮，以及RD的客服和培訓成本，也為了給刀耕火種的自如報表系統帶來一個質的飛躍，我們開發了所見即所得的可視化報表系統。

目的就是讓專業的人做專業的事。報表配置人員只需要集中精力寫好業務sql，其它的交給我們。

3.功能界面

3.3.1 基礎頁面

既然談到了可視化。這裡又是一波可視化的前端界面的展示。大家可以大致看一看都有什麼內容，希望與大家能夠得到共鳴，或者有寶貴的建議也可以反饋給我們。

這一張ppt是整個自如可視化的頁面，每個部分的功能都有詳細的介紹，一看就懂，不多敘述。

3.3.2 佈局面板

這是可視化的兩塊重點的配置區域。從E區選擇所需的組件，鼠標左鍵按住不放，拖拽到D區域鬆手。在D區域可以對已有的組件進行任意的拖拽、排布和縮放，為更加定製化、個性化的報表奠定基礎。

3.3.3 三步走生成一個報表

瞭解了界面的基本組成後，下面看到配置一張報表的三步。與之前第二章介紹的三步走來說可謂簡單了很多。第一步，拖拽組件；第二步，雙擊打開粘貼入sql；第三步，預覽模板輸出最終數據。

3.3.4 一張發出的報表

比如舉個例子，用戶拖拽的如本頁ppt右上角的一個佈局，以及編輯了每個組件裡面的sql後，最終渲染髮出來的報表郵件是這樣的。最上面兩個表格，中間一個折線圖，最下面三個扇形圖。

而整個配置過程，不需要任何的代碼，html、css都不需要。需要的只是你準備好sql，雙擊組件，粘貼進去，保存即可。

3.3.5 個性化的細節控制一

那麼什麼代碼都不需要了，用戶如何進行一些細節控制呢？如何對數值進行格式化控制、如何設置表格的字體和字號呢？如本頁ppt所示，這都是支持的。

只需要用戶動動鼠標進行勾選。完全替代了以往的代更火種的html和css的代碼的形式。當然，用戶也完全可以不需要這樣的配置，粘貼sql出報表即可，這些屬於美化的細節功能。

3.3.6 個性化的細節控制二

同樣的，本頁面也展示了其它的細節控制。比如上圖，是附件設置和表格顏色背景色的設置。下圖是折線圖、柱狀圖以及扇形圖的圖形選項的設置，如是否顯示圖例、x軸標籤是否傾斜。

3.3.7“先拆後聚”仍可只關注sql

還記得我們前面提到過的SQL拆解聚合，用戶以前需要寫較長的html代碼配置。儘管有預置模板（默認模板），儘管用戶只需要關注sql本身，但是面對冗長的html還是增加了學習和配置成本。所以，這裡我們為這個配置提供瞭如本頁ppt所示的配置方式，用戶只需要粘貼三個sql即可，拆解過程和識別輸出列等過程都是系統自動實現的。

比如右下角的圖，當用戶粘貼進三個拆解後的小sql之後，系統會自動識別出來哪些是維度列、哪些是指標列。如大圖所示，當用戶粘貼入查詢中間表的最終sql後，右邊會自動識別用戶即將要輸出的列，就是圖中哪些五彩斑斕的矩形色塊。

到這裡，可視化的界面功能就展示完了。下面簡單看一下設計與實現。

3.4 簡單架構圖

首先看到一個簡單的架構圖。按照ABC的順序。

A圖中，整個圖形交互層由前端UI和圖形化的服務端支持兩部分，前端UI當然是提供給用戶，用戶可以在瀏覽器中進行操作，就像前面所講到的拖拉拽、擺放和縮放。然後整個圖形交互層整體依賴於底層通用服務，以及數據查詢。

對於這裡提到的底層通用服務和大數據查詢引擎怎麼理解呢？看到B圖，大數據查詢引擎，就是說給它sql、它吐出數據；底層通用服務支持就是自如的底層服務，就是以往的刀耕火種的配置模式，給它配置，它吐出報表郵件。

所以，這個可視化做的就是在以往的基礎服務之上做了一層用戶交互。這裡的“圖形交互層”再接下來兩頁重點討論下，也很簡單。

3.5 圖形層交互流程圖

結合上一頁說到的圖形交互層，如本頁ppt右上角。對應到用戶操作流程和服務器流程上來看，就是ppt中那個帶箭頭的流程圖，藍色背景的區域就是用戶的操作，進行組件和佈局的拖拽配置。黃色背景的區域是服務端的動作，進行解析和持久化。其它透明部分，同以前的方式一樣，配置作業、通天調度。比較好理解。

3.6 服務端持久化流程圖

前端配置剛才通過功能展示看到了，也瞭解了與服務端的交互。這裡簡單看一下服務端持久化用戶組件和持久化用戶佈局的過程。

對於用戶組件和用戶佈局的保存請求，服務端首先進行參數的接收，然後進行一些校驗和處理，最後存儲到數據庫的兩張實體表中。

進行這樣結構化存儲的好處，是方便用戶對組件和佈局進行修改。但是當報表發送的時候，為了兼容底層，還是會把配置轉化為一個html文件，傳入底層進行處理和發送。

3.7 收益

本章最後，看一下可視化這一舉措所帶來的收益。通過前面的講解，相信大家也深有體會。降低了用戶的配置成本、降低了RD研發人員的教學成本，以及交互性、易用性的提升，真正的實現了平臺化。從刀耕火種的模板時代完成了到可視化的質的飛躍。

第六章 總結與答疑

1.回到出發點

所有分享的內容到這裡就結束了。讓我們回到最初的出發點，看到那三個無奈的表情包。你是否有人工寫sql查詢、人工導出數據、人工郵件以及人工美化的工作需求呢？需要每天佔用一部分維護時間，或者需要早起呢？如果有這樣的場景，不妨考慮下這個系統。希望通過今天的分享，能夠解決大家的一部分困惑，哪怕是一個小點的共鳴。

2.問題與答疑

最後，是問題與答疑時間。大家關於講到的功能點和解決方案有什麼不理解的地方或者更好的建議，歡迎暢談。

本次分享到此結束，感謝大家的支持。謝謝大家！