返回年份数组作为年份范围

我正在尝试查询一个包含以下内容的表character varying[]年份列，并将这些年份作为逗号分隔的年份范围字符串返回。年份范围将由数组中存在的连续年份确定，不连续的年份/年份范围应以逗号分隔。

数据类型的原因是character varying[]而不是integer[]是因为一些值包含ALL而不是年份列表。我们可以忽略这些结果。

到目前为止，我在解决这个问题方面运气不佳，因为我什至不知道从哪里开始。

有人能给我一些指导或提供一个有用的例子来说明如何解决诸如挑战之类的问题吗？

years_table Example

+=========+============================+
| id      | years                      |
| integer | character varying[]        |
+=========+============================+
| 1       | {ALL}                      |
| 2       | {1999,2000,2010,2011,2012} |
| 3       | {1990,1991,2007}           |
+---------+----------------------------+

产出目标：

SQL 查询示例：

SELECT id, [year concat logic] AS year_ranges
FROM years_table WHERE 'ALL' NOT IN years

Result:

+====+======================+
| id | year_ranges          |
+====+======================+
| 2  | 1999-2000, 2010-2012 |
| 3  | 1990-1991, 2007      |
+----+----------------------+

SELECT id, string_agg(year_range, ', ') AS year_ranges
FROM (
   SELECT id, CASE WHEN count(*) > 1
               THEN min(year)::text || '-' ||  max(year)::text 
               ELSE min(year)::text
              END AS year_range
   FROM  (
      SELECT *, row_number() OVER (ORDER BY id, year) - year AS grp
      FROM  (
         SELECT id, unnest(years) AS year
         FROM  (VALUES (2::int, '{1999,2000,2010,2011,2012}'::int[])
                      ,(3,      '{1990,1991,2007}')
               ) AS tbl(id, years)
         ) sub1
      ) sub2
   GROUP  BY id, grp
   ORDER  BY id, min(year)
   ) sub3
GROUP  BY id
ORDER  BY id

生产exactly想要的结果。

如果你处理一个 varchar 数组 (varchar[]，只需将其投射到int[]，然后再继续。这似乎是完全合法的形式：

years::int[]

将内部子选择替换为生产代码中源表的名称。

 FROM  (VALUES (2::int, '{1999,2000,2010,2011,2012}'::int[])
              ,(3,      '{1990,1991,2007}')
       ) AS tbl(id, years)

->

FROM  tbl

由于我们正在处理一个自然递增数（年份）我们可以使用快捷方式来形成连续年份的组（形成一个范围）。我从行号中减去年份本身（按年份排序）。对于连续年份，行号和年份都加一并产生相同的结果grp数字。否则，一个新的范围开始。

More on 窗函数在手册中here http://www.postgresql.org/docs/current/interactive/tutorial-window.html and here http://www.postgresql.org/docs/current/interactive/functions-window.html.

在这种情况下，plpgsql 函数可能会更快。你必须进行测试。这些相关答案中的示例：
连续重复/重复的有序计数 https://stackoverflow.com/questions/13078964/ordered-count-of-consecutive-repeats-duplicates/13079901#13079901
ROW_NUMBER() 显示意外值 https://stackoverflow.com/questions/11819251/row-number-shows-unexpected-values/11821003#11821003