sql - 为两个字段创建两个数组，保持数组的排序顺序同步(没有子查询)

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

我知道下面的查询是错误的，但结果与我想要的相似；绑定(bind)两个字段的方法(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}

有没有一种简单的方法来绑定(bind)字段进行排序，而不使用子查询？也许构建一个数组数组然后取消嵌套？

Answer 1

我将您的列名 group 更改为 grp 因为 group 是 reserved word在 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 clauses in the aggregate function 更快 array_agg() 像@Mosty demonstrates (自 PostgreSQL 9.0 以来一直存在)。 Mosty 还以不同的方式解释您的问题，并使用适当的工具进行解释。

ORDER BY 在子查询中安全吗？

The manual:

The aggregate functions array_agg, json_agg, [...] as well as similar user-defined aggregate functions, produce meaningfully different result values depending on the order of the input values. This ordering is unspecified by default, but can be controlled by writing an ORDER BY clause within the aggregate call, as shown in Section 4.2.7. Alternatively, supplying the input values from a sorted subquery will usually work. For example:

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

Beware that this approach can fail if the outer query level contains additional processing, such as a join, because that might cause the subquery's output to be reordered before the aggregate is computed.

所以是的，它在示例中是安全的。

没有子查询

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

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 不需要。

还有一种完全不同的方法，即 window functions :

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 进行测试以亲自查看。