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

はじめに

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

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

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

検証環境

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

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

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

他サイト

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

DB Fiddle

SQL Fiddle

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

Oracle Live SQL

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

rextester.com

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

連続値を取得

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

PostgreSQL

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

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

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に達しました。」

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

指定例

OPTION (MAXRECURSION 9999) --9999回まで
OPTION (MAXRECURSION 0) --無限

MySQL

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

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

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

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以降からになります。

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

日付リストを取得

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

PostgreSQL

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

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

目的の日付データ

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

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を使用します。

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では、ユーザー変数を用いて擬似的に再現しています。

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疑似列」を使います。

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」があるのだが、データベースを確認できるサイトもあったなと思って検索したが、なかなか見つけることが出来なかった。検索キーワードを「データベース」ではなく「SQL」にしたら、あっさり見つかった。

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