CN111126026B - 将sql语句解析生成可视化报表的方法和工具 - Google Patents

Abstract

本发明公开了一种将SQL语句解析生成可视化报表的方法，包括以下步骤：以预定的规则编辑SQL语句；根据预定的规则利用正则算法拆分所编辑的SQL语句，识别出不同的功能子句，其中包括：select查询子句，识别select查询子句中的字段和维度；和/或where条件子句，所述where条件子句中包括至少一组动态可变字段，识别所述至少一组动态可变字段和维度。将所述字段按维度展示为用户筛选组件，根据用户的筛选，将查询出的数据生成可视化报表。本发明还公开了基于上述方法的工具，电子设备与存储介质。

Description

将SQL语句解析生成可视化报表的方法和工具

技术领域

本发明涉及计算机的报表生成领域。更具体地，涉及将SOL语句自动解析生成可视化报表的方法和工具，以及相关电子设备和存储介质。

背景技术

SQL是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

SQL语句是类似于这样的形式：

SELECT*FROM Websites WHERE country＝‘CN’。

其中，select表示查询，where表示查询的条件，Websites是数据库中的数据表格。这个语句的含义就是从Websites数据表格中查询符合where条件的数据，其中，where条件为country(国家)列的数据为CN(中国)。

这些数据被查询出来之后，需要用可视化报表的形式展示出来，以供相关人员使用。而市场业务发展变化快需求变化大，而SQL数据分析师分析完数据后，需要通知BI(商业智能)专职研发人员定向研发可视化报表。可视化报表经过开发、测试、上线，整个周期费时耗力。

如果能够自动生成可视化报表，就能节约研发人员工作量，并且能够及时生成报表，提高效率。

将查询出来的数据生成可视化报表，如图1所示，按照行列生成可视化数据报表，是比较容易的。

但是，如果SQL语句中的where条件为动态可变的，如具有多个条件。或者select查询内容也有多个，可以进行选择。在这种情况下，不同的选择会导致SQL语句的查询结果不同，没有办法直接生成可视化数据报表。

这时候，需要用户在界面上进行where条件选择，或select查询内容选择，然后再根据选择结果生成显示查询结果的可视化报表。

例如，当SQL语句是这样的：

SELECT t.id，t.name，t.statu，t.typ

FROM persona_tag t

WHERE t.statu＝#{statu}

</if>

</if test＝”statu＝null”>

AND t.id＝#{termid}

此时，查询内容为四种，分别是t.id，t.name，t.statu，和t.typ。查询条件有两种，用and或者or分割开，这里是用and分割开。那么用户在查询的时候，就存在不同查询内容与不同条件的4X2＝8种组合可能。

那么如何在自动生成可视化报表的时候，能够自动识别出可供选择的内容，并且供用户选择呢？这是自动生成可视化报表时，亟待解决的技术难题。

发明内容

本发明的一个目的是至少解决上述问题，并提供后面将说明的其它优点；

本发明另一个目的是提供一种基于SQL语句的可视化报表生成方法，不仅能够将静态SQL语句生成可视化报表，还能够将动态SQL生成可视化报表；

本发明的又一个目的是提供一种基于SQL语句的可视化报表生成方法，能够能够提高生成可视化报表的效率，并且供用户选择报表生成内容；以及

本发明的再一个目的是提供将SQL语句解析生成可视化报表的工具，电子设备，以及存储介质，以实现上述方法。

为了实现根据本发明的这些目的和其它优点，提供了一种将SQL语句解析生成可视化报表的方法，包括以下步骤：

以预定的规则编辑SQL语句；

根据预定的规则利用正则算法拆分所编辑的SQL语句，识别出不同的功能子句，其中包括：

select查询子句，识别select查询子句中的字段和维度；和/或

where条件子句，所述where条件子句中包括至少一组动态可变字段，识别所述至少一组动态可变字段和维度；

将所述字段按维度展示为用户筛选组件，根据用户的筛选，将查询出的数据生成可视化报表。

通常，SQL语句是数据查询语句，里面必然含有select查询子句，而条件语句则是可选的。除了select语句和where语句，SQL语句中还可能包括left join外链这样的子语句，或者group分组这样的子语句，或者order排序这样的子语句。由于这样的语句，不涉及选择问题，因此不作为筛选组件中的可选内容。

在上述技术方案中，SQL语句要以预定的规则编辑，这样方便后续的拆分和识别。

例如，在select子句中，多个查询并列项之间，规则约定用“，”逗号间隔开，在后续拆分的时候，根据这个规则约定，就可以拆分出并列项。如，SELECT A，B，C，就可以拆分出3个并列项，也就是A，B，和C三个字段，并且知道select的维度为3。

例如，在where子句中，多个条件并列项之间，规则约定用“and”或“or”间隔开，在后续拆分的时候，根据这个规则约定，就可以拆分出并列项。如，WHERE A＝A`and B＝B`，就可以拆分出2个并列项，也就是A＝A`和B＝B`两个字段，并且知道where的维度是2。

然后，为用户展示筛选组件的页面，这个页面可以是最终展示的可视化报表的上部，如图2所示，图2是个显示页面，页面上部的选择框就是供筛选组件所用。也可以是个单独的页面，供用户选择。用户选择之后，在另一个新页面中，显示根据选择，查询出的数据结果。

在上面的例子中，用户的筛选组件中包括A，B，和C三个字段，或者还包括A＝A`和B＝B`两个字段。因为分属的种类不同，A，B，和C三个字段可以在一个筛选组件中选择，例如客户名称这个筛选组件。而A＝A`和B＝B`两个字段可以在另一个筛选组件中选择，例如时间这个筛选组件。维度其实就是选项。

假定用户选择了A客户和2019年，则最终可视化报表显示的就只是A客户在2019年的所有购物数据。也就是说，通过选择select中的部分内容，实现了要查询的内容是可选的。不仅仅是where条件可选，要查询的select内容也可选。

所述where条件子句中包括至少一组动态可变字段，这是指where中的条件可能会分为两种，一种是不能取消的，是不变的，而另一种是可选的，这种被称作是动态可变的。

例如，还是考虑上面那个查询A客户在2019年的所有购物数据这个例子，如果where条件中包括两个条件，第一个条件是要求金额数据为正(就是说，只包括A客户的购买金额，不包括给A客户返还的金额)，这个条件是不能选择去除的；而第二个条件是年份，如2018年或2019年，则第一个条件是非动态可变字段，而只有第二个条件是动态可变字段。做成可视化报表之后，只有第二个字段是可以进行选择的。

那么，如何知道第一个条件是非动态可变字段呢？是因为在编辑SQL的时候，是按照预定的规则编辑的，预定的规则中，包括区分动态可变字段和非动态可变字段。因此在后续的拆分中，就能够识别出动态可变字段与非动态可变字段。

如果where条件中不含动态可变字段，那么就不必用筛选组件进行where条件筛选了。或者说，筛选组件中的条件筛选内容为单一固定值。同样，如果select查询中只有一个查询选项，则筛选组件中的查询筛选内容也为单一固定值。这样就相当于可视化报表显示了静态SQL语句。所以说，本发明既能显示为静态SQL语句的可视化报表，也能显示为动态SQL语句的可视化报表。

所述的将SQL语句解析生成可视化报表的方法中：所述以预定的规则编辑SQL语句为通过让用户输入约定的模板SQL来实现。

在制定预定规则时，可以通过约定的方式，也可以通过设置模板SQL，让用户将开发的SQL语句填充在模板SQL中，来实现满足预定规则。

所述的将SQL语句解析生成可视化报表的方法中：在功能子句中包括where条件子句的情况下，还包括以下步骤：

丢弃识别出的所有动态可变字段，形成待验证SQL语句，利用durid数据库连接池的sqlutils进行SQL语句验证；

若验证无误，则继续生成可视化报表；若验证有误，则暂停可视化报表的生成，而将以预定的规则编辑的SQL语句退回。

Where条件子句中可能有动态可变字段，就是可供用户选择的字段，也可能有静态字段，就是固定不变，不能更改的字段。以预定的规则编辑的SQL语句中应当用特定字符区分了这两种字段，因此在系统用正则算法拆分SQL语句的时候，也能够识别出这两种字段。由于动态可变字段的不确定性，在进行SQL语句验证的时候，去除了where条件子句中的动态可变字段。之后，再利用阿里巴巴公司的durid数据库连接池的sqlutils进行SQL语句验证。如果验证无误，则说明开发者是按照预定的规则编辑的SQL语句，如果验证有误，则说明开发者没有按照预定的规则编辑SQL语句，因此退回给开发者，重新编辑。

验证的时候，可以只验证select查询子句中字段与维度是否与系统用正则算法拆分出的select查询子句中字段与维度相同。若相同，则判定为验证无误，若不同则判定为验证有误。

所述的将SQL语句解析生成可视化报表的方法中：在功能子句中还包括：

group分组子句，order排序子句，以及jion链接子句；

在丢弃识别出的所有动态可变字段时，将功能子句中的其余子句进行拼接，形成形成待验证SQL语句。

例如，在对按照预定的规则编辑的SQL语句进行正则算法拆分时，分析到子功能语句包括：select查询子句，where条件子句，group分组子句，和order排序子句，其中where条件子句中的条件字段均为动态可变字段。丢弃所有动态可变字段后，将select查询子句直接与group分组子句拼接在一起，就构成了待验证SQL语句。如果where条件子句中没有运行任何动态变量，则没有where条件子句的筛选组件，相当于where条件子句部分属于静态。这时候，就不需要丢弃动态可变字段，而按照预定规则编辑的SQL语句本身就是待验证SQL语句。

所述的将SQL语句解析生成可视化报表的方法中，识别select查询子句中的字段和维度的步骤中，还包括记录被识别字段的所占槽位；和/或识别所述至少一组动态可变字段和维度的步骤中，还包括识别静态字段，以及记录所有被识别字段所占槽位；

将查询出的数据生成可视化报表的步骤中，将用户筛选出的字段归入相应的槽位处，来生成可视化报表。

例如，SQL语句是这样的：

SELECT t.id，t.name，t.statu，t.typ

FROM persona_tag t

WHERE t.statu＝#{statu}

</if>

</if test＝”statu＝null”>

AND(t.id＝#{termid}

AND t.name＝#{page})

其中，where条件子句里面含有括号，括号中的条件字段具有优先级。那么记录槽位就是记录条件字段所处的优先级状况，或者说是where条件子句中，所有条件字段的优先级排序。这样，在用户选择条件字段的时候，就可以准确地获知条件字段所处的优先级，而不会错误地组合条件。

所述的将SQL语句解析生成可视化报表的方法中：在功能子句中包括select查询子句和where条件子句的情况下，利用durid数据库连接池的sqlutils进行SQL语句验证的步骤包括：

利用durid数据库连接池的sqlutils识别select查询子句中的字段和维度；

与利用正则算法拆分而识别出的select查询子句中的字段和维度进行比较；

若两者相同，则验证无误；若两者不同，则验证有误。

所述的将SQL语句解析生成可视化报表的方法中：所生成的可视化报表中设置有列排序功能。

就是说，即便SQL语句中没有order排序子句，也可以在生成可视化报表的过程中，加上主列排序功能。

所述的将SQL语句解析生成可视化报表的方法中：

识别select查询子句中的字段和维度的步骤中，还包括记录被识别字段的列名；和/或识别所述至少一组动态可变字段和维度的步骤中，还包括记录被识别字段的列名；

将查询出的数据生成可视化报表的步骤中，为所述列名设置别名，将别名显示在筛选组件中。

例如，在数据库表格中，字段列名为ADD，而在自动生成可视化报表时，则应该显示供用户选择的是ADD的实际含义，例如为地址。这是如何实现的呢？是通过为列名“ADD”设置别名“地址”，在生成可视化报表的时候，使用别名，来显示筛选组件中的选项。

实际上，别名除了可以显示在筛选组件中，在筛选数据得到的列头(列名)处，也可以使用别名。就是说，系统将SQL数据库内的表格的列头换成开发者预先设置的别名。而在模板SQL中，可以设置有列名和别名对应的字段。

所述的将SQL语句解析生成可视化报表的方法中：所述用户筛选组件的界面包括：输入框、选择框、多选框、和/或树形级联。

所述的将SQL语句解析生成可视化报表的方法中：所生成的可视化报表的界面包括线性图、堆叠图、柱状图、和/或饼图。

本发明还提供了将SQL语句解析生成可视化报表的工具，用于实现上述任一项所述的方法。

本发明还提供了一种电子设备，包括：至少一个处理器，以及与所述至少一个处理器通信连接的存储器，其中，所述存储器存储有可被所述至少一个处理器执行的指令，所述指令被所述至少一个处理器执行，以使所述至少一个处理器执行上述任一项所述的方法。

本发明还提供了一种存储介质，其上存储有计算机程序，该程序被处理器执行时，实现上述任一项所述的方法。

本发明至少包括以下有益效果：1)能够将静态SQL语句或动态SQL语句自动生成可视化报表；2)提高生成可视化报表的效率；3)能将SQL语言中的条件变量自动生成筛选组件，以供用户选择使用；4)能用多种方式显示筛选组件以及报表界面；5)能够识别复杂条件的SQL语句中各条件之间的关系，这样当进行条件选择时，能够准确获得查询结果；6)能够自动检查SQL语句是否符合预定规则，如果不符合，则返回，提高了工作效率。

本发明的其它优点、目标和特征将部分通过下面的说明体现，部分还将通过对本发明的研究和实践而为本领域的技术人员所理解。

附图说明

图1为表达背景技术的可视化报表的示意图；

图2为本发明能够自动生成筛选组件的可视化报表的示意图；

图3为本发明自动生成可视化报表的流程示意图；以及

图4为本发明可视化报表从SQL语句到生成，再到应用的流程示意图。

具体实施方式

下面结合附图对本发明做进一步的详细说明，以令本领域技术人员参照说明书文字能够据以实施。

应当理解，本文所使用的诸如“具有”、“包含”以及“包括”术语并不配出一个或多个其它元件或其组合的存在或添加。

首先进行术语解释：

SQL：是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

Druid：是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、PROXOOL等DB池的优点。

下面详细说明本发明：

开发者开发SQL语句，主要是用来查询数据的。数据存储在SQL数据库的数据表中，通过SQL语句进行数据筛选。但是，如果想把筛选出来的数据给用户看，则需要再额外开发能够展示筛选数据的可视化报表。

如果是静态SQL，这个问题比较好解决。因为静态SQL中不涉及到变量，所筛选出的数据就是一种。那么把这一种数据用表格或者图表展示出来就可以。但是静态SQL的用途非常受限，要想灵活应用，还是要开发动态SQL。

但是动态SQL中涉及到变量，如果是人工开发可视化报表，就需要人为地把变量摘出来，供用户选择。但如果想主动生成可视化报表，则需要系统主动识别出哪些是变量，并且把变量列出来，供用户选择。

至于本发明中用到的阿里巴巴公司的durid数据连接池，是用它的连接池功能避免自己写一套连接DB的代码，对后台计算后形成的验证sql进行模拟执行可以有效提醒sql使用人员他提供sql基本结构是否符合sql的规则。

为此，本发明使用约定特殊占位符方式收录数据分析人员提供的SQL，使用正则算法逻辑将可变部分识别并做适当替换后，采用durid进行sql语句正确性验证，验证正确后进行sql语句解析形成可用维度输出供数据分析人员编辑使用，编辑完毕后自动生成可视化报表与对接配置信息。数据分析人员就是用户，所谓编辑，就是数据分析人员选择变量，也就是通过筛选组件选择查询字段或条件字段。

因为报表是有筛选条件的，当用户使用了这个筛选项才使用这个维度进行做where未选择的条件要自动截取掉对应设定的条件保证其维度不参与where计算。

如图3所示，在获得开发者提供的SQL模板后，首先进行SQL语句拆分。本发明给出的方法是利用正则算法进行拆分。拆分出SQL中的所有子句，并且识别出select查询子句中的查询字段和维度。查询字段就是指都有哪些查询字段，维度就是指有多少个查询字段。还要识别出where条件子句中的条件字段和维度。条件字段就是指都有哪些条件字段，维度就是指有多少个条件字段。并且还要识别出条件字段中，哪些是静态的条件字段，哪些是动态的条件字段。去掉动态的条件字段，就把动态SQL变成了验证SQL。识别输出字段，就是识别在验证SQL中，select查询子句中的查询字段和维度。把验证SQL中识别出的select查询子句中的查询字段和维度与SQL语句拆分中得到的select查询子句中的查询字段和维度进行比较。如果无误，则形成动态SQL，实际上就是确认这个动态SQL无误。

验证利用的就是阿里巴巴公司的durid数据连接池进行验证，目的是避免自己再开发一套数据库连接工具。

验证无误后，将查询字段和动态条件字段按照各自的维度均展示给用户。例如，查询字段有三种，维度就是三，把这三种字段都展示给用户。条件字段有五种，其中静态两种，动态三种，则维度也是三，把这三种动态自动都展示给用户。

展示的方法可以是用最终生成的可视化报表的一部分展示，通常为上部，也可以单独弹出一个界面进行展示。展示的目的是为了让用户能够选择。用户选择之后，根据选择结果，进行数据筛选，将筛选结果用表格，或者其它可视化图形，如柱状图，饼状图之类的图形，展示出来。就是形成了用户界面。最终形成报表结构数据。

图4显示了从输入SQL语句到BI(商业智能)的示例性过程。其中包含以下步骤：

步骤一：用户输入约定的模板SQL；

步骤二：(条件转换)接收模板SQL，按约定规则进行正则化计算，形成SQL语句块结构；

例如：1、SELECT块，对应块在整体SQL中所占槽位

2、WHERE块，对应块在整体SQL中所占槽位

3、GROUP块，对应块在整体SQL中所占槽位

4、ORDER块，对应块在整体SQL中所占槽位

步骤三：(条件转换)使用SELECT块与GROUP块进行拼接形成验证SQL；

步骤四：(验证)使用Durid进行SQL验证，并得到SQLStatement集合，验证失败则返回错误信息至界面提示用户按错误信息修改；进行验证的一个主要目的是为了防止开发者在select查询子句中写出*这样的通配符。这个通配符的含义是查询所有。如果写成*这样的通配符，则筛选组件就不能拿到有效的select维度，不能真正去模拟执行。

步骤五：(维度识别)根据STEP2中获取SELECT块集合，结合SQLStatement集合识别所有可显示维度字段；

根据SELECT块自动计算可被用来分组的所有维度集合供后续GROUP块填充匹配使用；

步骤六：(条件识别)根据STEP2中WHERE块识别出所有去重后的条件维度字段；

步骤七：根据以上计算数据形成最终完整版动态SQL模板数据,将计算结果数据返回界面；

步骤八：报表名称定义

(维度可视化定义)根据SELECT识别的维度集展示成列表，供用户定义字段显示别名，勾选是否默认初始显示，是否支持透视(默认界面显示为全部支持透视)；

后台根据用户设置持久化，未设置别名的即使勾选设置，此维度也将不能用于列头展示或透视，必须定义别名其它配置才能生效。

步骤九：(条件组件选取)根据WHERE识别的维度集列表展示，供用户定义对应维度报表界面使用的筛选组件(如输入框、选择框、多选款、树形级联等)；

步骤十：(可视化图形选择)选择可用的charts(基于echarts)用户根据SELECT中识别的维度信息选择X轴维度、Y轴维度，定义维度title，报表title(目前支持线图、堆叠图、柱状图、饼图，每种图形需要独立开发支撑后才能供界面选择使用)；

步骤十一：以上各个步骤均会默认暂存(持久化)对应解阶段计算结果，提交报表设定后则修改状态，只有处于提交状态的自定义报表才会进入自定义报表发布队列，进入发布队列后相应报表才可以具有发布功能；

步骤十二：点击发布操作后，自动将报表链接、报表名称、可配维度传递到BI资源库，进入BI权限管理范畴。

本发明还提供了基于上述方法的工具，电子设备与存储介质。

本发明本质上是商业版在线数据的可视化，本发明技术方案至少带来以下有益效果：

1、提升了可视化报表输出效率；

2、数据分析人员通过SQL可自行生成分配报表；

3、减少BI研发人员工作量，节省人力；

本发明能够：

1、完整SQL转换为可支持魔方分析数据效果的动态SQL

2、SQL解析出可视化维度

3、通过自定义编辑定义维度自动生成可视化报表

尽管本发明的实施方案已公开如上，但其并不仅仅限于说明书和实施方式中所列运用，它完全可以被适用于各种适合本发明的领域，对于熟悉本领域的人员而言，可容易地实现另外的修改，因此在不背离权利要求及等同范围所限定的一般概念下，本发明并不限于特定的细节和这里示出与描述的图例。

Claims (11)

1.将SQL语句解析生成可视化报表的方法，其特征在于，包括以下步骤：

以预定的规则编辑SQL语句；

根据预定的规则利用正则算法拆分所编辑的SQL语句，识别出不同的功能子句，其中包括：

select查询子句，识别select查询子句中的字段和维度；和

where条件子句，所述where条件子句中包括至少一组动态可变字段，识别所述至少一组动态可变字段和维度；

将被识别的字段按维度展示为用户筛选组件，根据用户的筛选，将查询出的数据生成可视化报表；

在功能子句中包括where条件子句的情况下，还包括以下步骤：

丢弃识别出的所有动态可变字段，形成待验证SQL语句，利用durid数据库连接池的sqlutils进行SQL语句验证；

验证的步骤包括：利用durid数据库连接池的sqlutils识别select查询子句中的字段和维度；与利用正则算法拆分而识别出的select查询子句中的字段和维度进行比较；若两者相同，则验证无误；若两者不同，则验证有误；

若验证无误，则继续生成可视化报表；若验证有误，则暂停可视化报表的生成，而将以预定的规则编辑的SQL语句退回。

2.如权利要求1所述的将SQL语句解析生成可视化报表的方法，其特征在于：所述以预定的规则编辑SQL语句为通过让用户输入约定的模板SQL来实现。

3.如权利要求1所述的将SQL语句解析生成可视化报表的方法，其特征在于：在功能子句中还包括：

group分组子句、order排序子句以及jion链接子句；

在丢弃识别出的所有动态可变字段时，将功能子句中的其余子句进行拼接，形成待验证SQL语句。

4.如权利要求1所述的将SQL语句解析生成可视化报表的方法，其特征在于：

识别select查询子句中的字段和维度的步骤中，还包括记录被识别字段的所占槽位；和识别所述至少一组动态可变字段和维度的步骤中，还包括识别静态字段，以及记录所有被识别字段所占槽位；

将查询出的数据生成可视化报表的步骤中，将用户筛选出的字段归入相应的槽位处，来生成可视化报表。

5.如权利要求1所述的将SQL语句解析生成可视化报表的方法，其特征在于：所生成的可视化报表中设置有列排序功能。

6.如权利要求1所述的将SQL语句解析生成可视化报表的方法，其特征在于：

识别select查询子句中的字段和维度的步骤中，还包括记录被识别字段的列名；和识别所述至少一组动态可变字段和维度的步骤中，还包括记录被识别字段的列名；

将查询出的数据生成可视化报表的步骤中，为所述列名设置别名，将别名显示在筛选组件中。

7.如权利要求1所述的将SQL语句解析生成可视化报表的方法，其特征在于，所述用户筛选组件的界面包括：输入框、选择框、多选框、和/或树形级联。

8.如权利要求1所述的将SQL语句解析生成可视化报表的方法，其特征在于，所生成的可视化报表的界面包括线性图、堆叠图、柱状图、和/或饼图。

9.将SQL语句解析生成可视化报表的工具，用于实现权利要求1-8中任一项所述的方法。

10.电子设备，其特征在于，包括：至少一个处理器，以及与所述至少一个处理器通信连接的存储器，其中，所述存储器存储有被所述至少一个处理器执行的指令，所述指令被所述至少一个处理器执行，以使所述至少一个处理器执行权利要求1-8中任一项所述的方法。

11.存储介质，其上存储有计算机程序，其特征在于，该计算机程序被处理器执行时，实现权利要求1-8中任一项所述的方法。

Images