如何按條件從結果中選擇塊？

範例表：

master_id 1 created_at 22.02.1997
master_id 1 created_at 22.03.1997
master_id 1 created_at 22.04.1997
master_id 1 created_at 22.07.1997
master_id 1 created_at 22.08.1997
master_id 1 created_at 22.10.1997

查詢結果範例：

master_id 1 date_from 22.02.1997 date_to 22.04.1997
master_id 1 date_from 22.07.1997 date_to 22.08.1997
master_id 1 date_from 22.10.1997 date_to 22.10.1997 

注意：日期之間的間隔不得超過一個月。

一個經典的間隙和孤島問題：

SELECT master_id, min(created_at) AS date_from, max(created_at) AS date_to
FROM  (
  SELECT *
       , count(*) FILTER (WHERE step) OVER (PARTITION BY master_id ORDER BY created_at) AS range
  FROM  (
     SELECT *, created_at > lag(created_at) OVER (PARTITION BY master_id ORDER BY created_at) + interval '1 month' AS step
     FROM   tbl
     ) sub1
  ) sub2
GROUP  BY master_id, range
ORDER  BY master_id, range;

相關（附說明）：

• 將單獨的列組合成最小的相關範圍
• 選擇最長的連續序列
• 在 PostgreSQL / SSRS 中計算重疊時間的差異

引用自：https://dba.stackexchange.com/questions/236695