Halo数据库WITH语句的功能及增强

基本概念

  处理不确定深度的层级结构，比如组织机构，一个常用的设计是在一张表里面保存 ID 和 Parent_ID ，并且通过自连接的办法构造一棵树。这种方式对写数据的过程很友好，但是查询过程就变得相对复杂。Halo数据库提供了WITH语句，允许构造用于查询的辅助语句。这些语句通常称为公共表表达式(Common Table Expressions)cte。cte类似于只在查询执行期间存在的临时表。递归查询是指递归CTE的查询。

递归CTE有三个元素:

1.非递归项:非递归项是CTE查询定义，它构成CTE结构的基本结果集。

2.递归项:递归项是使用UNION或UNION ALL操作符将一个或多个CTE查询定义与非递归项连接起来。递归项引用CTE名称本身。

3.终止检查:当上一个迭代没有返回任何行时，递归将停止。

样本结构

异常报错

  如果不使用recursive关键字，union all连接的cte表达式不能自动递归，所以在汇总查询时不能使用上面的结果集。

问题解决

  经过我们对SQL标准进行了增强，对内核的重新改进之后，现已能在特定条件下实现自动递归，支持复杂的递归with查询语句。

执行过程  

1）执行non-recursive term。（如果使用的是union而非union all，则需对结果去重）其结果作为recursive term中对result的引用，同时将这部分结果放入临时的working table中

SELECT employee_id, last_name, manager_id, 0 reportLevel FROM employees WHERE employee_id = 11

2）执行recursive query。重复执行如下步骤，直到working table为空：用working table的内容替换递归的自引用，执行recursive term，（如果使用union而非union all，去除重复数据），并用该结果（如果使用union而非union all，则是去重后的结果）替换working table

SELECT e.employee_id, e.last_name, e.manager_id, reportLevel+1FROM reports_to_11 r, employees e WHERE r.eid = e.manager_id

3）继续执行recursive query，直到结果集和working table为空

4）结束递归，将前三个步骤的结果集合并，即得到最终的WITH RECURSIVE的结果集。

递归条件

对于是否使用recursive关键字，我们目前使用参考的规则如下。

目前已经支持的递归规则如下：

1）对于查询结果集中使用了distinct和group by关键字的查询cte查询表达式，recursive递归关键失效。

2）在子查询中使用了层次查询的结果集，支持recursive递归。

3）在关联查询中的右连接中使用了层次查询的结果集，支持recursive递归。

对于开窗聚合、分析函数使用情况的差异，以及复杂model关键字维度查询的递归情况，我们会在后续的系列文档中更新，敬请期待。