SQL: 特定レコードの前後n件レコードを取得 #PostgreSQL

動作確認DB: Postgresql11
サンプルテーブルの定義は以下で示す。

create.sql

CREATE TABLE tmp_events (
  id serial,
  date date not null,
  name varchar not null,
  PRIMARY KEY(id)
);

サンプルデータ
``` insert.sql

insert into tmp_events (date, name)
values
('2018-01-01','New Years Day'),
('2018-04-02','Easter Monday'),
('2018-05-10','Ascension Day'),
('2018-03-30','Good Friday'),
('2018-05-21','Whit Monday'),
('2018-05-27','Mothers Day'),
('2018-06-17','Fathers Day'),
('2018-07-14','Bastille Day'),
('2018-05-01','Labour Day'),
('2018-08-15','Assumption Day'),
('2018-11-01','All Saints Day'),
('2018-11-11','Armistice Day'),
('2018-12-26','St Stephens Day'),
('2018-05-08','V-E Day'),
('2018-12-25','Christmas Day');
```

検索結果

select.result

SELECT * FROM events ORDER BY date;
 id |    date    |      name
----+------------+-----------------
 1  | 2018-01-01 | New Years Day
 4  | 2018-03-30 | Good Friday
 2  | 2018-04-02 | Easter Monday
 9  | 2018-05-01 | Labour Day
14 | 2018-05-08 | V-E Day
 3  | 2018-05-10 | Ascension Day
 5  | 2018-05-21 | Whit Monday
 6  | 2018-05-27 | Mothers Day
 7  | 2018-06-17 | Fathers Day
 8  | 2018-07-14 | Bastille Day
 10 | 2018-08-15 | Assumption Day
 11 | 2018-11-01 | All Saints Day
 12 | 2018-11-11 | Armistice Day
 15 | 2018-12-25 | Christmas Day
 13 | 2018-12-26 | St Stephens Day
(15 rows)

上記の検索結果からIDが10のレコードの前後2件レコードを取得するには、以下のSQLが使えます。

select.sql

WITH cte AS (
  SELECT id, date, name, row_number() OVER (ORDER BY date)
    FROM events
), current AS (
  SELECT row_number
    FROM cte
  WHERE id = 25
)
SELECT cte.*
  FROM cte, current
    WHERE ABS(cte.row_number - current.row_number) <= 2
  ORDER BY cte.row_number;

取得結果.result

id |    date    |      name
----+------------+-----------------
 7  | 2018-06-17 | Fathers Day
 8  | 2018-07-14 | Bastille Day
 10 | 2018-08-15 | Assumption Day
 11 | 2018-11-01 | All Saints Day
 12 | 2018-11-11 | Armistice Day
(5 rows)