Oracle SQLで日付が欠ける理由—補完の落とし穴と対策

日次でアクセスログの集計をしたいのに、「なぜか一部の日付が出てこない」場合があります。

特にKPIを日単位で可視化する場面では、集計結果に“ゼロ件の日”が含まれないと、レポートやグラフの整合性に支障が出てしまいます。

この記事では、Oracle SQLにおける“日付の飛び”の原因と、その補完方法について実践的に解説します。

Oracle SQLで日付が飛ぶ理由を知る

日付ごとの件数を数えたいのに、なぜか「存在している日付しか出てこない」──。

SQLでGROUP BYを使っても、そもそも元データに存在しない日付は当然集計できません。つまり「ゼロ件」だった日付は最初から結果に含まれないわけです。

この現象は、たとえばKPIを日単位で追う場面でかなり厄介です。グラフにしても日付軸がガタガタになりますし、報告資料でも「数字がない＝0なのか未集計なのか」がわかりません。

このあたりを明示的に補うには、少しの工夫が必要になります。

GROUP BYだけでは日付が欠ける理由

SQLでは、存在する行からしか集計を行いません。

なので、GROUP BYで日付を指定しても、その日付がなければ当然結果にも出てきません。例えば：

SELECT
  TRUNC(created_at) AS date,
  COUNT(*) AS total
FROM
  access_log
GROUP BY
  TRUNC(created_at)
ORDER BY
  date;

-- access_logにデータが無い日は検索結果に出てこない・・

このようなSQLは、一見正しく動いているようでいて、実は「アクセスのなかった日」は結果に出てこないんですよね。

これを「データが欠損している」と気づけるかどうかが最初のポイントです。

TRUNCの使い忘れが原因になることも

日付が思ったようにまとまらない原因の一つが、TRUNC関数の使い忘れです。

日時（TIMESTAMP）で保存されているカラムをそのままGROUP BYすると、時分秒まで含まれた値ごとに分割されてしまいます。

-- これは日単位でまとまらないNG例
GROUP BY created_at

-- 日付単位でまとめたい場合はこう
GROUP BY TRUNC(created_at)

日付の粒度に揃える、という基本ですが、意外と見落としやすいポイントです。

特に「結果がなんだか多すぎる」「件数が分散している」と感じたら、まずここを疑うと良いです。

日付を補完するためのアプローチ

データが存在しない日付を“作る”には、まず「日付の一覧」をどこかから作り出さないといけません。その上でLEFT JOINを使って結合するのが王道です。

カレンダーテーブルとLEFT JOINの基本形

カレンダーテーブルは、あらかじめ用意された日付一覧テーブルです。自作してもいいですし、サブクエリで代用する方法もあります。

-- カレンダー的なテーブルとの結合
SELECT
  c.date,
  COUNT(a.id) AS total
FROM
  calendar c
LEFT JOIN
  access_log a
ON
  TRUNC(a.created_at) = c.date
GROUP BY
  c.date
ORDER BY
  c.date

calendar には1日ごとの日付が並んでいて、そこにログを結合しています。LEFT JOINなので、ログが存在しない日でも行自体は残るので、日付の抜けは”ほぼ”解消できます。

ただし、業務利用をしないのであればこのためだけにカレンダーテーブルを作る必要があることと、カレンダーテーブルを継続メンテナンスする必要があるのがデメリットとなります。

補完目的でしか使わない日付データのためにテーブルを永続化すると、誰が何のために作ったのか分かりづらくなり、保守や引き継ぎの際に混乱を招く原因にもなりますので、注意したいところです。

CONNECT BY LEVELで日付を生成する方法

カレンダーテーブルのような固定テーブルが用意できない場合は、SQLだけで日付を作り出す方法もあります。CONNECT BY LEVELを使えば、動的に連番を生成して日付に変換できます。

SELECT
  TO_DATE('2024-01-01', 'YYYY-MM-DD') + LEVEL - 1 AS days
FROM
  dual
CONNECT BY
  LEVEL <= 31;

これで「2024年1月の全日付」が取得できます。必要に応じてこのサブクエリをWITH句にして使えば、固定テーブルがなくても補完処理ができます。

実務でどう活用するか

JOIN先のテーブルにこの日付一覧を組み合わせれば、連続した日付軸での集計が可能になります。グラフやレポートの土台としては、かなり重宝しますし、特に月次や週次の欠損に敏感なKPI集計では、こうした“補完用ベース”が不可欠な場合があります。

実際に使った日付補完SQLの実例

実務で使った例の一つとして、月単位でアクセスログを日ごとに集計するSQLでした。

特に注意したのは「日付範囲の正確な指定」と「集計ゼロの日を落とさない」こと。以下は実際に使った形に近い構文です。

1ヶ月分の日付に対して件数を集計するSQL

まずは、対象月の日付を疑似的に生成し、それをベースにLEFT JOINして件数を出します。

WITH calendar AS (
  SELECT
    TO_DATE('2024-01-01', 'YYYY-MM-DD') + LEVEL - 1 AS days
  FROM
    dual
  CONNECT BY
    LEVEL <= 31
)
SELECT
  c.days,
  COUNT(a.id) AS total
FROM
  calendar c
LEFT JOIN
  access_log a
ON
  TRUNC(a.created_at) = c.days
GROUP BY
  c.days
ORDER BY
  c.days;

この構成で、ログがなくても日付行は出てくるようになります。WITH calendar AS (...) で一時的なカレンダーテーブルを作っておくと、SQLの見通しも良くなります。

また、TRUNCで時間を切り捨てる処理や、TO_DATEで起点日を明示することも重要です。曖昧な日付比較は意外とバグの温床になりやすいので、なるべく具体的に定義しておくと安心です。

試行錯誤でわかった落とし穴とコツ

最初は「LEFT JOINにしたし大丈夫！」と思っていたのに、なぜか集計結果がずれる。よくあるのがJOIN後にWHERE句を書いてしまうパターンです。これ、LEFT JOINの意味を台無しにしてしまいます。

「集計結果がズレる」パターン

LEFT JOINして0件の日付も出したかったのに、WHEREで絞り込みをかけるとINNER JOINと同じ挙動になってしまいます。

-- NG：LEFT JOINの後にWHEREでnullを除外してしまう
...
LEFT JOIN access_log a ON ...
WHERE a.status = 'success'

この書き方だと、a.status がNULLの行が落ちてしまうため、意図した結果にならないことがあります。

この対策としては、WHERE ではなく JOIN条件 の中にフィルタを書くようにします。あるいは CASE WHEN を使って条件分岐して集計するのもアリです。

-- OK例：JOIN条件にフィルタを入れる
LEFT JOIN access_log a
  ON TRUNC(a.created_at) = c.date
 AND a.status = 'success'

この方が、0件の行もちゃんと出てきます。

まとめ：Oracleの癖を押さえれば日付補完は怖くない

Oracle SQLでは、日付の扱いにちょっとした“癖”があります。ただ、それを押さえてさえいれば、補完や集計もそれほど難しくはありません。

特にTRUNCやTO_DATE、そしてLEFT JOINの使いどころをきちんと理解するだけで、KPI集計の精度がぐっと上がります。

最初は戸惑うかもしれませんが、一度流れを掴んでしまえば応用もききます。私自身、カレンダーテーブルの考え方を知ってから、SQLを書く時の視点が少し変わりました。

毎日のログを「どう見せたいか」を考えながら、丁寧に組み立てていくのが大事なんだなと感じています。