为两个字段创建两个数组，保持数组的排序顺序同步（无子查询）

Question

除了我对有人会如何做到这一点感到好奇以外，没有其他问题的押韵或理由。

平台： 当我希望使用SQL标准解决方案时，我的主要精力是在 PostgreSQL 8.4+上 。（我知道9.0+具有一些数组排序功能。）

SELECT    id, group, dt
FROM      foo
ORDER BY  id;

  id   | group |    dt
-------+-------+-----------
   1   |  foo  | 2012-01-01
   1   |  bar  | 2012-01-03
   1   |  baz  | 2012-01-02
   2   |  foo  | 2012-01-01
   3   |  bar  | 2012-01-01
   4   |  bar  | 2012-01-01
   4   |  baz  | 2012-01-01

我知道以下查询是错误的，但结果与我想要的类似；绑定两个字段的方法（的排序也group应排序dt）：

SELECT    id, sort_array(array_agg(group)), array_agg(dt)
FROM      foo
GROUP BY  id;

  id   |     group      |                dt
-------+----------------+------------------------------------
   1   |  {bar,baz,foo} | {2012-01-03,2012-01-02,2012-01-01}
   2   |  {foo}         | {2012-01-01}
   3   |  {bar}         | {2012-01-01}
   4   |  {bar,baz}     | {2012-01-01,2012-01-01}

有没有简单的方法来绑定字段以进行排序，而无需使用子查询？也许建立一个数组的数组，然后嵌套？

Answer 1

我将您的列名更改为group，grp因为它group是Postgres和每个SQL标准中的保留字，并且不应用作标识符。

我理解您的问题是这样的：

获取以相同的排序顺序排序的两个数组，以便相同的元素位置对应于两个数组中的同一行。

使用子查询 或 CTE 并在聚合之前对行进行排序。

SELECT id, array_agg(grp) AS grp, array_agg(dt) AS dt
FROM  (
    SELECT *
    FROM   tbl
    ORDER  BY id, grp, dt
    ) x
GROUP  BY id;

这是 更快 ，而不是使用单独ORDER BY的聚合函数的条款
array_agg()一样@Mosty演示（并一直在那里，因为PostgreSQL的9.0）。Mosty还会以不同的方式解释您的问题，并使用适当的工具进行解释。

是ORDER BY在子查询中安全吗？

手册：

聚合函数array_agg，json_agg以及类似的用户定义的聚合函数，会根据输入值的顺序产生有意义的不同结果值。默认情况下未指定此顺序，但可以通过ORDER BY在聚合调用中编写一个子句来控制它，如第4.2.7节所示。另外，通常也可以提供来自已排序子查询的输入值。例如：

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

请注意，如果外部查询级别包含其他处理（例如联接），则此方法可能会失败，因为这可能会导致子查询的输出在计算聚合之前重新排序。

是的，在此示例中是安全的。

没有子查询

如果您确实 需要 没有子查询 的解决方案，则可以：

SELECT id
     , array_agg(grp ORDER BY grp)
     , array_agg(dt  ORDER BY grp, dt)
FROM   tbl
GROUP  BY id;

注意ORDER BY grp, dt。dt除了打领带，我还进行排序，并使排序顺序明确。不需要grp，尽管。

使用窗口函数还有另一种完全不同的方法：

SELECT DISTINCT ON (id)
       id
     , array_agg(grp) OVER w AS grp
     , array_agg(dt)  OVER w AS dt
FROM   tbl
WINDOW w AS (PARTITION BY id ORDER BY grp, dt
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER  BY id;

请注意，使用DISTINCT ON (id)而不是仅DISTINCT产生相同的结果，但执行速度快一个数量级，因为我们不需要额外的排序。

我进行了一些测试，这几乎与其他两个解决方案一样快。不出所料，子查询版本仍然是最快的。测试一下EXPLAIN ANALYZE，看看自己。