sql - 如何创建每 2 行的范围

Question

我有下表：

+------------+
|    date    |
+------------+
| 2018-07-01 |
| 2018-09-01 |
| 2018-08-01 |
+------------+

如何以这种方式查询以获得以下结果？

+------------+------------+
|  min_date  |  max_date  |
+------------+------------+
| 2001-01-01 | 2018-07-01 |
| 2018-07-01 | 2018-08-01 |
| 2018-08-01 | 2018-09-01 |
+------------+------------+

标签： sqlpostgresql

Answer 1

lag()接受三个参数，第三个参数是默认值，所以你可以这样写：

select lag(date, 1, '2001-01-01' as date) over (order by date) as min_date,
       date as max_date
from t;