日付や時刻の処理は、データベース操作において非常に重要な要素です。SQLを使用して日付や時刻のデータを効果的に操作することは、データのフィルタリング、集計、比較など、さまざまな処理を実行するために必要なスキルです。
日付や時刻のフォーマット、日付や時刻の演算、日付や時刻の関数、を問題→解決案→解説の形式で書いていきます。
簡単な問題から始めて最終的には、高度なSQLを組める様になるかと思いますので、是非最後まで読んで下さい。
この記事で使用するサンプルデータは下記記事内に記載してあるSQLで作成可能なので、必要な方は利用して下さい。不足するデータやテーブルは、記事内でCREATE文とINSERT文を記載します。
日、月、年の加算や減算
問題
ある日付に対してある数の日、月、年の加算や減算を行う必要がある。例えば、従業員CLARKのHIREDATEを使って、CLARK雇用の5日前と5日後の日付、CLARK雇用の5ヶ月前と5ヶ月後の日付、最後にCLARK雇用の5年前と5年後の日付の6つの異なる日付を返したい。
解決案
時間の単位を指定するINTERVAL句を付けて標準の加算と減算を使います。
select hiredate - interval 5 day as hd_minus_5D,
hiredate + interval 5 day as hd_plus_5D,
hiredate - interval 5 month as hd_minus_5M,
hiredate + interval 5 month as hd_plus_5M,
hiredate - interval 5 year as hd_minus_5Y,
hiredate + interval 5 year as hd_plus_5Y
from emp
where deptno=10;または、次のようにDATE_ADD関数を使う方法でも解決可能です。
select date_add(hiredate,interval -5 day) as hd_minus_5D,
date_add(hiredate,interval 5 day) as hd_plus_5D,
date_add(hiredate,interval -5 month) as hd_minus_5M,
date_add(hiredate,interval 5 month) as hd_plus_5M,
date_add(hiredate,interval -5 year) as hd_minus_5Y,
date_add(hiredate,interval 5 year) as hd_plus_5DY
from emp
where deptno=10;解説
INTERVAL句とそれに伴う文字列リテラルは、ISO標準のSQL構文に相当します。この標準では、インターバル値をシングルクォートで囲む必要があります。MySQLはクォートのサポートを省略しており、標準から少し外れています。
2つの日付間の日数を求める
問題
2つの日付の差を求め、その結果を日数で表したい。例えば、従業員ALLENと従業員WARDのHIREDATEの日数の差を知りたい。
解決案
DATEDIFF関数を使って2つの日付間の日数を求めます。MySQLのDATEDIFFは2つのパラメータ（日数の差を求めたい2つの日付）だけが必要で、負の値を避けるために2つの日付の小さい方を最初に渡します。
select datediff(ward_hd, allen_hd)
from (
select hiredate as ward_hd
from emp
where ename = 'WARD'
) x,
(
select hiredate as allen_hd
from emp
where ename = 'ALLEN'
) y;解説
インラインビューXは従業員WARDのHIREDATEを、インラインビューYはALLENのHIREDATEを返します。XとYの間には結合が指定されていないので、直積が作成されることます。この例の場合、XとYのカーディナリティはどちらも1なので、結合がなくても弊害はありません。したがって、結果セットは最終的に1行になります。
後は、差分を求めるだけです。
2つの日付間の平日の日数を求める
問題
ある2つの日付間（その日付も含む）の「平日」の日数を求めたい。例えば、1月10日が月曜で1月11日が火曜の場合、この両日は一般的に平日であるため、この2つの日付間の平日の日数は2日である。「平日」とは土日以外の日と仮定する。
解決案
色々な方法がありますが、ここでは簡単にするために、以下のシーケンステーブルを使用します。
-- シーケンステーブルを作成(T500)
create table t500 (
ID int
);
drop procedure if exists loop_sample;
-- 区切り文字を一時的に「;」→「//」に変更する
delimiter //
-- loop_sampleというプロシージャーを作成する
create procedure loop_sample(in i int)
begin
-- 変数cntの定義
declare cnt int default 0;
-- 繰り返し処理
while cnt < i do
set cnt = cnt + 1;
insert into t500 values(cnt);
end while;
end
//
-- プロシージャーを実行する
call loop_sample(500);次の様に、週末以外の日数をカウントします。DATE_ADD関数を使って各日付に日数を加えます。DATA_FORMAT関数を使って各日付の曜日名を取得します。
select sum(case when date_format(
date_add(jones_hd,
interval t500.id-1 DAY),'%a')
in ('Sat','Sun')
then 0 else 1
end) as days
from (
select max(case when ename = 'BLAKE'
then hiredate
end) as blake_hd,
max(case when ename = 'JONES'
then hiredate
end) as jones_hd
from emp
where ename in ('BLAKE','JONES')
) x,
t500
where t500.id <= datediff(blake_hd,jones_hd)+1解説
次の手順をSQLに置き換えます。
- 開始日と終了日間（開始日と終了日を含む）の日付を返す。
- 週末を除いた日数をカウントする。
インラインビューXはステップ1を実行します。インラインビューXを調べると、集約関数MAXが使われていることに気付くでしょう。このレシピでは、MAX関数を使ってNULLを削除します。
2つの日付間（両日を含む）の日数は30日です。2つの日付が1つの行に存在するので、次に30日の日付ごとに1行を作成します。30日（行）を返すには、T500テーブルを使います。T500テーブル内のIDの値は直前の値より1つ大きいだけなので、T500が返す各行を2つの日付の早い方（JONES_HD）に追加し、JONES_HDからBLAKE_HDまでの連続する日付を作成します。
WHERE句を調べると、BLAKE_HDとJONES_HDの差に1を加えて30行を作成しています。また、IDの値は1から始まり、JONES_HDに1を加えるとJONES_HDは最終的なカウントから除外されるため、外側のクエリのSELECTリストではT500.IDから1を引いています。
結果セットに必要な数の行を作成したら、CASE式を使って、返された日付が平日か週末かの「フラグ」を付けます（平日の場合は1を返し、週末の場合は0を返します）。最後に、集約関数SUMを使って1の数を集計し最終的な答えを求めています。
2つの日付間の月数や年数を求める
問題
2つの日付の差を月数か年数のどちらかで知りたい。例えば、最初と最後の従業員雇用日の間の月数を知りたい。また、その値を年数としても表現したい。
解決案
1年は必ず12ヶ月なので、2つの日付間の月数を求めて12で割ると年数が求められます。この解決策に慣れたら、どのような年数が必要かによって結果の端数を切り上げたり切り捨てたりしたいでしょう。例えば、EMPテーブルの先頭のHIREDATEは17-DEC-2005で、末尾は12-JAN-2008です。年の計算を行うと（2008から2005を引いて）3年となりますが、月数の差は約25ヶ月（約2年強）です。各自の状況に合うように解決策を微調整してください。
YEAR関数とMONTH関数を使って、渡した日付の4桁の年と2桁の月を返します。
select mnth, mnth/12
from (
select (year(max_hd) - year(min_hd))*12 +
(month(max_hd) - month(min_hd)) as mnth
from (
select min(hiredate) as min_hd, max(hiredate) as max_hd
from emp
) x
) y;解説
インラインビューXはEMPテーブルの最初と最後のHIREDATEを返します。次に、MAX_HDとMIN_HD間の月数を求めるには、MIN_HDとMAX_HDの年数の差に12をかけ、MAX_HDとMIN_HDの月の差を加えます。MIN_HDとMAX_HD間の年数を求めるには、月数を12で割ります。ここでも、どのような結果が必要かによって、この値を切り上げたり切り捨てたりすると良いでしょう。
2つの日付間の秒数、分数、時間数を求める
問題
2つの日付間の秒数の差を返したい。例えば、ALLENとWARDのHIREDATEの差を秒、分、時間で返したい。
解決案
秒、分、時間は1日を構成する時間の単位なので、2つの日付間の日数がわかれば、秒数、分数、時間数がわかります。
DATEDIFF関数を使ってALLEN_HDとWARD_HD間の日数を返します。そして、乗算を行って各時間の単位を求めます。
select datediff(day,allen_hd,ward_hd)*24 hr,
datediff(day,allen_hd,ward_hd)*24*60 min,
datediff(day,allen_hd,ward_hd)*24*60*60 sec
from (
select max(case when ename = 'WARD'
then hiredate
end) as ward_hd,
max(case when ename = 'ALLEN'
then hiredate
end) as allen_hd
from emp
) x;解説
インラインビューXはWARDとALLENのHIREDATEを返します。後は、WARD_HDとALLEN_HD間の日数に24（1日の時間数）、1440（1日の分数）、86400（1日の秒数）をかけます。
1年間の各曜日の出現回数をカウント
問題
1年間の各曜日の出現回数をカウントしたい。
解決案
1年間の各曜日の出現回数を知るには、以下を行う必要があります。
- その年のすべての日付を作成する。
- 各曜日名がわかるように日付をフォーマットする。
- 各曜日名の出現回数をカウントする。
先程作成した、T500テーブルに対してSELECTを実行し、その年のすべての日付を返すのに十分な行数を生成します。DATE_FORMAT関数を使って各日付の曜日名を取得し、各曜日名の出現回数をカウントします。
select date_format(
date_add(
cast(
concat(year(current_date),'-01-01')
as date),
interval t500.id-1 day),
'%W') day,
count(*)
from t500
where t500.id <= datediff(
cast(
concat(year(current_date)+1,'-01-01')
as date),
cast(
concat(year(current_date),'-01-01')
as date))
group by date_format(
date_add(
cast(
concat(year(current_date),'-01-01')
as date),
interval t500.id-1 day),
'%W');解説
T500テーブルに対してSELECTを実行し、その年の各日付に対して1行を作成します。concat(year(current_date),’-01-01′) 部分はは現在の年の最初の日付を返します。これには、CURRENT_DATE関数が返す日付の年を返し、月と日を追加します。
上記解説部で、現在の年の最初の日付が得られたので、DATEADD関数を使ってT500.IDの各値を加え、その年のすべての日付を作成します。DATE_FORMAT関数を使って各日付の曜日を返します。T500テーブルから必要な数の行を作成するには、現在の年の最初の日付と次の年の最初の日付の日数の差を求めその数の行を返します。
これで現在の年のすべての日付を返すことができるので、DAYNAME関数が返す各曜日の出現回数をカウントします。
現在のレコードと次のレコードの日付の差を求める
問題
2つの日付間の日数の差を求めたい。例えば、部署10の全従業員に対して、次に雇用された従業員との雇用日の日数の差を求めたい。
解決案
簡単に解く方法は、現在の従業員の雇用日以降の最初のHIREDATEを探すことです。その後日数の差分を求めれば答えが出ます。
select x.ename, x.hiredate, x.next_hd,
datediff(x.next_hd, x.hiredate) as diff
from (
select deptno, ename, hiredate,
lead(hiredate)over(order by hiredate) as next_hd
from emp e
) x
where x.deptno=10;解説
ウィンドウ関数LEADを使い、2つの日付間の日数の差を求めます。
ウィンドウ関数ではWHERE句の後に評価されるため、インラインビューが必要であることに注意してください。
基本編のまとめ
ここまでで簡単な日付時刻操作のSQL基本編は終わりです。もし難しいと感じられた方は、以下の記事を読んでみると良いかと思います。
日付や時刻は一般的なデータ型ですが、単純な数値データ型よりも構造化されているので独特のくせがあります。相対的に見れば、他の分野よりもデータベース間であまり標準化されていませんが、どのデータベースにも同じ処理を行う主要な関数群があります。ただし、構文は少し異なります。日付や時刻の関数を使いこなせるようになれば、日付や時刻をうまく扱えるようになります。
ここから先は、もう少し高度で実践的な内容に踏み込んでいきます。
うるう年かどうかを判定
問題
現在の年がうるう年かどうかを判定したい。
解決案
アプリケーションを作ったことがある方であれば、この問題を色々な手法で解決してきたと思います。
この記事で紹介する方法は、一番簡単な手法で、2月の最終日を調べ、最終日が29日なら、現在の年はうるう年だと判断します。
LAST_DAY関数を使って2月の最終日を求めます。
select day(
last_day(
date_add(
date_add(
date_add(current_date,
interval -dayofyear(current_date) day),
interval 1 day),
interval 1 month))) dy
from t1;解説
まずは現在の日付からその日までの日数を引いてから1日を足し、現在の年の最初の日を求めます。すべての処理はDATE_ADD関数で行います。更にDATE_ADD関数を使って1ヶ月を加えます。これで2月になったので、LAST_DAY関数を使ってこの月の最終日を求めます。
別解
よくアプリケーションでこの判定を入れる際に、下記の様な計算を行うと思います。
年が4で割り切れて100で割り切れない場合、または年が400で割り切れる場合はうるう年とする。
SET @year = 2024;
SELECT
@year AS Input_Year,
CASE
WHEN @year % 4 = 0 AND @year % 100 != 0 OR @year % 400 = 0 THEN 'うるう年です。'
ELSE 'うるう年ではありません。'
END AS Leap_Year_Status;上記は@yearという変数に判定したい年を設定し、それを基準に閏年を判定します。
CASE式内で年が4で割り切れて100で割り切れない場合、または年が400で割り切れる場合を判定し、出力を分けています。
汎用的に利用できる様に、変数@yearで判定を行うようにしてあるので組み込みやすいと思います。
1年の日数を計算
問題
現在の年の日数をカウントしたい。
解決案
これもよくある例題です。現在の年の日数は、次の年の最初の日と現在の年の最初の日の差です。次の手順をSQLで実装するだけです。
- 現在の年の最初の日を求める。
- その日付に1年を加える（次の年の最初の日を得るため）。
- 手順2の結果から現在の年の最初の日を引く。
MySQLでは、ADDDATEを使って、現在の年の最初の日を求めます。DATEDIFFと時間間隔計算を使って、その年の日数を求めます。
select datediff((curr_year + interval 1 year),curr_year)
from (
select adddate(current_date,-dayofyear(current_date)+1) curr_year
from t1
) x;日付から単位時間を抽出
問題
現在の日付を日、月、年、秒、分、時の6つの部分に分割したい。その結果を数値として返したい。
解決案
現在の日付を使いましたが何でも良いです。
DATE_FORMAT関数を使って、日付から特定の単位時間を返します。
select date_format(current_timestamp,'%k') hr,
date_format(current_timestamp,'%i') min,
date_format(current_timestamp,'%s') sec,
date_format(current_timestamp,'%d') dy,
date_format(current_timestamp,'%m') mon,
date_format(current_timestamp,'%Y') yr
from t1;解説
簡単な内容なので、解説する事もないのですが、DATE_FORMAT関数はGROUPINGのキーとしても使用できたり、SQL結果をピボット展開や見出しの整形にも使用できるので、使い方を覚えると良いです。
月の最初の日と最終日を求める
問題
現在の月の最初の日と最終日を求めたい。
解決案
これも頻出問題で、頻繁にプログラムで書くことがあります。現在の月を使いましたが、先ほどの問題と同じで何でも構いません。少し調整をすれば、任意の月に対して使えるようにすることができます。
DATE_ADD関数とDAY関数を使って、現在の日付の月のその日までの日数を求めます。そして、その値を現在の日付から引いて1を加えてその月の最初の日を求めます。現在の月の最終日を求めるには、LAST_DAY関数を使います。
select date_add(current_date,
interval -day(current_date)+1 day) firstday,
last_day(current_date) lastday
from t1;解説
その月の最初の日を求めるには、DAY関数を使います。DAY関数は、渡した日付の月の日を返します。DAY(CURRENT_DATE)が返す値を現在の日付から引くと、前月の最終日が得られます。その日付に1日を加えて、現在の月の最初の日を得ます。現在の月の最終日を求めるには、LAST_DAY関数を使います。
1年の中の特定の曜日のすべての日付を求める
問題
1年の中のある特定の曜日のすべての日付を見つけたい。例えば、現在の年の金曜日の一覧を作成したい。
解決案
これもスケジュール管理アプリやTODOリストアプリなんかを作る際に必ずと言って良いほど使用します。
解決策のポイントは現在の月のすべての日付を取得し、対象となる曜日の日付だけを残すことです。サンプルは、すべての金曜日を残します。
再帰CTEを使ってその年のすべての日付を取得し、金曜日ではない日付を取り除きます。
with recursive cal (dy,yr)
as
(
select dy, extract(year from dy) as yr
from
(select adddate(adddate(current_date, interval - dayofyear(current_date) + 1 day), interval 1 day) as dy) as tmp1
union all
select date_add(dy, interval 1 day), yr
from cal
where extract(year from date_add(dy, interval 1 day)) = yr
)
select dy from cal
where dayofweek(dy) = 6;解説
現在の年のすべての金曜日を求めるには、現在の年のすべての日を取得する必要があります。まず、DAYOFYEAR関数を使ってその年の最初の日を求めます。DAYOFYEAR(CURRENT_DATE)が返す値を現在の日付から引いて1を加え、現在の年の最初の日を求めます。
カレンダーを作成
問題
現在の月のカレンダーを作成したい。横7列、（通常は）縦5行の卓上カレンダーのような形式にする。
解決案
正直、SQLでやるべき事ではない気がしますが、以下の様に実行すると当月のカレンダーが作成できます。
現在の月のすべての日付を返し、その月の各週を曜日ごとに並べてカレンダーを作成します。
カレンダーに使える形式にはさまざまな形式があります。例えば、UnixのCALコマンドは、日曜日から土曜日の順に曜日をフォーマットします。
再帰CTEを使って、現在の月のすべての日付を返します。そして、MAXとCASEを使って曜日ごとに並べます。
WITH RECURSIVE x(dy, dm, mth, dw, wk) AS (
SELECT
dy,
DAY(dy) AS dm,
MONTH(dy) AS mth,
DAYOFWEEK(dy) AS dw,
CASE
WHEN DAYOFWEEK(dy) = 1 THEN WEEK(dy) - 1
ELSE WEEK(dy)
END AS wk
FROM (
SELECT DATE_ADD(CURRENT_DATE, INTERVAL -DAY(CURRENT_DATE) + 1 DAY) AS dy
FROM t1
) x
UNION ALL
SELECT
DATE_ADD(dy, INTERVAL 1 DAY),
DAY(DATE_ADD(dy, INTERVAL 1 DAY)),
mth,
DAYOFWEEK(DATE_ADD(dy, INTERVAL 1 DAY)),
CASE
WHEN DAYOFWEEK(DATE_ADD(dy, INTERVAL 1 DAY)) = 1 THEN WEEK(DATE_ADD(dy, INTERVAL 1 DAY)) - 1
ELSE WEEK(DATE_ADD(dy, INTERVAL 1 DAY))
END
FROM x
WHERE MONTH(DATE_ADD(dy, INTERVAL 1 DAY)) = mth
)
SELECT
MAX(CASE WHEN dw = 2 THEN dm END) AS Mo,
MAX(CASE WHEN dw = 3 THEN dm END) AS Tu,
MAX(CASE WHEN dw = 4 THEN dm END) AS We,
MAX(CASE WHEN dw = 5 THEN dm END) AS Th,
MAX(CASE WHEN dw = 6 THEN dm END) AS Fr,
MAX(CASE WHEN dw = 7 THEN dm END) AS Sa,
MAX(CASE WHEN dw = 1 THEN dm END) AS Su
FROM x
GROUP BY wk
ORDER BY wk;解説
最初の部分では、WITH RECURSIVEを使用して再帰的なクエリを定義しています。xという仮想テーブルを作成し、その中で日付(dy)、日(dm)、月(mth)、曜日(dw)、週(wk)の列を取得しています。
再帰的な部分は、最初の行の日付(dy)を基準にして、1日ずつ増やしながら次の日付を生成する処理です。DATE_ADD関数を使用して日付を増やし、日(dm)、月(mth)、曜日(dw)、週(wk)の値を取得しています。月が変わると再帰が終了するように、WHERE句で現在の月(mth)と一致する場合にのみ再帰が行われます。
メインクエリの部分では、再帰的に生成されたテーブル x を使用して、曜日ごとに日付をグループ化しています。CASE文を使用して、曜日ごとに該当する日(dm)を取得し、それぞれの曜日に対応する列名(Mo, Tu, We, Th, Fr, Sa, Su)で表示しています。
最後に、週ごとにグループ化し、結果を週(wk)の昇順で並べ替えています。
長く複雑なSQL文は、人ブロックずつ分解して実行することで理解できます。
別解
カレンダーを作成する方法は多々ありますが、大概の方はアプリケーション側（JAVAやPHPなど）で解決していると思いますが、下記の様な簡単なSQL文でも作成可能です。
SET @YEAR = 2023;
SET @MONTH = 6;
SET @START_DATE = CONVERT(CONCAT(@YEAR, '-', @MONTH, '-01'), DATE);
SET @END_DATE = LAST_DAY(@START_DATE);
SET @FIRST_WEEK_START_DATE = DATE_ADD(@START_DATE, INTERVAL ((DAYOFWEEK(@START_DATE) - 1) * -1) DAY);
SET @LAST_WEEK_END_DATE = DATE_ADD(@END_DATE, INTERVAL (7 - (DAYOFWEEK(@END_DATE))) DAY);
WITH RECURSIVE DATE_TABLE(DATE_VALUE) AS (
SELECT @FIRST_WEEK_START_DATE
UNION ALL
SELECT DATE_ADD(DATE_VALUE, INTERVAL 1 DAY)
FROM DATE_TABLE
WHERE DATE_VALUE < @LAST_WEEK_END_DATE
)
SELECT
WEEK(DATE_VALUE) AS WEEK_NO,
MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 1 THEN DATE_VALUE END) AS SUNDAY,
MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 2 THEN DATE_VALUE END) AS MONDAY,
MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 3 THEN DATE_VALUE END) AS TUESDAY,
MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 4 THEN DATE_VALUE END) AS WEDNESDAY,
MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 5 THEN DATE_VALUE END) AS THURSDAY,
MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 6 THEN DATE_VALUE END) AS FRIDAY,
MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 7 THEN DATE_VALUE END) AS SATURDAY
FROM DATE_TABLE
GROUP BY WEEK_NO
ORDER BY WEEK_NO;- 最初に、指定された年と月から開始日と終了日を設定します。
- 開始日から最初の週の開始日を計算し、最終週の終了日を設定します。
WITH RECURSIVEを使用して、開始日から終了日までの日付一覧を生成します。再帰的なCTE (Common Table Expression) を使用することで、日付の連続性を確保します。- 日付一覧を基に、曜日ごとに行を展開し、週番号と曜日ごとの日付を取得します。CASE式を使用して、各曜日に対応する日付を選択します。
- 得られた結果を週番号でグループ化し、曜日ごとの最大日付を取得します。これにより、1週間ごとに1つのレコードが生成されます。
- 週番号で結果を並べ替えます。
これにより、指定した年月のカレンダーデータが週ごとに表示されます。各週の日曜日から土曜日までの日付が表示されます。
1年分のカレンダーを表示
年間カレンダーをSQLで作成できると便利な場合がありますので、ついでに置いておきます。
SET @YEAR = 2023;
WITH RECURSIVE MonthCalendar AS (
SELECT 1 AS Month
UNION ALL
SELECT Month + 1
FROM MonthCalendar
WHERE Month < 12
),
CalendarDates AS (
SELECT
CONCAT(@YEAR, '-', LPAD(Month, 2, '00'), '-01') AS StartDate,
LAST_DAY(CONCAT(@YEAR, '-', LPAD(Month, 2, '00'), '-01')) AS EndDate
FROM MonthCalendar
),
CalendarTable AS (
SELECT
StartDate,
EndDate,
DATE_ADD(StartDate, INTERVAL ((DAYOFWEEK(StartDate) - 1) * -1) DAY) AS FirstWeekStartDate,
DATE_ADD(EndDate, INTERVAL (7 - DAYOFWEEK(EndDate)) DAY) AS LastWeekEndDate
FROM CalendarDates
),
DateTable AS (
SELECT
DATE_VALUE
FROM CalendarTable
CROSS JOIN (
SELECT StartDate + INTERVAL (n - 1) DAY AS DATE_VALUE
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY a.n) AS n
FROM (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) a
CROSS JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) b
) AS numbers
CROSS JOIN CalendarTable
WHERE StartDate + INTERVAL (n - 1) DAY BETWEEN FirstWeekStartDate AND LastWeekEndDate
) AS dates
)
SELECT
WEEK(DATE_VALUE) AS WEEK_NO,
MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 1 THEN DATE_VALUE END) AS SUNDAY,
MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 2 THEN DATE_VALUE END) AS MONDAY,
MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 3 THEN DATE_VALUE END) AS TUESDAY,
MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 4 THEN DATE_VALUE END) AS WEDNESDAY,
MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 5 THEN DATE_VALUE END) AS THURSDAY,
MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 6 THEN DATE_VALUE END) AS FRIDAY,
MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 7 THEN DATE_VALUE END) AS SATURDAY
FROM DateTable
GROUP BY WEEK_NO
ORDER BY WEEK_NO;MonthCalendarCTE: まず、1から12までの数値を持つ仮想テーブルMonthCalendarを作成します。これは、表示したい年の各月を表すために使用されます。CalendarDatesCTE:MonthCalendarを利用して、指定した年の各月の開始日と終了日を計算します。開始日は各月の1日であり、終了日はLAST_DAY関数を使用して求められます。CalendarTableCTE:CalendarDatesの結果を元に、各月のカレンダーテーブルの必要な日付範囲を計算します。開始日と終了日のほか、第1週の開始日と最終週の終了日も求められます。DateTableCTE:CalendarTableの結果を元に、各週の日付一覧を生成します。ROW_NUMBER関数を使用して 1 から 366 の数値を生成し、その数値に対応する日付を計算します。この日付は、第1週の開始日と最終週の終了日の範囲内に存在する必要があります。- メインクエリ:
DateTableから取得した日付一覧を利用して、週ごとにカレンダーデータを集計します。WEEK関数を使用して週番号を取得し、CASE式を使って各曜日に対応する日付を選択します。最終的な結果は、週ごとにグループ化され、週番号で並び替えられます。
複雑に見えるかもしれませんが、上記の様な手順で１つずつ見ると理解できると思います。
年の四半期の開始日と終了日を表示
問題
ある年の4つの四半期の開始日と終了日を返したい。
解決案
1年には4つの四半期があるため、4行作成する必要があります。必要な数の行を作成して、日付関数を使って開始日と終了日が属する四半期を返すだけです。
現在の日付からその年の最初の日を求めます。そして、CTEを使って四半期ごとに1行ずつ、計4行を作成し、ADDDATEを使って各四半期の最終日を求めます。具体的には前の四半期の最終日の3ヶ月後、またはその四半期の最初の日から1を引いて求めます。
with recursive x (dy,cnt)
as (
select
adddate(current_date,(-dayofyear(current_date))+1) dy
,id
from t1
union all
select adddate(dy, interval 3 month ), cnt+1
from x
where cnt+1 <= 4
)
select quarter(adddate(dy,-1)) QTR
, date_add(dy, interval -3 month) Q_start
, adddate(dy,-1) Q_end
from x
order by 1;解説
まずその年の最初の日を求め、DATEADD関数や同等の関数を使ってnヶ月を繰り返し加えます。ここで、nは現在の反復回数に3をかけた値です（4回繰り返すので、3×1ヶ月、3×2ヶ月のように加えていきます）。
DYの値は、各四半期の終了日の1日後です。各四半期の終了日を得るには、DATEADD関数を使ってDYから1日を引くだけです。各四半期の開始日を求めるには、DATEADD関数を使ってDYから3ヶ月を引きます。各四半期の終了日に対してDATEPART関数を使って、開始日と終了日が属する四半期を求めます。
欠損日付を埋める
問題
指定された範囲のすべての日付（またはすべての月や週や年）に対して行を作成する必要がある。このような行セットは、要約レポートを作成するのによく使う。例えば、従業員が雇用されたすべての年のすべての月における従業員の雇用人数をカウントしたい。全従業員の雇用日を調べると、2005年から2008年に雇用されている。
解決案
ここでのポイントは、たとえ従業員が一人も雇用されなかったとしても各月の行を表示したい点です。2005年から2008年の間に従業員が雇用されなかった月もあるので、そのような月を自分で作成し、HIREDATEでEMPテーブルと外部結合する必要があります。実際のHIREDATEの日付部分を切り捨てて月の部分だけ使うので、HIREDATEが存在する場合には作成した月と一致します。
再帰CTEを使って開始日と終了日の間の各月を作成し、EMPテーブルとの外部結合を使って雇用者数を調べます。
with recursive x (start_date,end_date)
as
(
select
adddate(min(hiredate),
-dayofyear(min(hiredate))+1) start_date
,adddate(max(hiredate),
-dayofyear(max(hiredate))+1) end_date
from emp
union all
select date_add(start_date,interval 1 month)
, end_date
from x
where date_add(start_date, interval 1 month) < end_date
)
select x.start_date mth, count(e.hiredate) num_hired
from x left join emp e
on (extract(year_month from start_date)
=
extract(year_month from e.hiredate))
group by x.start_date
order by 1;解説
よくあるパターンなので解説は不要かとも思いますが、まずは、集約関数MINとMAXをDAYOFYEAR関数とADDDATE関数と共に使って、境界の日付を求めます。
次に、CTEでMAX_HDをその年の最終月までインクリメントします。
これで最終的な結果セットに必要なすべての月が得られたので、EMP.HIREDATEと外部結合し、EMP.HIREDATEに集約関数COUNTを使って各月の雇用者数をカウントします。
特定の単位時間を探す
問題
指定された月、曜日、またはその他の単位時間と一致する日付を探したい。例えば、2月か12月に雇用されたすべての従業員と、火曜日に雇用された従業員を探したい。
解決案
日付の月名と曜日名を検索します。この問題は簡単な割にさまざまな場面で使用し汎用性が高いです。
例えば、HIREDATEを検索したいけれども、月を抽出して年を無視したい場合を例にします。
この問題に対する解決案では、月名と曜日名で検索しています。組込みの日付フォーマット関数を利用すれば、様々な組み合わせで検索するように簡単に変更できます。
select ename
from emp
where monthname(hiredate) in ('February','December')
or dayname(hiredate) = 'Tuesday';解説
解説する内容はほとんどありません。単純に関数の使用方法をドキュメントを確認するだけで解決できます。
日付の特定の部分を使ってレコードを比較
問題
同じ月の同じ曜日に雇用された従業員を知りたい。例えば、ある従業員が2006年12月3日日曜日に雇用され、別の従業員が2007年12月9日日曜日に雇用されている場合、曜日と月が一致しているので、この2人を該当者として取り上げたい。EMPテーブルでは、3人の従業員だけがこの要件を満たす。
解決案
この問題のポイントは自己結合を上手く使うことです。ある従業員のHIREDATEと別の従業員のHIREDATEを比較したいので、EMPテーブルを自己結合する必要があります。すると、HIREDATEのあらゆる組み合わせを比較できます。そして、HIREDATEから曜日と月を取り出して比較するだけです。
EMPテーブルを自己結合した後、DATE_FORMAT関数を使って、比較のためにHIREDATEを曜日と月にフォーマットします。
select concat(a.ename,
' と、同月同平日に採用 ',
b.ename) msg
from emp a, emp b
where date_format(a.hiredate,'%w%M') =
date_format(b.hiredate,'%w%M')
and a.empno < b.empno
order by a.ename;解説
まず、各従業員が他の従業員のHIREDATEにアクセスできるようにEMPを自己結合します。
EMPテーブルを自己結合すると、SCOTTのHIREDATEと他の全従業員のHIREDATEを比較できます。EMPNOでのフィルタリングは、SCOTTのHIREDATEがOTHER_HDSの1つとして返されないようにするためです。次に、日付フォーマット関数を使ってHIREDATEの曜日と月を比較し、一致したものだけを残します。
この時点で、HIREDATEは正しくマッチしていますが、結果セットにはこのレシピの「問題」で示した3行ではなく、6行が返されています。余計な行がある原因は、EMPNOのフィルタリングです。「等しくない」という条件でフィルタリングしているため、逆をフィルタリングできません。
重複を取り除くには、「<」を使います。
最後に、この結果を連結して文字列を作ります。
重複する日付範囲を特定
新しいテーブルを作りましょう。次のSQLを実行して下さい。
emp_project と言うテーブルを作ります。
CREATE TABLE `emp_project` (`empno` INT NOT NULL , `ename` VARCHAR(20) NOT NULL , `proj_id` INT NOT NULL , `proj_start` DATE NOT NULL , `proj_end` DATE NOT NULL )) ;
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7782, 'CLARK', 1, '2020-06-16', '2020-06-18');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7782, 'CLARK', 4, '2020-06-19', '2020-06-24');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7782, 'CLARK', 7, '2020-06-22', '2020-06-25');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7782, 'CLARK', 10, '2020-06-25', '2020-06-28');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7782, 'CLARK', 13, '2020-06-28', '2020-07-02');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7839, 'KING', 2, '2020-06-17', '2020-06-21');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7839, 'KING', 8, '2020-06-23', '2020-06-25');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7839, 'KING', 14, '2020-06-29', '2020-06-30');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7839, 'KING', 11, '2020-06-26', '2020-06-27');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7839, 'KING', 5, '2020-06-20', '2020-06-24');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7934, 'MILLER', 3, '2020-06-18', '2020-06-22');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7934, 'MILLER', 12, '2020-06-27', '2020-06-28');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7934, 'MILLER', 15, '2020-06-30', '2020-07-03');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7934, 'MILLER', 9, '2020-06-24', '2020-06-27');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7934, 'MILLER', 6, '2020-06-21', '2020-06-23');
問題
既存のプロジェクトが終了する前に新たなプロジェクトを開始したすべての従業員を見つけたい。
解決案
この問題も自己結合を利用することで簡単に解けます。
ここでのポイントは、新たなプロジェクトの開始日PROJ_STARTが、別のプロジェクトのPROJ_STARTの日付以降で、その別のプロジェクトの終了日PROJ_ENDの日付以前である行を見つけることです。
まずは、プロジェクトを同じ従業員による他のプロジェクトと比較する必要があります。従業員でEMP_PROJECTを自己結合し、従業員ごとに2つのプロジェクトの考えられるすべての組み合わせを作成します。
あるプロジェクトの開始日が、同じ従業員の別のプロジェクトの開始日と終了日の間にあれば、重複していることになります。
select a.empno,a.ename,
concat('プロジェクト ',b.proj_id,
' 重複しているプロジェクト ',a.proj_id) as msg
from emp_project a,
emp_project b
where a.empno = b.empno
and b.proj_start >= a.proj_start
and b.proj_start <= a.proj_end
and a.proj_id != b.proj_id;解説
解説で書いた通りですが、自己結合し、新たなプロジェクトの開始日PROJ_STARTが、別のプロジェクトのPROJ_STARTの日付以降で、その別のプロジェクトの終了日PROJ_ENDの日付以前である行を見つけることです。
この問題は予約管理システムや、生産計画システムなど幅広く利用できます。簡単な割に応用範囲が広いので、是非覚えて下さい。
まとめ
日付や時間の操作は、データベースを問い合わせる人に共通する問題です。
一連の出来事が日付や時刻と共に格納されていると、ユーザは日付に基づいた独創的な質問をするようになります。
その一方、日付はSQLでベンダ間の標準化が遅れている部分の1つでもあります。今回はMySQLをベースに解説してきましたが、基本的には組込関数を変更して、引数をそろえてあげれば動作します。
データ分析の分野ではPythonの人気が非常に高まっていますが、PythonよりもSQLの方がパフォーマンスが高い事が多いです。
今後、SQLでのデータ分析なども記事に書いていきます。皆様も是非SQLをご活用ください！
