postgresql - 取消组合 postgres 中的列

Question

我有一张表，是用 cron 构建的。假设它有两列，Hours 和 Jobname。例如:作业 1 运行 {1,2,3,4} 小时，作业 2 运行 {3,4,5,6} 小时。

我需要一个查询，它取消列小时数的分组并列出每小时的工作。

预期输出:

Hours      Job Name1          Job 12          Job 13          Job 1, Job24          Job 1, Job25          Job 26          Job 2

Answer 1

假设您的工作列是数据类型 text(或类似) 的逗号分隔列表 - 由 ', ' 分隔 - 我转换它到一个数组 string_to_array()然后 unnest()它分隔行:

SELECT hour, unnest(string_to_array(jobs, ', ')) AS job
FROM   tbl
ORDER  BY 1;

你也可以使用 regexp_split_to_table(jobs, ', ') ，它更简单，但不能很好地适应更长的字符串。

相关问题:

• Create table from string
• Count the number of rows that contain a letter/number

在 Postgres 9.3 或更高版本中，使用 LATERAL 联接代替。假设查询简单并且作业被定义为NOT NULL，这有效并且通常根据元素的原始顺序返回作业:

SELECT hour, job
FROM   tbl, unnest(string_to_array(jobs, ', ')) job  -- implicitly LATERAL
ORDER  BY 1;

SQL Fiddle.

对于更复杂的查询，或者如果 jobs 可以是 NULL:

SELECT hour, job
FROM   tbl
LEFT   JOIN LATERAL unnest(string_to_array(jobs, ', '))
       WITH ORDINALITY AS j(job, ord) ON TRUE
ORDER  BY hour, ord;

WITH ORDINALITY 要求 Postgres 9.4 只需要保证数组元素的原始顺序。详情:

• What is the difference between LATERAL and a subquery in PostgreSQL?
• PostgreSQL unnest() with element number