一种统计度量中数据高效处理方法

统计度量在网管系统越来越重要，丰富多样的展现形式让信息更加直观明了。数据源是呈现的根基，存储过程提供了灵活、简单的通用数据处理方式，且不依赖于服务端的语言，为统计度量提供可靠的数据源。本文结合某自研项目的实战，分享存储过程的应用技巧和心得。...



文章简介

统计度量在网管系统越来越重要，丰富多样的展现形式让信息更加直观明了。之前介绍的D3很好的解决了呈现问题，然而巧妇难为无米之炊，数据源是一切呈现的根基，存储过程提供了灵活、简单的通用数据处理方式，且不依赖于服务端的语言(C/JAVA等)，为统计度量提供可靠的数据源。本文结合某自研项目中的实战，分享存储过程的应用技巧和心得。

背景

定做合身的服饰，需要全面、准确的指标参数，细节成就品质。设计丰富多样的统计报表，同样需要全面、准确的数据源，依据保障可信。根据业务复杂度，当前主要采用如下两种方式进行业务统计：

针对逻辑比较简单的业务统计，在服务端写sql，该方法要求对服务端的语言熟悉；

针对复杂业务统计，在服务端嵌套多层sql也可行，但代码复杂度高，调试繁琐，调整也非常困难，存储过程运行在数据库中，在sql的基础上增加了预处理、逻辑判断等，调试、维护非常方便。但是存储过程对数组、结构体等复杂数据结构支持不够，多出参处理更加麻烦。

解决思路针对上述复杂业务统计中多出参处理的问题，本文推荐两种方式来解决：

1. 合并参数

约定好数据内容，用分隔符(如：a1,a2,an;b1,b2,bn;c1,c2,cn)拼成长字符串，数据使用者再对字符串进行拆分。

2. json数组

利用临时表，在服务端做简单处理成json格式，可直接应用。实践情况

1. 开发环境

为方便举例，本文结合自研项目的实战进行详细说明。

开发框架：Express(一个基于 Node.js 平台的极简、灵活的 web 应用开发框架，它提供一系列强大的特性，帮助你创建各种 Web 和移动设备应用。)

服务端：Nodejs+ MySQL，引入数据库插件sequelize(提供对 MySQL，MariaDB，SQLite 和 PostgreSQL 数据库的简单访问，通过映射数据库条目到对象，或者对象到数据库条目。简而言之，就是 ORM(Object-Relational-Mapper))

客户端：js

2. 合并参数

此方案为传统做法，只简单介绍下基本步骤。

一个存储过程的出参不能太多，可以将一些参数拼成字符串作为一个出参。下图为存储过程核心代码，红框中指出来拼串过程。下图为服务端调用存储过程代码：下图为客户端获取的数据，outMonthAvgList就是拼成的长字符。此数据无法被直接用来呈现。拼串增加了客服端的逻辑处理，并且分割字符串，给数据源的可靠性带来隐患。

3. Json数组

下面介绍下改进的方案：

（一）存储过程处理流程

--   创建存储过程，不定义出参；

--  创建临时表，根据需要返回的统计参数来定义临时表列名；

--  向临时表中插入统计数据；

--  SELECT输出临时表所有数据。返回结果如下图：（二）服务端Nodejs处理流程

--  调用存储过程；

--  生成json格式，将存储过程中不连续的日期补齐，保证是该月完整的数据(本可以在存储过程中处理，但是频繁的insert操作效率低)；（三）客户端处理流程

有了下图所示数据，直接将response的值拿来即用(如不满意，重新定义json格式，再取一遍数据)。4. MySQL存储过程实战小技巧

--工具。工欲善其事，必先利其器。Navicat for MySQL让编辑和执行存储过程变得easy。

--  临时表。虽说MySQL 能自动销毁临时表，但作为严谨的工程师，不会随便留下垃圾。每个临时表名都用时间戳做后缀(保证唯一性)，第一插入数据前清空表格，用完删除表格(特殊情况除外)。尽量减少表格的更新操作(比较耗时，数据的预处理工作量大，可在服务端进行)。-- 输出要唯一。调试时，可以有多个SELECT输入。最终一个存储过程只能有一次输出(类似return)。

--  时间周期处理。尽量用库函数，近一星期(绿框)，某月(红框，如果是不是当前月，天数固定；如果是当前月，则只能统计到当天)。--  空、NULL、‘’。三个值互不相同，作为判断条件，会带来未知隐患。聪明的工程师采用排除法，先统计总数，再统计已知类型，总数减去已知就是那些干扰项数。

--- 游标。多记录操作利器，和临时表异曲同工。当记录比较少时(据说万以下)，两者效率相差不大，当记录多时，临时表有优势。没有标准，当发现存储过程比较慢时，可以作为一个优化方案。--  函数。如何一个方法在多处用到，可以提取成函数。

实践效果存储过程保留sql高效的基础上，提供了丰富逻辑判断，减少了多层嵌套，调试非常方便。

通过临时表返回多出参方案，克服了存储过程变量类型缺陷，减少了服务端和客户端耦合，代码维护难度降低。Web侧只需切换数据源，便可展现不同统计项。

推广建议

本文实践经验来源于自研项目，且经过验证可行，有效；统计度量形式多样，采用存储过程可以快速的响应新需求，为功能模板化提供便捷。存储过程运行在数据库中，只要能访问数据库，不依赖于服务端语言，为代码移植减少障碍，是一种通用的数据处理方式。当前主流关系数据库基本支持存储过程，值得推广。

    关注 WXCOP