大家晚上好，我是百度外卖·研发中心·大数据研发部的高级研发工程师，刘海宇。今天有幸为大家分享下我们大数据部门的一个报表系统。

大家应该都看到了图中的彩色字体，“自如报表”、十分醒目。这个“自如”在我们今晚的分享中是啥呢？不是租房、不是搬家，这个“自如”呢其实是我们马上要介绍的这个报表系统的项目代号。

为什么叫“自如”呢？来自首席架构梁福坤赋名，抽象自《金刚经》第三十一品 知见不生分，“于一切法，应如是知，如是见，如是信解，不生法相。”，初衷想让我们的报表系统可以做到：展示灵活、数据源灵活，不要拘相。这种平台化交付设计在百度外卖大数据平台中体现的淋漓尽致，通过我们本节课的介绍，大家会有较深刻体会。

下面来看下本次课程的一个简要的介绍。从上一页的大标题也看出来了，本次课程会紧密围绕“报表”两个字来展开，数据报表、报表平台，离不开“报表”两个字。

课程内容

如本页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.问题与答疑

最后，是问题与答疑时间。大家关于讲到的功能点和解决方案有什么不理解的地方或者更好的建议，欢迎畅谈。

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