聚合查询中的单个列并包含许多列 [英] Aggregate a single column in query with many columns

问题描述

当查询中有许多其他列时，是否有适当的方法聚合单个列?

Is there a proper way to aggregate a single column when I have many other columns in the query?

我已经尝试了此答案，虽然有效，但是我的查询变得更加冗长.

I've tried this answer which works, but my query has become a lot more verbose.

我当前的查询如下:

SELECT t1.foo1, t1.foo2, t2.foo3, t2.foo4, string_agg(t3.aggregated_field, ', ')
FROM tbl1 t1
LEFT JOIN tbl2 t2 ON t1.id = t2.fkeyid
LEFT JOIN tbl3 t3 ON t2.id = t3.fkeyid
GROUP BY t1.foo1, t1.foo2, t2.foo3, t2.foo4, t2.foo5, t2.foo6
ORDER BY t2.foo5, t2.foo6

该查询还有更多的字段和LEFT JOIN，重要的部分是所有这些字段都具有1到1或1到0的关系，除了我要聚合的一个字段1到n之外，用在上面的伪查询中.

The query has many more fields and LEFT JOINs, the important part is that all these fields have 1 to 1 or 1 to 0 relationship except one field that is 1 to n which I want to aggregate, represented by t3.aggregated_field in the pseudo-query above.

当我使用聚合函数时，SELECT和ORDER BY中列出的所有字段都必须是聚合的或GROUP BY子句的一部分.这使我的查询比现在更加冗长.

As I'm using an aggregate function, all fields listed in the SELECT and ORDER BY must be either aggregated or part of the GROUP BY clause. This makes my query way more verbose than it already is.

也就是说，假设foo1是主键，则当重复此字段时，除aggregated_field以外的所有其他字段也都相同.我希望将这些重复的行作为具有聚合字段值的单行结果. (基本上是select distinct带有聚合列)

That is, assuming foo1 is a primary key, when this field is repeated, all others except aggregated_field are also equal. I want these repeated rows as a single row result with the aggregated field value. (basically a select distinct with an aggregated column)

是否有更好的方法来执行此操作(而不必将所有其他字段放在GROUP BY中)，还是应该仅对后端中的结果集进行迭代，对每行从1到n的行执行查询?关系吗?

Is there a better way to do this (without having to put all other fields in the GROUP BY) or should I just iterate over the result set in my back-end executing a query for each row fetching this 1 to n relationship?

服务器正在运行PostgreSQL 9.1.9，更具体地说:

The server is running PostgreSQL 9.1.9, more specifically:

x86_64-unknown-linux-gnu上的PostgreSQL 9.1.9，由gcc(GCC)4.1.2 20080704(Red Hat 4.1.2-54)编译，64位

PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-54), 64-bit

推荐答案

简单查询

对于PostgreSQL 9.1或更高版本，这可能更简单.如这个紧密相关的答案所述:

Simple query

This can be much simpler with PostgreSQL 9.1 or later. As explained in this closely related answer:

• 足以GROUP BY表的主键.由于:

  foo1是主键

  foo1 is a primary key

  ..您可以将示例简化为:

  .. you can simplify your example to:

  SELECT foo1, foo2, foo3, foo4, foo5, foo6, string_agg(aggregated_field, ', ')
  FROM   tbl1
  GROUP  BY 1
  ORDER  BY foo7, foo8;  -- have to be spelled out, since not in select list!
  

  查询多个表

  但是，既然您拥有:

  Query with multiple tables

  However, since you have:

  还有许多字段和LEFT JOIN，重要的是所有这些字段都具有1到1或1到0的关系，除了我要聚合的一个字段是1到n之外

  many more fields and LEFT JOINs, the important part is that all these fields have 1 to 1 or 1 to 0 relationship except one field that is 1 to n which I want to aggregate

  .. 先聚集，然后加入应该更快，更简单:

  .. it should be faster and simpler to aggregate first, join later:

  SELECT t1.foo1, t1.foo2, ...
       , t2.bar1, t2.bar2, ...
       , a.aggregated_col 
  FROM   tbl1 t1
  LEFT   JOIN tbl2 t2 ON ...
  ...
  LEFT   JOIN (
     SELECT some_id, string_agg(agg_col, ', ') AS aggregated_col
     FROM   agg_tbl a ON ...
     GROUP  BY some_id
     ) a ON a.some_id = ?.some_id
  ORDER  BY ...
  

  这样，您查询的大部分就根本不需要聚合.

  This way the big portion of your query does not need aggregation at all.

  我最近在SQL Fiddle中提供了一个测试用例，以证明此相关答案中的要点:

  I recently provided a test case in an SQL Fiddle to prove the point in this related answer:

  • PostgreSQL-按数组排序

  由于您指的是此相关答案:不，DISTINCT在此方面完全无济于事情况.

  Since you are referring to this related answer: No, DISTINCT is not going to help at all in this case.

  这篇关于聚合查询中的单个列并包含许多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！