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

Question

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

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

到目前为止，我在解决这个问题上运气不佳，因为我什至不确定从哪里开始。

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

years_table 示例

+=========+============================+
| 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

结果:

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

Answer 1

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

产生完全所需的结果。

如果您处理一个 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

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

手册中有关窗口函数的更多信息here和 here .

在这种情况下，plpgsql 函数可能会更快。你必须测试。这些相关答案中的示例:
Ordered count of consecutive repeats / duplicates
ROW_NUMBER() shows unexpected values