LoginSignup
14
11

More than 1 year has passed since last update.

PostgreSQLAdvent Calendar 2021Day 2
@kwatch

[SQL] 日付の抜けをカレンダーテーブルで埋める

Last updated at Posted at 2021-12-01

（PostgreSQL Advent Calendar 2021 2日目）

SQLの集計結果において日付の抜けがあるとき、カレンダーテーブル（日付表）を使うと簡単に埋められます。この記事では、カレンダーテーブルの使い方と作り方を説明します。主にPostgreSQLを対象としてますが、記事の最後のほうでMySQLでの作り方を説明します。

日付の抜けがある集計結果

日付をキーにして集計をするとき、日付が抜けてしまうことがあります。

たとえば次のSQLでは、注文テーブルをもとにして1週間分の売上金額を日付ごとに集計しています。

select 注文.日付, sum(注文.金額) as 売上
from 注文
where 注文.日付 between '2021-12-01' and '2021-12-07'
group by 注文.日付
order by 注文.日付

ここで、実行結果が次のようだとしましょう。

    日付    | 売上
------------+-------
 2021-12-01 |  8000
 2021-12-03 | 10800
 2021-12-05 | 20100
 2021-12-06 | 22000
 2021-12-07 | 25000
(5 rows)

この結果を見ると、1週間分の集計なのに 2021-12-02 と 2021-12-04 の日付が抜けていますね。これは、この日の注文がなかったせいです。

注文がなかった日の売上を0円として集計するにはどうしたらいいでしょうか。つまり次のような集計結果を得るにはどうしたらいいでしょうか。

    日付    | 売上
------------+-------
 2021-12-01 |  8000
 2021-12-02 |     0
 2021-12-03 | 10800
 2021-12-04 |     0
 2021-12-05 | 20100
 2021-12-06 | 22000
 2021-12-07 | 25000
(7 rows)

日付の抜けをカレンダーテーブルで埋める

ここで、カレンダーテーブルについて説明します。

「カレンダーテーブル」とは、日付の一覧が格納されたテーブルです。

select cal.*
from calendar as cal   -- ← これがカレンダーテーブル
where cal.date between '2021-12-01' and '2021-12-07';

実行結果：

    date
------------
 2021-12-01
 2021-12-02
 2021-12-03
 2021-12-04
 2021-12-05
 2021-12-06
 2021-12-07
(7 rows)

もしこのようなカレンダーテーブルがあれば、先ほどの集計結果と外部結合することにより、抜けた日付を埋めることができます。

select cal.date as 日付, coalesce(t.売上, 0) as 売上
from (
  /* 日付の抜けがある集計結果に、*/
  select 注文.日付, sum(注文.金額) as 売上
  from 注文
  where 注文.日付 between '2021-12-01' and '2021-12-07'
  group by 注文.日付
  -- order by 注文.日付
) as t
/* カレンダーテーブルを外部結合する */
right outer join calendar as cal on t.日付 = cal.date
where cal.date between '2021-12-01' and '2021-12-07'
order by cal.date

実行結果：

    日付    | 売上
------------+-------
 2021-12-01 |  8000
 2021-12-02 |     0
 2021-12-03 | 10800
 2021-12-04 |     0
 2021-12-05 | 20100
 2021-12-06 | 22000
 2021-12-07 | 25000
(7 rows)

このように、「日付の抜けがある集計結果」を「日付の抜けがないカレンダーテーブル」と外部結合することで、集計結果における日付の抜けを埋められます。

それでは、このようなカレンダーテーブルを作るにはどうしたらいいでしょうか。そのためには、PostgreSQLのgenerate_series()関数を使います。

PostgreSQLのgenerate_series()関数

PostgreSQLには、generate_series() という関数があります。これは連続した整数を生成するための関数であり、Pythonのrange()関数、Rubyの1..n、UNIXのseqコマンドと同様のものです。

select g.i
from generate_series(1, 7) as g(i);
  /* ここで「as g(i)」というエイリアスを使うことで、
     テーブル名として g、カラム名として i という別名がつく */

実行結果：

 i
---
 1
 2
 3
 4
 5
 6
 7
(7 rows)

整数は順番に生成されるので、SQLに order by 句をつける必要はありません。また第3引数にstepを指定でき、たとえば -1 を指定すると逆順を生成できます。

select g.i
from generate_series(7, 1, -1) as g(i);

実行結果：

 i
---
 7
 6
 5
 4
 3
 2
 1
(7 rows)

なお generate_series()関数は整数だけでなく日時(timestamp型)も生成できますが、今回は扱いません。またgenerate_series()関数についての詳細は、PostgreSQLのマニュアルを参照してください。

カレンダーテーブルを生成する

PostgreSQLでは、日付を表すdate型に次のような性質があります。

  • date型に整数を足した結果は、date型
  • date型からdate型を引いた結果は、integer型
/* date型に整数を足した結果は、date型 */
postgresql=> select '2021-12-31'::date + 7;
  ?column?
------------
 2022-01-07      -- ← date型
(1 row)

/* date型からdate型を引いた結果は、integer型 */
postgresql=> select '2021-12-31'::date - '2020-12-01'::date;
 ?column?
----------
       30        -- ← integer型
(1 row)

この性質と、先ほど説明したgenerate_series()関数を使えば、カレンダーテーブルが生成できます。たとえば 2021-12-01 から1週間の日付を生成するには、次のようにします。「日付」＋「0〜6の整数」で1週間分の日付を生成していることに注目してください。

select '2021-12-01'::date + g.i as date
from generate_series(0, 6) as g(i);

実行結果：

    date
------------
 2021-12-01
 2021-12-02
 2021-12-03
 2021-12-04
 2021-12-05
 2021-12-06
 2021-12-07
(7 rows)

これでカレンダーテーブルが簡単に生成できるようになりました。もし動作の仕組みが分かりにくいと感じたら、次のSQLを見れば分かるようになるでしょう。

select '2021-12-01'::date as date       -- date型
     , g.i                              -- integer型
     , '2021-12-01'::date + g.i as date -- date型+integer型
from generate_series(0, 6) as g(i);

実行結果：

    date    | i |    date
------------+---+------------
 2021-12-01 | 0 | 2021-12-01
 2021-12-01 | 1 | 2021-12-02
 2021-12-01 | 2 | 2021-12-03
 2021-12-01 | 3 | 2021-12-04
 2021-12-01 | 4 | 2021-12-05
 2021-12-01 | 5 | 2021-12-06
 2021-12-01 | 6 | 2021-12-07
(7 rows)

このカレンダーテーブルを使って、先ほどの集計結果の日付を埋めてみましょう。

select cal.date as 日付, coalesce(t.売上, 0) as 売上
from (
  /* 日付の抜けがある集計結果に、*/
  select 注文.日付, sum(注文.金額) as 売上
  from 注文
  where 注文.日付 between '2021-12-01' and '2021-12-07'
  group by 注文.日付
  -- order by 注文.日付
) as t
/* カレンダーテーブルを外部結合する */
right outer join (
  select '2021-12-01'::date + g.i as date
  from generate_series(0, 6) as g(i)
) as cal on t.日付 = cal.date
order by cal.date

実行結果：

    日付    | 売上
------------+------
 2021-12-01 | 2000
 2021-12-02 |    0
 2021-12-03 | 2000
 2021-12-04 |    0
 2021-12-05 | 2000
 2021-12-06 | 2000
 2021-12-07 | 2000
(7 rows)

ストアド関数を定義する

カレンダーテーブルを generate_series() で毎回作るのは、少し面倒です。そこで、これをストアド関数にしてしまいましょう。このとき、引数には3つのアプローチがあります。

  • (A) 開始日と日数（integer型）を指定する
  • (B) 開始日と終了日（date型）を指定する
  • (C) 開始日と期間（interval型）を指定する

(C)のinterval型というのは、PostgreSQLにおいて期間を表すデータ型です。たとえば '1 months'::interval で「1ヶ月」という期間を表し、'1 months - 1 days'::interval で「1ヶ月から1日を引いた期間」を表します1。またdate型とinterval型とを足すことができ、たとえば

  • '2021-12-01'::date に '1 months'::interval を足すと '2022-01-01 00:00:00'2（つまり1ヶ月後）になるし、
  • '1 months - 1 days'::interval を足すと '2021-12-31 00:00:00'（つまりその月の最終日）になります。
/* 1ヶ月後の日付を求める */
psql=> select '2021-12-01'::date + '1 months'::interval;
      ?column?
---------------------
 2022-01-01 00:00:00
(1 row)

/* その月の最終日を求める */
psql=> select '2021-12-01'::date + '1 months - 1 days'::interval;
      ?column?
---------------------
 2021-12-31 00:00:00
(1 row)

interval型についての詳細はPostgreSQLのマニュアルを参照してください。

これを踏まえたうえで、(A)と(B)と(C)それぞれの方法でストアド関数を定義してみます3。PostgreSQLのストアド関数は引数によるオーバーロードができるので、3つとも同じ関数名にしても問題なく共存できます。PostgreSQLにおけるストアド関数についての詳細はPostgreSQLのマニュアルを参照してください。

/* (A) 開始日と日数を指定する */
create or replace function calendar(start date, ndays integer)
returns table(date date) as $$
  begin
    return query
    select start + g.i
    from generate_series(0, ndays - 1) as g(i);
  end
$$ language plpgsql;

/* (B) 開始日と終了日を指定する */
create or replace function calendar(start date, last date)
returns table(date date) as $$
  begin
    return query
    select start + g.i
    from generate_series(0, last - start) as g(i);
  end                  /* 「last - start」が「日数 - 1」を表す */
$$ language plpgsql;

/* (C) 開始日と期間を指定する */
create or replace function calendar(start date, period interval)
returns table(date date) as $$
  begin
    return query
    select start + g.i
    from generate_series(0, (start + period)::date - 1 - start) as g(i);
  end                  /* 「(start + period)::date - 1」が終了日を表す */
$$ language plpgsql;

それぞれの使い方は次のようになります。

/* (A) 開始日と日数を指定する */
select * from calendar('2021-12-01', 7);

/* (B) 開始日と終了日を指定する */
select * from calendar('2021-12-01', '2021-12-07'::date);

/* (C) 開始日と期間を指定する */
select * from calendar('2021-12-01', '1 weeks'::interval);
-- または
select * from calendar('2021-12-01', '7 days'::interval);

実行結果はどれも同じです。

    date
------------
 2021-12-01
 2021-12-02
 2021-12-03
 2021-12-04
 2021-12-05
 2021-12-06
 2021-12-07
(7 rows)

なお「(C) 開始日と期間を指定する」の方法は、「1ヶ月間」のように日数では指定しにくい期間を指定するときに便利です（「1ヶ月間」の日数は月によって異なるため）。

/* 1ヶ月分のカレンダー */
psql=> select * from calendar('2021-12-01'::date, '1 months'::interval);

    date
------------
 2021-12-01
 2021-12-02
 2021-12-03
 ....省略....
 2021-12-30
 2021-12-31
(31 rows)

またストアド関数にしない場合と比べて、ストアド関数にしたほうがほんのわずか（0.1ミリ秒程度）遅くなります。つまりストアド関数のほうが動作コストがかかります。ただしその動作コストは、集計作業にかかる動作コストに比べれば無視できる程度のはずなので、気にする必要はありません（気になるなら自分自身で計測してみてください）。

カレンダーテーブルを再帰SQLで作る

generate_series()関数はPostgreSQL用なので、他のRDBMSでは使えません。そのような場合でも、再帰SQLを使えばカレンダーテーブルを定義できます。たとえばMySQLでは8.0から再帰SQLが使えるようになったので、MySQL 8.0で試してみましょう。

/* MySQL 8.0: 再帰SQLを使って、1週間分のカレンダーを生成する */
with recursive cal as (
    select cast('2021-12-01' as date) as date
  union all
    select date_add(cal.date, interval 1 day)
    from cal
    where cal.date < '2021-12-07'
  )
select * from cal;

実行結果：

+------------+
| date       |
+------------+
| 2021-12-01 |
| 2021-12-02 |
| 2021-12-03 |
| 2021-12-04 |
| 2021-12-05 |
| 2021-12-06 |
| 2021-12-07 |
+------------+
7 rows in set (0.01 sec)

このように、再帰SQLを使ってもカレンダーテーブルを生成できます。ただし、PostgreSQLでは generate_series() 関数を使ったほうが柔軟な指定ができ、パフォーマンスもいいでしょう。

なお再帰SQLについては、MySQLのマニュアルやPostgreSQLのマニュアルを参照してください。

カレンダーテーブルを連番テーブルで作る

MySQL 5.xのように再帰SQLが使えない場合は、連番が格納されたテーブルを使えばカレンダーテーブルを生成できます。

たとえば、0〜999までの連番が格納されたテーブル「seq999」を用意します4。

create table seq999 (
  i   integer   primary key
);

insert into seq999(i)
values (0)
     , (1)
     , (2)
     , (3)
     /* ....省略.... */
     , (999);

insert文はプログラムで生成するといいでしょう。UNIXなら次のようにします。

$ echo "insert into seq999(i)"             >  insert.sql
$ echo "values (0)"                        >> insert.sql
$ seq 1 999 | sed 's/\(.*\)/     , (\1)/'  >> insert.sql

このような連番テーブルがあれば、カレンダーテーブルは次のようにして生成できます。再帰SQLよりも簡単なので、MySQL 8.0であってもこちらのほうがいいかもしれません。

select date_add('2021-12-01', interval seq.i day) as date
from seq999 as seq
where seq.i < 7;

実行結果 on MySQL：

+------------+
| date       |
+------------+
| 2021-12-01 |
| 2021-12-02 |
| 2021-12-03 |
| 2021-12-04 |
| 2021-12-05 |
| 2021-12-06 |
| 2021-12-07 |
+------------+
7 rows in set (0.01 sec)

なお連番テーブルの作り方については、書籍『達人に学ぶSQL徹底指南書 第2版』の「第10章 SQLで数列を扱う」にも説明があります。持っている人は読んでみてください。

まとめ

この記事ではカレンダーテーブル（日付表）について説明しました。カレンダーテーブルを使うと抜けた日付を埋めることができるので、日付をキーにした集計結果で使うといいでしょう。

またカレンダーテーブルを作る方法と、それをストアド関数にする方法を紹介しました。カレンダーテーブルの作成は、PostgreSQLではgenerate_series()関数を使い、他のRDBMSでは再帰SQLまたは連番テーブルを使いましょう。

集計作業において、カレンダーテーブルは重宝します。もし日付をキーにした集計用SQLにおいてカレンダーテーブルを使っていないなら、潜在的なバグだといえます。しかし集計のたびにカレンダーテーブルをいちいち作るのは面倒です。カレンダーテーブルを生成するストアド関数が標準で使える世界線が来てほしいものです。

  1. Postgresqlのinterval型では、三単現の 's' をつけてもいいし、つけなくてもいいです。つまり '1 month' と '1 months' のどちらでも構いません。また '1 mon' や '1 mons' のような短い書き方もできます。 ↩

  2. date型にinterval型を足した結果は、date型ではなくtimestamp型になります。そのため、実行結果は 2021-01-01 や 2020-12-31 ではなく 2021-01-01 00:00:00 や 2020-12-31 00:00:00 になります。 ↩

  3. なおこれらのストアド関数では、逆順での日付の生成をサポートしていません。必要ならカスタマイズするか、SQLに order by date desc をつけてください。 ↩

  4. 連番の個数は適宜変更してください。また連番は、0始まりと1始まりの両方が考えられます。その場合のテーブル名は、格納する連番が0〜999なら（つまり0始まりなら）「seq999」、1〜1000なら（つまり1始まりなら）「seq1000」という名前にするといいでしょう。 ↩

14
11
4

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
14
11