返回的年数组作为今年范围 [英] Return array of years as year ranges

问题描述

我试图查询包含一个字符改变年[] 列一个表，然后返回这些年用逗号分隔的一年范围的字符串。年会范围内通过连续多年present在阵列内确定，年/年范围不属于顺序应该是逗号分隔。

究其原因，数据类型是字符改变[] ，而不是整数是因为一些[]中值中包含所有，而不是几年名单。我们可以省略这些结果。

到目前为止，我有一点运气接近的问题，因为我真的不甚至知道从哪里开始。

会有人能够给我一些指导或提供一人可能是如何解决如挑战一个有用的例子吗？

years_table 示例

  + ========= + =========================== = +
| ID |年|
|整数|字符改变[] |
+ ========= + ============================ +
| 1 | {ALL} |
| 2 | {1999,2000,2010,2011,2012} |
| 3 | {1990,1991,2007} |
+ --------- + ---------------------------- +
 

输出目标：

例如SQL查询：

  SELECT ID，[年CONCAT逻辑] AS year_ranges
从years_table WHEREALL好几年没
 

结果：

  + ==== + ====================== +
| ID | year_ranges |
==== + + + ======================
| 2 | 1999- 2000年，2010- 2012年|
| 3 | 1990- 1991年，2007年|
+ ---- + ---------------------- +
 

解决方案

  SELECT标识，string_agg（year_range，'，'）AS year_ranges
FROM（
   SELECT标识，CASE WHEN COUNT（*）＆GT; 1
               THEN分（年）::文|| ' - '||最大（年）::文
               ELSE分钟（年）::文
              END AS year_range
   FROM（
      SELECT *，ROW_NUMBER（）OVER（ORDER BY ID，年） - 一年GRP
      FROM（
         SELECT标识，UNNEST（年）截至今年
         FROM（VALUES（2 :: INT，{} 1999,2000,2010,2011,2012':: INT []）
                      （3，'{1990,1991,2007}'）
               ）AS TBL（ID，年）
         ）SUB1
      ）SUB2
   GROUP BY ID，GRP
   ORDER BY ID，分（年）
   ）SUB3
GROUP BY ID
ORDER BY ID
 

生成的究竟的期望的结果。

如果你处理的VARCHAR数组（ VARCHAR [] ，只需将其转换为 INT [] ，继续之前这似乎是在为完全合法的形式：

  ::年INT []
 

在生产code源表的名称替换内子选择。

  FROM（VALUES（2 :: INT，{} 1999,2000,2010,2011,2012':: INT []）
              （3，'{1990,1991,2007}'）
       ）AS TBL（ID，年）
 

- >

  FROM TBL
 

由于我们正在（年份）处理一个升自然数，我们可以使用快捷方式形成连续多年组（形成范围）。我减去行数（按年份排序）当年的自己。连续多年，无论是行号和一岁的增量并产生相同 GRP 号码。否则，一个新的范围开始。

在手动更多关于窗口功能这里和这里。

一个PLPGSQL功能可能会更快在这种情况下。你必须进行测试。在这些相关答案示例：搜索
<一href=\"http://stackoverflow.com/questions/13078964/ordered-count-of-consecutive-repeats-duplicates/13079901#13079901\">Ordered连续重复/重复结果的数
<一href=\"http://stackoverflow.com/questions/11819251/row-number-shows-unexpected-values/11821003#11821003\">ROW_NUMBER()显示异常值

I'm attempting to query a table which contains a character varying[] column of years, and return those years as a string of comma-delimited year ranges. The year ranges would be determined by sequential years present within the array, and years/year ranges which are not sequential should be separated be commas.

The reason the data-type is character varying[] rather than integer[] is because a few of the values contain ALL instead of a list of years. We can omit these results.

So far I've had little luck approaching the problem as I'm not really even sure where to start.

Would someone be able to give me some guidance or provide a useful examples of how one might solve such as challenge?

years_table Example

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

Output Goal:

Example SQL Query:

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

Produces exactly the desired result.

If you deal with an an array of varchar (varchar[], just cast it to int[], before you proceed. It seems to be in perfectly legal form for that:

years::int[]

Replace the inner sub-select with the name of your source table in productive code.

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

->

FROM  tbl

Since we are dealing with a naturally ascending number (the year) we can use a shortcut to form groups of consecutive years (forming a range). I subtract the year itself from row number (ordered by year). For consecutive years, both row number and year increment by one and produce the same grp number. Else, a new range starts.

More on window functions in the manual here and here.

A plpgsql function might be even faster in this case. You'd have to test. Examples in these related answers:
Ordered count of consecutive repeats / duplicates
ROW_NUMBER() shows unexpected values

这篇关于返回的年数组作为今年范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！