SQLで指定期間内の日付リストを取得する(連続値の生成)

# はじめに
自社アプリケーションにてマスタ設定により入力形式(数値入力、文字入力、フリー入力、リストボックス、カレンダーなど)を切り替えることが出来る。
ユーザーからある項目には日付を入力したいが、日付不明な場合には"-"をセットしたいという要望があった。今のカレンダー入力では不明でも必ず日付を指定する必要があるので、同僚が数値入力を提案するメールをしていた。
そのメールを見て、数値入力だと10桁を手入力することになりユーザーの作業が不便になってしまう。自分なら指定する期間が限定されるけど、リストボックスにしたらいいと思うよと同僚に提案した。

リストボックスのデータはSQLで生成する仕組みになっているのだが、どうやってSQLを組めばいいのか？

調査した結果、対象データベースのPostgreSQLでは GENERATE_SERIES 関数を使用すれば連続値の生成が出来ることが分かった。
どうせなら記事用に他のデータベースも似たことが出来るのか調べてみました。

# 検証環境
ブラウザでRDBMS別のSQL動作確認ができる「db<>fiddle」を使用しました。

https://dbfiddle.uk/?rdbms=mysql_8.0

現状(2021/03/20)で最新版を試しています。

* PostgreSQL 13
* SQLServer 2019
* MySQL 8.0
* Oracle 18c

## 他サイト
他にブラウザでSQL実行を確認できるサイト

### DB Fiddle
https://www.db-fiddle.com/

### SQL Fiddle
http://sqlfiddle.com/

現在は、Oracleは対象外となっています。

### Oracle Live SQL
https://livesql.oracle.com/apex/livesql/file/index.html

※Oracle公式なのでOracleアカウントが必要

### rextester.com
https://rextester.com/l/mysql_online_compiler

データベース以外にもプログラム言語を確認できる。

# 連続値を取得
前提として、ユーザー関数は作らず標準のみとします。
先ずは1〜10までの値を取得してみます。

## PostgreSQL
PostgreSQLでは、集合を返す GENERATE_SERIES 関数を使用する。
https://www.postgresql.jp/document/9.6/html/functions-srf.html

```sql
select generate_series(1, 10) row_no;
```

### 結果
PostgreSQLのみ結果を表示します。

|row_no|
|--:|
|1|
|2|
|3|
|4|
|5|
|6|
|7|
|8|
|9|
|10|

10 rows

## SQLServer
SQLServerでは、再帰CTEを使用します。
[共通テーブル式を使用する再帰クエリ - MSDN](https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2005/ms186243(v=sql.90)?redirectedfrom=MSDN)

```sql
WITH dummy AS
(
  SELECT 1 AS row_no 
  UNION ALL
  SELECT row_no + 1
  FROM dummy
  WHERE dummy.row_no < 10
)
SELECT * FROM dummy;
```

### 注意 
SQLServerの既定では、再帰処理が100回を超えると以下のエラーメッセージを表示し、処理が停止します。

「メッセージ530　レベル16　状態1　行1 ステートメントが終了しました。ステートメントの完了の前に最大再帰数100に達しました。」

この最大再帰数は、オプションを指定することにより任意の値に変更可能です。（ゼロを指定すると、無限）

```sql:指定例
OPTION (MAXRECURSION 9999) --9999回まで
OPTION (MAXRECURSION 0) --無限
```

## MySQL
MySQLでは、ユーザー変数を用いて擬似的に再現しています。
テーブルは何か指定する必要があり、`information_schema.COLUMNS` を都合よく使用させてもらっています。
`information_schema.COLUMNS`の件数が2173なので、これ以下なら問題ありません。

```sql
SET @num=0;
SELECT @num:=@num+1 row_no
FROM information_schema.COLUMNS
LIMIT 10
```

`SET @num=0;`を使用しない方法

```sql
SELECT 0 row_no
WHERE (@num:=1-1)*0
UNION ALL
SELECT @num:=@num+1 row_no
FROM information_schema.COLUMNS
LIMIT 10
```

## Oracle
Oracleでは、階層問合せ用の「LEVEL疑似列」を使います。
※Oracle 10g以降からになります。

```sql
SELECT LEVEL row_no FROM DUAL
CONNECT BY LEVEL <= 10;
```

# 日付リストを取得
例として当日前後５日の日付リストを取得します。

## PostgreSQL
PostgreSQLでは、集合を返す GENERATE_SERIES 関数を使用する。

```sql
select generate_series(current_date - 5, current_date + 5, '1 day')::date ymd
```

### 結果
PostgreSQLのみ結果を表示します。
2021-03-20が当日となります。

|ymd|
|:--|
|2021-03-15|
|2021-03-16|
|2021-03-17|
|2021-03-18|
|2021-03-19|
|2021-03-20|
|2021-03-21|
|2021-03-22|
|2021-03-23|
|2021-03-24|
|2021-03-25|

11 row

### 目的の日付データ
本来の目的の日付データは、日付が不明な場合に'-'を指定できるようにする必要があるため、先頭に'-'を追加します。

```sql
select '-' ymd
union all
select to_char( generate_series(current_date - 5, current_date + 5, '1 day'), 'yyyy-mm-dd')
```

|ymd|
|:--|
|-|
|2021-03-15|
|2021-03-16|
|2021-03-17|
|2021-03-18|
|2021-03-19|
|2021-03-20|
|2021-03-21|
|2021-03-22|
|2021-03-23|
|2021-03-24|
|2021-03-25|

## SQLServer
SQLServerでは、再帰CTEを使用します。

```sql
WITH DAYS AS
(
SELECT DATEADD(DAY,-5, CURRENT_TIMESTAMP) AS YMD
UNION ALL
SELECT DATEADD(DAY, 1, YMD)
FROM DAYS
WHERE DAYS.YMD < DATEADD(DAY, 5, CURRENT_TIMESTAMP)
)
SELECT CAST(YMD AS date) YMD FROM DAYS;
```

## MySQL
MySQLでは、ユーザー変数を用いて擬似的に再現しています。

```sql
SET @num=-5-1;
SELECT date_format(ymd, '%Y-%m-%d') ymd
FROM
(SELECT date_add(CURRENT_TIMESTAMP, interval t.row_no day) ymd
FROM (SELECT @num:=@num+1 row_no
FROM information_schema.COLUMNS) t) days
WHERE ymd <= date_add(CURRENT_TIMESTAMP, interval 5 day)
```

## Oracle
Oracleでは、階層問合せ用の「LEVEL疑似列」を使います。

```sql
SELECT TO_CHAR(CURRENT_DATE + X.LVL - 5, 'yyyy-mm-dd') ymd
FROM 
(SELECT LEVEL AS LVL FROM DUAL
CONNECT BY LEVEL <= 5 * 2 + 1) X
```

# 最後に
オンライン上で膨大な数のプログラミング言語を実行できるサイト「[CodingGround](https://www.tutorialspoint.com/codingground.htm)」があるのだが、データベースを確認できるサイトもあったなと思って検索したが、なかなか見つけることが出来なかった。検索キーワードを「データベース」ではなく「SQL」にしたら、あっさり見つかった。

今回は検索キーワードとして「連続値」がキーポイントであったので、タイトルに入れてみました。