GROUP BY和汇总顺序数值 [英] GROUP BY and aggregate sequential numeric values

问题描述

使用PostgreSQL 9.0.

Using PostgreSQL 9.0.

假设我有一个包含以下字段的表:company，profession和year.我想返回一个结果，其中包含独特的公司和专业，但是会根据数字序列汇总(成一个数组)年:

Let's say I have a table containing the fields: company, profession and year. I want to return a result which contains unique companies and professions, but aggregates (into an array is fine) years based on numeric sequence:

示例表:

+-----------------------------+
| company | profession | year |
+---------+------------+------+
| Google  | Programmer | 2000 |
| Google  | Sales      | 2000 |
| Google  | Sales      | 2001 |
| Google  | Sales      | 2002 |
| Google  | Sales      | 2004 |
| Mozilla | Sales      | 2002 |
+-----------------------------+

我对一个查询感兴趣，该查询将输出类似于以下内容的行:

I'm interested in a query which would output rows similar to the following:

+-----------------------------------------+
| company | profession | year             |
+---------+------------+------------------+
| Google  | Programmer | [2000]           |
| Google  | Sales      | [2000,2001,2002] |
| Google  | Sales      | [2004]           |
| Mozilla | Sales      | [2002]           |
+-----------------------------------------+

本质特征是只能将连续年分组在一起.

The essential feature is that only consecutive years shall be grouped together.

推荐答案

There's much value to @a_horse_with_no_name's answer, both as a correct solution and, like I already said in a comment, as a good material for learning how to use different kinds of window functions in PostgreSQL.

但是我不禁感到，对于这种问题，答案中的方法有点费力.基本上，在继续对数组中的年份进行聚合之前，您需要的是用于分组的附加条件.您已经有了company和profession，现在只需区分某些属于不同序列的年份即可.

And yet I cannot help feeling that the approach taken in that answer is a bit too much of an effort for a problem like this one. Basically, what you need is an additional criterion for grouping before you go on aggregating years in arrays. You've already got company and profession, now you only need something to distinguish years that belong to different sequences.

这正是上述答案所提供的，而我认为这正是可以以更简单的方式完成的工作.方法如下:

That is just what the above mentioned answer provides and that is precisely what I think can be done in a simpler way. Here's how:

WITH MarkedForGrouping AS (
  SELECT
    company,
    profession,
    year,
    year - ROW_NUMBER() OVER (
      PARTITION BY company, profession
      ORDER BY year
    ) AS seqID
  FROM atable
)
SELECT
  company,
  profession,
  array_agg(year) AS years
FROM MarkedForGrouping
GROUP BY
  company,
  profession,
  seqID

这篇关于GROUP BY和汇总顺序数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！