Postgresql generate_series动态间隔

Question

Hi我想在Postgresql中使用generate_series制作动态时间序列。

例如，我有一组随机日期列表：

cleaning date
2015-03-01 00:00
2015-05-31 00:00
2015-06-13 00:00

我想用这些清洁日期动态地划分日期段。

结果是这样的：

start date         end date
0000-00-00 00:00   2015-03-01 00:00
2015-03-01 00:00   2015-05-31 00:00
2015-05-31 00:00   2015-06-13 00:00
2015-06-13 00:00   (current_timestamp)

我不知道如何在generate_series中设置动态间隔。。。generate_series有可能吗？

Answer 1

我真的不认为使用generate_series有什么意义。

为了得到你建议的结果，你可以使用ROW_NUMBER和自加入：

CREATE TABLE tab(cleaning timestamp);
INSERT INTO tab(cleaning)
VALUES ('2015-03-01 00:00'), ('2015-05-31 00:00'), ('2015-06-13 00:00');

主要查询：

WITH cte AS
(
   SELECT cleaning, ROW_NUMBER() OVER(ORDER BY cleaning) AS rn
   FROM tab
)
SELECT COALESCE(c1.cleaning,'0001-01-01'::timestamp) AS start_date,
       COALESCE(c2.cleaning,CURRENT_TIMESTAMP) AS end_date
FROM cte c1
FULL JOIN cte c2
  ON c1.rn = c2.rn-1
ORDER BY start_date;

SqlFiddleDemo

输出：

╔═══════════════════════════╦═════════════════════════╗
║        start_date         ║        end_date         ║
╠═══════════════════════════╬═════════════════════════╣
║ January, 01 0001 00:00:00 ║ March, 01 2015 00:00:00 ║
║ March,   01 2015 00:00:00 ║ May,   31 2015 00:00:00 ║
║ May,     31 2015 00:00:00 ║ June,  13 2015 00:00:00 ║
║ June,    13 2015 00:00:00 ║ March, 30 2016 11:03:38 ║
╚═══════════════════════════╩═════════════════════════╝

编辑：

另一种可能性是使用LAG/LEAD窗口函数：

SELECT COALESCE(LAG(cleaning) OVER(ORDER BY cleaning), '0001-01-01'::timestamp) 
       AS start_date
      ,cleaning AS end_date
FROM tab
UNION ALL
SELECT MAX(cleaning), CURRENT_TIMESTAMP
FROM tab
ORDER BY start_date;

SqlFiddleDemo2