在Netezza中使用GROUP_CONCAT时缺少ORDER BY的解决方法 [英] Workaround of lacking of ORDER BY when using GROUP_CONCAT in Netezza

问题描述

在Netezza中，GROUP_CONCAT函数不支持ORDER BY，它使用自己的内部order by，即您用作参数的列的order.

In Netezza, GROUP_CONCAT function does not support ORDER BY, it uses its own internal order by, which is order by the column you use as parameter.

SELECT 'GROUP BY '||TOOLKIT.SQLEXT.GROUP_CONCAT(PRIMARY_KEY, ', ') AS GROUP_BY
FROM (
SELECT 1 AS SEQ, 'DATA_DATE' AS PRIMARY_KEY
UNION ALL
SELECT 2 AS SEQ, 'ACCT_ID'   AS PRIMARY_KEY
) S;

这将返回:

      GROUP_BY
GROUP BY ACCT_ID, DATA_DATE

但是如何通过SEQ而不是PRIMARY_KEY来获得结果顺序，以获得这样的结果:

But how to get result order by SEQ instead of PRIMARY_KEY to get result like this:

      GROUP_BY
GROUP BY DATA_DATE, ACCT_ID

推荐答案

我们可以在第一个GROUP_CONCAT参数的开头添加SEQ，以强制内部ORDER BY对我们起作用，然后删除我们添加的内容.为了安全删除，我们在序列的开头和结尾添加了额外的"@".

We can add SEQ at beginning of first GROUP_CONCAT parameter to force internal ORDER BY to work for us, and then remove what we have added. In order to remove safely, we added extra '@' at beginning and end of the sequence.

SELECT 'GROUP BY '||TOOLKIT.SQLEXT.REGEXP_REPLACE(
                    TOOLKIT.SQLEXT.GROUP_CONCAT('@'||LPAD(SEQ, 4, '0')||'@'
                    ||PRIMARY_KEY, ', '), 
                    '@[0-9]{4}@', '') AS GROUP_BY
FROM (
SELECT 1 AS SEQ, 'DATA_DATE' AS PRIMARY_KEY
UNION ALL
SELECT 2 AS SEQ, 'ACCT_ID'   AS PRIMARY_KEY
) S;

这将返回我们想要的:

GROUP_BY
GROUP BY DATA_DATE, ACCT_ID

这篇关于在Netezza中使用GROUP_CONCAT时缺少ORDER BY的解决方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！