GROUP BY子句之后的Oracle CONNECT BY子句

I have just run across this interesting article here, showing how to simulate wm_concat() or group_concat() in Oracle using a hierarchical query and window functions:

我刚刚在这里讨论了这篇有趣的文章,展示了如何使用分层查询和窗口函数在Oracle中模拟wm_concat()或group_concat():

SELECT deptno,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
FROM   (SELECT deptno,
               ename,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
        FROM   emp)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;

Although, I find this not a very readable solution, it's quite interesting, specifically because the CONNECT BY .. STARTS WITH clause comes after the GROUP BY clause. According to the specification, this shouldn't be possible. I've tried this using a simple query and it does work, though! The following two queries return the same results:

虽然,我发现这不是一个非常易读的解决方案,但它非常有趣,特别是因为CONNECT BY ... STARTS WITH子句位于GROUP BY子句之后。根据规范,这是不可能的。我尝试使用简单的查询,但它确实有效!以下两个查询返回相同的结果:

-- wrong according to the specification:
select level from dual group by level connect by level <= 2;
-- correct according to the specification:
select level from dual connect by level <= 2 group by level;

Is this an undocumented feature? Or just syntax indifference for convenience? Or do the two statements subtly behave differently?

这是一个没有文档的功能吗?或者只是语法无差异以方便?或者这两个陈述巧妙地表现得不同?

2 个解决方案

#1

I think this is just an insignificant syntax difference.

我认为这只是一个无关紧要的语法差异。

More specifically, I think this is a documentation bug. The syntax diagram for 8i implies that either order is supported. Nothing in the 8i reference implies the order makes any difference. But that diagram also kind of implies that you can have multiple group_by_clause or hierarchical_query, which isn't true:

更具体地说,我认为这是一个文档错误。 8i的语法图表示支持任一顺序。 8i参考中没有任何内容暗示订单有任何区别。但是这个图表也暗示你可以有多个group_by_clause或hierarchical_query,这不是真的:

--You can't group twice: ORA-01787: only one clause allowed per query block
select level from dual connect by level <= 2 group by level group by level;

My guess is that when Oracle fixed the syntax diagram for 9i they also forgot the order could be different. Or maybe they intentionally left it out, because it seems more logical to do the hierarchical part first.

我的猜测是,当Oracle修复了9i的语法图时,他们也忘了订单可能会有所不同。或者他们可能故意将其排除在外,因为首先执行分层部分似乎更合乎逻辑。

There are several minor syntax variations like this that are undocumented. I don't think it means that they are unsupported. Oracle probably regrets allowing so many weird options and wants things to at least look simple. For example, HAVING can come before GROUP BY, many of the old parallel features still work (but are ignored), etc. (This is why I always laugh when people say they are going to quickly "parse SQL" - good luck figuring this out!)

有几个这样的次要语法变体没有记录。我认为这并不意味着它们不受支持。 Oracle可能会后悔允许这么多奇怪的选项,并希望事情至少看起来很简单。例如,HAVING可以在GROUP BY之前出现,许多旧的并行功能仍然有效(但被忽略)等等。(这就是为什么当人们说他们要快速“解析SQL”时我总是笑的 - 祝你好运出来了!)

Oracle 8i syntax: GROUP BY子句之后的Oracle CONNECT BY子句

Oracle 8i语法:

Oracle 9i syntax: GROUP BY子句之后的Oracle CONNECT BY子句

Oracle 9i语法:

#2

Look at the execution plans. In my environment they are identical, with a CONNECT BY operation feeding into a HASH GROUP BY. So it appears that placing the GROUP BY first is just an odd syntax that produces the same result as the more natural ordering.

看看执行计划。在我的环境中,它们是相同的,CONNECT BY操作输入HASH GROUP BY。所以看起来首先放置GROUP BY只是一种奇怪的语法,它产生的结果与更自然的顺序相同。

Technically, this is probably a bug in the parser, since as you say the spec indicates that the hierarchical-query clause should come prior to the group-by clause. But it doesn't appear to make any difference in how the query is executed.

从技术上讲,这可能是解析器中的一个错误,因为正如您所说,规范表明层次查询子句应该在group-by子句之前。但它似乎没有对查询的执行方式产生任何影响。

#1