[SQL] 連番データ行や期間内の全日付行を生成する方法 [SQL Server]

SQL で連番データ行や範囲内の全日付行を生成する方法

SQL で何もないところから連続するデータを生成する方法をまとめます。対象データ自体は歯抜けになっているけれど、固定件数分取得したい場合などの要件を満たそうとすると、ここでまとめた方法が使えるかもしれません。

今回は SQL Server を対象としますが、考え方は別のデータベースでも使えるはずです。

連番のデータを生成できると、範囲内の日付をすべて生成することもできます。たとえば 2019/4/1～2019/4/30 までの範囲のすべての日付を行で生成することができるということです。

連番を生成する

連番表を生成する方法としては2つあります。

1つ目は 再帰クエリ を使う方法です。これは構文は違えど、どのデータベースでも使える方法だと思います。

2つ目は 別テーブル(例えばシステムビュー)を使って、行番号だけを ROWNUMBER() で取得する方法です。

順番に見ていきます。

再帰クエリでN行生成する

再帰クエリは再帰的な問い合わせを行うSELECT文です。共通テーブル式(CTE)を使うことで再帰問い合わせが可能になります。

DECLARE @N INT = 10;

WITH SeqTable AS (
    SELECT 1 AS SEQ
    UNION ALL
    SELECT SEQ+1 FROM SeqTable WHERE SEQ < @N
)
SELECT SEQ FROM SeqTable OPTION(MAXRECURSION 32767);

--1
--2
--3
--..
--10

上記SQLは再帰的な問い合わせを行い、N行のデータを取得します。取得するデータは連番となり、1～Nまでのデータとなります。

WITH句内で再帰呼び出しが行われ、SEQ+1 が再帰的に呼び出されて連番行が次々にUNIONされていくようなイメージです。そしてSEQがN以上になったとき、再帰呼び出しが中断されます。

ここで再帰的に生成された共通テーブルに対して問い合わせを行うと、連番表が取得できます。

なお、SQL Server において、再帰クエリは最大32767回の再帰呼び出しを行えます。ただしデフォルトだと上限値が100になっているので、OPTION(MAXRECURSION 32767) として上限値を設定しています。

最大回数でも速度的に問題なさそうです。

ROWNUMBER() を使って連番を生成する

ROW_NUMBER() 関数は行番号(順位)を返してくれる関数です。ある並び順を指定することで、その並び順での順位が得られます。

これを使って以下のようにすることで連番データが生成できます。

DECLARE @MAX INT = 10;

SELECT TOP(@MAX)
    ROW_NUMBER() OVER (ORDER BY object_id) AS SEQ
FROM sys.all_objects

--1
--2
--3
--..
--10

やっていることは sys.all_objects テーブルのデータを object_id の順で並べたときの 行番号(順位)を ROW_NUMBER() 関数で取得するといった感じです。

当然ですがここで生成できる連番数は sys.all_objects のデータ数に依存します。

sys.all_objects (TRANSACT-SQL) – SQL Server | Microsoft Docs

sys.all_objects は SQL Server で内部的に使われるシステムビューで、データ数も数千程度なのであまり多くのデータ生成には使えません。

もちろん別のビューやテーブルを対象にしても連番は生成できますが、実行時にソートが行われるため注意しましょう。

あまり大量の連番生成には向かないかもしれません。再帰クエリの方が汎用的と思います。

日付範囲からすべての対象日付を生成する

連番データが生成できるようになると、その応用で日付範囲を指定して、対象日付をすべて生成するというようなこともできるようになります。

DECLARE @START DATE = '2019/01/01';
DECLARE @END DATE = '2019/12/31';

WITH DateTable AS (
    SELECT @START AS MyDate
    UNION ALL
    SELECT DATEADD(DAY, 1, MyDate) FROM DateTable WHERE MyDate < @END
)
SELECT MyDate FROM DateTable OPTION(MAXRECURSION 32767);

--2019-01-01
--2019-01-02
--2019-01-03
--..
--2019-12-31

365行分のデータが生成されていることが確認できます。

やっていることは連番生成時に Seq+1 としていたところを DATEADD() 関数を使って1日ずつ足していく形にしただけです。

これで指定範囲の日付を生成できるようになりました。

逆順で生成したり、九九表を生成したりしてみる

逆順

応用編として、連番をN～1の順で生成してみます。

DECLARE @MAX INT = 10;

WITH SeqTable AS (
    SELECT @MAX AS SEQ
    UNION ALL
    SELECT SEQ-1 FROM SeqTable WHERE SEQ > 1
)
SELECT SEQ FROM SeqTable OPTION(MAXRECURSION 32767);

--10
--9
--8
--..
--1

九九表

九九表も作れたりします。

WITH SeqTable AS (
    SELECT
        1 AS SEQ 
        , 1 AS X1
        , 2 AS X2
        , 3 AS X3
        , 4 AS X4
        , 5 AS X5
        , 6 AS X6
        , 7 AS X7
        , 8 AS X8
        , 9 AS X9
    UNION ALL
    SELECT 
        SEQ+1
        , X1+1
        , X2+2
        , X3+3
        , X4+4
        , X5+5
        , X6+6
        , X7+7
        , X8+8
        , X9+9
     FROM SeqTable WHERE SEQ < 9
)
SELECT * FROM SeqTable OPTION(MAXRECURSION 32767);

--1    1    2    3    4    5    6    7    8    9
--2    2    4    6    8    10    12    14    16    18
--3    3    6    9    12    15    18    21    24    27
--4    4    8    12    16    20    24    28    32    36
--5    5    10    15    20    25    30    35    40    45
--6    6    12    18    24    30    36    42    48    54
--7    7    14    21    28    35    42    49    56    63
--8    8    16    24    32    40    48    56    64    72
--9    9    18    27    36    45    54    63    72    81

繰り返して1~9まで足し合わせています。

以上。