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

一尘不染

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

sql

我试图查询包含charactervarying[]年份列的表，并以逗号分隔的年份范围的字符串形式返回这些年份。年份范围将由数组中出现的连续年份确定，非连续年份/年份范围应以逗号分隔。

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

阅读 209

2021-03-08

共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

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

在此处和此处的手册中，有关 窗口功能的
更多信息。

2021-03-08