带有ORDER BY NULL的LISTAGG实际用作订购条件是什么? [英] What does LISTAGG with ORDER BY NULL actually use as the order criteria?

问题描述

如果我愿意

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,4,5      5,222,4

当使用ORDER BY进行不确定性排序(NULL或常量)时，查询似乎已经完成了字母数字排序，当使用ORDER BY ROWNUM(确定性)时，查询保持了输入顺序.

The query appears to have done an alphanumerical sort when using ORDER BY with non-deterministic ordering (NULL or a constant) and has maintained the input order when using ORDER BY ROWNUM (deterministic).

LISTAGG 文档状态

order_by_clause确定返回串联值的顺序.仅当ORDER BY列列表实现唯一排序时，该函数才具有确定性.

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.

当查看分析函数时，它指出:

每当order_by_clause对多个行产生相同的值时，该函数的行为如下:[...]对于所有其他分析函数，结果取决于窗口规范.如果使用RANGE关键字指定逻辑窗口，则该函数为每一行返回相同的结果.如果使用ROWS关键字指定物理窗口，则结果不确定.

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.

据我所知，应该确定非确定性顺序-但是，该函数根据字母数字排序而不是根据行的处理顺序来给出确定性输出(即常用视图).

So as far as I can tell from the documentation a non-deterministic ordering is to be expected - however, the function gives a deterministic output based on an alphanumerical sort and not on the order in which the rows are processed (which is the commonly held view).

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

This is different to the behaviour of other analytic functions (when using a physical window with the ROWS keyword):

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', '222', '4' ) );

针对所有不同的顺序给出一致的输出:

Which gives a consistent output for all the different orderings:

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

是否提供了官方文档，说明在向LISTAGG提供不确定的订购时如何应用订购?

Is there official documentation for how an ordering is applied when LISTAGG is supplied a non-deterministic ordering?

注意:ORDER BY NULL的行为在此处上进行了注释.说明:

Note: The behaviour of ORDER BY NULL is commented on here stating:

在此示例中，尽管使用NULL排序子句，但元素仍按字母顺序聚合.使用常量ORDER BY表达式时，这似乎是默认行为.

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网站中行为的一种评论.

But this is just an comment on the behaviour in a non-Oracle site.

推荐答案

可能的顺序取决于很多因素，包括查询的执行计划，实例的配置，数据库的版本(如果您正在使用)数据库云服务器与否.

Possibly the order will depend on many factor, including the execution plan of the query, the configuration of your instance, the version of the database, if you are on exadata or not.

如果您没有为oracle提供任何特定的标准来对数据进行排序，那么即使您从试验中似乎似乎已经以特定的方式对数据进行了统一排序，也无法将其视为任何特定的排序.

If you are not giving oracle any specific criteria to order the data you can't count on any specific ordering, even if from your trials you seem to get the data consistently sorted in a specific way.

根据 listagg的Oracle文档 :

• order_by_clause确定返回串联值的顺序.仅当ORDER BY列列表实现唯一排序时，该函数才具有确定性.

这篇关于带有ORDER BY NULL的LISTAGG实际用作订购条件是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！