oracle – 带有ORDER BY NULL的LISTAGG实际上用作订单标准是什么

SELECT LISTAGG( COLUMN_VALUE ) WITHIN GROUP ( ORDER BY NULL )   AS OrderByNULL,LISTAGG( COLUMN_VALUE ) WITHIN GROUP ( ORDER BY 1 )      AS OrderByCONST,LISTAGG( COLUMN_VALUE ) WITHIN GROUP ( ORDER BY ROWNUM ) AS OrderByROWNUM
FROM   TABLE( SYS.ODCIVARCHAR2LIST( '5','222','4' ) );

输出是：

ORDERBYNULL ORDERBYCONST ORDERBYROWNUM
----------- ------------ -------------
222,4,5     222,5      5,222,4

当使用带有非确定性排序的ORDER BY(NULL或常量)时,查询似乎已经完成了字母数字排序,并且在使用ORDER BY ROWNUM(确定性)时保持了输入顺序.

LISTAGG文档指出：

The order_by_clause determines the order in which the concatenated values are returned. The function is deterministic only if the ORDER BY column list achieved unique ordering.

在查看analytic functions时,它指出：

Whenever the order_by_clause results in identical values for multiple rows,the function behaves as follows: […] For all other analytic functions,the result depends on the window specification. If you specify a logical window with the RANGE keyword,then the function returns the same result for each of the rows. If you specify a physical window with the ROWS keyword,then the result is nondeterministic.

因此,据我所知,从文档中可以预期非确定性排序 – 但是,该函数根据字母数字排序而不是处理行的顺序(即commonly held view)给出确定性输出. .

这与其他分析函数的行为不同(当使用带有ROWS关键字的物理窗口时)：

SELECT LAST_VALUE( COLUMN_VALUE )
         OVER ( ORDER BY NULL   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
         AS BYNULL,LAST_VALUE( COLUMN_VALUE )
         OVER ( ORDER BY 1      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
         AS BYCONST,LAST_VALUE( COLUMN_VALUE )
         OVER ( ORDER BY ROWNUM ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
         AS BYROWNUM
FROM   TABLE( SYS.ODCIVARCHAR2LIST( '5','4' ) );

这为所有不同的排序提供了一致的输出：

BYNULL BYCONST BYROWNUM
------ ------- --------
4      4       4
4      4       4
4      4       4

当LISTAGG提供非确定性排序时,是否有关于如何应用排序的官方文档？

注意：ORDER BY NULL的行为在here上注释：

In this example,the elements have been aggregated alphabetically,despite the NULL ordering clause. This appears to be the default behaviour when using a constant ORDER BY expression

但这只是对非Oracle站点中的行为的评论.

如果您没有给oracle任何特定的标准来订购数据,那么即使从您的试验中,您似乎也可以按照特定的方式对数据进行一致排序.

根据Oracle documentation for listagg：

> order_by_clause确定返回连接值的顺序.仅当ORDER BY列列表实现唯一排序时,该函数才是确定性的.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!